Horton
Menu

Re: Adding REPACK [concurrently]

Antonin Houska <ah@cybertec.at>

View thread
From: Antonin Houska <ah@cybertec.at>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>, Pg Hackers <pgsql-hackers@lists.postgresql.org>, Robert Treat <rob@xzilla.net>
Date: 2026-02-25T16:03:19Z
Lists: pgsql-hackers 
Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

> On 2026-Feb-25, Antonin Houska wrote:

> > For REPACK, I suggest a variant of toast_flatten_tuple() that writes the
> > output to a file, and a corresponding function that reads it while allocating
> > separate chunks of memory for the individual TOASTed attributes - the restored
> > tuple would reference the chunks using the "external indirect" TOAST pointers,
> > as if it had been processed by ReorderBufferToastReplace(). Does that make
> > sense to you?
> 
> Hmm, so on the apply side when reading the file, we would first reach
> each toast attribute value, which we know to insert directly to the
> toast table (keeping track of each individually toast pointer as we do
> so); then we reach the heap tuple itself, we [... somehow ...] interpret
> these external indirect toast pointers and substitute the toast pointers
> that we created.  So we never have to construct the entire tuple, or
> indeed do anything else with the toasted values other than insert them
> into the toast table.

Yes, that's what I mean.

> Actually, can't we simply insert the toasted values directly in the
> decoding worker into the new toast table?  That could save a lot of
> writing to the file, since we only save the raw heap tuples with no
> toasted contents; but it's not clear to me that this is valid.  (And we
> might create extra bloat if a tuple is inserted and later deleted
> concurrently with the repack; but that would happen with the original
> approach as well, no?)

The problem I see here is that for UPDATE you need the old tuple to determine
if its TOAST value should be deleted or if the new tuple should reuse it -
this is how I understand toast_tuple_init(). So the worker would have to store
all the changes somewhere temporarily until it can fully apply the changes
(i.e. until the initial copy and index build is complete).

Besides that, if the worker had to switch between the past (for the decoding)
and present (for the TOAST operations), it would have to invalidate system
caches repeatedly. 0004 does that, but 0005 makes that unnecessary. (I don't
know if the repeated cache invalidation would be a serious performance
problem, but from coding perspective I find it more convenient if the worker
only deals with decoding and does not have to do this time travel and
invalidations at all.)

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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow old WAL recycling during REPACK CONCURRENTLY

  2. Advance restart_lsn more eagerly in LogicalConfirmReceivedLocation

  3. Remove unnecessary signal handler change

  4. Improve REPACK (CONCURRENTLY) error messages some more

  5. Revert "Allow logical replication snapshots to be database-specific"

  6. Move REPACK (CONCURRENTLY) test out of stock regression tests

  7. REPACK: do not require REPLICATION or LOGIN

  8. Add missing initialization

  9. Simplify declaration of memcpy target

  10. Reserve replication slots specifically for REPACK

  11. doc: Add an example of REPACK (CONCURRENTLY)

  12. Allow logical replication snapshots to be database-specific

  13. Avoid different-size pointer-to-integer cast

  14. Fix valgrind failure

  15. Add CONCURRENTLY option to REPACK

  16. Rename cluster.c to repack.c (and corresponding .h)

  17. Allow index_create to suppress index_build progress reporting

  18. Make index_concurrently_create_copy more general

  19. Document the 'command' column of pg_stat_progress_repack

  20. Introduce the REPACK command

  21. Toggle logical decoding dynamically based on logical slot presence.

  22. Split vacuumdb to create vacuuming.c/h

  23. Remove ReorderBufferTupleBuf structure.

  24. Revert changes to CONCURRENTLY that "sped up" Xmin advance

  25. VACUUM: ignore indexing operations with CONCURRENTLY