Horton
Menu

Re: Adding REPACK [concurrently]

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

View thread
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Srinath Reddy Sadipiralla <srinath2133@gmail.com>, Mihail Nikalayeu <mihailnikalayeu@gmail.com>, Antonin Houska <ah@cybertec.at>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Pg Hackers <pgsql-hackers@lists.postgresql.org>, Robert Treat <rob@xzilla.net>
Date: 2026-04-01T11:38:16Z
Lists: pgsql-hackers 
On 2026-Apr-01, Amit Kapila wrote:

> What about if the blocking process is an autovacumm that is working to
> prevent XID wraparound? I think we already avoid killing it in such
> cases.

If we just let REPACK finish, it will also renew the table's XID, so
autovacuum is not needed in that case.  I mean, there's no reason to let
autovacuum process the contents of a table that is going to be replaced
completely.

One potentially problematic case would be that an emergency autovacuum
has been running for a long time and about to finish, and REPACK is
started.  But in that case, autovacuum already has ShareUpdateExclusive,
so REPACK wouldn't be able to start at all, which means it won't kill
autovacuum.  And in the case where autovacuum is doing emergency
vacuuming, then it won't commit suicide, so it will be able to complete
before repack starts.

> BTW, one can say that cancelling a long-running report query also
> wastes a lot of effort of the user generating such a report. Why can't
> REPACK wait for such a select to finish instead of cancelling it?

I don't understand exactly which scenario you're concerned about.  Is
there a long-running query which, after spending a lot of time running a
report, tries to upgrade its lock level on the table?  Keep in mind that
this check only runs when the affected session runs the deadlock
checker, which means it's been waiting to acquire a lock for
deadlock_timeout seconds.  It's not repack that kills the query.

[ ... reflects ...]  Oh, actually what Srinath proposed does exactly
that -- kill other queries.  Hmm, yeah, I'm less sure about that
particular bit.  Here I'm only talking about my proposal to have the
deadlock detector handle the case of somebody waiting to lock the table.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.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