Date: Wed, 2 Apr 2025 19:32:31 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: Performance issues during pg_restore -j with big partitioned table
Message-ID: <84379bfb-bbda-84e6-cacc-e863ba9d6c37@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=US-ASCII
UI-OutboundReport: notjunk:1;M01:P0:BaMygxJ1kA4=;NmYZSrT6PYzsaaOCjLpRbTNft8x
 /uMg11HEy58PBLlVtkV2/l5mjM7K2vqHg3OxL7iooMnNDp7cylVwqo3u+nnA59oD5Sxbya+XI
 JlUB2+XoyMHZdYQNe+jJ6lIOP6mQf44ea++hipLtNzsVyb615gJ28lNsuZn3mkAbMzGVHTfX4
 5iEnP+s07th8WB/SN+Vamhp3lkt1bjE/N9pM2hCcIkU8oJi0SRs6WO7KaiO8FEwCu6fQIRM/1
 kUgY0fScL3Zmqgs5ZnftKC7vhwX7EUS+zWkkcGw9KYzUEukjV+8wcA9xL0J9HDE09ZQsKImAg
 4WdKmJr2IChy0IKXyl9sdMBHZwvzwHkW/VvYi0S6jHOQhxICGJlwQXTBc77W/4/j0C95WqzyK
 SkSVs7a80T6bMSe/jAWLz5DE/vBRqVb+o/yo4iw7Ensc8rYs9NL/ozcFMyCgDJODTqLrEoSgp
 MbrXnM3E1gnpJBxdaflMynoT0e4itUokDKcUPya/01Grn9Gqf7QO7Jgz6HReR0GZBN7Ei9Im/
 STsx9aY1cdUanM4SoU6jIFphUQKMv0KC3vVcVP4vRDjdu1tl3x66ya0WoS1O/0dEWpX+RAkpJ
 tIhxAxoPIXJ5jiJnsosklgrOmYJCrjJdhN/4R0GqC/kflhaWsbqI3zdjJGFynSx7iR5OMPDno
 yO8mxPvS5jKVRcBe/Rk+3J8Nun7BSObMhQiF9UwbqKc8JkQQrj2qhaWs1ZKsbQ3VCy2UChxlO
 iV945/3z0FnK5xEoHmB8RezhS0y8omkvV/RhwxQKPbZfB7Tr4khat2m1yf7Ut0OHyI4xrMBkE
 7Is3dAjABrhSWQqbrXV6CQGZfjtAb2Z5B3GBzUEvAWumBoE9VcAbVxMzHXOgcsY2gfH4cG8+J
 jCiOqwXjjn4DHnpJMRDdO/pNSFx90WkzD6MhAIWky2+TznfwUWzk79XQrnLAtlhKnqO1i+Dq9
 julSNMMnRY9YNbEsLpdyOTaS8gMuMy2YynL/kqMdSpq/9xHtM/ebi7nUQEhMAKh+JWDOwLJQy
 eJ5AoPcLEsOZmAp+XAdurGW9IAY2lkPWiR2YfARWNRSlDl55BZaD/sMQUtgv840rK0RZ/U4eE
 9qUvHf09MG4v5jOfDLrFZ5jkqdYuiFxQcuMfYEVM5jgFerjsr/1/+1gP/cg1ZjXWIR0L/H4yy
 YPmhJAysdyjTUzlb3IhqGcvnoh0/JDe0m122masHj9bEzxJJiXWj+AU9OXaT3jVQjcgYQwVPb
 HaY5CvouqQz/0Qf79W0Z26cQKKmosYUAtOLeW7s7J+HWb3FQcPfLwUf7OgZlW12yBlBYD6Bez
 Q/6qbvi/xUCCWqAThhQ3VFTkLk+0GdKb9JA+sdZGf6I7ciMJ5g1cq8XZMJzD1AYC7Ueuloo4k
 fQt1PaqUPdObxlqd25+lAyH7DBc8B8BiU78wZ+RkO6bAxSNpAktc2R0Q2W1optqaL8zENJwZQ
 +2f8eYO9utH+1nwiK7YepT0TZfig=
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/84379bfb-bbda-84e6-cacc-e863ba9d6c37%40gmx.net>
Precedence: bulk

Hello list.

My database includes one table with 1000 partitions, all of them rather
sizeable. I run:

   pg_restore -j12 --no-tablespaces --disable-triggers --exit-on-error --n=
o-owner --no-privileges -n public -d newdb custom_format_dump.pgdump

Right now after 24h of restore, I notice weird behaviour, so I have
several questions about it:

+ 11 postgres backend processes are sleeping as "TRUNCATE TABLE waiting".
   I see that they are waiting to issue a TRUNCATE for one of the
   partitions and then COPY data to it.  Checking the log I see that
   several partitions have already been copied finished, but many more
   are left to start.

   Why is a TRUNCATE needed at the start of a partition's COPY phase? I
   didn't issue a --clean on the command line (I don't need it as my
   database is newly created), and I don't see a mention of related
   TRUNCATE in the pg_restore manual.

+ 1 postgres backend process is doing:

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

   According to my logs this started right after COPY DATA for
   another_table was finished. And apparently it has a lock on
   the_master_partitioned_table that all other TRUNCATE have to wait for.

   Is this a bug in the dependency resolution? Wouldn't it make sense for
   this to wait until all 1000 partitions have finished their COPY DATA
   phase?

+ Investigating why the above ALTER TABLE takes so long, I notice that it
   is issuing a lot of writes to the WAL.  Digging deeper shows a lot of
   time spent in SetHintBits().  Is there a way to avoid that in a clean
   pg_restore?


Thanks in advance,
Dimitris