Creating and Managing Indexes — Strategies and Maintenance

Knowing that indexes help is not enough — the choice of which columns to index, in what order for composite indexes, and with what conditions for partial indexes determines whether your queries are fast or still slow. Composite indexes follow strict ordering rules that control which queries benefit from them. Monitoring index usage helps you eliminate dead weight. And running VACUUM and REINDEX regularly keeps indexes healthy and accurate. This lesson covers the practical strategies for a FastAPI application’s index management.

Composite (Multi-Column) Indexes #

-- A composite index covers multiple columns in a specific order
-- The order of columns in the index critically affects which queries it helps

-- Index: (status, created_at)
CREATE INDEX idx_posts_status_created ON posts(status, created_at DESC);

-- Queries that CAN use this index:
-- WHERE status = 'published'                         ✓ uses first column
-- WHERE status = 'published' ORDER BY created_at     ✓ uses both columns
-- WHERE status = 'published' AND created_at > '...' ✓ uses both columns

-- Queries that CANNOT use this index:
-- WHERE created_at > '2025-01-01'                   ✗ skips first column
-- ORDER BY created_at DESC                           ✗ first column missing
-- The "leftmost prefix rule": must include columns from the left in order

-- Choose column order: put equality-filtered columns first, range columns last
-- Example: status = 'published' AND created_at > X
-- Index should be: (status, created_at) — equality first, then range

Partial Indexes #

-- Partial index: only index rows matching a condition
-- Smaller index (only indexed rows), faster build and lookup

-- Only index published posts (most queries only care about published)
CREATE INDEX idx_posts_published ON posts(created_at DESC)
    WHERE status = 'published';

-- This index is used for:
SELECT * FROM posts WHERE status = 'published' ORDER BY created_at DESC;

-- But NOT for:
SELECT * FROM posts WHERE status = 'draft' ORDER BY created_at DESC;
-- (Falls back to sequential scan or a different index)

-- Other partial index patterns:

-- Index active users only (soft delete pattern)
CREATE INDEX idx_users_email_active ON users(email)
    WHERE deleted_at IS NULL;

-- Index unprocessed jobs (keeps index small as queue drains)
CREATE INDEX idx_jobs_pending ON jobs(created_at)
    WHERE status = 'pending';

-- Index on a rare condition (avoid indexing the common case)
CREATE INDEX idx_posts_featured ON posts(published_at DESC)
    WHERE is_featured = TRUE;

Index Maintenance #

-- ── VACUUM — reclaim dead tuple space ────────────────────────────────────────
-- PostgreSQL never overwrites rows — updates create new versions (MVCC)
-- Old versions (dead tuples) accumulate until VACUUM reclaims them

-- Manual vacuum (normally handled by autovacuum)
VACUUM posts;            -- reclaim dead tuples (non-locking)
VACUUM ANALYZE posts;    -- reclaim + update statistics
VACUUM FULL posts;       -- full rewrite (locks table!) — use rarely

-- ── REINDEX — rebuild corrupted or bloated indexes ────────────────────────────
REINDEX INDEX idx_posts_status;          -- rebuild specific index
REINDEX TABLE posts;                     -- rebuild all indexes on table
REINDEX INDEX CONCURRENTLY idx_posts_status;  -- non-blocking rebuild (PG12+)

-- ── Monitor index usage ───────────────────────────────────────────────────────
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan       AS index_scans,
    idx_tup_read   AS tuples_read,
    idx_tup_fetch  AS tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;   -- unused indexes (0 scans) are candidates for removal

-- ── Monitor table bloat ───────────────────────────────────────────────────────
SELECT
    relname  AS table,
    n_dead_tup,
    n_live_tup,
    ROUND(n_dead_tup::NUMERIC / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1)
        AS dead_pct
FROM pg_stat_user_tables
ORDER BY dead_pct DESC NULLS LAST;

Covering Indexes (Index-Only Scans) #

-- An index-only scan reads data entirely from the index, never touching the table
-- Possible when all needed columns are in the index

-- Query needs: id, title, status, created_at
-- If index covers all four columns, PostgreSQL reads only the index:
CREATE INDEX idx_posts_list ON posts(status, created_at DESC)
    INCLUDE (id, title);    -- INCLUDE adds extra columns to the index leaf pages

-- Now this query is an index-only scan (no table access):
SELECT id, title, status, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 10;

-- EXPLAIN will show "Index Only Scan" — typically 3-5x faster than regular index scan
-- Tradeoff: larger index size due to extra columns

Common Mistakes #

Mistake 1 — Wrong column order in composite index #

❌ Wrong — range column first, blocks use for equality queries:

CREATE INDEX idx_wrong ON posts(created_at, status);
-- WHERE status = 'published' — cannot use this index efficiently!

✅ Correct — equality column first:

CREATE INDEX idx_right ON posts(status, created_at);   -- ✓

Mistake 2 — Never running VACUUM ANALYZE after bulk loads #

❌ Wrong — planner uses stale statistics after bulk import:

-- After loading 1M rows, planner still thinks table has 1000 rows!
-- Makes terrible query plan choices (wrong join order, wrong index)

✅ Correct — update statistics after major data changes:

VACUUM ANALYZE posts;   -- ✓ updates row count and column statistics

Mistake 3 — Keeping unused indexes #

❌ Wrong — 15 indexes on a table, 8 never used:

-- Unused indexes slow down every INSERT/UPDATE/DELETE on the table

✅ Correct — audit and drop unused indexes:

SELECT indexname FROM pg_stat_user_indexes
WHERE relname = 'posts' AND idx_scan = 0
  AND indexname NOT LIKE '%_pkey';   -- skip primary key
-- Drop the unused ones ✓

Quick Reference #