A production table has grown to 50 GB but only holds 20 GB of live data. The rest is bloat from updates and deletes that left behind dead rows. Until PostgreSQL 19, you had two options: lock the table with VACUUM FULL or install the pg_repack extension. Now PostgreSQL 19 REPACK CONCURRENTLY is built in and removes bloat without blocking writes. You get the same rebuild behavior that pg_repack provided, but as a native command that ships with every installation.
This matters for any table that sees frequent updates or deletes. Over time, dead tuples accumulate inside data files. VACUUM reclaims that space for reuse but does not shrink the file on disk. A table that once held 100 GB of data may still occupy 100 GB even after you delete 70 GB of rows. REPACK rewrites the table into a new, compact file and swaps it in place while your application keeps running.
What REPACK CONCURRENTLY Does and Why It Matters
The Bloat Problem and Traditional Solutions
Every UPDATE in PostgreSQL creates a new row version and marks the old one dead. DELETE marks rows dead immediately. VACUUM scans the table, marks dead tuple space as reusable, and updates the free space map. New inserts and updates can fill those holes, but the file itself stays the same size on disk. If you delete half your rows, df -h still shows the full file size. Queries still scan those empty pages. Indexes still reference dead tuples until VACUUM cleans them.
VACUUM FULL rewrites the entire table into a new file and reclaims disk space, but it takes an AccessExclusiveLock for the entire operation. No reads, no writes. On a 50 GB table, that might mean 20 minutes of downtime. The pg_repack extension solved this by creating a shadow table, copying live rows via triggers, then swapping the tables with a brief lock. It has been the standard tool since 2010, but you had to install it separately, keep it updated across PostgreSQL versions, and manage another dependency.
How REPACK CONCURRENTLY Works
PostgreSQL 19 adds REPACK TABLE tablename CONCURRENTLY; as a native command. The REPACK documentation describes the process: it creates a new table with the same structure, installs triggers on the original to capture changes, copies all live rows to the new table, applies captured changes, swaps the table definitions, and drops the old file. During the copy phase, REPACK holds a ShareUpdateExclusiveLock, which permits SELECT, INSERT, UPDATE and DELETE but blocks schema changes and other REPACK or VACUUM FULL operations. The final swap takes an AccessExclusiveLock, but only for a few milliseconds while the table definitions switch.
REPACK TABLE orders CONCURRENTLY;
Your application sees no downtime. Writes continue throughout. The only interruption is that brief final lock, shorter than a typical connection timeout. Compare this to VACUUM FULL, which holds AccessExclusiveLock from start to finish, or to pg_repack, which behaves almost identically but requires external installation and maintenance.
Resource Requirements and When to Use REPACK
REPACK needs free disk space equal to the table size, because it builds a full copy before dropping the old file. A 50 GB table requires 50 GB free during the operation. Check df -h on your data volume before you start. Memory usage depends on maintenance_work_mem and the number of indexes; plan for at least 1 GB per large index being rebuilt.
Use REPACK when bloat exceeds 30% of the table size. After bulk UPDATE or DELETE operations that affect millions of rows, dead tuples pile up faster than autovacuum can reclaim them. Before a major version upgrade, repacking shrinks files and speeds up the pg_upgrade process. For tables under 10 GB with moderate bloat, regular VACUUM often suffices. For tables over 100 GB, schedule REPACK during low-traffic windows even though it allows writes, because the I/O load can still affect query performance on slower disks.
Detecting Table Bloat Before Running REPACK
Querying pgstattuple for Exact Bloat Metrics
The pgstattuple extension provides precise bloat measurements. Install it once per database:
CREATE EXTENSION pgstattuple;
Then query a specific table:
SELECT * FROM pgstattuple('orders');
The pgstattuple documentation explains each column. Look at dead_tuple_percent and free_percent. A healthy table shows under 10% dead tuples. Anything over 30% is a candidate for REPACK. Example output:
table_len | 53687091200
tuple_count | 12000000
tuple_len | 21474836480
dead_tuple_percent | 38.5
free_percent | 5.2
Here, 38.5% of the table is dead tuples. The table occupies 50 GB (table_len) but live data is only 20 GB (tuple_len). Free space is 5.2%, meaning those dead tuples have not yet been reclaimed by VACUUM. This table needs REPACK.
pgstattuple scans the entire table, so it can be slow on large tables. Run it during off-peak hours or use the faster estimates below for routine checks.
Estimating Bloat with pg_stat_user_tables
For a quick estimate without a full scan, query pg_stat_user_tables:
SELECT schemaname, relname, n_live_tup, n_dead_tup,
round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;
This shows approximate dead tuple counts based on statistics. If dead_pct is over 30%, investigate with pgstattuple. Check table size at the same time:
SELECT pg_size_pretty(pg_total_relation_size('orders'));
If the table is 50 GB and dead_pct is 40%, you know roughly 20 GB is bloat. The counts are not perfectly accurate because they depend on autovacuum updating stats, but they are fast and good enough for monitoring scripts. For production health checks, see PostgreSQL Health Monitoring with Python for a complete bloat detection tool.
Checking Free Space with pg_freespace
The pg_freespace extension shows free space distribution across pages. Install it:
CREATE EXTENSION pg_freespace;
Then query a table:
SELECT blkno, avail FROM pg_freespace('orders') WHERE avail > 0 LIMIT 20;
This lists page numbers (blkno) and available bytes (avail) in each page. If you see many pages with 4000+ bytes free (out of 8192 byte pages), VACUUM has reclaimed space but the file is still bloated. REPACK will eliminate those gaps. This query is most useful for understanding bloat patterns after VACUUM, not for routine checks.
Running REPACK CONCURRENTLY: Syntax and Options
Basic REPACK Command and Transaction Handling
Run REPACK outside any transaction block. It cannot be wrapped in BEGIN/COMMIT because it uses multiple internal transactions:
REPACK TABLE orders CONCURRENTLY;
If you try to run it inside a transaction, you get an error:
ERROR: REPACK cannot run inside a transaction block
The command runs to completion or fails. You see progress messages in the psql output. On a 10 GB table with default settings and SSD storage, expect about 15 minutes total. During that time, writes continue normally. The final swap takes under 2 seconds, during which new queries wait but existing transactions finish. Most applications never notice.
Repacking Specific Indexes and Constraints
REPACK can rebuild indexes without touching the table data. If an index is bloated but the table is not, this is faster:
REPACK INDEX orders_pkey CONCURRENTLY;
To rebuild only certain indexes while repacking the table:
REPACK TABLE orders (INDEX orders_customer_idx, INDEX orders_created_idx) CONCURRENTLY;
This rebuilds the table and the two named indexes. Other indexes are left alone. Use this when you know specific indexes are bloated from partial index scans or frequent updates to indexed columns. Check index sizes with:
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'orders';
Setting Work Memory and Parallelism
Increase maintenance_work_mem before large repacks. The default is often 64 MB, which slows index builds:
SET maintenance_work_mem = '2GB';
REPACK TABLE orders CONCURRENTLY;
This setting applies only to your session. For persistent changes, edit postgresql.conf. Higher values speed up the index rebuild phase but use more RAM. Allow 1-2 GB per index being built.
Enable parallel workers for faster index builds:
SET max_parallel_maintenance_workers = 4;
REPACK TABLE orders CONCURRENTLY;
With 4 workers, a large B-tree index builds in parallel. This helps on multi-core systems with fast disks. The table copy phase does not parallelize, so this only affects index rebuild time. On a 50 GB table with 5 indexes, parallelism can cut total time from 40 minutes to 25.
Set high timeouts to avoid mid-operation failures:
SET statement_timeout = 0;
SET lock_timeout = '10s';
REPACK TABLE orders CONCURRENTLY;
The statement_timeout of 0 disables the timeout entirely. The lock_timeout of 10 seconds lets REPACK wait briefly for the final lock. If another transaction holds a conflicting lock, REPACK retries. For more on tuning these parameters, see PostgreSQL Administration & Performance Tuning.
Monitoring REPACK Progress and Locks
Tracking Shadow Table Creation with pg_stat_progress_cluster
PostgreSQL exposes REPACK progress through the pg_stat_progress_cluster view. Query it from another session while REPACK runs:
SELECT pid, datname, relid::regclass, command, phase,
heap_tuples_scanned, heap_tuples_written, heap_blks_total, heap_blks_scanned
FROM pg_stat_progress_cluster
WHERE command = 'REPACK';
The phase column shows exactly where the operation is: initializing when creating the shadow table, seq scanning table during the full copy, sorting tuples if the table is clustered, writing new heap as rows are inserted into the shadow table, swapping relation files during the final swap, and rebuilding indexes when indexes are being recreated. The progress reporting documentation lists all phases.
Watch heap_tuples_scanned versus heap_tuples_written. If you have 12 million live rows and 8 million dead, scanned will reach 20 million but written will stop at 12 million. Example output every 30 seconds on a 50 GB table:
-- T+0s
phase: initializing | heap_tuples_scanned: 0 | heap_tuples_written: 0
-- T+30s
phase: seq scanning table | heap_tuples_scanned: 2400000 | heap_tuples_written: 1500000
-- T+60s
phase: seq scanning table | heap_tuples_scanned: 4800000 | heap_tuples_written: 3000000
-- T+300s
phase: writing new heap | heap_tuples_scanned: 20000000 | heap_tuples_written: 12000000
-- T+600s
phase: rebuilding indexes | heap_tuples_scanned: 20000000 | heap_tuples_written: 12000000
-- T+900s
phase: swapping relation files | heap_tuples_scanned: 20000000 | heap_tuples_written: 12000000
The discrepancy between scanned and written confirms 40% bloat. Run this query in a loop to watch progress: watch -n 10 "psql -c 'SELECT ...'. If the phase stalls for more than a few minutes, check for lock contention or disk I/O bottlenecks with iostat.
Watching Lock Waits with pg_locks and pg_stat_activity
REPACK takes a brief AccessExclusiveLock during the final swap. If another transaction holds a conflicting lock, REPACK waits. Find waiting locks:
SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted AND relation = 'orders'::regclass;
If you see AccessExclusiveLock | granted: f, the swap is blocked. Check what holds the lock:
SELECT pid, usename, state, wait_event, query
FROM pg_stat_activity
WHERE pid IN (
SELECT pid FROM pg_locks WHERE relation = 'orders'::regclass AND granted
);
A long-running transaction or an idle connection in state idle in transaction can block the swap for minutes. Kill it with SELECT pg_terminate_backend(pid); if necessary. Set log_lock_waits = on in postgresql.conf to log these waits automatically:
log_lock_waits = on
deadlock_timeout = 1s
After one second of waiting, PostgreSQL logs the blocking query. You see messages like LOG: process 12345 still waiting for AccessExclusiveLock on relation 16384 after 1000.123 ms. This helps diagnose why a REPACK that should take 15 minutes is still running after an hour. Most often the culprit is an application leaving transactions open or a monitoring script holding a long SELECT.
REPACK Failure Modes and Recovery
Disk Space Exhaustion Mid-Repack
If the disk fills during the copy phase, PostgreSQL aborts with an error:
ERROR: could not extend file "base/16384/16389": No space left on device
HINT: Check free disk space.
The original table is untouched and still live. The shadow table remains on disk until you drop it or autovacuum cleans it up. Check disk space before starting:
df -h /var/lib/postgresql/data
If the data volume has 100 GB free and the table is 60 GB, you risk failure. REPACK needs at least the table size plus 20% for indexes and temporary sort files. After a failure, find leftover shadow tables:
SELECT oid, relname, pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relname LIKE 'pg_temp_%' AND relkind = 'r';
Drop them manually:
DROP TABLE pg_temp_12345;
The pg_temp_ prefix is how PostgreSQL names internal shadow objects. If autovacuum is running, it will eventually drop these, but manual cleanup is faster and frees disk space immediately. No data is lost; the original table was never modified.
Canceling a REPACK and Cleaning Up Shadow Objects
Cancel a running REPACK with Ctrl-C in psql or from another session:
SELECT pg_cancel_backend(12345);
Replace 12345 with the PID from pg_stat_progress_cluster. The cancellation is clean: the original table remains queryable, triggers are removed, and shadow objects are marked for cleanup. PostgreSQL logs:
ERROR: canceling statement due to user request
Autovacuum drops the shadow table and indexes within a few minutes. If you need immediate cleanup, query pg_class as shown above and drop the temporary objects. If the final swap lock times out because another transaction holds a conflicting lock, REPACK fails with:
ERROR: could not obtain lock on relation "orders"
The original table is still live. Writes never stopped. You can retry REPACK immediately or wait for the blocking transaction to finish. Worst case is wasted time and disk space; there is no risk of data loss or corruption. The shadow table is always built from a consistent snapshot, and the swap is atomic.
REPACK vs pg_repack Extension: When to Migrate
Feature Parity and Differences
The pg_repack extension has been the standard tool since PostgreSQL 9.0. You run it from the shell:
pg_repack -t orders -d production
Native REPACK in PostgreSQL 19 uses SQL syntax:
REPACK TABLE orders CONCURRENTLY;
No external binary, no version compatibility issues, no separate installation. The core behavior is identical: both create a shadow table, copy live rows, capture changes with triggers, and swap definitions with a brief lock. Key differences: pg_repack has a --no-order flag to skip clustering and save time; native REPACK always preserves the cluster order if you have run CLUSTER orders USING orders_pkey; before. To maintain physical order, cluster first then repack:
CLUSTER orders USING orders_pkey;
REPACK TABLE orders CONCURRENTLY;
pg_repack can rebuild an entire database with pg_repack -d production, repacking all bloated tables in one command. Native REPACK requires per-table commands. For automation, you need a script that queries bloated tables and issues individual REPACK statements. pg_repack also supports repacking system catalogs with --no-superuser-check; native REPACK does not touch system tables. For most production workloads, these differences do not matter. The native command is simpler and better integrated with PostgreSQL's locking and progress reporting.
Migration Strategy from pg_repack to Native REPACK
Test native REPACK on a development or staging database first. Compare timing and bloat reduction with pg_repack. Run both on the same table and measure:
-- Before
SELECT pg_size_pretty(pg_total_relation_size('orders'));
-- pg_repack
\timing
pg_repack -t orders -d production
-- Native REPACK
\timing
REPACK TABLE orders CONCURRENTLY;
-- After
SELECT pg_size_pretty(pg_total_relation_size('orders'));
If results are similar, migrate your cron jobs or maintenance scripts. Convert pg_repack calls to psql commands:
#!/bin/bash
# Old: pg_repack -t orders -d production
# New:
psql -d production -c "REPACK TABLE orders CONCURRENTLY;"
You do not need to uninstall the pg_repack extension immediately. Keep it installed for rollback if you encounter issues. Native REPACK is PostgreSQL 19+ only; if you run older versions, pg_repack remains the only option. For mixed-version environments, maintain both tools until all clusters are upgraded. Once you confirm native REPACK works reliably, remove pg_repack from your build and deployment scripts.
Production Best Practices and Scheduling
REPACK Frequency and Maintenance Windows
Run REPACK monthly on high-churn tables that see millions of updates or deletes per week. For moderate-activity tables, quarterly is usually enough. Base the schedule on bloat monitoring: if dead_tuple_percent stays under 20% for months, skip the repack. Schedule during low-traffic windows even though writes are not blocked; the I/O spike can slow queries on slower disks. A typical cron entry for weekend maintenance:
0 2 * * 6 psql -d production -c "REPACK TABLE orders CONCURRENTLY;" >> /var/log/postgres/repack.log 2>&1
This runs at 2 AM every Saturday. Redirect output to a log file and check it for errors. Avoid repacking during peak hours on systems with spinning disks or limited I/O capacity. On SSD-backed databases with spare CPU, daytime repacks are often fine. Test on staging first. Set max_parallel_maintenance_workers = 2 during business hours to limit CPU usage:
SET max_parallel_maintenance_workers = 2;
REPACK TABLE orders CONCURRENTLY;
This keeps index rebuilds from consuming all cores. At night, raise it to 4 or 8 for faster completion. Monitor disk I/O with iostat -x 5 during the first few repacks to understand the impact on your hardware.
Combining REPACK with ANALYZE and Index Maintenance
Always run ANALYZE after REPACK. The planner statistics are stale because the table was rebuilt with a new layout:
REPACK TABLE orders CONCURRENTLY;
ANALYZE orders;
Without ANALYZE, the planner may choose poor query plans based on old row counts and distribution statistics. Run ANALYZE immediately after the repack completes; it takes seconds to minutes even on large tables. REPACK rebuilds all indexes by default, so separate REINDEX CONCURRENTLY is usually not needed. If you excluded specific indexes with REPACK TABLE orders (INDEX ...), rebuild the remaining ones manually:
REINDEX INDEX CONCURRENTLY orders_updated_at_idx;
A complete maintenance checklist: check bloat with pgstattuple or pg_stat_user_tables, verify at least 2x free disk space with df -h, set maintenance_work_mem = '2GB' or higher, run REPACK, verify size reduction with pg_total_relation_size, run ANALYZE, check query plans with EXPLAIN ANALYZE. For ongoing performance tuning, see PostgreSQL Performance Tuning in 2026 for a complete guide to planner statistics and index maintenance.
Measuring REPACK Impact: Before and After Metrics
Capture size metrics before starting. Run these queries and save the output:
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
pg_size_pretty(pg_relation_size('orders')) AS table_size,
pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;
Also capture bloat from pgstattuple:
SELECT dead_tuple_percent, free_percent FROM pgstattuple('orders');
Example before metrics on a heavily updated table: total_size: 50 GB, table_size: 32 GB, indexes_size: 18 GB, dead_tuple_percent: 45%. Run REPACK, then repeat the queries. Realistic after metrics: total_size: 22 GB, table_size: 15 GB, indexes_size: 7 GB, dead_tuple_percent: 2%. The table shrank from 50 GB to 22 GB, a 56% reduction. Dead tuples dropped from 45% to 2%.
Measure query performance with EXPLAIN ANALYZE before and after. A sequential scan on the bloated table:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';
-- Before: Buffers: shared hit=409600 read=0
After REPACK:
-- After: Buffers: shared hit=163840 read=0
Buffer reads dropped from 409,600 pages to 163,840, a 60% reduction. Queries are faster immediately because fewer pages need scanning. Note that freeing space does not shrink the filesystem until the OS reclaims it; df -h may still show the old size for a few minutes. The improvement in query performance is instant. Typical timing for a 10 GB table with 100,000 writes per hour: 15-minute repack, final swap lock held 1.2 seconds. Most applications see no impact beyond slightly higher I/O during the copy phase.
REPACK Checklist and Next Steps
Before running REPACK in production, verify this checklist: check bloat with SELECT * FROM pgstattuple('tablename'); and confirm dead_tuple_percent is over 30%. Verify free disk space with df -h; you need at least 2x the table size. Set maintenance_work_mem = '2GB' or higher for large tables. Set max_parallel_maintenance_workers = 4 for faster index builds. Run REPACK TABLE tablename CONCURRENTLY; during a low-traffic window. Monitor progress with SELECT * FROM pg_stat_progress_cluster WHERE command = 'REPACK';. After completion, run ANALYZE tablename; to update planner statistics. Measure size reduction with SELECT pg_size_pretty(pg_total_relation_size('tablename'));. Compare query performance with EXPLAIN ANALYZE before and after.
Schedule monthly repacks for high-churn tables, quarterly for others. Automate bloat detection with a monitoring script that queries pg_stat_user_tables and alerts when dead_pct exceeds 30%. For tables over 100 GB, test REPACK on a replica first to measure timing and resource usage. If you are migrating from pg_repack, run both tools side by side on staging to confirm identical results before switching production workloads. For deeper coverage of vacuum tuning, autovacuum settings and long-term maintenance strategies, see PostgreSQL Administration & Performance Tuning.
Frequently Asked Questions
Does REPACK CONCURRENTLY block SELECT queries?
No. REPACK holds a ShareUpdateExclusiveLock during the copy phase, which permits all SELECT, INSERT, UPDATE and DELETE operations. Only schema changes like ALTER TABLE are blocked. The final swap takes an AccessExclusiveLock for under 2 seconds, briefly pausing new queries while existing transactions finish. Most applications never notice this short pause.
How much free disk space does REPACK need?
At least the current table size, plus 20% for indexes and temporary sort files. A 50 GB table requires 60 GB free during the operation. Check with df -h before starting. If the disk fills mid-repack, PostgreSQL aborts cleanly and the original table remains intact. The shadow table is dropped automatically or can be removed manually.
Can I cancel a running REPACK without data loss?
Yes. Press Ctrl-C in psql or run SELECT pg_cancel_backend(pid); from another session. The original table is never modified during the copy phase, so cancellation is safe. The shadow table and triggers are cleaned up automatically. You can retry REPACK immediately after canceling. No data is lost and writes continue throughout.
Should I run ANALYZE after REPACK?
Always. REPACK rebuilds the table with a new physical layout, making planner statistics stale. Run ANALYZE tablename; immediately after REPACK completes. Without it, the planner may choose poor query plans based on old row counts and distribution data. ANALYZE takes seconds to minutes even on large tables and ensures optimal query performance.
How is REPACK different from VACUUM FULL?
VACUUM FULL takes an AccessExclusiveLock for the entire operation, blocking all reads and writes. REPACK CONCURRENTLY allows all queries and writes to continue, taking a brief lock only during the final swap. Both rebuild the table and reclaim disk space. Use REPACK for production tables where downtime is not acceptable. VACUUM FULL is faster on small tables where a few minutes of downtime is tolerable.