P-2 — Indexes: When and How to Add Them

PostgreSQL does not automatically create indexes on foreign key columns. This surprises many engineers. When you delete a row from the parent table, PostgreSQL must scan the child table to check for references — without an index, this is a full sequential scan.

-- Foreign keys in tasks
ALTER TABLE tasks ADD CONSTRAINT tasks_project_fk
  FOREIGN KEY (project_id) REFERENCES projects(id);
 
ALTER TABLE tasks ADD CONSTRAINT tasks_assigned_fk
  FOREIGN KEY (assigned_to) REFERENCES users(id);
 
-- These indexes are NOT created automatically — add them manually:
CREATE INDEX idx_tasks_project_id  ON tasks (project_id);
CREATE INDEX idx_tasks_assigned_to ON tasks (assigned_to);
CREATE INDEX idx_comments_task_id  ON comments (task_id);
CREATE INDEX idx_comments_author   ON comments (author_id);

Rule: every foreign key column should have an index. Without it, joins and deletions that reference that column are slow. On a live table you can't afford to lock, add these with CREATE INDEX CONCURRENTLY (covered above) rather than a plain CREATE INDEX.

---

The Write Cost of Indexes

Every index you add slows down every INSERT, UPDATE, and DELETE on that table.

A table with no indexes:
  INSERT: write 1 place (the table)
  UPDATE: write 1 place
  DELETE: write 1 place
 
The same table with 5 indexes:
  INSERT: write 6 places (table + 5 indexes)
  UPDATE: write up to 6 places (table + any index whose column changed)
  DELETE: write 6 places

For a table that has 100,000 reads per second but only 10 writes per second, many indexes are fine. For a table that has 50,000 writes per second (like an event log or metrics table), every index is expensive.

This is why you should not add an index "just in case." Measure first, add indexes for proven slow queries.

---

Common Index Mistakes

Mistake 1: Indexing a low-cardinality column

-- A 'status' column with 4 possible values has low cardinality
-- An index on it is often useless when many rows match
CREATE INDEX idx_orders_status ON orders (status);
 
-- If 'confirmed' represents 80% of rows, PostgreSQL ignores the index
-- and does a sequential scan — it is cheaper to scan than to use the index
-- when the result set is large
 
-- FIX: use a partial index for the selective values only
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- Indexes only pending orders (maybe 2%) — highly selective

Mistake 2: Over-indexing a write-heavy table

-- A logging table receiving 10,000 inserts/second
-- Every index you add costs you on every insert
 
-- BAD: indexing everything
CREATE INDEX idx_events_user_id    ON events (user_id);
CREATE INDEX idx_events_type       ON events (event_type);
CREATE INDEX idx_events_created_at ON events (created_at);
CREATE INDEX idx_events_session_id ON events (session_id);
-- 4 extra writes per INSERT, 10,000 inserts/sec = 40,000 extra write ops/sec

Mistake 3: Indexing a column used in a function

-- The index is NOT used because the function wraps the column
CREATE INDEX idx_users_email ON users (email);
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- ❌ Doesn't use the index (LOWER() prevents it)
 
-- FIX: create an expression index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- ✅ Now uses the expression index

Mistake 4: Not running ANALYZE after bulk loads

-- After inserting millions of rows, PostgreSQL's statistics are stale
-- The planner may choose sequential scan even when an index exists
 
INSERT INTO events SELECT ... FROM staging;  -- insert 5 million rows
 
-- Run ANALYZE to update statistics
ANALYZE events;
-- Now the planner knows the table is large and uses indexes appropriately

---

Monitoring Index Usage

-- Are your indexes actually being used?
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- Low idx_scan = index may not be needed
-- Consider dropping unused indexes (they still slow down writes)
 
-- Table statistics: sequential scans vs index scans
SELECT
  relname AS table,
  seq_scan,
  idx_scan,
  n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
-- High seq_scan on large tables = potentially missing indexes

---

Practical Exercise: Indexing the Task Manager

Starting with the task manager schema from F-7 with 50,000 rows seeded:

-- Run these EXPLAIN queries and note the execution plans
 
-- Q1: Are we doing sequential scans?
EXPLAIN ANALYZE
SELECT * FROM tasks WHERE project_id = 42 AND status = 'todo';
-- Likely: Seq Scan (no index yet)
 
-- Add the right index
CREATE INDEX CONCURRENTLY idx_tasks_project_status
ON tasks (project_id, status);
 
-- Run EXPLAIN again — should now show Index Scan
EXPLAIN ANALYZE
SELECT * FROM tasks WHERE project_id = 42 AND status = 'todo';
 
-- Q2: Add indexes for common query patterns
-- Tasks assigned to a user, ordered by due date
CREATE INDEX CONCURRENTLY idx_tasks_assigned_due
ON tasks (assigned_to, due_date)
WHERE status NOT IN ('done', 'cancelled');
 
-- Q3: Comments by task (very common query)
CREATE INDEX CONCURRENTLY idx_comments_task_created
ON comments (task_id, created_at);
 
-- Q4: User lookup by email (login flow)
CREATE INDEX CONCURRENTLY idx_users_email_lower
ON users (LOWER(email));
 
-- Q5: Check that foreign keys are indexed
CREATE INDEX CONCURRENTLY idx_tasks_project_id  ON tasks (project_id);
CREATE INDEX CONCURRENTLY idx_tasks_assigned_to ON tasks (assigned_to);
CREATE INDEX CONCURRENTLY idx_comments_task_id  ON comments (task_id);
CREATE INDEX CONCURRENTLY idx_comments_author   ON comments (author_id);
CREATE INDEX CONCURRENTLY idx_project_members_user
ON project_members (user_id);
 
-- Q6: Verify your indexes
\d tasks
-- Should show all the indexes you created

---

Summary

---

Module P-3 covers transactions and ACID in practice — what BEGIN, COMMIT, ROLLBACK actually do, isolation levels, and writing safe atomic operations for scenarios like bank transfers and inventory deduction.

Next: P-3 — Transactions and ACID in Practice →

---