Database Query Optimization

DatabasesSeniordb

The Question

What techniques do you use to optimize database queries?

What a Strong Answer Covers

• EXPLAIN vs EXPLAIN ANALYZE
• seq scan vs index scan.

Senior-Level Answer

Query optimization is the discipline of reducing query latency and server load without changing correctness. It spans schema design, index strategy, query rewriting, and infrastructure choices.

**EXPLAIN / query plan analysis** is always the first step. Before optimising, measure. `EXPLAIN ANALYZE` (Postgres) or `EXPLAIN` (MySQL) shows whether the planner is performing a sequential scan, index scan, or nested loop join, and at what row estimate. Optimizing without looking at the query plan is guesswork.

**Indexing** is the highest-leverage change. Create indexes on columns used in `WHERE`, `JOIN ON`, and `ORDER BY` clauses. Composite indexes must match the query's column order—a composite index on `(user_id, created_at)` supports `WHERE user_id = ? ORDER BY created_at` but not `WHERE created_at = ?` alone. Partial indexes (Postgres) index only rows matching a condition, reducing index size for sparse queries. Covering indexes include all projected columns in the index itself, eliminating the heap fetch entirely.

**Avoiding N+1 queries**: N+1 occurs when code issues one query to fetch N parent records and then N individual queries for child records. Fix with JOIN or ORM eager loading (`SELECT IN` batch). Tools like Django Debug Toolbar or SQLAlchemy's echo mode surface N+1 in development.

**Query rewriting**: Rewrite correlated subqueries as JOINs or CTEs. Avoid `SELECT *`—projecting only needed columns reduces I/O and network transfer. Avoid functions on indexed columns in WHERE clauses (`WHERE YEAR(created_at) = 2024` prevents index use; `WHERE created_at >= '2024-01-01'` uses it).

**Pagination**: Offset pagination on large tables is expensive—the database must scan and discard all preceding rows. Keyset (cursor) pagination (`WHERE id > last_seen_id LIMIT 20`) is O(1) per page.

**Partitioning and sharding**: For very large tables, range or list partitioning prunes partitions at query time, reducing scanned data. Sharding distributes data across nodes but increases query complexity.

**Connection pooling**: Avoid opening a new database connection per request—connection setup is expensive. Use PgBouncer (Postgres) or application-level pools (SQLAlchemy pool). Pool exhaustion is a common performance cliff in high-concurrency applications.

What Separates a 2/3 from a 3/3

Common Mistakes

• Recommending indexes without analyzing the query plan first.
• Not distinguishing composite index column order—treating (a,b) and (b,a) as equivalent.
• Forgetting that over-indexing slows write performance (every index must be updated on INSERT/UPDATE/DELETE).
• Not mentioning N+1 as a query optimization problem distinct from index optimization.

Follow-Up Questions

• How does a covering index eliminate a heap fetch? — The index contains all columns the query needs (SELECT + WHERE), so the DB never has to look up the actual row—avoids random I/O.
• Why does wrapping an indexed column in a function prevent index use? — The index is built on the raw column value; the function produces a derived value not in the index. The planner must evaluate the function on every row.
• When would you choose a partial index over a full index? — When a query targets a small, well-defined subset of rows (e.g., WHERE status = 'pending')—the index is smaller and fits more easily in cache.
• How does keyset pagination scale better than offset pagination for 10M+ row tables? — Offset requires the DB to scan and discard N rows to reach the page start. Keyset uses an indexed condition to jump directly to the next page position—O(1) I/O.

Related Questions

• ACID
• Indexes
• Clustered vs Non-clustered
• Isolation Levels
• Normalization