Table copy with SERIALIZABLE is incredibly slow

From: peter plachta <pplachta(at)gmail(dot)com>
To: "pgsql-performa(dot)" <pgsql-performance(at)postgresql(dot)org>
Subject: Table copy with SERIALIZABLE is incredibly slow
Date: 2023-07-31 05:00:15
Message-ID: CAGTqnmYptofgKW6X+MhuA1RiFQPX0CbCPcE4B38rC-62S8Kc1w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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!

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Laurenz Albe 2023-07-31 06:30:39 Re: Table copy with SERIALIZABLE is incredibly slow
Previous Message peter plachta 2023-07-31 04:48:06 Results of experiments with UUIDv7, UUIDv8