Message-ID: <b8e6ebbdf1f89cefe06577f9e864e132ff00a99c.camel@cybertec.at>
Subject: Re: Table copy with SERIALIZABLE is incredibly slow
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: peter plachta <pplachta@gmail.com>, "pgsql-performa."
	 <pgsql-performance@postgresql.org>
Date: Mon, 31 Jul 2023 08:30:39 +0200
In-Reply-To: 
 <CAGTqnmYptofgKW6X+MhuA1RiFQPX0CbCPcE4B38rC-62S8Kc1w@mail.gmail.com>
References: 
	<CAGTqnmYptofgKW6X+MhuA1RiFQPX0CbCPcE4B38rC-62S8Kc1w@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/b8e6ebbdf1f89cefe06577f9e864e132ff00a99c.camel%40cybertec.at>
Precedence: bulk

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 50=
0Gb/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.
>=20
> The typical procedure for this type of thing is basically CDC:
>=20
> 1. create 'log' table/create trigger
> 2. under SERIALIZABLE: select=C2=A0* from current_table insert into new_t=
able
>=20
> What we're finding is that for the 1st 30 mins the rate is 10Gb/s, then i=
t 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.
>=20
> =3D=3D=3D
>=20
> Q: what are some ways in which we could optimize the copy?
>=20
> Btw this is Postgres 9.6
>=20
> (we tried unlogged table (that did nothing), we tried creating indexes af=
ter
> (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