pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance issues during pg_restore -j with big partitioned table
12+ messages / 3 participants
[nested] [flat]
* Performance issues during pg_restore -j with big partitioned table
@ 2025-04-02 17:32 Dimitrios Apostolou <jimis@gmx.net>
2025-04-02 17:39 ` Re: Performance issues during pg_restore -j with big partitioned table Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-04 13:13 ` Re: Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 16:45 ` Re: Performance issues during pg_restore -j with big partitioned table Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: Dimitrios Apostolou @ 2025-04-02 17:32 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Performance issues during pg_restore -j with big partitioned table
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
@ 2025-04-02 17:39 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-02 17:45 ` Re: Performance issues during pg_restore -j with big partitioned table Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-02 17:46 ` Re: Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2 siblings, 2 replies; 12+ messages in thread
From: Adrian Klaver @ 2025-04-02 17:39 UTC (permalink / raw)
To: Dimitrios Apostolou <jimis@gmx.net>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Performance issues during pg_restore -j with big partitioned table
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-02 17:39 ` Re: Performance issues during pg_restore -j with big partitioned table Adrian Klaver <adrian.klaver@aklaver.com>
@ 2025-04-02 17:45 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-02 17:51 ` Re: Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
1 sibling, 1 reply; 12+ messages in thread
From: Adrian Klaver @ 2025-04-02 17:45 UTC (permalink / raw)
To: Dimitrios Apostolou <jimis@gmx.net>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Performance issues during pg_restore -j with big partitioned table
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-02 17:39 ` Re: Performance issues during pg_restore -j with big partitioned table Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-02 17:45 ` Re: Performance issues during pg_restore -j with big partitioned table Adrian Klaver <adrian.klaver@aklaver.com>
@ 2025-04-02 17:51 ` Dimitrios Apostolou <jimis@gmx.net>
0 siblings, 0 replies; 12+ messages in thread
From: Dimitrios Apostolou @ 2025-04-02 17:51 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Performance issues during pg_restore -j with big partitioned table
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-02 17:39 ` Re: Performance issues during pg_restore -j with big partitioned table Adrian Klaver <adrian.klaver@aklaver.com>
@ 2025-04-02 17:46 ` Dimitrios Apostolou <jimis@gmx.net>
1 sibling, 0 replies; 12+ messages in thread
From: Dimitrios Apostolou @ 2025-04-02 17:46 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Performance issues during pg_restore -j with big partitioned table
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
@ 2025-04-04 13:13 ` Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 15:02 ` Re: Performance issues during pg_restore -j with big partitioned table Adrian Klaver <adrian.klaver@aklaver.com>
2025-06-17 16:30 ` Re: Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2 siblings, 2 replies; 12+ messages in thread
From: Dimitrios Apostolou @ 2025-04-04 13:13 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Performance issues during pg_restore -j with big partitioned table
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 13:13 ` Re: Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
@ 2025-04-04 15:02 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-04 15:09 ` Re: Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
1 sibling, 1 reply; 12+ messages in thread
From: Adrian Klaver @ 2025-04-04 15:02 UTC (permalink / raw)
To: Dimitrios Apostolou <jimis@gmx.net>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Performance issues during pg_restore -j with big partitioned table
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 13:13 ` Re: Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 15:02 ` Re: Performance issues during pg_restore -j with big partitioned table Adrian Klaver <adrian.klaver@aklaver.com>
@ 2025-04-04 15:09 ` Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 15:23 ` Re: Performance issues during pg_restore -j with big partitioned table Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 12+ messages in thread
From: Dimitrios Apostolou @ 2025-04-04 15:09 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Performance issues during pg_restore -j with big partitioned table
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 13:13 ` Re: Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 15:02 ` Re: Performance issues during pg_restore -j with big partitioned table Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-04 15:09 ` Re: Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
@ 2025-04-04 15:23 ` Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 12+ messages in thread
From: Adrian Klaver @ 2025-04-04 15:23 UTC (permalink / raw)
To: Dimitrios Apostolou <jimis@gmx.net>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Performance issues during pg_restore -j with big partitioned table
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 13:13 ` Re: Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
@ 2025-06-17 16:30 ` Dimitrios Apostolou <jimis@gmx.net>
1 sibling, 0 replies; 12+ messages in thread
From: Dimitrios Apostolou @ 2025-06-17 16:30 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
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
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Performance issues during pg_restore -j with big partitioned table
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
@ 2025-04-04 16:45 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-04-04 17:09 ` Re: Performance issues during pg_restore -j with big partitioned table Adrian Klaver <adrian.klaver@aklaver.com>
2 siblings, 1 reply; 12+ messages in thread
From: Ron Johnson @ 2025-04-04 16:45 UTC (permalink / raw)
To: pgsql-general
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!
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Performance issues during pg_restore -j with big partitioned table
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 16:45 ` Re: Performance issues during pg_restore -j with big partitioned table Ron Johnson <ronljohnsonjr@gmail.com>
@ 2025-04-04 17:09 ` Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 12+ messages in thread
From: Adrian Klaver @ 2025-04-04 17:09 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2025-06-17 16:30 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-02 17:32 Performance issues during pg_restore -j with big partitioned table Dimitrios Apostolou <jimis@gmx.net>
2025-04-02 17:39 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-02 17:45 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-02 17:51 ` Dimitrios Apostolou <jimis@gmx.net>
2025-04-02 17:46 ` Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 13:13 ` Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 15:02 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-04 15:09 ` Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 15:23 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-06-17 16:30 ` Dimitrios Apostolou <jimis@gmx.net>
2025-04-04 16:45 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-04-04 17:09 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox