pg_repack operational overview

The extension works by:

1. Creating a log table and triggers on the original table to capture concurrent changes (INSERTs, UPDATEs, DELETEs).
2. Building a fresh, compact copy of the table alongside the original.
3. Streaming data from the original table to the new copy.
4. Rebuilding indexes on the new table.
5. Replaying captured changes from the log table onto the new copy to ensure data parity.
6. Performing an atomic swap using a brief ACCESS EXCLUSIVE lock to replace the old table with the new one.
7. Dropping the old table.

During this process:

• The original table and indexes remain available for both reads and writes, allowing the application to continue working
• A new table is populated by streaming data from the original
• New indexes are rebuilt on the new table
• A change log table captures changes during the rebuild so they can be replayed
• Extra Write-Ahead Logs (WAL) are generated because every byte written to the new copy is also written to WAL

 

Application downtime

No. The application can read and write to the database while pg_repack is running. Existing connections, transactions, prepared statements, and replication consumers continue to work.

There are two brief moments when pg_repack takes an exclusive lock on the specific table being processed:

1. At the start, when installing a trigger and change log table
2. At the end, when swapping the rebuilt table in place of the original

Both windows are typically sub-second on a healthy database.

 

Resource impact and performance observations

The following changes may be observed:

• A small increase in CPU and write IOPS on the database because the table is being copied
• The free storage metric drops during the run, then drops further (because the new copy lives next to the old one), and then recovers to a higher level than before

The following will not occur:

• Connection drops
• Failed queries
• A maintenance window banner

Disk space required

The database needs free storage at least as large as the single biggest table, including its indexes and TOAST (The Oversized-Attribute Storage Technique) storage, plus a safety margin.

It is the biggest single table that matters, not the sum of all tables. pg_repack rewrites one table at a time, so peak disk usage is driven by the largest table in the database, not the total database size.

To check the total relation size (heap + TOAST + indexes), use:

SELECT pg_total_relation_size('schema.tablename');

The 10% buffer covers the change log and WAL pressure during the run.

 

pg_repack execution time

The duration depends on the size of the table and database. As a rule of thumb, assume one minute per GB. The job is asynchronous. Once started, the application can continue normal operations.

 

Failure handling and data safety

The data is safe. pg_repack only swaps to the new table at the very end, after the copy is fully built and verified. If anything fails before that point, including a crash or running out of disk, the original table remains untouched and stays the live one. The application sees no impact.

 

Performance impact during execution

Queries may be slightly slower, but rarely in a way that is noticeable.

 

Supported PostgreSQL versions for pg_repack

Currently, only PostgreSQL 15.12 and 15.13 versions are supported.

 

Compatibility with replication and recovery features

Yes. pg_repack is compatible with read replicas, Multi-AZ deployments, and Point-in-Time Recovery.

 

Impact on row identifiers and data values

No. The data and row IDs remain unchanged.