PostgreSQL Indexes

Overview

An index lets PostgreSQL find rows without scanning the whole table, the same way a book's index lets you jump to a page instead of reading cover to cover. The default index type, B-Tree, keeps values in a sorted tree so equality checks, range scans, and sorts all avoid full table scans. Other index types (Hash, GIN, BRIN) trade that general-purpose shape for something narrower and faster at a specific job. Every index also has a cost: each INSERT, UPDATE, or DELETE has to update every index on the table, so more indexes means slower writes.

Quick Reference

Syntax

-- Basic B-Tree index (the default)
CREATE INDEX idx_users_email ON users (email);

-- Composite index - column order matters
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Partial index - only indexes matching rows
CREATE INDEX idx_active_users ON users (email) WHERE is_active = true;

-- Covering index - avoids a heap fetch for included columns
CREATE INDEX idx_orders_covering ON orders (user_id, status)
  INCLUDE (created_at, total_amount);

-- Build without locking the table for writes
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Examples

-- Inspect which indexes exist and how often they're used
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  tablename = 'orders'
ORDER  BY idx_scan DESC;

-- Confirm the planner is actually using an index
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

// Creating an index from application code (node-postgres)
await pool.query(`
  CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_status
  ON orders (user_id, status)
  INCLUDE (created_at, total_amount)
`)

Visual Diagram

Common Mistakes

• Indexing every column "just in case", each extra index slows down every write on that table, often for an index that's never used.
• Creating a composite index in the wrong column order, so it can't serve the query's actual filter pattern.
• Forgetting CONCURRENTLY when adding an index to a live, high-traffic table, a plain CREATE INDEX takes a lock that blocks writes for the duration of the build.
• Assuming an index is used just because it exists, always confirm with EXPLAIN ANALYZE rather than guessing from the schema.
• Letting statistics go stale after a bulk load, run ANALYZE so the planner's cost estimates reflect the new data.

Performance

• B-Tree index size scales with the number of distinct values and row count, very low-cardinality columns (like a boolean) rarely benefit from an index unless paired with a partial index or used as a leading composite column.
• INCLUDE columns add data to the index leaf pages without making them part of the sort key, use this to satisfy "index-only scans" for frequently selected extra columns.
• GIN indexes are powerful for JSONB/array containment but are the most expensive to maintain on write-heavy tables; consider fastupdate tuning or a less frequently updated summary table if writes dominate.
• BRIN indexes are tiny (a few pages) because they store value ranges per block rather than per row, excellent for append-only, naturally time-ordered tables like event logs.

Best Practices

• Index for your actual query patterns, pull them from pg_stat_statements, not intuition.
• Put the most selective, most commonly filtered column first in a composite index.
• Use partial indexes when queries consistently filter to a subset of rows (soft-deletes, active flags, status enums).
• Always build indexes with CONCURRENTLY on tables that take live write traffic.
• Periodically review pg_stat_user_indexes for indexes with idx_scan = 0 and drop them, unused indexes are pure write-path cost.