public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dimitrios Apostolou <[email protected]>
To: [email protected]
Subject: ALTER TABLE ADD FOREIGN KEY to partitioned table, is not parallelized
Date: Tue, 3 Jun 2025 17:34:12 +0200 (CEST)
Message-ID: <[email protected]> (raw)

Hello list,

I'm debugging the abysmal performance of pg_restoring a huge 
(10TB) database, which includes a table with more than 1000 partitions.

As part of pg_restore -j... --section=post-data
I see *days* being spent in the sequential creation of foreign keys:

   ALTER TABLE the_master_partitioned_table
   ADD CONSTRAINT ...
   FOREIGN KEY (columnX) REFERENCES another_table(columnX)

Each of the above queries take 1-2 days, and multiple such queries are 
issued sequentially.

My questions:

+ This master_partitioned_table contains at least 5 foreign key
   constraints from different columns to different tables.
   Despite pg_restore being invoked with -j, they all run sequentially.
   I guess pg_restore dependency resolution decides so. Is there a
   reason for that?

+ The backend process for each of the above ALTER TABLE commands, does not
   parallelize the foreign key checks for the different partitions. I
   know, because in the logs I see gigabytes of temporary files being
   written, with the CONTEXT showing queries issued incrementally on
   all the different partitions:

   :LOG:      temporary file: path "pg_tblspc/16390/PG_17_202406281/pgsql_tmp/pgsql_tmp3363462.579", size 1073741824
   :CONTEXT:  SQL statement "SELECT fk."columnX" FROM ONLY "public"."table_partition_214" fk
              LEFT OUTER JOIN ONLY "public"."another_table" pk
                  ON ( pk."columnX" OPERATOR(pg_catalog.=) fk."columnX")
              WHERE pk."columnX" IS NULL AND (fk."columnX" IS NOT NULL)"

   Why can't the backend issue these queries in parallel workers?

+ Based on the pg_restore manual, I am experimenting with manually issuing
   DISABLE TRIGGERS before the restoration, but I can't see a difference
   when I'm doing the section=post-data separately. Is it supposed to speed
   things up?


Thanks in advance,
Dimitris

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: ALTER TABLE ADD FOREIGN KEY to partitioned table, is not parallelized
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox