PostgreSQL Indexing Best Practices: The Complete Guide for Production

-- Default B-tree index (type is implicit)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Explicit type declaration (same result)
CREATE INDEX idx_orders_status ON orders USING btree (status);

-- Descending index — useful when queries sort DESC on this column
CREATE INDEX idx_orders_created_at_desc ON orders (created_at DESC);

-- Hash index — equality only
CREATE INDEX idx_sessions_token ON sessions USING hash (session_token);

-- GIN index on JSONB column — supports @>, ?, ?&, ?| operators
CREATE INDEX idx_products_metadata ON products USING gin (metadata);

-- GIN index for full-text search
CREATE INDEX idx_articles_search ON articles USING gin (to_tsvector('english', title || ' ' || body));

-- GIN index for array containment queries
CREATE INDEX idx_posts_tags ON posts USING gin (tags);

-- GIN index for LIKE/ILIKE with pg_trgm
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_customers_name_trgm ON customers USING gin (name gin_trgm_ops);

-- GiST index for range overlap queries (e.g., booking systems)
CREATE INDEX idx_reservations_period ON reservations USING gist (during);

-- GiST index for geometric containment
CREATE INDEX idx_locations_coords ON locations USING gist (coordinates);

-- Full-text search also works with GiST (smaller than GIN, slower to query)
CREATE INDEX idx_articles_fts_gist ON articles USING gist (to_tsvector('english', body));

-- BRIN on a time-series events table — data appended in time order
CREATE INDEX idx_events_occurred_at_brin ON events USING brin (occurred_at);

-- BRIN with smaller block range for finer granularity (default is 128)
CREATE INDEX idx_logs_created_brin ON logs USING brin (created_at) WITH (pages_per_range = 32);

-- Check physical correlation of a column (values close to 1.0 or -1.0 = good BRIN candidate)
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'events'
ORDER BY abs(correlation) DESC;

-- SP-GiST for inet (IP address) range lookups
CREATE INDEX idx_blocklist_cidr ON ip_blocklist USING spgist (cidr_range);

-- Index only pending orders — 3% of rows, 100% of the interesting queries
CREATE INDEX idx_orders_pending ON orders (created_at DESC)
WHERE status = 'pending';

-- This query uses the partial index efficiently
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

-- Partial index on soft-deleted rows (exclude deleted)
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;

-- Unique constraint only on active records (allows multiple deleted rows with same email)
CREATE UNIQUE INDEX idx_users_email_unique_active ON users (email)
WHERE deleted_at IS NULL;

-- WRONG: range column (created_at) before equality column (status)
-- This index cannot satisfy: WHERE status = 'pending' ORDER BY created_at DESC
CREATE INDEX idx_orders_bad ON orders (created_at DESC, status);

-- CORRECT: equality column first, then range/sort column
CREATE INDEX idx_orders_good ON orders (status, created_at DESC);

-- This query now uses a single index scan efficiently
SELECT id, customer_id, total
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 100;

-- Multi-tenant composite index: tenant equality first, then filter column
CREATE INDEX idx_invoices_tenant_status ON invoices (tenant_id, status, due_date DESC);

-- Supports all of these queries efficiently:
-- WHERE tenant_id = $1
-- WHERE tenant_id = $1 AND status = 'overdue'
-- WHERE tenant_id = $1 AND status = 'overdue' ORDER BY due_date DESC

-- Check which prefix columns are actually used in plans
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT id, amount, due_date
FROM invoices
WHERE tenant_id = '3f7a2c1d' AND status = 'overdue'
ORDER BY due_date DESC
LIMIT 20;

-- Without INCLUDE: query needs heap fetch to get 'amount' and 'currency'
CREATE INDEX idx_payments_status_created ON payments (status, created_at DESC);

-- With INCLUDE: index-only scan satisfies the full query without touching the heap
CREATE INDEX idx_payments_status_created_covering ON payments (status, created_at DESC)
INCLUDE (amount, currency);

-- This query now shows "Index Only Scan" in EXPLAIN output
EXPLAIN (ANALYZE, BUFFERS)
SELECT status, created_at, amount, currency
FROM payments
WHERE status = 'settled'
ORDER BY created_at DESC
LIMIT 200;

-- Verify index-only scan is happening
-- Look for: "Index Only Scan using idx_payments_status_created_covering"
-- And: "Heap Fetches: 0" (or close to 0 if visibility map is current)

-- Tables with high sequential scans relative to index scans — missing index candidates
SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_scan - idx_scan AS seq_scan_excess,
    n_live_tup AS live_rows,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND n_live_tup > 10000          -- only tables large enough to care about
ORDER BY seq_scan_excess DESC
LIMIT 20;

-- Tables with zero index scans but many sequential scans — likely missing all indexes
SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    n_live_tup AS live_rows,
    pg_size_pretty(pg_total_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
WHERE idx_scan = 0
  AND seq_scan > 100
  AND n_live_tup > 5000
ORDER BY seq_tup_read DESC;

-- Slowest queries by total execution time — targets for index optimization
SELECT
    query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows
FROM pg_stat_statements
WHERE calls > 50
ORDER BY total_exec_time DESC
LIMIT 25;

-- Run EXPLAIN ANALYZE on a slow query to diagnose index usage
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT order_id, customer_id, total_amount
FROM orders
WHERE customer_id = 'cust-7821'
  AND status IN ('pending', 'processing')
ORDER BY created_at DESC
LIMIT 50;

-- Key things to look for in the output:
-- "Seq Scan" (bad) vs "Index Scan" or "Index Only Scan" (good)
-- "Rows Removed by Filter" (high number = full scan)
-- "Buffers: shared hit=X read=Y" (high 'read' = going to disk)
-- "Execution Time" at the bottom

-- Indexes with zero or very low scan counts since last statistics reset
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS index_scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_size_pretty(pg_relation_size(indrelid)) AS table_size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisprimary          -- exclude primary keys
  AND NOT indisunique           -- exclude unique constraints (enforced on writes)
  AND pg_relation_size(indexrelid) > 8192  -- skip trivially small indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Check when statistics were last reset (confirms observation window)
SELECT stats_reset FROM pg_stat_bgwriter;

-- PostgreSQL 16+: see the last time each index was actually scanned
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    last_idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE last_idx_scan < now() - interval '30 days'
  OR last_idx_scan IS NULL
ORDER BY pg_relation_size(indexrelid) DESC;

-- Estimate index bloat using pgstattuple extension
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    indexname,
    pg_size_pretty(index_size) AS index_size,
    round(avg_leaf_density::numeric, 1) AS leaf_density_pct,
    round((1 - avg_leaf_density / 100) * 100, 1) AS bloat_pct,
    leaf_fragmentation
FROM (
    SELECT
        indexname,
        pg_relation_size(indexrelid) AS index_size,
        (pgstatindex(indexrelid::regclass)).*
    FROM pg_stat_user_indexes
    WHERE schemaname = 'public'
) AS idx_stats
WHERE index_size > 10 * 1024 * 1024   -- only indexes larger than 10MB
ORDER BY bloat_pct DESC
LIMIT 20;

-- CREATE INDEX CONCURRENTLY — does not lock writes to the table
-- Takes longer to build (multiple passes) but safe for production
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status != 'archived';

-- Monitor progress in PostgreSQL 12+ (check pg_stat_progress_create_index)
SELECT
    phase,
    blocks_done,
    blocks_total,
    round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct_complete
FROM pg_stat_progress_create_index
WHERE relid = 'orders'::regclass;

-- REINDEX CONCURRENTLY — rebuilds index without locking reads or writes
-- Available from PostgreSQL 12+
REINDEX INDEX CONCURRENTLY idx_orders_customer_status;

-- Rebuild all indexes on a table concurrently
REINDEX TABLE CONCURRENTLY orders;

-- Rebuild all indexes in a schema concurrently (PostgreSQL 14+)
REINDEX SCHEMA CONCURRENTLY public;

-- Check for invalid indexes left by failed concurrent reindex
SELECT schemaname, tablename, indexname
FROM pg_indexes
JOIN pg_class ON relname = indexname
JOIN pg_index ON indexrelid = pg_class.oid
WHERE NOT indisvalid;

-- VACUUM reclaims dead tuple space and updates visibility map (enables index-only scans)
VACUUM ANALYZE orders;

-- VACUUM FULL reclaims space aggressively but locks the table — use rarely
-- Prefer REINDEX CONCURRENTLY + VACUUM ANALYZE over VACUUM FULL for indexes
VACUUM FULL orders;  -- acquires AccessExclusiveLock — blocks all reads and writes

-- Check autovacuum activity on a specific table
SELECT
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    vacuum_count,
    autovacuum_count,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- Speed up autovacuum for a high-churn table without affecting others
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,    -- trigger at 1% dead tuples (default 20%)
    autovacuum_vacuum_cost_delay = 2,          -- reduce throttling (ms, default 20)
    autovacuum_analyze_scale_factor = 0.005   -- analyze at 0.5% changes (default 10%)
);