pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTable copy with SERIALIZABLE is incredibly slow
2+ messages / 2 participants
[nested] [flat]
* Table copy with SERIALIZABLE is incredibly slow
@ 2023-07-31 05:00 peter plachta <pplachta@gmail.com>
2023-07-31 06:30 ` Re: Table copy with SERIALIZABLE is incredibly slow Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 2+ messages in thread
From: peter plachta @ 2023-07-31 05:00 UTC (permalink / raw)
To: pgsql-performance
Hi all
Background is we're trying a pg_repack-like functionality to compact a
500Gb/145Gb index (x2) table from which we deleted 80% rows. Offline is not
an option. The table has a moderate (let's say 100QPS) I/D workload running.
The typical procedure for this type of thing is basically CDC:
1. create 'log' table/create trigger
2. under SERIALIZABLE: select * from current_table insert into new_table
What we're finding is that for the 1st 30 mins the rate is 10Gb/s, then it
drops to 1Mb/s and stays there.... and 22 hours later the copy is still
going and now the log table is huge so we know the replay will also take a
very long time.
===
Q: what are some ways in which we could optimize the copy?
Btw this is Postgres 9.6
(we tried unlogged table (that did nothing), we tried creating indexes
after (that helped), we're experimenting with RRI)
Thanks!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Table copy with SERIALIZABLE is incredibly slow
2023-07-31 05:00 Table copy with SERIALIZABLE is incredibly slow peter plachta <pplachta@gmail.com>
@ 2023-07-31 06:30 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2023-07-31 06:30 UTC (permalink / raw)
To: peter plachta <pplachta@gmail.com>; pgsql-performance
On Sun, 2023-07-30 at 23:00 -0600, peter plachta wrote:
> Background is we're trying a pg_repack-like functionality to compact a 500Gb/145Gb
> index (x2) table from which we deleted 80% rows. Offline is not an option. The table
> has a moderate (let's say 100QPS) I/D workload running.
>
> The typical procedure for this type of thing is basically CDC:
>
> 1. create 'log' table/create trigger
> 2. under SERIALIZABLE: select * from current_table insert into new_table
>
> What we're finding is that for the 1st 30 mins the rate is 10Gb/s, then it drops to
> 1Mb/s and stays there.... and 22 hours later the copy is still going and now the log
> table is huge so we know the replay will also take a very long time.
>
> ===
>
> Q: what are some ways in which we could optimize the copy?
>
> Btw this is Postgres 9.6
>
> (we tried unlogged table (that did nothing), we tried creating indexes after
> (that helped), we're experimenting with RRI)
Why are you doing this the hard way, when pg_squeeze or pg_repack could do it?
You definitely should not be using PostgreSQL 9.6 at this time.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-07-31 06:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-31 05:00 Table copy with SERIALIZABLE is incredibly slow peter plachta <pplachta@gmail.com>
2023-07-31 06:30 ` Laurenz Albe <laurenz.albe@cybertec.at>
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