Index bloat: the storage line hiding inside your database size

Quick answer

Indexes commonly account for 40 to 60 percent of a PostgreSQL database's total size, and on write-heavy tables index bloat of 30 to 50 percent is routine. On a 2 TB database that means roughly 1 TB of indexes carrying perhaps 350 GB of bloat, about $40 per month at gp3's $0.115 per GB-month, plus the buffer pool it wastes. Two cheap wins dominate: drop redundant indexes where a composite index on (a, b) makes a separate index on (a) unnecessary, and run REINDEX CONCURRENTLY on bloated indexes, which rebuilds them compactly without blocking writes. A typical first pass reclaims 15 to 30 percent of total database size.

When a database grows, people look at tables. The indexes are frequently the larger half, and unlike table data, a substantial part of index size is often pure waste: bloat from page splits and dead entries, duplicate indexes created by different engineers for the same query, and indexes covering queries that no longer exist.

How big indexes actually get

Workload	Typical index-to-table ratio	Typical bloat
Read-heavy reporting schema	0.8 to 1.5x	5 to 15 percent
Transactional OLTP	0.6 to 1.2x	20 to 35 percent
High-churn queue or state table	1.0 to 2.5x	40 to 70 percent
Append-only event log	0.3 to 0.6x	under 10 percent

The queue table case is the one that gets out of hand. A table where rows are inserted, updated through a few states, and deleted produces constant index churn. B-tree pages split, entries are marked dead, and because PostgreSQL only reclaims a page when it becomes entirely empty, an index on such a table can end up several times larger than the live entries justify. It is common to find a 40 GB index on a table holding 200,000 live rows.

Measuring it

Three queries give you the whole picture. First, pg_relation_size on each index compared with the table size, to see the ratio. Second, the pgstattuple extension's pgstatindex function, which reports avg_leaf_density; anything below 60 percent on a B-tree indicates meaningful bloat, against a healthy figure of 85 to 90 percent after a rebuild. Third, pg_stat_user_indexes for idx_scan counts, to find indexes nothing uses.

Do not skip the third. Unused indexes are the cleanest saving available: they cost storage, they slow every write, and removing them has no query-side downside. Just confirm your measurement window covers monthly and quarterly reporting jobs before dropping anything, and remember that indexes enforcing unique constraints show low scan counts while still being essential.

Redundant indexes

Existing indexes	Redundant one	Why
(tenant_id), (tenant_id, created_at)	(tenant_id)	Composite serves the prefix
(status), (status, priority, id)	(status)	Composite serves the prefix
(email) unique, (email)	the non-unique one	Unique constraint already indexes it
(a, b), (b, a)	neither, usually	Different leading columns serve different queries

The leading-column rule is the one worth internalizing: a B-tree on (a, b, c) can serve queries filtering on a, on a and b, and on a, b, and c. A separate index on (a) is therefore pure duplication. On a large table that single observation can free tens of gigabytes. The last row is the counterexample: (a, b) and (b, a) are not redundant, because only the leading column supports a range or equality lookup efficiently.

What reclaiming is worth

Take a 2 TB database where indexes are 1 TB. Suppose measurement finds 280 GB of bloat, 90 GB in three redundant indexes, and 60 GB in five indexes nothing has scanned in six months. Reclaiming all of it removes 430 GB.

Effect	Value
Storage at $0.115/GB-mo	about $49/mo
Backup storage overage at $0.095/GB-mo	about $41/mo
Buffer pool freed	possible one-size downsize, up to $378/mo
Write throughput	fewer index maintenance operations per write
Backup and restore time	proportionally shorter

The direct storage figure is small. The buffer pool effect is what makes this worth doing: 430 GB less data competing for cache can be the difference between a 99.9 percent and a 97 percent cache hit ratio, which on a busy database is a large latency and I/O difference.

Rebuilding without downtime

REINDEX CONCURRENTLY, available since PostgreSQL 12, rebuilds an index without blocking reads or writes. It needs enough free space for a second copy of the index during the rebuild, and it is slower than a blocking REINDEX, but for production systems it is almost always the right choice. On MySQL, ALTER TABLE with ALGORITHM=INPLACE achieves a similar result for InnoDB secondary indexes.

The important caveat, again: reclaiming space inside the database does not shrink a managed volume whose allocated storage has already autoscaled. The freed space becomes reusable, which stops further growth, but capturing the storage saving on the invoice requires migrating to a smaller allocation afterwards. Plan the sequence as reindex, drop redundant and unused indexes, verify the new size, then migrate to a right-sized volume.

Preventing recurrence

Require a justification for each new index in code review, naming the query it serves. Check for prefix redundancy before adding a composite. Schedule REINDEX CONCURRENTLY on your handful of high-churn tables as a routine monthly job rather than a heroic annual cleanup. And revisit the unused-index list quarterly, because indexes added for a feature that was later removed are the most reliable source of new waste.

Price allocated storage from Terraform so index growth shows up as a reviewed cost. Compare storage options against the resource catalog, and see indexing cost and performance for the query side of the trade.

FAQ

How much of a database is indexes?

Commonly 40 to 60 percent of total size in PostgreSQL. The ratio varies by workload: read-heavy reporting schemas run 0.8 to 1.5 times table size, transactional OLTP 0.6 to 1.2 times, high-churn queue or state tables 1.0 to 2.5 times, and append-only event logs 0.3 to 0.6 times. High-churn tables also carry the worst bloat, often 40 to 70 percent.

How do I measure index bloat?

Use the pgstattuple extension's pgstatindex function and look at avg_leaf_density: anything below 60 percent on a B-tree indicates meaningful bloat, against 85 to 90 percent after a rebuild. Compare pg_relation_size for each index against its table for the ratio, and check idx_scan in pg_stat_user_indexes to find indexes nothing uses.

Which indexes are redundant?

Any index whose columns are a leading prefix of another index. A B-tree on (a, b, c) serves queries filtering on a, on a and b, and on all three, so a separate index on (a) is pure duplication. A non-unique index duplicating a unique constraint is also redundant. But (a, b) and (b, a) are not redundant, because only the leading column supports efficient lookups.

What is index cleanup worth?

On a 2 TB database with 1 TB of indexes, a typical pass reclaims 430 GB from bloat, redundant indexes, and unused ones. That is about $49 per month in storage and $41 in backup overage, but the larger effect is buffer pool: 430 GB less data competing for cache can move a hit ratio from 97 to 99.9 percent, potentially enabling a one-size downsize worth up to $378 per month.

Can I rebuild indexes without downtime?

Yes. REINDEX CONCURRENTLY, available since PostgreSQL 12, rebuilds an index without blocking reads or writes, at the cost of needing free space for a second copy during the rebuild and running slower than a blocking REINDEX. On MySQL, ALTER TABLE with ALGORITHM=INPLACE achieves a similar result for InnoDB secondary indexes.

How does C3X help with index storage cost?

C3X prices allocated storage from Terraform, so the volume growth that index bloat drives appears as a reviewed cost change in the pull request. Because reclaiming space inside the database does not shrink an already-autoscaled managed volume, the sequence that actually captures the saving is reindex, drop redundant indexes, then migrate to a smaller allocation you have priced in advance.

What to do next

Keep index growth from becoming permanent storage cost. C3X reads your Terraform and prices your resources against a live catalog. Start with the quickstart.