PostgreSQL Index Bloat: Why Small Tables Can Have Huge Indexes

🌐 Language: English Version | 中文版

During a production database capacity review, we ran into a typical PostgreSQL index bloat case: several tables did not contain much live data, and one table had no live rows at all, yet their index files still occupied a large amount of disk space. Ordinary VACUUM had already run. n_dead_tup did not look alarming. The database files, however, did not shrink.

This kind of issue is easy to misread as “the database needs cleanup.” The real question is more specific: is the space pressure coming from heap bloat or index bloat? This article focuses on the diagnosis path: why the issue happens, why VACUUM did not fix it, what can stop the bleeding in the short term, and what should change in the write model over time.

TL;DR

• Ordinary PostgreSQL VACUUM can clean dead tuples in heap pages and make that space reusable, but it does not automatically shrink already-bloated B-tree index files.
• When table data is small but indexes are huge, first separate heap bloat from index bloat. Looking only at n_dead_tup is not enough.
• Frequently deleted, rebuilt, and batch-updated derived tables can create long-term index write amplification when they are stored as heavily indexed primary business tables.
• Short-term remediation usually means dropping confirmed-unused indexes and rebuilding bloated ones with REINDEX CONCURRENTLY.
• Long-term remediation must change the write model: use staging + diff instead of broad DELETE + bulk_create, and make index lifecycle review part of engineering practice.

1. The Symptom: Small Tables, Huge Indexes

The first step in a capacity investigation is not to run a cleanup script. It is to split table data size from index size.

SELECT
    c.relname AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_data,
    pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS indexes,
    c.reltuples::bigint AS approx_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY pg_total_relation_size(c.oid) DESC;

The shape of the problem looked roughly like this:

Table type	Total size	Table data	Indexes	Estimated rows
Periodic derived table	50+ GB	A few MB	50+ GB	Tens of thousands
Rule detail table	Several GB	Hundreds of KB	Several GB	Thousands
Execution record table	Hundreds of MB	0 bytes	Hundreds of MB	0
Extended attribute table	Hundreds of MB	Hundreds of KB	Hundreds of MB	Thousands

That shape tells us the issue is not “too much live business data.” Historical write behavior has inflated index files.

Then check autovacuum statistics:

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    autoanalyze_count,
    vacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

This is where misdiagnosis often starts. Some tables may already have n_dead_tup = 0, so it is tempting to conclude that autovacuum has handled the bloat. But if disk usage is still high, the main issue is probably not the heap. It is the indexes.

Next, inspect index size and usage:

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 30;

Common findings:

• A single B-tree index can grow to tens of GB.
• Some indexes have idx_scan = 0 but still occupy significant disk space.
• Some tables may be empty while their indexes still exist on disk.
• Primary key indexes, time-based indexes, and ORM-generated foreign-key indexes can all be inflated by historical churn.

At this point, continuing to run ordinary VACUUM (ANALYZE) usually will not produce the expected result.

2. Why Ordinary VACUUM Did Not Shrink the Indexes

PostgreSQL uses MVCC. An UPDATE does not overwrite a row in place. It writes a new row version and marks the old version as no longer visible. A DELETE also does not immediately return physical space to the operating system. It first creates reclaimable space.

The main job of ordinary VACUUM is to clean dead tuples in heap pages and make that space reusable inside the same table file. It can also remove index entries pointing to dead tuples. What it does not do is automatically shrink an already-expanded B-tree index file back to a small file.

So the database can reach this state:

• n_dead_tup = 0
• table data is only a few MB
• indexes are still tens of GB
• repeated VACUUM runs do not materially reduce disk usage

That does not mean VACUUM failed. It means VACUUM solved a different problem from the one we were facing.

If the goal is to shrink bloated index files, typical options are:

• REINDEX INDEX CONCURRENTLY index_name
• REINDEX TABLE CONCURRENTLY table_name
• a table/index rewrite tool such as pg_repack

VACUUM FULL can also rewrite objects and reclaim space, but it requires heavier locking and is usually not the first choice for online systems. Since PostgreSQL 12, REINDEX CONCURRENTLY is a practical option for online index remediation. It still consumes IO, and the old and new indexes coexist for a period of time, so disk headroom must be checked before execution.

3. Root Cause: High-Churn Derived Data Stored Like Stable Primary Data

The root cause is rarely one database parameter. It is usually a mismatch between the write model and the storage model.

Consider derived data such as reporting results, rule-hit details, execution windows, and temporary computation outputs. Their characteristics are often similar:

• upstream data refreshes periodically
• configuration changes can trigger broad recalculation
• historical periods expire
• calculation rules may change and require recomputing a future range

This data is naturally high churn. But in many systems it is stored as long-lived primary data, with many indexes added for read paths. The write model then becomes:

Delete derived records for a target object over a future range
Bulk create new period records
Bulk create detail records
Bulk create associated state records

Or:

Calculation rule changes
Clear the old derived structure
Regenerate according to the new configuration

At a small scale this is simple and usually correct. Under PostgreSQL MVCC, however, running this pattern for a long time continuously creates dead tuples and index garbage. The more important point is write amplification: every extra B-tree index must be maintained during deletes, inserts, and updates. The application thinks it is only rebuilding a derived result set. The database is maintaining multiple indexes for every rewrite.

This is why simply making autovacuum more aggressive is not enough. Autovacuum can reduce accumulation. It cannot remove the root cause of storing high-churn derived data as heavily indexed long-lived primary data.

4. Another Root Cause: ORM Defaults Plus Accumulated Business Indexes

Another common source is that indexes are not designed as a whole. They accumulate over time.

For example:

• an ORM creates a single-column index for a foreign-key field
• a business query adds a composite index
• an ordering column gets db_index=True
• a unique constraint creates its own index
• a historical LIKE query adds another index
• the query pattern changes later, but the old index is never removed

A high-write table may end up with indexes like:

(entity_id)
(rule_id)
(effective_date)
(entity_id, rule_id)
(entity_id, effective_date)
(entity_id, status, effective_date)
PRIMARY KEY (id)
UNIQUE (entity_id, attribute_id, value_id)

Some of these indexes may still serve hot queries. Some may be historical leftovers. Some may be covered by the prefix of a composite index.

Do not drop an index only because idx_scan = 0. At minimum, check whether statistics have just been reset:

SELECT datname, stats_reset
FROM pg_stat_database
WHERE datname = current_database();

Also confirm:

• whether the index backs a unique constraint or primary key
• whether it is required by foreign-key maintenance
• whether low-frequency but critical background jobs use it
• whether ORM-generated query paths depend on it
• whether an existing composite index already covers it
• whether key queries regress under EXPLAIN

A safer workflow is to observe candidate indexes over a meaningful period, combine pg_stat_user_indexes with slow-query logs and EXPLAIN, and only then remove confirmed-unused indexes with DROP INDEX CONCURRENTLY.

5. Short-Term Remediation: Drop Unused Indexes and Rebuild Bloated Ones

Short-term remediation has three parts.

First, record the before state.

SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_before
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 20;

Also check long-running transactions and invalid indexes:

SELECT
    pid,
    usename,
    state,
    xact_start,
    now() - xact_start AS xact_age,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

SELECT
    c.relname AS index_name,
    i.indisvalid,
    i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid OR NOT i.indisready;

Second, drop confirmed-unused indexes:

DROP INDEX CONCURRENTLY IF EXISTS idx_unused_column;

CONCURRENTLY reduces the impact on normal reads and writes, but it still consumes database resources. Process a small number of indexes at a time and watch database IO and application metrics.

Third, rebuild bloated indexes online:

REINDEX INDEX CONCURRENTLY idx_large_active_index;

If multiple indexes on the same table are bloated, evaluate:

REINDEX TABLE CONCURRENTLY high_churn_table;

Operational notes:

• REINDEX CONCURRENTLY cannot run inside a transaction block.
• If it fails, it may leave an invalid _ccnew index that must be cleaned up before retrying.
• The old and new indexes coexist during the rebuild, so disk headroom is required.
• Reindexing creates IO pressure and should be batched during low-traffic windows.
• After rebuilding, compare object sizes and key query plans again.

Use the same query for before/after validation. Do not change the measurement after remediation:

SELECT
    c.relname AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_data,
    pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS indexes
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY pg_total_relation_size(c.oid) DESC;

Then sample core queries with EXPLAIN ANALYZE to ensure that index cleanup did not introduce obvious query regressions.

6. Medium-Term Governance: Give Indexes a Lifecycle

Index cleanup should not start only after disk alerts. A useful medium-term practice has four parts.

First, monitor index size and usage.

Track:

• individual index size
• table-data-to-index-size ratio
• idx_scan growth
• idx_tup_read / idx_tup_fetch
• pg_stat_database.stats_reset

If statistics were reset recently, idx_scan = 0 only means “not used since the reset.” It does not prove long-term non-use.

Second, tune autovacuum per high-churn table.

Global defaults are often conservative. For frequently updated tables, table-level reloptions can be more appropriate:

ALTER TABLE high_churn_table SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 1000
);

If updated columns are not indexed, consider fillfactor to leave room for HOT updates:

ALTER TABLE high_churn_table SET (
    fillfactor = 90
);

Third, create a low-traffic REINDEX procedure.

Not every index needs scheduled rebuilding. But indexes on known high-churn tables should have a review rule:

• bloat ratio crosses a threshold
• an individual index crosses a size threshold
• write/delete volume remains high
• a code change is planned and the table already shows bloat symptoms

Fourth, clean up temporary backup tables.

Many databases accumulate _backup, _bak, _copy, or date-suffixed tables. They may not cause index bloat directly, but they consume capacity and add noise to investigations. If a temporary backup table enters the main database, it needs a retention window, an owner, and a cleanup rule.

7. Long-Term Remediation: Reduce Write Amplification

Database-level REINDEX can fix already-bloated files. It cannot prevent recurrence. Long-term remediation has to change the write model.

For periodic derived data, staging + diff is usually a better pattern:

Upstream raw data
        |
        v
staging table or in-memory structure
        |
        v
Diff against current effective rows by business key
        |
        +-- New: insert
        +-- Changed: update
        +-- Missing: soft disable or small-batch cleanup
        +-- Same: no write

The key is to stop deleting and rebuilding a broad future range on every run. Only rows that actually changed should be written.

Configuration screens should follow the same idea. Saving configuration should distinguish:

• normal state, threshold, or priority changes: update by primary key or business key
• incompatible structural changes: clear and rebuild only when necessary
• expired historical data: archive or partition cleanup, not unbounded deletes on the primary table

If data has a natural time lifecycle, partitioning may also be worth evaluating:

• current and future periods stay in active partitions
• historical partitions become read-only or archived
• data beyond retention is removed with DROP PARTITION
• broad deletes on a large primary table are avoided

Finally, index design should enter engineering review. Each new index should answer:

• which interface or SQL does it serve
• what are the typical WHERE, JOIN, and ORDER BY clauses
• is it covered by the prefix of an existing composite index
• is it on a high-churn table
• how will it be verified after release with pg_stat_user_indexes and EXPLAIN
• who owns its removal if the query path disappears

Indexes are not free. They trade space and write cost for read performance. More indexes may be acceptable on read-heavy stable tables. On high-churn derived tables, every additional index is a persistent write-amplification multiplier.

8. Takeaways

The visible problem was PostgreSQL index bloat. The deeper issue was the combination of write-model design, ORM-generated defaults, accumulated business indexes, and missing operational lifecycle.

The short-term path is:

Find large objects
Separate heap size from index size
Confirm the boundary of ordinary VACUUM
Drop confirmed-unused indexes
REINDEX bloated indexes concurrently
Validate capacity and query plans

The long-term changes are more important:

• Do not store frequently rebuilt derived data as if it were stable primary data.
• Do not allow ORM defaults and business indexes to accumulate without review.
• Do not treat idx_scan = 0 as the only signal for dropping an index.
• Do not wait for disk alerts before maintaining database objects.
• Do not use broad DELETE + bulk_create when a diff-based write model would work.

When a table with only a few MB of data carries tens of GB of indexes, the issue is no longer just “clean up the database.” It is evidence that the data model, write path, index design, and maintenance process need to be designed together.