On Thu, 30 Jan 2025 at 16:29, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> On 2025-Jan-30, Michael Banck wrote:
>
> > > I haven't addressed the problem of a new command yet - for that I'd like to
> > > see some sort of consensus, so that I do not have to do all the related
> > > changes many times.
> >
> > Well, looks like this patch-set is blocked on the bikeshedding part?
> >
> > Somebody should call a shot here, then.
>
> A bunch of people discussed this patch in today's developer meeting in
> Brussels. There's pretty much a consensus on using the verb REPACK
> CONCURRENTLY for this new command -- where unadorned REPACK would be
> VACUUM FULL, and we'd have something like REPACK WITH INDEX or maybe
> REPACK USING INDEX to take the CLUSTER place.
>
> For the record, there was an observation that [...]
Further observations:
First, due to the XLog-based change detection this feature can't work
for unlogged tables without first changing them to logged (which
implies first writing the whole table to XLog, to not cause issues on
any replicas). However, documentation for this limitation seems to be
missing from the patches, and I hope a solution can be found without
requiring LOGGED.
Second, I'm concerned about long-running snapshots: While I've not
read the patches fully, I think they work something like the
following:
1. Mark some start LSN as start for decoding changes
2. Do the usual REPACK operations, but with reduced locking
3. Apply the decoded changes
4. Switch the relfilenodes over
For (2), I think the scan needs a snapshot to guarantee we keep the
original tuples of updates around, wich will hold back any other
VACUUM activity in the database. For CIC/RIC, a solution is being
created [0], but I'm not sure the same can be applied to this REPACK
CONCURRENTLY: while CIC/RIC doesn't care much about cross-page update
chains (it's only interested in TID+field values for possibly-live
tuples), REPACK seems to require access to the fields of the old
versions of updated tuples to correctly apply updates, thus requiring
a single snapshot for the full scan.
Maybe that's something that can be further improved upon, maybe not.
REPACK CONCURRENTLY is an improvement over the current situation
w.r.t. locks, but it'd be nice if this new system does not impact the
visibility horizons of the cluster by more than the current.
Kind regards,
Matthias van de Meent
Neon (https://neon.tech)
[0] https://www.postgresql.org/message-id/flat/CANtu0oiLc-%2B7h9zfzOVy2cv2UuYk_5MUReVLnVbOay6OgD_KGg%40mail.gmail.com
