Postgres 19’s Table-Shrinking Trick Has a Catch

PostgreSQLQL's Multi-Version Concurrency Control (MVCC) architecture, while foundational for concurrent database operations, comes with an inherent cost: table bloat. When a row is updated, PostgreSQLQL doesn't modify it in place; instead, it writes a brand-new copy and marks the old version as "dead." This mechanism ensures transactions always see a consistent snapshot of the data, but it leaves behind a trail of obsolete data.

Eventually, VACUUM processes clean up these dead rows, making their space available for new data within the same table. Crucially, a routine VACUUM operation does not release this reclaimed space back to the operating system. If you delete half your table, the file on disk often remains the same size, a common source of frustration for DBAs.

Historically, the only built-in way to physically shrink a table and return space to the OS was VACUUM FULL. This command rewrites the entire table, compacting it onto new disk pages. However, VACUUM FULL acquires an ACCESS EXCLUSIVE lock, blocking all reads and writes to the table until it completes—a consequence so severe even PostgreSQLQL's own documentation advises against its use in production environments. This left DBAs reliant on third-party extensions like pg_repack for online table compaction.

PostgreSQLQL 19 finally gives DBAs a built-in, online solution for table bloat: REPACK CONCURRENTLY. This command sidesteps the blocking VACUUM FULL by creating a new, compact version of a table while allowing concurrent reads and writes to the original. It’s a game-changer for maintaining performance without downtime.

Mechanism is clever: REPACK first copies all live rows into a new, temporary table. Critically, it then uses WAL-based logical decoding to capture any changes (inserts, updates, deletes) that occurred on the original table during this copy phase. These captured changes are replayed onto the new table, ensuring the replacement is fully up-to-date.

Finally, REPACK performs a brief, exclusive lock to atomically swap the old table file with the new, compact one. This minimizes the critical window where the table is unavailable. Better Stack’s test demonstrated a 493 MB table shrinking to 228 MB in under 10 seconds, all while staying accessible until the final, momentary cutover. This isn't a universal benchmark, but it highlights the potent disk savings and operational efficiency REPACK offers.

REPACK CONCURRENTLY doesn't perform magic; it merely offloads the heavy lifting. While the bulk of the table copy proceeds online, the final, crucial step—swapping the old table file for the new, compacted version—demands an exclusive lock. This ACCESS EXCLUSIVE lock prevents any concurrent operations, whether reads or writes, on the table. It’s a tiny, atomic window, but it must wait for every single active transaction touching that table to complete.

This bottleneck creates a significant operational risk. A Better Stack test demonstrated this perfectly: a 20-second analytical report, active during the cutover, delayed the final swap. This caused all subsequent queries targeting that table to queue for a full 18 seconds, effectively creating a head-of-line blocking scenario. The promise of online repacking is diminished if a single long-running query can derail concurrent operations.

To mitigate this, treat the final cutover as a critical event. Schedule REPACK CONCURRENTLY during off-peak hours, when long-running reports or batch jobs are unlikely to contend for the table. Proactively check for active sessions on the target table before initiating the command, or consult resources like PostgreSQLQL Documentation: Routine Vacuuming for more on managing table maintenance. The goal is to ensure the metadata swap occurs without unexpected delays, transforming a clever trick into a reliable operational tool.

PostgreSQLQL 19’s native REPACK CONCURRENTLY offers a compelling alternative to third-party tools like pg_repack, which has been a community staple for over a decade. Built-in support streamlines extension management, sidesteps compatibility headaches, and potentially improves integration with core database operations. This evolution reflects a growing trend towards internalizing critical DBA functionalities.

However, "native" does not equate to "magic." While the Better Stack video cites a target release date of November 19 for PostgreSQLQL 19, users must verify the current release status and provider support before planning any migration. Managed services, in particular, may delay feature adoption or impose restrictions on new commands, so always confirm capabilities with your specific vendor.

The development path for PostgreSQLQL 19 has been, as the video notes, "rocky," with features announced, canceled, and scaled back in favor of stability. This cautious approach is characteristic of the PostgreSQLQL Global Development Group. Therefore, before relying on REPACK CONCURRENTLY for production workloads, confirm its precise behavior and any operational caveats in the official PostgreSQLQL 19 documentation.

Ultimately, REPACK CONCURRENTLY is a powerful new arrow in the DBA's quiver, not a silver bullet. Its brief, yet critical, exclusive lock means that strategic planning remains essential. Running it during low-activity windows or when long-running queries are absent will maximize its benefit and prevent the kind of 18-second queueing observed in Better Stack’s tests.

What does REPACK do in PostgreSQL 19?

REPACK rebuilds a table into a more compact physical layout, reclaiming unused disk space while allowing normal database activity to continue.

How is REPACK different from VACUUM FULL?

VACUUM FULL rewrites a table while holding a blocking lock for the operation. REPACK CONCURRENTLY does most of its work while the table remains accessible, then needs a brief exclusive lock to switch files.

Can REPACK CONCURRENTLY still affect application traffic?

Yes. The final swap must wait for queries using the table to finish, and new requests may queue behind the lock.

Does PostgreSQL 19 make pg_repack obsolete?

It may reduce the need for pg_repack in some cases, but teams should compare the built-in feature’s availability and operational fit with their existing tooling.