Database Performance Tuning — Interview Q&A
Q: Walk through your actual process when someone reports "the app feels slow" and a database is a suspected cause.
A: Start with data, not guesses — find the actual slow queries first (pg_stat_statements or the slow query log), don't assume which query is the problem. For the worst offenders, run EXPLAIN ANALYZE to see the real execution plan, not just the query text — a Seq Scan on a large table is usually the clearest, most actionable signal. From there follow the tuning hierarchy in order: is this a missing index (usually the fastest win), a genuinely bad query structure (a correlated subquery, an N+1 pattern from the application layer), or a configuration issue (memory settings, connection exhaustion) — and only after those are ruled out would hardware/infrastructure scaling be the answer, not the first reach.
Q: Why does column order matter in a composite index?
A: A composite index is only useful for queries filtering on a left-to-right prefix of its columns, the same way a phone book sorted by last-name-then-first-name is useless for finding everyone with a specific first name. Putting a range-filtered column (>, BETWEEN) before an equality-filtered column effectively stops the index from being useful for anything after it in the definition, because the B-tree can't maintain a useful sorted position past a range condition. The practical rule: equality-filtered, highly selective columns first, range-filtered columns last.
Q: What's the N+1 query problem, and why is it so common in production despite being easy to explain?
A: It's when code fetches a list of N records with one query, then issues a separate query per record to fetch related data — N+1 total queries where 1 or 2 would suffice with eager loading. It's common specifically because it's invisible during development: a test dataset of 5 records produces 5 extra queries, imperceptibly fast, and the problem only becomes visible once real production data volume makes N large enough that the cumulative round-trip latency is user-visible — by which point it's shipped and often not obviously connected to the original code change that introduced it.
Q: A read replica shows different data than the primary immediately after a write — is this a bug?
A: Not a bug — it's the expected behavior of asynchronous replication, where a write commits on the primary before it's necessarily propagated to replicas, typically within milliseconds but with no hard guarantee. It becomes a real, user-visible problem in a specific common pattern: a user submits data, the application redirects to a page reading from a replica, and the just-submitted data appears missing. The fix isn't avoiding replicas — it's routing reads that must reflect a just-completed write back to the primary, or the same session/connection that performed the write, while staleness-tolerant reads (dashboards, analytics) use replicas freely.
Q: How do you distinguish a genuinely slow query from a query that's fast but frequently blocked on a lock?
A: They produce the same visible symptom — high latency — but need entirely different fixes, so the diagnostic step matters. Check pg_locks/pg_stat_activity for actual lock waits, not just the query's own EXPLAIN ANALYZE timing in isolation — a query that runs in 2ms once it acquires its needed lock, but waits 500ms for that lock to free up, will show a fast plan but a slow overall experience. If lock contention is the real cause, the fix is about transaction design (shorter transactions, consistent lock ordering across code paths) rather than query optimization or indexing, which wouldn't help at all here.
Q: When would you reach for table partitioning, and what's the actual performance mechanism behind it?
A: Once a table is large enough that even well-indexed queries, and routine maintenance like VACUUM or reindexing, are slowed down by overall table size rather than query selectivity — that's the signal partitioning addresses, not query correctness. The mechanism is partition pruning: a query filtering on the partition key (commonly a date range) only scans the relevant partition, not the entire table, and maintenance operations can run per-partition. It's worth introducing specifically once that size threshold is real, not preemptively — added too early, it adds real complexity (every query implicitly needs to consider partition boundaries) without a corresponding benefit yet.
Q: How would you explain the difference in tuning approach between an OLTP system and an OLAP data warehouse to someone applying OLTP instincts to a warehouse that's performing poorly?
A: OLTP workloads are many small, fast queries — single-row lookups, small concurrent updates — where narrow, highly selective indexes and avoiding lock contention are the priorities. OLAP workloads are the opposite: fewer, much larger queries aggregating across millions of rows, where columnar storage (grouping data by column rather than row) and enough memory for sort/hash operations matter far more than narrow row-level indexes. Applying OLTP tuning instincts — adding many narrow indexes — to an OLAP warehouse mostly adds write overhead without addressing the actual bottleneck, which is usually scan/aggregation throughput; the real fix there is often a columnar engine (Redshift, ClickHouse, BigQuery) rather than more indexes on the existing row-oriented database.