Horton

Thread

Commits

  1. Elide not-null constraint checks on child tables during PK creation

  1. Performance issues during pg_restore -j with big partitioned table

    Dimitrios Apostolou <jimis@gmx.net> — 2025-04-02T17:32:31Z

    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 --no-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

  2. Re: Performance issues during pg_restore -j with big partitioned table

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-04-02T17:39:36Z

    
On 4/2/25 10:32 AM, Dimitrios Apostolou wrote:
> 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 
> --no-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.

--clean will drop the object entirely not TRUNCATE.

I'm guessing that this is being done by you per:

https://www.postgresql.org/message-id/53760c70-4a87-a453-9e02-57abc9cb2e54%40gmx.net

"After each failed attempt, I need to issue a TRUNCATE table1,table2,...
before I try again. "

> 

> 
> Thanks in advance,
> Dimitris
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  3. Re: Performance issues during pg_restore -j with big partitioned table

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-04-02T17:45:29Z

    
On 4/2/25 10:39 AM, Adrian Klaver wrote:
> 

> --clean will drop the object entirely not TRUNCATE.
> 
> I'm guessing that this is being done by you per:
> 
> https://www.postgresql.org/message-id/53760c70-4a87-a453-9e02-57abc9cb2e54%40gmx.net
> 
> "After each failed attempt, I need to issue a TRUNCATE table1,table2,...
> before I try again. "

Oops, forgot to engage brain.

 From pg_backup_archiver.c:

* In parallel restore, if we created the table earlier in
* this run (so that we know it is empty) and we are not
* restoring a load-via-partition-root data item then we
* wrap the COPY in a transaction and precede it with a
* TRUNCATE.  If wal_level is set to minimal this prevents
* WAL-logging the COPY.  This obtains a speedup similar
* to that from using single_txn mode in non-parallel
* restores.
*
* We mustn't do this for load-via-partition-root cases
* because some data might get moved across partition
* boundaries, risking deadlock and/or loss of previously
* loaded data.  (We assume that all partitions of a
* partitioned table will be treated the same way.)

> 
>>
> 
>>
>> Thanks in advance,
>> Dimitris
>>
>>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  4. Re: Performance issues during pg_restore -j with big partitioned table

    Dimitrios Apostolou <jimis@gmx.net> — 2025-04-02T17:46:43Z

    On Wed, 2 Apr 2025, Adrian Klaver wrote:

>
>
> On 4/2/25 10:32 AM, Dimitrios Apostolou wrote:
>>  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
>>  --no-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.
>
> --clean will drop the object entirely not TRUNCATE.
>
> I'm guessing that this is being done by you per:
>
> https://www.postgresql.org/message-id/53760c70-4a87-a453-9e02-57abc9cb2e54%40gmx.net
>
> "After each failed attempt, I need to issue a TRUNCATE table1,table2,...
> before I try again. "

Thanks Adrian. I'm now testing restore without --data-only. All I'm doing
prior to the above pg_restore command is "createdb -T template0 newdb".
It's possible though that I'm missing something here, the whole thing is
way more complicated than I expected...


Dimitris

  5. Re: Performance issues during pg_restore -j with big partitioned table

    Dimitrios Apostolou <jimis@gmx.net> — 2025-04-02T17:51:45Z

    On Wed, 2 Apr 2025, Adrian Klaver wrote:
>
>
> On 4/2/25 10:39 AM, Adrian Klaver wrote:
>>
>
>>  --clean will drop the object entirely not TRUNCATE.
>>
>>  I'm guessing that this is being done by you per:
>>
>>  https://www.postgresql.org/message-id/53760c70-4a87-a453-9e02-57abc9cb2e54%40gmx.net
>>
>>  "After each failed attempt, I need to issue a TRUNCATE table1,table2,...
>>  before I try again. "
>
> Oops, forgot to engage brain.
>
> From pg_backup_archiver.c:
>
> * In parallel restore, if we created the table earlier in
> * this run (so that we know it is empty) and we are not
> * restoring a load-via-partition-root data item then we
> * wrap the COPY in a transaction and precede it with a
> * TRUNCATE.  If wal_level is set to minimal this prevents
> * WAL-logging the COPY.  This obtains a speedup similar
> * to that from using single_txn mode in non-parallel
> * restores.

This makes sense. It creates the table earlier, and then truncates it just
before copying data into it. I wonder if this really circumvents the WAL
since I don't have --single-transaction  (incompatible to -j).


Dimitris

  6. Re: Performance issues during pg_restore -j with big partitioned table

    Dimitrios Apostolou <jimis@gmx.net> — 2025-04-04T13:13:09Z

    On Wed, 2 Apr 2025, Dimitrios Apostolou wrote:

> Hello list.
>
> My database includes one table with 1000 partitions, all of them rather

I was not clear here: my database dump has all that, and the database is
brand new and empty.

> sizeable. I run:
>
>  pg_restore -j12 --no-tablespaces --disable-triggers --exit-on-error
> --no-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?

Trying again, pg_restore exited with error after almost 24h:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 8904; 2606 16529 CONSTRAINT test_runs_raw test_runs_raw_partitioned_pkey
pg_restore: error: could not execute query: ERROR:  deadlock detected
DETAIL:  Process 465409 waits for AccessExclusiveLock on relation 44437 of database 44090; blocked by process 465408.
Process 465408 waits for AccessShareLock on relation 44383 of database 44090; blocked by process 465409.
HINT:  See server log for query details.

From the logs I see that:

+ Process 465409 waits for AccessExclusiveLock on relation 44437 of database 44090; blocked by process 465408.
   --> 44437 is test_runs_raw__part_max10120k (a single partition)
+ Process 465408 waits for AccessShareLock     on relation 44383 of database 44090; blocked by process 465409.
   --> 44383 is test_runs_raw  (the master partitioned table)

Process 465409:
   ALTER TABLE ONLY public.test_runs_raw
     ADD CONSTRAINT test_runs_raw_partitioned_pkey PRIMARY KEY (workitem_n, run_n);

Process 465408:
   COPY public.test_runs_raw__part_max10120k(...) FROM stdin;


Bug? This happened on a postgres compiled from last week's master branch.
The dump I'm trying to restore is from postgres 17.4.


Thanks
Dimitris

  7. Re: Performance issues during pg_restore -j with big partitioned table

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-04-04T15:02:43Z

    On 4/4/25 06:13, Dimitrios Apostolou wrote:
> On Wed, 2 Apr 2025, Dimitrios Apostolou wrote:
> 

> Bug? This happened on a postgres compiled from last week's master branch.

Are you talking about the dev version?

> The dump I'm trying to restore is from postgres 17.4.
> 
> 
> Thanks
> Dimitris
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  8. Re: Performance issues during pg_restore -j with big partitioned table

    Dimitrios Apostolou <jimis@gmx.net> — 2025-04-04T15:09:14Z

    On Fri, 4 Apr 2025, Adrian Klaver wrote:

> On 4/4/25 06:13, Dimitrios Apostolou wrote:
>>  On Wed, 2 Apr 2025, Dimitrios Apostolou wrote:
>>
>
>>  Bug? This happened on a postgres compiled from last week's master branch.
>
> Are you talking about the dev version?

In this thread, yes. My mistake I didn't mention it from the start. I
compiled it in order to see if the problems I mentioned in my previous
thread were fixed.


Dimitris

  9. Re: Performance issues during pg_restore -j with big partitioned table

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-04-04T15:23:35Z

    On 4/4/25 08:09, Dimitrios Apostolou wrote:
> On Fri, 4 Apr 2025, Adrian Klaver wrote:
> 
>> On 4/4/25 06:13, Dimitrios Apostolou wrote:
>>>  On Wed, 2 Apr 2025, Dimitrios Apostolou wrote:
>>>
>>
>>>  Bug? This happened on a postgres compiled from last week's master 
>>> branch.
>>
>> Are you talking about the dev version?
> 
> In this thread, yes. My mistake I didn't mention it from the start. I
> compiled it in order to see if the problems I mentioned in my previous
> thread were fixed.

Since it still has not established that an actual problem exists in 
previous versions, this is premature. Especially as dev has not even 
reached alpha AFAIK.

To get at a solution more detailed information is needed. I would also 
suggest testing using something less the 1000 large partitions.

Information needed for any given report:

1) Postgres version being dumped from.

2) The complete pg_dump command.

3) The Postgres version being restored to.

4) The complete pg_restore command.

5) The error messages.

Also have you tried a schema only dump/restore?

> 
> 
> Dimitris
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  10. Re: Performance issues during pg_restore -j with big partitioned table

    Ron <ronljohnsonjr@gmail.com> — 2025-04-04T16:45:25Z

    On Wed, Apr 2, 2025 at 1:32 PM Dimitrios Apostolou <jimis@gmx.net> wrote:

> 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
> --no-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.
>

TRUNCATE statements inside of "toc.dat" files?  I'm skeptical.

Are you maybe doing something else in that database besides pg_restore?

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

  11. Re: Performance issues during pg_restore -j with big partitioned table

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-04-04T17:09:56Z

    
On 4/4/25 9:45 AM, Ron Johnson wrote:

> 
> TRUNCATE statements inside of "toc.dat" files?  I'm skeptical.

See my post here:

https://www.postgresql.org/message-id/7be2dcc6-3ba4-4e3f-a154-8d13d816aa9b%40aklaver.com



-- 
Adrian Klaver
adrian.klaver@aklaver.com

  12. Re: Performance issues during pg_restore -j with big partitioned table

    Dimitrios Apostolou <jimis@gmx.net> — 2025-06-17T16:30:51Z

    For the record, I haven't seen this deadlock again. I guess it was a bug 
on the master branch that got fixed, because I've been testing later 
versions a few times.

Dimitris



On Fri, 4 Apr 2025, Dimitrios Apostolou wrote:

> On Wed, 2 Apr 2025, Dimitrios Apostolou wrote:
>
>>  Hello list.
>>
>>  My database includes one table with 1000 partitions, all of them rather
>
> I was not clear here: my database dump has all that, and the database is 
> brand new and empty.
>
>>  sizeable. I run:
>>
>>  pg_restore -j12 --no-tablespaces --disable-triggers --exit-on-error
>>  --no-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?
>
> Trying again, pg_restore exited with error after almost 24h:
>
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 8904; 2606 16529 CONSTRAINT test_runs_raw 
> test_runs_raw_partitioned_pkey
> pg_restore: error: could not execute query: ERROR:  deadlock detected
> DETAIL:  Process 465409 waits for AccessExclusiveLock on relation 44437 of 
> database 44090; blocked by process 465408.
> Process 465408 waits for AccessShareLock on relation 44383 of database 44090; 
> blocked by process 465409.
> HINT:  See server log for query details.
>
> From the logs I see that:
>
> + Process 465409 waits for AccessExclusiveLock on relation 44437 of database 
> 44090; blocked by process 465408.
>  -->  44437 is test_runs_raw__part_max10120k (a single partition)
> + Process 465408 waits for AccessShareLock     on relation 44383 of database 
> 44090; blocked by process 465409.
>  -->  44383 is test_runs_raw  (the master partitioned table)
>
> Process 465409:
>   ALTER TABLE ONLY public.test_runs_raw
>     ADD CONSTRAINT test_runs_raw_partitioned_pkey PRIMARY KEY (workitem_n,
>     run_n);
>
> Process 465408:
>   COPY public.test_runs_raw__part_max10120k(...) FROM stdin;
>
>
> Bug? This happened on a postgres compiled from last week's master branch. The 
> dump I'm trying to restore is from postgres 17.4.
>
>
> Thanks
> Dimitris
>
>