PostgreSQL index size surprises many teams in production. Indexes often grow close to table size, or larger, especially on write-heavy systems.

This article explains the main causes and practical actions to reduce index growth.

1. Why PostgreSQL Indexes Get Large

Main reasons:

• multiple indexes on overlapping columns
• high update rate creating dead tuples
• low-selectivity indexes with little query benefit
• wide index keys (long text, composite keys)
• missing maintenance (VACUUM, REINDEX when needed)

2. MVCC Impact on Index Growth

PostgreSQL uses MVCC. Updates create new row versions. Old versions remain until cleanup, and index entries also reflect this churn.

Implications:

• write-heavy tables can accumulate index bloat quickly
• autovacuum tuning directly affects index growth behavior

3. Check Which Indexes Are Worth Keeping

Common anti-pattern: adding indexes for occasional queries, then never reviewing usage.

Find low-usage indexes:

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

Large indexes with near-zero scans are strong cleanup candidates.

4. Detect Index Bloat Signals

Useful indicators:

• table write volume high, index size growth outpaces table growth
• query latency does not improve despite additional indexes
• autovacuum runs frequently but bloat still increases

Track index growth trend weekly per table, not only point-in-time size.

5. Design Better Indexes

Guidelines:

• index only columns used by frequent filters, joins, and ordering
• prefer narrow keys
• avoid redundant prefix-overlap indexes
• use partial indexes for filtered workloads

Example partial index:

CREATE INDEX CONCURRENTLY idx_orders_open_created_at
ON orders (created_at)
WHERE status = 'OPEN';

This is often smaller and faster than a full-table index.

6. Composite Index Order Matters

For (a, b, c), PostgreSQL can use left-prefix access efficiently. If most queries filter by b only, this index may not help.

Choose column order based on real query predicates and cardinality.

7. Maintenance Strategy

Baseline:

• ensure autovacuum settings fit write volume
• use REINDEX CONCURRENTLY for heavily bloated critical indexes
• run periodic index review as part of DBA operations

Example:

REINDEX INDEX CONCURRENTLY idx_orders_open_created_at;

8. Practical Optimization Workflow

1. Identify top tables by index storage.
2. List indexes by size and usage.
3. Remove redundant or unused indexes safely.
4. Replace broad indexes with targeted partial/composite indexes.
5. Tune autovacuum and monitor growth trend.

9. Production Checklist

• Every index has a known query use case.
• No large unused indexes in pg_stat_user_indexes.
• Autovacuum tuned for high-write tables.
• Index growth reviewed regularly with alerts.

Index size is a performance budget decision. Keep only indexes that clearly pay for their write and storage cost.