The Bloat Busters: pg_repack vs pg_squeeze

submited by

Style Pass

2024-04-28 06:30:03

As the database size increases and the number of transactions per second rise, you'll inevitably face the challenge of the table bloat. Although PostgreSQL assists as much as possible with its auto-vacuum feature, there will come a time when you will compel whether to run FULL VACUUM. Unless you have option of longish downtime windows, this is not an easy decision.

Thankfully, the rich ecosystem of PostgreSQL offers more than one solution how to make it simpler. Ignoring older tools like pg_reorg, two contenders worth considering are pg_repack and pg_squeeze. This article dives into their strengths and weaknesses to help you decide which one is better for your specific use case.

In scenarios with a continuous and predictable transaction pattern, you can usually rely on auto-vacuum. However, there are use cases where this won't be sufficient. Such examples typically involve "bulk" operations—whether it's bulk imports, deletions, or a combination of both. Imagine scenarios where:

In all these cases, you might end up with a huge table and the traditional solution would be FULL VACUUM, which is associated with significant downtime due to table locking.