From: Antonin Houska <ah@cybertec.at>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
cc: Pavel Stehule <pavel.stehule@gmail.com>,
    Michael Paquier <michael@paquier.xyz>,
    PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: why there is not VACUUM FULL CONCURRENTLY?
In-reply-to: <202401301031.7viyzjhkmal4@alvherre.pgsql>
References: <202401301031.7viyzjhkmal4@alvherre.pgsql>
Comments: In-reply-to Alvaro Herrera <alvherre@alvh.no-ip.org>
   message dated "Tue, 30 Jan 2024 11:31:17 +0100."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <5185.1706694913.1@antos>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 31 Jan 2024 10:55:13 +0100
Message-ID: <5186.1706694913@antos>
Archived-At: <https://www.postgresql.org/message-id/5186.1706694913%40antos>
Precedence: bulk

Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

> On 2024-Jan-30, Pavel Stehule wrote:
> =

> > One of my customer today is reducing one table from 140GB to 20GB.  No=
w he
> > is able to run archiving. He should play with pg_repack, and it is wor=
king
> > well today, but I ask myself, what pg_repack does not be hard to do
> > internally because it should be done for REINDEX CONCURRENTLY. This is=
 not
> > a common task, and not will be, but on the other hand, it can be nice =
to
> > have feature, and maybe not too hard to implement today. But I didn't =
try it
> =

> FWIW a newer, more modern and more trustworthy alternative to pg_repack
> is pg_squeeze, which I discovered almost by random chance, and soon
> discovered I liked it much more.
> =

> So thinking about your question, I think it might be possible to
> integrate a tool that works like pg_squeeze, such that it runs when
> VACUUM is invoked -- either under some new option, or just replace the
> code under FULL, not sure.  If the Cybertec people allows it, we could
> just grab the pg_squeeze code and add it to the things that VACUUM can
> run.

There are no objections from Cybertec. Nevertheless, I don't expect much c=
ode
to be just copy & pasted. If I started to implement the extension today, I=
'd
do some things in a different way. (Some things might actually be simpler =
in
the core, i.e. a few small changes in PG core are easier than the related
workarounds in the extension.)

The core idea is that: 1) a "historic snapshot" is used to get the current
contents of the table, 2) logical decoding is used to capture the changes =
done
while the data is being copied to new storage, 3) the exclusive lock on th=
e
table is only taken for very short time, to swap the storage (relfilenode)=
 of
the table.

I think it should be coded in a way that allows use by VACUUM FULL, CLUSTE=
R,
and possibly some subcommands of ALTER TABLE. For example, some users of
pg_squeeze requested an enhancement that allows the user to change column =
data
type w/o service disruption (typically when it appears that integer type i=
s
going to overflow and change bigint is needed).

Online (re)partitioning could be another use case, although I admit that
commands that change the system catalog are a bit harder to implement than
VACUUM FULL / CLUSTER.

One thing that pg_squeeze does not handle is visibility: it uses heap_inse=
rt()
to insert the tuples into the new storage, so the problems described in [1=
]
can appear. The in-core implementation should rather do something like tup=
le
rewriting (rewriteheap.c).

Is your plan to work on it soon or should I try to write a draft patch? (I
assume this is for PG >=3D 18.)

[1] https://www.postgresql.org/docs/current/mvcc-caveats.html

-- =

Antonin Houska
Web: https://www.cybertec-postgresql.com