Why is pg_dump Smaller Than Database Size? PostgreSQL Index Bloat Explained

If you’ve ever run pg_dump on a production PostgreSQL database and watched a multi-gigabyte database shrink down to a fraction of its size in the dump file, you’ve probably had a moment of panic. Did you lose data? Is the dump incomplete? In our experience running production Postgres databases for Rails applications at Essence Solusoft, the answer is almost always the same: index bloat, not data loss. This post explains what’s actually happening, verifies it against real cases, and walks through the cleanup steps to reclaim that disk space.

The Symptom: A Database That’s “Bigger” Than It Should Be

Here’s the pattern: your production database reports several gigabytes of disk usage. You run pg_dump, and the resulting file (even accounting for compression) is dramatically smaller. You check your row counts, your record counts match, nothing is missing. Yet the gap between your live database size and your dump size can run into gigabytes on larger databases, which is unsettling if you’re trying to reconcile storage costs or plan capacity.

This isn’t a hypothetical. It’s a documented, recurring issue on the official PostgreSQL mailing list. In one real case, a user reported a roughly 30GB database producing a pg_dump of only about 2GB, a 15x difference. The response from the list nailed the cause: tables can accumulate free (dead) tuples from updates and deletes that VACUUM makes reusable internally but does not return to the operating system, and indexes on that same table can become significantly bloated on top of that. In a separate case on the same mailing list, a database dropped from 165MB to 30MB immediately after running REINDEX, even though a VACUUM FULL had already been run beforehand, showing that vacuuming the table doesn’t necessarily deal with the bloat sitting in the indexes.

Why This Happens: MVCC and Dead Tuples

PostgreSQL uses MVCC (Multi-Version Concurrency Control) to let readers and writers work without blocking each other. When you UPDATE or DELETE a row, Postgres doesn’t overwrite or immediately remove the old version, it writes a new version (for updates) and marks the old one as dead. Autovacuum comes along later and reclaims that dead space for reuse within the table or index, but by default it does not shrink the file on disk and hand that space back to the operating system.

The same thing happens inside indexes specifically. Postgres indexes are stored as fixed-size 8KB pages. When rows are deleted or updated, the corresponding index entries become dead too, and they sit on the page taking up space until an index vacuum or a full reindex clears them out. Over time, especially on tables with heavy update/delete traffic, this can leave an index taking up significantly more disk space than the data it’s indexing actually requires.

Why pg_dump Doesn’t Show the Bloat

This is the part of the claim worth being precise about. pg_dump doesn’t “skip” dead index entries as some kind of special optimization, it never touches physical bloat in the first place, because pg_dump produces a logical export. For each table, it writes out the live rows (via COPY or INSERT statements) and, for each index, it writes out the definition (the CREATE INDEX statement), not the physical index file. When you restore that dump, Postgres builds every index from scratch against the restored data, which means the restored copy has zero bloat, regardless of how much the original had accumulated.

That’s exactly why your record counts stay consistent between the live database and the dump: pg_dump was only ever exporting logical data. The size difference you’re seeing lives entirely in physical storage artifacts, dead tuples and bloated index pages, that a logical dump was never going to include.

How to Confirm It’s Bloat (Not Something Else)

Before you go straight to cleanup, verify what’s actually consuming the space so you don’t run a heavy maintenance operation for nothing.

Compare table size vs. total size including indexes:

SELECT relname AS table_name,

       pg_size_pretty(pg_table_size(relid)) AS table_size,

       pg_size_pretty(pg_total_relation_size(relid) – pg_table_size(relid)) AS index_size

FROM pg_stat_user_tables

ORDER BY pg_total_relation_size(relid) DESC

LIMIT 20;

Find indexes that are getting zero use (candidates for dropping outright, not just reindexing):

SELECT schemaname, relname AS table_name, indexrelname AS index_name,

       idx_scan, idx_tup_read, idx_tup_fetch,

       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size

FROM pg_stat_user_indexes

WHERE idx_scan = 0

ORDER BY pg_relation_size(indexrelid) DESC;

A word of caution here: an index with idx_scan = 0 isn’t automatically safe to drop. It may be enforcing a unique or primary key constraint, it may support a report that only runs monthly or quarterly, and if you’re checking a replica, an index that looks unused on the primary might still be needed there. Check your stats reset time, check all nodes taking traffic, and confirm the index isn’t backing a constraint before dropping anything.

For a precise (not estimated) bloat measurement, the pgstattuple extension gives you exact numbers rather than statistical guesses:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatindex(‘your_index_name’);

Cleaning Up: Steps to Free the Space

1. Reindex bloated indexes

REINDEX rebuilds an index from scratch, discarding all the dead entries and fragmented pages, and writes a fresh, tightly packed index in their place. On a live production system, use the concurrent variant so the index stays available for reads and writes during the rebuild:

REINDEX INDEX CONCURRENTLY your_index_name;

Or for every index on a table:

REINDEX TABLE CONCURRENTLY your_table_name;

Plain REINDEX (without CONCURRENTLY) is faster but takes a stronger lock, so reserve it for a maintenance window rather than running it against a busy production table. Regular reindexing is inexpensive relative to the disk and I/O savings, and is worth scheduling periodically rather than only reacting to a bloat crisis.

2. Drop indexes that are genuinely unused

Once you’ve confirmed via pg_stat_user_indexes (and checked constraints and replicas) that an index isn’t serving any purpose, drop it directly:

DROP INDEX CONCURRENTLY your_unused_index_name;

Using CONCURRENTLY here too avoids locking out writes to the table while the index is removed. This is often where the biggest, easiest wins are: duplicate indexes on the same columns and indexes left behind after a schema or query pattern change are more common than most teams expect.

3. Reclaim table-level bloat with VACUUM FULL or pg_repack

If the bloat is in the table itself rather than (or in addition to) its indexes, plain VACUUM won’t shrink the file on disk, it only marks space as reusable internally. To actually return space to the OS, you need either:

• VACUUM FULL your_table_name; — rewrites the entire table into a new file and rebuilds its indexes in the process. This is effective but takes an exclusive lock on the table for the duration, meaning reads and writes are blocked. Reserve this for maintenance windows on tables you can afford to lock.
• pg_repack — a widely used extension that achieves the same result (a clean rewrite of the table and its indexes) with far less locking, by building a new copy alongside the original, tracking ongoing changes via triggers, and swapping the tables atomically once caught up. This is generally the safer choice for tables that can’t tolerate downtime.

4. Automate the monitoring, not just the fix

One-off cleanups solve the immediate problem, but bloat comes back if the underlying write pattern doesn’t change. Track index and table bloat as part of regular monitoring (via pgstattuple, the pg_stat_user_indexes queries above, or established bloat-estimation queries from tools like the ioguix bloat scripts), so you catch bloat trending upward before it becomes a multi-gigabyte surprise the next time someone runs pg_dump and asks why the numbers don’t match.

Practical Takeaways

• The size mismatch between your live database and pg_dump is expected behavior, not a red flag on data integrity. If your record counts match, your data is fine, the gap is physical bloat that a logical export never carries.
• Don’t assume VACUUM FULL alone fixes bloated indexes. Real cases show a database shrinking further after REINDEX even post-VACUUM FULL, because table vacuuming and index bloat are separate problems.
• Use REINDEX CONCURRENTLY and DROP INDEX CONCURRENTLY in production to avoid downtime; save plain REINDEX and VACUUM FULL for maintenance windows.
• Verify before dropping. A zero-scan index isn’t automatically dead weight, check constraints, replicas, and stats-reset timing first.
• Treat bloat monitoring as ongoing maintenance, not a one-time fire drill, especially on high-churn tables.

Why This Matters for Rails Teams Running Postgres

Most Ruby on Rails applications rely heavily on PostgreSQL for storing and managing application data, and Active Record’s habit of adding indexes liberally (for every foreign key, every find_by, every uniqueness validation) means index bloat and unused indexes can quietly build up over a project’s lifetime. 

At Essence Solusoft, database size audits are part of how we review client infrastructure, and this exact mismatch, a database that “looks” alarmingly large until you actually inspect what’s live versus what’s bloat, is one of the more common false alarms we help clients work through, right alongside the genuine cleanup opportunities it often uncovers.

Need a PostgreSQL performance or storage audit for your Rails application? Get in touch with Essence Solusoft.