pg_repack Guide: Efficiently Resolving PostgreSQL Bloat with Zero Downtime – ITFROMZERO

Table of Contents

When the Hard Drive Stays Full Despite Deleting Millions of Records

Imagine this scenario: Your database alerts you that storage is running low. You quickly run a DELETE command to clear 200GB of old log data. However, the disk usage remains stuck at 500GB, as if nothing happened. I once encountered this situation while managing a large transaction system. The actual data was only 300GB, but the OS still reported 500GB of usage. This is the classic Bloat phenomenon in PostgreSQL.

Many people’s first instinct is to run VACUUM FULL. Don’t do that in Production! This command takes an Access Exclusive Lock on the table. Your entire application will hang, and every read/write query will be queued. For large tables, this process can take hours.

To resolve this thoroughly without service disruption, pg_repack is the lifesaver that every database engineer needs to master.

Why Does PostgreSQL Get Bloat?

The root of the problem lies in the MVCC (Multi-Version Concurrency Control) mechanism. When you UPDATE, Postgres doesn’t overwrite the old row. It marks the old row as “expired” (a dead tuple) and inserts a new one. The DELETE command works similarly, only marking the row as deleted without immediately reclaiming disk space.

The Autovacuum process cleans up these dead tuples. However, it only allows Postgres to reuse that space for new data; it doesn’t return the capacity to the operating system. If you delete a large amount of data suddenly, or if the table has an extremely high update frequency, the data file will bloat uncontrollably.

Comparing Traditional Solutions

Before using pg_repack, let’s look back at the limitations of familiar methods:

VACUUM: Only performs internal cleanup within the table. Disk space is not reduced.
VACUUM FULL: Efficiently reclaims disk space but causes table locking (Downtime).
CLUSTER: Reorganizes data based on an index, also causing table locking similar to VACUUM FULL.

How Does pg_repack Work?

pg_repack allows you to restructure tables and indexes without long-term table locking. You can still SELECT, INSERT, UPDATE, and DELETE normally while it works.

The process consists of 5 steps:

Create a shadow table containing all data from the original table.
Set up a trigger on the original table to record all subsequent changes into a log table.
Rebuild indexes on the new shadow table.
Apply all changes from the log table to the shadow table to synchronize data.
Swap the table names in the system catalog and drop the old table. This step takes only a few milliseconds.

Installation and Practical Usage Guide

You need to install pg_repack at both the operating system level and enable the extension within the database.

1. Tool Installation

For PostgreSQL 15 on Ubuntu, use the following command:

sudo apt-get update
sudo apt-get install postgresql-15-repack

Next, log in to the database and enable the extension:

CREATE EXTENSION pg_repack;

2. Checking the Bloat Level

Don’t rush to repack everything. Prioritize tables with a bloat ratio over 20%. You can use the pg_bloat_check script to scan the entire system. If a 100GB table has 40GB of bloat, it’s time to act.

3. Executing Commands from the Terminal

Note: You run pg_repack from the command line, not within psql.

Repack a specific table:

pg_repack -h localhost -U postgres -d shop_db -t orders

Optimize Indexes only (saves more time):

pg_repack -h localhost -U postgres -d shop_db -t orders --only-indexes

Dry run to check:

pg_repack -h localhost -U postgres -d shop_db -t orders --dry-run

Important Considerations for Production Deployment

After optimizing TB-scale tables many times, I’ve gathered 4 key considerations:

Free Disk Space: You need at least enough free space equal to the size of the original table + indexes. If the table is 300GB and the hard drive has less than 350GB remaining, do not run repack as it will cause a disk overflow.
I/O Resources: The data copying process consumes significant I/O. Perform this during off-peak hours (e.g., 2 AM) to avoid impacting user experience.
Primary Key Requirement: pg_repack requires the table to have a Primary Key or a Unique Index (Not Null). Otherwise, the tool will not work.
Long Transactions: If another query is running for too long on that table, pg_repack will be unable to perform the final swap step.

Conclusion

Managing Bloat is a mandatory task when operating large-scale PostgreSQL. pg_repack helps you reclaim disk space, speed up queries, and keep the system available 24/7. Always test in a Staging environment before applying to Production to best control resource risks.