Recommended Free Tools
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
PostgreSQL 19 adds REPACK, a table-rewrite command that can remove dead-row space and, with (CONCURRENTLY), keep reads and writes available through most of the operation. It is not lock-free: the final file swap still needs an ACCESS EXCLUSIVE lock. PostgreSQL 19 is documented as an unsupported beta release, so check the status and syntax for your exact server build before using the command.
What REPACK does—and what ordinary VACUUM does not
PostgreSQL uses multiversion concurrency control (MVCC): updates and deletes leave old row versions behind until they can be removed. VACUUM makes space occupied by dead tuples reusable inside the table. It usually does not shrink the table file or return that space to the operating system.
REPACK rewrites a table into a new file, copying live rows and leaving out dead tuples. The resulting file can return unused space to the operating system, subject to the table’s fillfactor. Because it creates a replacement copy before discarding the old files, the rewrite needs temporary disk space.
Autovacuum and manual vacuuming are for ongoing maintenance. Rewriting a table just to make it as small as possible is often counterproductive if normal activity will make it grow again. Use REPACK when reclaiming substantial excess space or changing the table’s physical row order is a real operational goal.
#1 Best Overall
Which PostgreSQL 19 REPACK mode fits?
| Operation | Space result | Availability and constraints | Best fit |
|---|---|---|---|
VACUUM or autovacuum |
Makes dead-tuple space reusable within the table; usually does not return it to the operating system, apart from certain empty pages at the end of the file. | Does not take the table-wide ACCESS EXCLUSIVE lock used by rewrites, though it consumes I/O. |
Routine maintenance and controlling dead-row accumulation. |
VACUUM FULL |
Rewrites and compacts a table, returning space to the operating system. | Takes an ACCESS EXCLUSIVE lock while processing and requires temporary space. PostgreSQL 19 documentation marks it deprecated in favor of REPACK behavior. |
Legacy or special-case workflows; consult documentation for the deployed version. |
REPACK |
Rewrites the table to reclaim dead-tuple storage; USING INDEX can reorder rows. |
Without CONCURRENTLY, holds an ACCESS EXCLUSIVE lock for the rewrite, blocking other table operations. |
Reclaim space or deliberately cluster rows when blocking is acceptable. |
REPACK (CONCURRENTLY) |
Same rewrite goal, using change capture while the copy is made. | Reads and writes can continue through most of the operation; a final ACCESS EXCLUSIVE swap lock and eligibility requirements still apply. Temporary disk is required. |
Reclaim space when availability matters and the table meets the prerequisites. |
pg_repack extension |
Separate extension for reorganizing tables and indexes online. | Its project documentation lists compatibility through PostgreSQL 19 and requires a primary key or qualifying unique index plus substantial free disk. Check compatibility with your server and provider. | An alternative for installations not using the PostgreSQL 19 core command. |
PostgreSQL describes REPACK in its PostgreSQL 19 REPACK command documentation as combining functionality associated with VACUUM FULL and CLUSTER. The separate pg_repack project is an extension, not the core SQL command.
How REPACK CONCURRENTLY works
Concurrent mode copies the table while capturing changes made during that copy through logical decoding. It applies those changes before requesting the lock needed to swap the replacement files into place. This shortens the period when the table is blocked; it does not guarantee zero blocking. PostgreSQL also warns that concurrent REPACK is not MVCC-safe.
Rank #2
The word “online” therefore describes availability through most of the rewrite, not an absence of locks or operational prerequisites. If the final lock cannot be acquired promptly, the swap may wait behind other activity.
Free tools Windows power users keep installed
One-click scans. No signup required.
Check the release status before using the command
The PostgreSQL 19 documentation page identifies the version as unsupported and references Beta 4, released September 24, 2026. The release notes shown for PostgreSQL 19 retain a placeholder final-release date and an as-of date of September 14, 2026. Treat REPACK as a development/beta feature unless the official status for your server has since changed; confirm current release documentation and your provider’s supported version before planning production work.
Check eligibility and prepare the operation
Before starting a rewrite, confirm the target version, privileges, table eligibility, index health, disk capacity, and logical-decoding configuration. The PostgreSQL command documentation is the authority for any version-specific changes.
- Privileges: the executing role needs
MAINTAINon the table. - Indexes: REPACK refuses a table with an invalid index. Remove or reindex invalid indexes before proceeding.
- Concurrent-mode table eligibility: documented exclusions include materialized views, unlogged tables, partitioned tables, system catalogs, TOAST tables, non-heap access methods, and tables without a primary key and index-based replica identity.
- Replication slots: concurrent mode needs permission in the server configuration to create another replication slot; check
max_repack_replication_slots. - Transaction context: concurrent mode cannot run inside a transaction block.
- Disk headroom: PostgreSQL’s general rewrite guidance estimates temporary space at approximately the size of the table. The pg_repack extension separately estimates about twice the combined target table and index size for a full-table repack; that extension estimate is not a guaranteed requirement for the core command.
- Impact: a non-concurrent REPACK blocks reads and writes for the rewrite. Concurrent mode can still block at the final swap.
PostgreSQL’s routine vacuuming documentation explains why rewrite operations need a new copy while the old one remains. Do not assume the operation’s duration or required headroom from a different table or deployment.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Run the appropriate SQL form
These are syntax examples from the PostgreSQL 19 command documentation, not a tested production procedure. Confirm eligibility, free disk, index validity, privileges, and replication-slot configuration first.
-- Rewrite a table to reclaim storage
REPACK employees;
-- Rewrite and reorder rows using an index
REPACK employees USING INDEX employees_ind;
-- Run concurrently using the previously selected clustering index
REPACK (CONCURRENTLY) employees USING INDEX;The documented command supports options including VERBOSE, ANALYZE, and CONCURRENTLY. It also supports a table with specified columns, or USING INDEX to choose or reuse an index for row ordering. The command temporarily sets search_path to pg_catalog, pg_temp; account for that behavior in operational scripts.
Monitor progress and understand row ordering
PostgreSQL exposes core REPACK progress through pg_stat_progress_repack. Consult the view’s documented fields for the server version in use when monitoring an operation.
USING INDEX physically reorders rows according to an index. PostgreSQL says this can help range queries or requests that return multiple rows with similar index values, but whether it helps depends on the workload’s access patterns. Repacking without a deliberate ordering goal is not a reason by itself to choose an index.
Quick Recap
Choose REPACK, VACUUM FULL, or pg_repack by the actual need
- For normal dead-tuple cleanup and reusable internal space, use routine vacuuming and allow autovacuum to respond to update activity.
- For returning substantial excess table space to the operating system, consider a rewrite and budget for the temporary copy.
- If table reads and writes must continue through most of that rewrite, consider
REPACK (CONCURRENTLY)only when the table and server meet its constraints. - If you cannot use the PostgreSQL 19 core command, assess the separate pg_repack extension against the deployed PostgreSQL version, provider support, index requirements, and its own capacity guidance.
- Do not treat
VACUUM FULLas interchangeable with ordinaryVACUUM: it rewrites the table and blocks table operations during processing.
