Index Strategies

// Part 01

Why Index Strategy Matters

-- Every index makes reads faster and writes slower
-- The tradeoff: read speedup must outweigh write cost

-- Read benefit: full table scan → index lookup
-- O(n) → O(log n) for equality; O(log n + k) for range (k = matching rows)

-- Write cost: every INSERT, UPDATE, DELETE updates all indexes
-- Table with 1 index:  1 heap write + 1 index write per INSERT
-- Table with 10 indexes: 1 heap write + 10 index writes per INSERT

-- Example: orders table (FreshCart)
-- Current indexes: pk (order_id), customer_id, store_id, order_date
-- Every INSERT into orders: 1 heap write + 4 index writes = 5 total writes
-- Adding 6 more indexes: 1 + 10 = 10 writes per INSERT — 2× write overhead

-- Rule: add an index only when the read speedup on measured queries
-- demonstrably outweighs the write overhead for the table's write volume

// Part 02

Composite Index Design — Column Order Is Everything

-- Index: CREATE INDEX idx ON orders (store_id, order_status, order_date)

-- ✅ Uses the index — leading column(s) specified:
WHERE store_id = 'ST001'
WHERE store_id = 'ST001' AND order_status = 'Delivered'
WHERE store_id = 'ST001' AND order_status = 'Delivered' AND order_date = '2024-01-15'
WHERE store_id = 'ST001' AND order_date > '2024-01-01'   -- partial: store_id only, date filtered after

-- ❌ Cannot use the index — leading column missing:
WHERE order_status = 'Delivered'                         -- no store_id → cannot enter index
WHERE order_date > '2024-01-01'                          -- no store_id → cannot enter index
WHERE order_status = 'Delivered' AND order_date = '...'  -- still no store_id

-- Rule: the index is useful for any query that specifies
-- the FIRST N columns in order (for any N from 1 to total columns)
-- Queries skipping any leading column get zero benefit from this index

-- Query: WHERE store_id = 'ST001' AND order_date BETWEEN '2024-01-01' AND '2024-01-31'

-- Option A: idx_a ON orders (store_id, order_date)
-- store_id equality: planner navigates to the ST001 section of the index
-- order_date range: scans the contiguous date range within ST001
-- ✅ Both conditions use the index efficiently

-- Option B: idx_b ON orders (order_date, store_id)
-- order_date range: planner scans the 31-day date range (all stores mixed)
-- store_id filter: applied AFTER the date scan, as an index filter
-- ❌ Less efficient: scans all stores' orders for January, then filters ST001

-- The rule: for composite indexes, put EQUALITY filter columns
-- BEFORE range filter columns. Equality narrows to an exact section;
-- range then scans within that narrow section.

-- Another example:
-- Query: WHERE customer_id = 42 AND order_date > '2024-01-01' AND total_amount > 500
-- Best index: (customer_id, order_date, total_amount)
--   customer_id = equality → narrow to customer 42's orders
--   order_date > range → scan date range within customer 42
--   total_amount > applied after (only one range per index used efficiently)
-- NOT: (order_date, total_amount, customer_id)
--   order_date > range → wide scan, then filter for customer 42

-- When ALL filter conditions are equality (no range), put the
-- most selective column first — reduces the number of rows examined
-- after the first condition is applied.

-- Check column selectivity (distinct values / total rows):
SELECT
  'store_id'     AS col, COUNT(DISTINCT store_id)::NUMERIC / COUNT(*) AS selectivity FROM orders
UNION ALL
SELECT 'order_status', COUNT(DISTINCT order_status)::NUMERIC / COUNT(*) FROM orders
UNION ALL
SELECT 'customer_id',  COUNT(DISTINCT customer_id)::NUMERIC / COUNT(*) FROM orders;

-- Example results:
-- store_id:     0.067  (10 stores / 150 orders)
-- customer_id:  0.133  (20 customers / 150 orders)
-- order_status: 0.027  (4 statuses / 150 orders)

-- For WHERE customer_id = ? AND order_status = ?:
-- Best: idx ON (customer_id, order_status) — customer_id more selective
-- Worse: idx ON (order_status, customer_id) — status only narrows to ~25% first

-- Exception: if one column is ALWAYS an equality filter (always in WHERE),
-- put it first regardless of selectivity — it is always the entry point

// Part 03

Covering Indexes — Eliminating the Heap Fetch

-- Query: SELECT order_id, total_amount, order_date
--        FROM orders WHERE customer_id = 5 AND order_status = 'Delivered'

-- Without covering index:
-- 1. Index lookup on (customer_id, order_status) → row location
-- 2. Heap fetch for each matching row → reads order_id, total_amount, order_date
-- Two I/O steps per matching row

-- With covering index (INCLUDE adds columns to index leaf nodes):
CREATE INDEX idx_orders_cust_status_covering
  ON orders (customer_id, order_status)
  INCLUDE (order_id, total_amount, order_date);
-- 1. Index lookup → row location AND order_id, total_amount, order_date already there
-- Zero heap fetch — Index Only Scan

-- The distinction:
-- Columns in the key (before INCLUDE): used for searching and sorting
-- Columns in INCLUDE: stored in leaf nodes only — not searchable, just fetchable
-- INCLUDE columns have no overhead in internal index nodes — only in leaves
-- WHERE customer_id = 5 AND order_status = 'Delivered' — key columns
-- SELECT order_id, total_amount, order_date — INCLUDE columns

-- When to use INCLUDE:
-- ✅ Column is frequently SELECTed alongside the key columns
-- ✅ Heap rows are wide (many columns) — heap fetch is expensive
-- ✅ The queries are read-heavy and latency-sensitive
-- ❌ Column changes frequently — each update must also update the index leaf

// Part 04

Partial Indexes — Index Only the Rows You Query

-- Pattern 1: index only active/live rows
-- If 90% of orders are Delivered, a partial index on Processing orders
-- is 10% the size and serves the queue-processing use case precisely
CREATE INDEX idx_orders_processing
  ON orders (order_date, store_id)
  WHERE order_status = 'Processing';
-- Used by: WHERE order_status = 'Processing' AND order_date >= '...'
-- Not used by: WHERE order_status = 'Delivered' (different condition)

-- Pattern 2: index only non-deleted rows (soft delete pattern)
CREATE INDEX idx_customers_active
  ON customers (loyalty_tier, customer_id)
  WHERE is_deleted = false;
-- 100,000 customer table with 80% soft-deleted:
-- Full index: 100,000 entries
-- Partial index: 20,000 entries — 5× smaller, 5× faster to scan

-- Pattern 3: index only recent data (time-series pattern)
CREATE INDEX idx_orders_recent
  ON orders (order_date DESC, store_id)
  WHERE order_date >= '2024-01-01';
-- Operational queries almost always filter on recent data
-- Historical queries can use a seq scan (they are rare anyway)

-- Pattern 4: unique partial index (conditional uniqueness)
CREATE UNIQUE INDEX idx_customers_active_email
  ON customers (LOWER(email))
  WHERE is_deleted = false;
-- Allows same email to appear in deleted + active rows
-- But prevents two active rows from sharing an email

// Part 05

Functional Indexes — Indexing Expressions

-- Pattern 1: case-insensitive search
CREATE INDEX idx_customers_email_lower
  ON customers (LOWER(email));
-- Enables: WHERE LOWER(email) = 'user@example.com'  → Index Scan
-- Without: full table scan on every login attempt

-- Pattern 2: date truncation for monthly grouping
CREATE INDEX idx_orders_month
  ON orders (DATE_TRUNC('month', order_date));
-- Enables: WHERE DATE_TRUNC('month', order_date) = '2024-01-01'  → Index Scan
-- Without: function on every row = Seq Scan

-- Pattern 3: computed margin percentage
CREATE INDEX idx_products_margin
  ON products (ROUND((unit_price - cost_price) / NULLIF(unit_price, 0) * 100, 1));
-- Enables: WHERE ROUND((unit_price - cost_price)/NULLIF(unit_price,0)*100,1) > 40
-- Without: expression computed on every row = Seq Scan

-- Pattern 4: JSONB field extraction
-- (for tables with JSONB columns storing structured data)
CREATE INDEX idx_events_user_id
  ON events ((payload->>'user_id'));
-- Enables: WHERE payload->>'user_id' = '42'  → Index Scan
-- Without: full JSONB column scan

-- Key requirement: the query's WHERE expression must be IDENTICAL to the index expression
-- WHERE lower(email) matches idx ON (lower(email))  ✅
-- WHERE lower(trim(email)) does NOT match idx ON (lower(email))  ❌ — different expression

// Part 06

The Write Cost Tradeoff — When Indexes Hurt

-- Check index sizes (large index = large write per row)
SELECT
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(schemaname || '.' || indexname)) AS index_size
FROM pg_indexes
WHERE tablename IN ('orders', 'order_items', 'customers')
  AND schemaname = 'main'
ORDER BY tablename, pg_relation_size(schemaname || '.' || indexname) DESC;

-- Check index usage stats (has this index been used recently?)
SELECT
  relname         AS table_name,
  indexrelname    AS index_name,
  idx_scan        AS times_used,
  idx_tup_read    AS rows_read_via_index,
  idx_tup_fetch   AS rows_fetched_from_heap,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE schemaname = 'main'
ORDER BY idx_scan ASC;           -- sort by usage ascending → unused at top

-- Red flags:
-- idx_scan = 0 → never used → paying write cost with zero read benefit → DROP IT
-- idx_scan < 10 → rarely used → consider dropping and recreating only if needed
-- Large index_size + idx_scan = 0 → expensive to maintain, no benefit

// Part 07

Index Audit — Finding Unused, Duplicate, and Bloated Indexes

-- ── 1. Unused indexes (never used since last stats reset) ──────
SELECT
  schemaname,
  relname         AS table_name,
  indexrelname    AS index_name,
  idx_scan        AS scan_count,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisprimary          -- exclude PKs
  AND NOT indisunique           -- exclude unique constraints (they enforce data integrity)
ORDER BY pg_relation_size(indexrelid) DESC;

-- ── 2. Duplicate / redundant indexes ──────────────────────────
-- Index A is redundant if Index B covers all of A's key columns as a prefix
SELECT
  a.tablename,
  a.indexname AS redundant_index,
  a.indexdef  AS redundant_def,
  b.indexname AS covering_index,
  b.indexdef  AS covering_def
FROM pg_indexes AS a
JOIN pg_indexes AS b
  ON a.tablename = b.tablename
  AND a.indexname != b.indexname
  AND b.indexdef LIKE '%' || SPLIT_PART(a.indexdef, '(', 2) || '%'
WHERE a.schemaname = 'public';

-- ── 3. Index bloat (dead versions consuming space) ──────────
-- Run VACUUM ANALYZE to reclaim bloat first
-- Then check if index size is proportional to table size
SELECT
  t.relname   AS table_name,
  pg_size_pretty(pg_table_size(t.oid))           AS table_size,
  pg_size_pretty(pg_indexes_size(t.oid))         AS all_indexes_size,
  ROUND(pg_indexes_size(t.oid)::NUMERIC /
    NULLIF(pg_table_size(t.oid), 0) * 100, 0)    AS index_to_table_pct
FROM pg_class AS t
WHERE t.relkind = 'r'
  AND t.relname IN ('orders', 'order_items', 'customers', 'products')
ORDER BY pg_indexes_size(t.oid) DESC;

-- ── 4. Most used indexes (confirm high-value indexes) ────────
SELECT
  relname         AS table_name,
  indexrelname    AS index_name,
  idx_scan,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 20;

// Part 08

Index Maintenance — REINDEX, VACUUM, and Bloat

-- VACUUM: reclaim dead tuples and index entries (non-blocking)
VACUUM orders;                    -- reclaim dead tuples in orders + all its indexes
VACUUM ANALYZE orders;            -- reclaim + refresh statistics in one pass
VACUUM VERBOSE ANALYZE orders;    -- with progress output

-- VACUUM FULL: rewrite the table from scratch (exclusive lock — schedule carefully)
-- Only use when bloat is severe and the window allows a table lock
VACUUM FULL orders;               -- rewrites heap and all indexes — exclusive lock

-- REINDEX: rebuild one or all indexes (removes bloat from the index structure)
REINDEX INDEX idx_orders_customer_id;         -- one index (locks the index)
REINDEX TABLE orders;                         -- all indexes on the table (locks all)
REINDEX TABLE CONCURRENTLY orders;            -- PostgreSQL 12+ — no lock (slower)

-- When to REINDEX:
-- pg_stat_user_indexes shows index size >> expected size for the row count
-- After bulk DELETE that removed > 30% of rows
-- After VACUUM FULL on the table (rebuild indexes to match the new heap layout)

-- Autovacuum tuning for high-write tables:
-- Default autovacuum triggers when 20% of rows are dead
-- For high-volume tables, trigger earlier:
ALTER TABLE orders
  SET (autovacuum_vacuum_scale_factor = 0.05,    -- trigger at 5% dead rows
       autovacuum_analyze_scale_factor = 0.02);  -- refresh stats at 2% changes

// Part 09

The Complete Index Decision Framework

// Part 10

What This Looks Like at Work

-- Find candidates for removal on the orders table
SELECT
  indexrelname    AS index_name,
  idx_scan        AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  CASE
    WHEN idx_scan = 0                  THEN '🔴 Never used — drop'
    WHEN idx_scan < 10                 THEN '🟡 Rarely used — investigate'
    ELSE                                    '🟢 Active — keep'
  END AS recommendation
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE relname = 'orders'
  AND NOT indisprimary
  AND NOT indisunique
ORDER BY idx_scan ASC;

-- Also check for indexes redundant to broader composites:
-- If idx_orders_customer_id EXISTS and
--    idx_orders_customer_date ON (customer_id, order_date) ALSO EXISTS:
-- The single-column idx_orders_customer_id is fully covered by the composite
-- (queries on customer_id alone can use the composite's left prefix)
-- → drop the single-column index, keep the composite

-- Keep (or create):

-- 1. Primary key (auto-created, cannot remove)
-- orders_pkey ON orders(order_id)

-- 2. FK to customers — every JOIN on customer_id
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 3. FK to stores — every JOIN on store_id
CREATE INDEX idx_orders_store_id ON orders(store_id);

-- 4. Composite for the queue-processing query (most write-sensitive path)
-- Partial: only Processing orders (< 20% of table)
CREATE INDEX idx_orders_processing_date
  ON orders(order_date, store_id)
  WHERE order_status = 'Processing';

-- 5. Composite covering index for the delivery report
-- Covers: WHERE order_status = 'Delivered' AND order_date range
-- INCLUDE avoids heap fetch for total_amount and customer_id
CREATE INDEX idx_orders_delivered_covering
  ON orders(order_status, order_date)
  INCLUDE (store_id, customer_id, total_amount);

-- 6. Index for customer-facing order history
CREATE INDEX idx_orders_customer_date
  ON orders(customer_id, order_date DESC)
  INCLUDE (order_id, order_status, total_amount);

-- 7. Index for store performance dashboard (by store + month)
CREATE INDEX idx_orders_store_month
  ON orders(store_id, DATE_TRUNC('month', order_date));

-- Removed: 11 indexes covering retired reports, one-off queries,
-- and low-selectivity columns — saving 11 write operations per INSERT

🎯 Pro Tip

Run an index audit every quarter. pg_stat_user_indexes resets on server restart but otherwise accumulates since the last reset — so a 90-day window gives reliable signal about which indexes are genuinely unused. Any index with idx_scan = 0 after 90 days of production traffic should be dropped unless it enforces a unique constraint. Set a calendar reminder to review the audit output and drop unused indexes before they accumulate to the point of measurable write degradation.

// Part 11

Interview Prep — 5 Questions With Complete Answers

// Part 12

Errors You Will Hit — And Exactly Why They Happen

What comes next

In Module 59, you learn Query Best Practices — the full set of SQL writing habits that separate beginner queries from production-grade code: sargability, NULL handling, anti-patterns, and writing SQL that stays fast as data grows.