SQL · Advanced interview

EXPLAIN (ANALYZE) shows how the planner executes the query — the join order, access methods (sequential scan vs index scan), and estimated vs actual rows and time per node. You look for full scans on big tables, nested loops over large row counts, big mismatches between estimated and actual rows (stale statistics), and expensive sorts. The bottleneck is usually the node with the most time or a row-estimate that's wildly off.

Missing or unusable indexes, non-sargable predicates (functions on columns), bad join order from stale statistics, returning or scanning too many rows, N+1 query patterns, and large sorts/hashes spilling to disk. Fixes: add the right index (sometimes composite or covering), rewrite predicates to use indexes, update statistics, select only needed columns, add pagination, and batch or join instead of looping. Always verify with the plan, not intuition.

A covering index contains all the columns a query needs (in the key or as included columns), so the database can answer entirely from the index without touching the table heap — an index-only scan. It eliminates the extra random I/O of fetching rows, which can dramatically speed read-heavy queries. The trade-off is a larger index and more write overhead.

Multi-Version Concurrency Control keeps multiple versions of a row so readers see a consistent snapshot without blocking writers and vice versa — readers don't take locks for reads. Writes still take row locks to prevent conflicting updates, and the engine decides visibility by transaction timestamps. MVCC (used by PostgreSQL, InnoDB, Oracle) is why 'readers don't block writers' and reduces lock contention compared to pure locking.

A deadlock occurs when two transactions each hold a lock the other needs, forming a cycle, so neither can proceed; the database detects this and aborts one. You avoid them by acquiring locks in a consistent order across the app, keeping transactions short, touching rows in a deterministic sequence, using lower isolation where safe, and adding retry logic since the loser's transaction is rolled back. Consistent lock ordering is the biggest lever.

Default to the database's sensible default (often READ COMMITTED) and raise it only where a specific anomaly would cause a correctness bug. Use REPEATABLE READ / SERIALIZABLE for operations that read-then-write based on what they read (to avoid lost updates and phantoms), accepting more blocking or serialization-failure retries. Often it's cleaner to use explicit row locks (SELECT ... FOR UPDATE) or optimistic concurrency than to globally raise isolation.

Partitioning splits one large table into smaller physical pieces by a key — range (e.g. by date), list, or hash — while it still appears as one table. It speeds queries that can prune to relevant partitions, makes bulk deletes (drop an old partition) cheap, and eases maintenance. You use it for very large tables, especially time-series data, where most queries target a slice.

Reads: add read replicas and route read traffic to them (accepting replication lag), plus caching. Writes: vertical scaling first, then sharding the data across nodes by a shard key when a single primary can't keep up — at the cost of cross-shard queries and transactions becoming hard. You also tune indexes, batch writes, and offload analytics to a separate warehouse. Sharding is a last resort because of its complexity.

N+1 is when you run one query to fetch a list, then one more query per item to fetch related data — 1 + N round trips that crush performance at scale, common with naive ORM lazy loading. Fixes: fetch the related data in a single join or a batched IN query (eager loading / dataloader pattern), so you do a constant number of queries. Connection pooling also helps by reusing connections instead of opening one per query.

You denormalize — duplicating or precomputing data — when read performance and query simplicity matter more than write simplicity, e.g. storing a cached count or embedding frequently-joined fields to avoid expensive joins at scale. What you give up is the single-source-of-truth guarantee: you now must keep the duplicated data in sync on every write, risking inconsistency. It's a deliberate trade of write complexity for read speed.

Options that stack: ensure proper indexing and prune unused indexes, partition by time or key, archive or roll off old rows to cold storage, and move heavy read/analytic load to replicas or a warehouse. For writes, batch and consider sharding. The right move depends on access patterns — often partitioning plus archiving handles time-series growth without sharding.

OLTP (transactional) systems handle many small, concurrent read/write transactions with low latency — your normalized application database. OLAP (analytical) systems run large aggregating queries over historical data, typically denormalized and stored columnar in a data warehouse. You keep them separate because their workloads conflict: you ETL/stream from OLTP into OLAP rather than running heavy analytics on the production database.