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

The usual mistake is treating every index as a read-performance win and forgetting the write and storage cost. Here is how I would look at index growth before adding another “quick” index to a busy table.

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)

MVCC Is Part of the Story

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

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.

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.

Design Smaller, More Useful 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.

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.

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;

A Practical Optimization Pass

  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.

Before Keeping an Index

  • 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 the indexes that clearly pay for their write and storage cost, and make the rest justify their place.