Sulat.com / Writing on AISunday, November 22, 2026
Databases/21 November 2026

What a PostgreSQL 19 REPACK post leaves out about the final lock

A post on PostgreSQL 19's REPACK CONCURRENTLY gets the four steps right. The docs and mailing list add a lock queue, a disk bill, an empty-table snapshot and a 105 million change limit.

Glossy blue 3D PostgreSQL elephant logo beside a glass press squeezing a tall stack of database table slabs while light streams flow through it

At a glance

  • REPACK (CONCURRENTLY) in PostgreSQL 19 rewrites a bloated table while it takes reads and writes, and takes ACCESS EXCLUSIVE only for the final swap.
  • The swap can wait behind open transactions, and queries queue behind the waiting lock. A list patch measured a one-row SELECT stuck for 14.7 seconds.
  • Free disk must cover a full copy of the table and its indexes before any space comes back.
  • The concurrent mode isn't MVCC-safe: an older snapshot can see the table as empty after the swap.
  • It can't finish if more than about 105 million rows change during the run, needs a primary key or index-based replica identity, and runs one per cluster.

PostgreSQL 19 adds a REPACK command that rewrites a bloated table into a compact new file, and its CONCURRENTLY option does that while the table keeps taking reads and writes, according to the project’s release notes. The PostgreSQL Global Development Group released version 19 on 19 November 2026.

Developer alex shapalov, who describes himself on X as building Postgres for AI agents, posted a short video explainer at 7:25am NZDT on 21 November. It says a 100 GB table with 60 GB of bloat shrinks to 40 GB, and it lists four things the concurrent mode does: copies live rows into a compact table, keeps taking writes during the rebuild, replays new writes on the copy and briefly locks the table for the final swap.

alex shapalov on X, 21 November NZDT: the REPACK post this piece checks against the PostgreSQL 19 documentation.

All four lines match the 19 documentation. The documentation also lists conditions the post leaves out, and the PostgreSQL developers’ mailing list shows how some of them behave on a busy table. This piece checks the post against both.

What the post says, and what the docs add

The post saysThe 19 documentation saysSource
From 100 GB to 40 GB if 60 GB is bloatREPACK rewrites the table into a new file with no extra space beyond the fillfactor setting, and the freed space goes back to the operating system. While it runs it needs free disk at least equal to the table plus its indexes.REPACK reference
Keeps taking orders during the rebuildMost of the run holds SHARE UPDATE EXCLUSIVE, which lets reads and writes continue.REPACK reference
Replays new writes on the copyChanges made during the copy are captured with logical decoding and applied before the final lock is requested, then a last pass runs once the lock is held.REPACK reference
Briefly locks the table for the final swapACCESS EXCLUSIVE is taken only for the swap. It is typically held for a short time, depending on how many changes built up while the lock was being waited for.REPACK reference
The four lines of the post against the PostgreSQL 19 documentation.

shapalov’s 17 November article on the 19 release notes does say the final swap needs a short exclusive lock and that the operation needs extra disk space. The 20 November post drops both. A reply from Caleb Jimmy asked how long “brief” is when the table is hot, and the documentation answers that only in terms of accumulated changes. The mailing list gives numbers.

How a concurrent repack runs

REPACK copies the table’s live rows into a new file, sorted by an index when USING INDEX is given, builds a new file for each index, then swaps the old files for the new ones and deletes the old ones. Without CONCURRENTLY it takes an ACCESS EXCLUSIVE lock at the start and holds it to the end, which blocks every read and write. With CONCURRENTLY the bulk of the run holds only SHARE UPDATE EXCLUSIVE, the same lock VACUUM takes, and ACCESS EXCLUSIVE arrives once, for the swap.

A background worker captures the changes made during the copy using logical decoding, the mechanism logical replication is built on, and the backend applies them to the new copy before it asks for the final lock. Logical decoding needs a replication slot, and REPACK draws one from its own pool, sized by the new max_repack_replication_slots setting, which defaults to five and is set at server start. If effective_wal_level isn’t already logical, the command raises it at startup and waits for every process to acknowledge, according to the progress reporting page.

Four steps: copy live rows, record new writes, replay them, swap files under a short ACCESS EXCLUSIVE lock
Writes keep flowing for three steps; only the last swap takes the heavy lock.

The start has its own wait. The initializing phase waits for every transaction that already holds a transaction ID, including ones that began during the wait, so it can outlast the longest transaction open when the command started. A second REPACK (CONCURRENTLY) holds a transaction ID until it finishes, which is why a second run queues behind the first.

Run it and watch it

The syntax is one option on the command. The second form reuses the index already set as the table’s clustering index.

Code
REPACK (CONCURRENTLY) orders;
REPACK (CONCURRENTLY) orders USING INDEX;

-- from a second session
SELECT * FROM pg_stat_progress_repack;

The catch-up phase in that view also covers waiting for the ACCESS EXCLUSIVE lock, so a command blocked by a conflicting lock stays in catch-up, the documentation says.

How long “briefly” lasts on a hot table

The lock request queues behind every transaction already using the table, and queries that arrive after the request queue behind it. That makes the wait for the lock, not the swap, the part that varies. On 6 October, shihao zhong posted a patch to the PostgreSQL developers’ list that put numbers on it for the development branch: one session held a read lock for 15 seconds, and a single-row SELECT that arrived while REPACK waited for its final lock took 14.7 seconds.

A lock_timeout of 3 seconds made it worse, zhong wrote, because REPACK then failed after all the copying was done. Álvaro Herrera of EnterpriseDB, who committed the feature, replied that he didn’t want to change the behaviour in 19 and that documenting the shortcomings was the most he would do for now. zhong’s patch, which has REPACK check for the lock every 50 milliseconds instead of queueing, remains a proposal.

Caleb Jimmy on X, 21 November NZDT, replying to the post with the question the list thread answers.

14.7 s

The wait of a one-row SELECT stuck behind a queued REPACK, on the development branch in October.

3 s

The lock_timeout that made REPACK fail after the copy finished instead of waiting.

1.3 to 5.7 s

The range of longest write stalls for the concurrent mode across two identical runs, against 16.1 s for VACUUM FULL.

The stall figures come from Chat2DB, which makes a database client and tested release candidate 1 on a laptop.1 On a 3 million row table of 795 MB with four pgbench clients updating random rows at full speed, the longest write stall was 16.1 seconds under VACUUM FULL and 11.4 under plain REPACK. Under CONCURRENTLY it was 5.7 seconds in one run and 1.3 in another, a fourfold spread on an identical test. Chat2DB’s conclusion is to plan for a stall of a second or more, not zero.

Bar chart of longest write stall: VACUUM FULL 16.1 s, REPACK 11.4 s, CONCURRENTLY 5.7 s and 1.3 s
In one release-candidate test, the concurrent mode cut the longest stall from 16.1 seconds to between 1.3 and 5.7.

pg_squeeze, the extension REPACK’s design is based on, lets an operator cap the exclusive lock with squeeze.max_xlock_time: if the final stage runs over, it releases the lock, processes the new changes and tries again. The REPACK reference page lists three options, VERBOSE, ANALYZE and CONCURRENTLY, and no lock cap.

What it costs in disk

The 100 GB to 40 GB example holds at the end of the run. Getting there needs a full copy first: the documentation says REPACK creates a temporary copy of the table and all its indexes, so free disk has to be at least the table size plus the index sizes. A USING INDEX run that the planner implements as a sequential scan and sort needs a temporary sort file of up to the table size on top, and the complete data of every concurrent change, apart from untouched TOAST columns, is stored on disk separately.

A 100 GB table with 60 GB bloat shrinks to 40 GB, but needs at least 100 GB free before the rebuild starts
The space comes back at the end, but the rebuild needs a full copy’s worth of free disk first.

Layerbase, a hosted Postgres provider, reported on 20 November that the space does come back on 19.0: a 312 MB table with its indexes, after two thirds of its rows were deleted, dropped to 98 MB with plain REPACK, then to 50 MB after half the remainder was deleted and the concurrent mode ran.

REPACK also counts as one long transaction for wraparound purposes, so VACUUM can’t clean dead rows from other tables while it runs. The documentation advises watching pg_stat_activity.backend_xid on very large tables and setting maintenance_work_mem high before starting. It advances relfrozenxid but isn’t a wraparound tool, since it takes far longer than VACUUM and can fail after most of the work is done.

What it refuses to run on

Table or setupWhat happens
No primary key and no index-based replica identityRefused, because captured changes can’t be matched to rows. Chat2DB’s release-candidate run refused a REPLICA IDENTITY FULL table as well, and let the same table through once it had a NOT NULL unique index set with REPLICA IDENTITY USING INDEX.
Unlogged or temporary tablesRefused.
Partitioned tablesThe parent is refused. Chat2DB confirmed that running the command on each partition works.
System catalogs, TOAST tables, materialized views, tables not using the heap access method, tables marked user_catalog_tableRefused.
Inside a transaction blockRefused. REPACK with no table name, which processes every table the user holds MAINTAIN on, isn’t allowed with CONCURRENTLY either.
All repack replication slots in useRefused. The pool defaults to five.
A table with an invalid indexRefused in both modes. Drop or reindex the index first.
Source: PostgreSQL 19 REPACK reference page; Chat2DB’s release-candidate tests for the replica identity and partition rows.

The limits found before release

REPACK with the CONCURRENTLY option is not MVCC-safe.

PostgreSQL 19 documentation, REPACK reference page

Old snapshots see an empty table

MVCC, the rule that lets each transaction see a consistent snapshot of the data, doesn’t cover the swap. After the repack commits, a transaction holding a snapshot taken before it started sees the table as empty. Chat2DB reproduced it: a REPEATABLE READ transaction took its snapshot on another table, REPACK (CONCURRENTLY) finished, and the first transaction’s count of a 1,000-row table returned 0, with no error. A transaction that had already touched the table was protected, Chat2DB found, because REPACK waited for it. boringSQL, a blog by Radim Marek, adds that REPACK waits only for transactions that have written something, so a read-only transaction isn’t waited for.

A ceiling of about 105 million row changes

Marek reported on 25 September that the command can’t finish if more than about 105 million rows are updated or deleted while it runs, and the 19 documentation lists the same limit. His tests found the REPACK backend keeps about 50 bytes in memory per change, so a container capped at 1 GB was killed at about 18 million changes and took the whole server through a crash restart, while an 8 GB run reached 104,820,740 changes and failed with an allocation error.2 Herrera and Nathan Bossart said on 8 October that the limit is acceptable for 19 because a patch queued for PostgreSQL 20 removes it.

One at a time

The documentation’s initializing phase waits on other REPACK (CONCURRENTLY) runs, and Antonin Houska wrote in June that only one backend in the whole cluster can run it at a time. Chat2DB started a second run on a different table and watched it wait on the first, finishing at 6.9 seconds against 2.8 for the first. Houska’s patch set for the next development cycle removes that limit and makes the command MVCC-safe. It hadn’t been committed when 19 shipped.

Migrations

The documentation says DDL from other transactions can make the repack fail. Chat2DB queued an ALTER TABLE behind a running repack; with a lock_timeout set, the ALTER TABLE failed and the repack carried on.

A TOAST bug caught in September

Thom Brown found while stress testing on 23 September that rewriting a table’s TOAST table during the repack’s startup could silently discard committed updates to large column values, with no error and clean integrity checks. Herrera pushed a fix on 28 September that locks the TOAST table up front. The commit message says a rewrite arriving during startup now waits for REPACK, and that the two can still deadlock, in which case the deadlock detector cancels one of them.

Checklist: primary key, free disk, no long transactions, no migrations, under 105 million row changes
Five checks before pointing the concurrent mode at a busy table.

Where it came from

Houska, of the Austrian consultancy CYBERTEC, wrote pg_squeeze, and the commit that added the concurrent mode says the design and most of the code are his, heavily based on that extension. Herrera committed it with Mihail Nikalayeu as co-author.

DateWhat happenedSource
10 March 2026REPACK committed, absorbing VACUUM FULL and CLUSTER. The older commands stay, de-emphasised in the docs.Commit ac58465e0
6 April 2026CONCURRENTLY option committed. The commit message lists three loose ends: replication slot supply, one run at a time and a deadlock risk at the final lock.Commit 28d534e2a
7 April 2026Dedicated replication slot pool added, default five.Commit e76d8c749
23 to 28 September 2026TOAST update-loss bug reported, then fixed.pgsql-hackers thread
6 to 8 October 2026Lock queueing and the 105 million row ceiling discussed; neither changed for 19.pgsql-hackers threads
19 November 2026PostgreSQL 19 released with REPACK.Release announcement
How REPACK got into PostgreSQL 19.
PersonRole in the storySource
Antonin Houska, CYBERTECAuthor of REPACK and its concurrent mode, and of pg_squeezeCommit 28d534e2a
Álvaro Herrera, EnterpriseDBCommitted the feature and the slot pool; decided not to change lock queueing for 19Commit ac58465e0
Mihail NikalayeuCo-author of the concurrent modeCommit 28d534e2a
Thom BrownFound the TOAST update-loss bugpgsql-hackers thread
shihao zhongWrote the lock-wait patch and the TOAST fix patchpgsql-hackers thread
Radim Marek, boringSQLMeasured the 105 million row ceilingboringSQL
Who did what, from the commits and the mailing list.

How it compares with pg_repack and pg_squeeze

pg_repack 1.5.3pg_squeezeREPACK (CONCURRENTLY)
How it captures changesA trigger writes to a log tableLogical decodingLogical decoding
Installed asExtension plus a client programExtension loaded at server startBuilt in
Exclusive locksBriefly at the start and at the swapAt the end, capped by squeeze.max_xlock_timeAt the swap only; no cap setting in the docs
Free diskAbout twice the table and its indexesAbout twice the table and its indexesAt least the table and its indexes
Key neededPrimary key or unique index on a NOT NULL columnAn identity indexPrimary key or index-based replica identity
Sources: the pg_repack manual, the pg_squeeze README and the PostgreSQL 19 REPACK reference page.

pg_repack’s manual says it waits 60 seconds by default for its exclusive locks, then cancels the conflicting queries, and terminates any remaining backends after twice that. REPACK (CONCURRENTLY) queues for the lock instead, and a lock_timeout makes it fail rather than cancel anyone else.


What the post leaves out

The four lines are accurate. What the 20 November post doesn’t say is what to check before running the command:

  • The table needs a primary key or an index-based replica identity, or the command refuses to start.
  • Free disk must cover a full copy of the table and its indexes before the 60 GB comes back.
  • The final lock queues behind open transactions, and queries queue behind it, so a long reader stalls the table.
  • A transaction with an older snapshot can see the table as empty after the swap.
  • More than about 105 million concurrent updates or deletes make the run fail at the end.

PostgreSQL 20 work targets the snapshot problem, the one-run limit and the row ceiling: Houska’s patches for MVCC safety and for running several repacks at once are posted for the next development cycle, and Herrera and Bossart both point to a queued patch that removes the 105 million row ceiling.


Glossary

Bloat
Space in a table’s files taken up by dead row versions that VACUUM can reuse but can’t return to the operating system.
MVCC
Multiversion concurrency control, the rule that gives each transaction a consistent snapshot of the data.
Logical decoding
Reading row-level changes out of the write-ahead log, the mechanism logical replication uses.
Replica identity
The column or index that identifies a row when its change is decoded; the primary key by default.
ACCESS EXCLUSIVE
The strongest table lock. It blocks reads and writes.
SHARE UPDATE EXCLUSIVE
The lock VACUUM takes. It allows reads and writes but blocks other schema changes.
TOAST
The side table where PostgreSQL stores large column values.

Sources

  1. Chat2DB says its timings are for comparing the methods with each other, not absolute figures, and that release candidate 1 isn’t the final release. ↩︎
  2. The run stopped at 104,820,740 replayed changes. boringSQL says the structure holding one entry per change doubles in size as it fills, and past 104,857,600 entries the next doubling asks for more memory than PostgreSQL allows in a single allocation. ↩︎

Discover more from Sulat AI & Tech News

Subscribe to get the latest posts sent to your email.