Index Strategy: Less Is Often More

~14 min · indexes, strategy

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The cost of every index

Each index makes INSERT, UPDATE (on indexed columns), and DELETE more expensive. Each index takes disk space and competes for cache. Each index has to be maintained by VACUUM. Indexes also constrain your write throughput. Adding indexes to "speed things up" without measuring is a great way to slow your writes by 5× while making no read query meaningfully faster.

The strategy in three rules

Add indexes for queries you measure — not queries you imagine. EXPLAIN ANALYZE is the source of truth.
Always index foreign keys — no exceptions for tables that ever have parent rows deleted.
Periodically prune unused indexes — pg_stat_user_indexes.idx_scan = 0 over months means the index is dead weight.

The unused-index audit

PostgreSQL tracks how often each index has been used. After your application has been in production for a while, this catalog query reveals indexes nobody uses. Drop them after a sanity check.

Code

Find unused indexes·sql

SELECT
    s.schemaname || '.' || s.relname AS table,
    s.indexrelname                   AS index,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
    s.idx_scan                       AS times_used
FROM   pg_stat_user_indexes s
JOIN   pg_index i ON i.indexrelid = s.indexrelid
WHERE  NOT i.indisunique           -- never drop unique indexes
ORDER  BY s.idx_scan, pg_relation_size(s.indexrelid) DESC;
-- Indexes with idx_scan = 0 over a long period are candidates to drop.

Find duplicate indexes·sql

-- Two indexes covering identical columns are pure overhead
SELECT
    indrelid::regclass AS table,
    array_agg(indexrelid::regclass) AS indexes,
    array_agg(indkey) AS column_lists
FROM   pg_index
GROUP  BY indrelid, indkey
HAVING COUNT(*) > 1;

Reindex for bloat (rare but useful)·sql

-- After heavy churn, indexes can bloat. REINDEX rebuilds them.
REINDEX INDEX CONCURRENTLY orders_customer_idx;
-- CONCURRENTLY avoids a long lock; use it in production.

External links

PostgreSQL pg_stat_user_indexes

Exercise

On a real database, run the unused-index query. Identify three candidates to drop. Verify with code search that nothing references them. Drop them in a transaction; ROLLBACK if anything breaks.