PostgreSQL + Database Internals
25 min
Day 85: Indexes: B-tree, GIN, partial; EXPLAIN ANALYZE
Indexes: trading write cost for read speed
Without an index, finding rows matching a condition means scanning the whole table (a sequential scan). An index is a separate, ordered data structure pointing at rows matching a value, letting the database jump straight to matches — the same storage-hierarchy trade-off from Phase 0, Day 1, applied to query performance.
- B-tree (default) — great for equality and range queries (=, <, >, BETWEEN), sorted order
- GIN (Generalized Inverted Index) — for values containing multiple elements: arrays, full-text search, JSONB
- Partial index — indexes only rows matching a WHERE clause (e.g. WHERE status = 'active'), smaller and faster when you only ever query that subset
Indexes aren't free
Every index must be updated on every INSERT/UPDATE/DELETE affecting the indexed column — more indexes means slower writes and more storage. Indexing everything "just in case" is a real anti-pattern, not a safe default.
EXPLAIN ANALYZE: reading the plan
Seeing whether an index is actually used
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Look for:
-- Seq Scan on orders (bad, if the table is large)
-- Index Scan using idx_orders_customer_id (good)
-- actual time=... rows=... — the REAL measured cost, not just an estimateKey terms
- Sequential scan
- Reading every row in a table to find matches — the fallback with no usable index.
- B-tree index
- A sorted, balanced tree structure supporting fast equality/range lookups.
- EXPLAIN ANALYZE
- Runs a query and shows its real execution plan and measured cost, not just an estimate.
Why shouldn't you add an index to every column "just in case a query needs it"?
