Index Design

1. What Is It?

Index design is the practice of choosing which columns to index, in what order, with what conditions, and using what index type — to make your actual query workload fast without incurring unnecessary write overhead or storage costs.

Creating indexes blindly or following rules of thumb ("index all foreign keys") leads to bloated schemas with too many indexes that hurt write performance, or missing indexes that leave critical queries doing full table scans. Good index design starts from the queries, not from the schema.

2. How It Works

The query-first approach:

1. Identify your most frequent and most latency-sensitive queries
2. For each query, determine its filter columns (WHERE), join columns (ON), sort columns (ORDER BY), and output columns (SELECT)
3. Design an index that serves the query's access pattern
4. Verify with EXPLAIN (ANALYZE)
5. Monitor write performance impact

Composite index column ordering: the ESR rule

For a composite index, order columns by:

1. Equality predicates first — WHERE status = 'paid'
2. Sort columns next — ORDER BY placed_at DESC
3. Range predicates last — WHERE placed_at > '2024-01-01'

This ordering maximizes how much of the index the query can use before needing to evaluate remaining predicates outside the index.

Copy
-- Query:
SELECT id, total_cents FROM orders
WHERE user_id = 42          -- equality
  AND status = 'paid'       -- equality
  AND placed_at > '2024-01-01'  -- range
ORDER BY placed_at DESC;

-- Optimal index:
CREATE INDEX idx_orders_user_status_placed ON orders(user_id, status, placed_at DESC);
-- user_id (equality), status (equality), placed_at (range + sort)

Covering indexes — include all queried columns

A covering index contains every column the query touches, enabling an index-only scan:

Copy
-- Query only needs id, total_cents, placed_at:
SELECT id, total_cents, placed_at
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY placed_at DESC;

-- Covering index:
CREATE INDEX idx_orders_covering ON orders(user_id, status, placed_at DESC)
    INCLUDE (total_cents, id);
-- INCLUDE adds columns to leaf nodes only (no sort key), keeping the index smaller

In PostgreSQL, INCLUDE columns appear only in the leaf nodes and don't affect sort order, which keeps internal node pages smaller.

Partial indexes — index only relevant rows

Copy
-- Only active (non-deleted) users are queried by email in production
CREATE INDEX idx_users_active_email ON users(email)
    WHERE deleted_at IS NULL;

-- Much smaller than a full index; faster for the filtered query

Partial indexes are powerful when queries consistently filter on a fixed condition. The partial index is only used when the query's WHERE clause is compatible with the index predicate.

Functional indexes — index expressions

Copy
-- Case-insensitive email lookup
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Query that uses this index:
SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.com');

Without the functional index, WHERE LOWER(email) = ? forces a sequential scan because the function call prevents use of the plain email index.

Index types beyond B-Tree:

3. What SDEs Actually Need to Know

Always index foreign keys in PostgreSQL. PostgreSQL does not automatically create indexes on FK columns (unlike some other databases). Without the index, every FK validation on insert and every join on that column does a sequential scan of the referenced side.

Copy
-- After this DDL, manually create the index:
ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id);
CREATE INDEX idx_orders_user_id ON orders(user_id);

pg_stat_user_indexes reveals unused indexes:

Copy
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan;
-- idx_scan = 0 means this index has never been used since last stats reset

Unused indexes are write overhead with no read benefit. Drop them.

Too many indexes on a write-heavy table causes contention. A table with 15 indexes that receives millions of inserts per day spends significant time maintaining those indexes. Profile write latency with EXPLAIN (ANALYZE) on INSERT/UPDATE statements to see index maintenance cost.

Multi-column uniqueness is a constraint, but also an index:

Copy
-- Prevents duplicate (user_id, project_id) pairs AND creates an index
ALTER TABLE project_memberships ADD CONSTRAINT uq_membership UNIQUE (user_id, project_id);
-- No need to separately CREATE INDEX on (user_id, project_id)

Concurrent index creation: CREATE INDEX on a large table locks writes for the duration in most databases. Use CREATE INDEX CONCURRENTLY in PostgreSQL to build the index without blocking:

Copy
CREATE INDEX CONCURRENTLY idx_orders_placed_at ON orders(placed_at);
-- Takes longer, but doesn't lock the table

4. Tradeoffs & Decisions

Index per column vs. composite index

Separate single-column indexes on (user_id) and (status) do not combine as effectively as one composite index on (user_id, status). The database may use a bitmap index intersection (reading both single-column indexes and ANDing the row sets), but this is slower than a single seek on the composite index.

If your query always filters on both columns together, a composite index is better. If queries sometimes filter on one column alone, you may need both — but measure before adding indexes defensively.

Redundant indexes waste space:

Copy
-- If you have (user_id, status, placed_at), a separate index on (user_id) is redundant
-- The composite index satisfies all queries that need (user_id) alone
-- Drop the single-column index to save write overhead

Any index whose leading columns are a prefix of another index is usually redundant.

Stale statistics cause bad plans. The query planner uses column statistics to choose between index scan and sequential scan. After a large data load, run ANALYZE to update statistics:

Copy
ANALYZE orders;  -- update statistics for orders table

In PostgreSQL, autovacuum runs ANALYZE automatically. If you load millions of rows in one batch, run ANALYZE manually before running queries.

5. Interview Cheat Sheet

Key sentences:

• "Index design starts from the query workload — I look at what columns are in WHERE, JOIN, ORDER BY, and SELECT, then design the index to serve that pattern."
• "Composite index column order matters: equality predicates first, then sort columns, then range predicates."
• "A covering index contains all columns the query needs, enabling an index-only scan with no heap access."
• "In PostgreSQL, foreign key columns are not automatically indexed — you must create them manually."

Common follow-ups:

Q: How do you identify missing indexes in production? A: pg_stat_statements shows queries by total execution time. For the slowest queries, run EXPLAIN (ANALYZE) to check for sequential scans on large tables. pg_stat_user_indexes shows which existing indexes are being used (or not). Also check the slow query log if configured.

Q: What is a covering index and when do you use it? A: A covering index contains all columns touched by a query (both WHERE and SELECT), allowing the database to return results from the index alone without accessing the table. Use it for hot read paths where you want to minimize I/O — especially for frequently-run lightweight queries that return a few columns from a large table.

Q: When would you drop an index? A: When idx_scan in pg_stat_user_indexes shows it's rarely or never used, when it duplicates the leading columns of another index, or when the write overhead on a high-throughput table outweighs the read benefit. Always verify by running EXPLAIN on the queries you think use it before dropping.