Reaching for pg_repack, pg_squeeze, or VACUUM FULL first is usually the wrong move , change my mind

Uncategorized
1

Reclaiming disk space when you’re already out of disk space ; what’s the right order of operations?

This comes up often enough that I want to hear how others actually handle it, because the standard advice has a chicken-and-egg problem.

The scenario: heavily bloated tables from accumulated dead tuples, plus a pile of indexes that may or may not be earning their keep, and not much free space on the volume. The usual recommendations are pg_repack, pg_squeeze, or VACUUM FULL but all three want temporary space you don’t have. pg_repack in particular needs a new table, new indexes, and a log table for changes during the copy, so peak usage can exceed VACUUM FULL. You reach for it to avoid the ACCESS EXCLUSIVE lock and get surprised by the disk cost instead.

A few things I’d like to argue out:

1. Is dropping unused indexes always the correct first move? It’s the only step that returns space with no temporary copy. But idx_scan = 0 in pg_stat_user_indexes isn’t proof stats reset, replicas have their own counters, and constraint-backing indexes can look idle. What’s your bar before you actually drop one?

2. Does REINDEX buy you anything real here, or is it a distraction? It does nothing for heap bloat, so it can’t replace a repack. But it’s granular , one index at a time, so you only need space for the largest single index rather than the whole relation. Is incremental reclaim through REINDEX (or REINDEX CONCURRENTLY) a legitimate strategy when you’re constrained, or just delaying the inevitable?

3. When is plain VACUUM the actual answer? It won’t return space to the OS except by truncating trailing pages, but it makes the space reusable and stops further file growth. How often is the real problem “this table keeps growing” rather than “this table is bloated”?

4. What’s holding the xmin horizon? If dead tuples accumulate faster than vacuum reclaims them, repacking is a treadmill. Long-running transactions, inactive replication slots, orphaned prepared transactions. An abandoned slot from a decommissioned replica seems like the most common culprit , how often does this turn out to be the root cause rather than the tooling?

5. Does pg_repack --tablespace solve the constraint in practice? Repacking into a different mount sidesteps the space problem if you have another volume available. Is anyone using this routinely, or does the I/O cost make it impractical?

My instinct is: drop genuinely unused indexes, fix whatever is blocking vacuum, then REINDEX incrementally, and only reach for VACUUM FULL when you can take the outage and can’t get space anywhere else. Interested in where people disagree.

2

@Dba1

1. Is dropping unused indexes always the correct first move?

Identifying and removing both duplicate and unused indexes is a good practice, especially if you are dealing with storage or performance issues.

Maybe initially you can target a few potential tables that contribute to the large size and havng underforming query workloads as well.

Its always a best practice to perform such operations in a lower/non-production environment and assess the query behaviour before running in production.

2. Does REINDEX buy you anything real here, or is it a distraction?

Reindexing helps when you notice the Tables/Indexes bloating heavily or the index build failing due to concurrent runs, which leave the index invalid.

Additionally, if you see any signs of Index corruption, or if the query is misbehaving or the optimiser is not using the expected Index, it might be a good idea to try Reindexing.

Well, combining both VACUUM + REINDEX would be a better choice for handling dead tuples, table/index bloating, reindexing, etc.

It’s an impacted operation that would lock the targeted tables during reindex execution. For minimal impact or locking, you can test with REINDEX … CONCURRENTLY as well.

3. When is plain VACUUM the actual answer?

The VACUUM process helps remove dead tuples, so the space they occupy can be reused by the table for future inserts/updates. This space is not reclaimed to disk but can be reused for other subsequent transactions.

If autovacuum is enabled, PostgreSQL self-heals and prevents the database from becoming more bloated and fragmented.

pg_repack can also be a good choice for removing bloat from tables and indexes, and restoring the physical order of clustered indexes. It required a comparatively shorter duration lock when compared with the Vacuum Full operation.

Below, you can read more about this tool’s use case or caveats before consider running it.

4. What’s holding the xmin horizon?

To deal with faster accumulation of dead tuples, it’s suggested to enable autovacuum, which is more vigilant in detecting such increases and fixing them accordingly without manual intervention.

You are right that long-running Trx’s and inactive slots can halt the old row versioning cleanup, but here we need to monitor exactly what is causing the problems. The pg_gather tool (GitHub - jobinau/pg_gather: Scan PostgreSQL Instance for potential problems. pg_gather is a SQL-only script leveraging the built-in features of psql. · GitHub) can be very handy for fetching dead tuples/bloat, long-running transactions, dead replication slots, and much more information to make a conclusive decision.

5. Does pg_repack --tablespace solve the constraint in practice?

It depends on your needs; sometimes you might need to move your data to slower or faster disks to improve performance or meet other business requirements. So rebuilding (moving) the table and indexes into a different tablespace can be less impactful using pg_repack --tablespace option.

I think this is a more practical way or has a lower impact compared to other options, ALTER TABLE .. SET TABLESPACE or REINDEX, etc.

But again, each use case/workload, or database environment is different, so whatever option you choose, evaluate or well-test it in a lower/staging environment before even considering it for production.

3

My two cents; The order of operations needed to recover space using pg_repack when you have very little space.

  1. create an ordered list by size of indexes starting with the smallest
  2. create an ordered list by size of tables starting with the smallest
  3. Method 1: create a script, repack one object at a time
    1. starting with the smallest index perform perform a repack
    2. continue with tables starting with the smallest ones
  4. Method 2: create a script
    1. use pgrepack to “move” the objects in question to another partition via table spaces

Hope this helps.

Robert