Horton
Menu

Re: Adding REPACK [concurrently]

Robert Treat <rob@xzilla.net>

View thread
From: Robert Treat <rob@xzilla.net>
To: Andres Freund <andres@anarazel.de>
Cc: Antonin Houska <ah@cybertec.at>, Amit Kapila <amit.kapila16@gmail.com>, Mihail Nikalayeu <mihailnikalayeu@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Srinath Reddy Sadipiralla <srinath2133@gmail.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Pg Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-04-10T16:14:59Z
Lists: pgsql-hackers 
On Thu, Apr 9, 2026 at 10:20 AM Andres Freund <andres@anarazel.de> wrote:
> On 2026-04-09 10:43:14 +0200, Antonin Houska wrote:
> > What Andres proposed (AFAIU) should help to avoid this problem because
> > REPACK's request for AEL would get in front of the VACUUM's request for SUEL
> > in the queue.
>
> Note that that already happens today.
>
> This works today (without the error triggering patch):
>
> S1: REPACK starts
> S2: LOCK TABLE / VACUUM / ... starts waiting
> S1: REPACK tries to get AEL
> S1: REPACK's lock requests get reordered in the wait queue to be before S2 and
>     just gets the lock
> S1: REPACK finishes
> S2: lock acquisition completes.
>
> That's because we do already have this "jumping the wait queue" logic, which I
> had forgotten about.
>

You know, I was wondering how this wasn't already a problem for
pg_repack/pg_squeeze, and I guess this explains it :-P

>
> What does *not* work is this:
>
> S1: REPACK starts
> S2: BEGIN; SELECT 1 FROM table LIMIT 1;
> S2: LOCK TABLE / VACUUM / ... starts waiting
> S1: REPACK tries to get AEL
> S1: lock is not granted, can't be reordered to be before S2, because S2 holds
>     conflicting lock, deadlock detector triggers
> S2: lock acquisition completes
>
> But with my proposal to properly teach the deadlock detector about assuming
> there's a wait edge for the eventual lock upgrade by S1, the first example
> would still work, because the lock upgrade would not be considered a hard
> cycle, and the second example would have S2 error out.
>

In the above S2 will error out if you try to run a VACUUM, but the
point still stands that calling an explicit LOCK or similar could lead
to this issue. In the current repack world, we document the need for
lock escalation at the end of the repacking and caution that doing
things like DDL or explicit LOCKing could cause trouble, so don't do
that. What you're proposing above would be an improvement though,
IMHO.

>
> > Anti-wraparound (failsafe) VACUUM is a bit different case [1] (i.e. it should
> > possibly have higher priority than REPACK), but I think this prioritization
> > should be implemented in other way than just letting it get in the way of
> > REPACK (at the time REPACK is nearly finished).
>
> Yea, it makes no sense to interrupt the long running repack, given that the
> new relation will have much less stuff for vacuum to do.
>

We might be talking about 2 different scenarios. In the case where we
are at the point of lock escalation, you would probably want the
repack to get priority over a waiting vacuum, even a failsafe vacuum.
But outside of that scenario, we can't know that the repack is the
better option (and statistically it probably isn't) since a repack
that is actively copying rows might still need to rebuild some large
number of indexes (or just some really expensive index) which could
take significantly longer than a failsafe vacuum would need to ensure
wraparound avoidance. I don't think we'd go as far as saying the
failsafe vacuum should cancel the repack, but I think ideally we'd
like it to not be canceled either, since that would increase
likelihood for dba/monitoring to pick up on the situation, and in the
case that REPACK fails for some reason, the failsafe vacuum could
immediately start working without having to go through any additional
hoops.

Robert Treat
https://xzilla.net

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