From: Laurenz Albe <laurenz.albe@cybertec.at>
To: peter plachta <pplachta@gmail.com>
To: pgsql-performa. <pgsql-performance@postgresql.org>
Subject: Re: Table copy with SERIALIZABLE is incredibly slow
Date: Mon, 31 Jul 2023 08:30:39 +0200
Message-ID: <b8e6ebbdf1f89cefe06577f9e864e132ff00a99c.camel@cybertec.at> (raw)
In-Reply-To: <CAGTqnmYptofgKW6X+MhuA1RiFQPX0CbCPcE4B38rC-62S8Kc1w@mail.gmail.com>
References: <CAGTqnmYptofgKW6X+MhuA1RiFQPX0CbCPcE4B38rC-62S8Kc1w@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, pplachta@gmail.com
Subject: Re: Table copy with SERIALIZABLE is incredibly slow
In-Reply-To: <b8e6ebbdf1f89cefe06577f9e864e132ff00a99c.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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