SQL and Relational Databases
SQL is more than a query language. A relational database combines a declarative data model, constraints, indexes, a cost-based optimizer, concurrency control, logging, recovery, and operational tooling. In an interview, the senior distinction is not knowing every join type; it is knowing which invariants belong in the database, what transaction and isolation guarantees the workflow needs, how access patterns shape indexes, and how the design behaves during contention, failover, migration, and growth.
How to use this chapter
- Read the mental model and mechanics without taking notes.
- Close the page and explain the topic aloud in five minutes.
- Work the quantitative example on paper.
- Answer the question ladder without revealing the answer signals.
- Complete one design drill and score yourself with the senior checklist.
Learning objectives
- Model entities, relationships, invariants, and lifecycle state with explicit keys and constraints.
- Explain transactions, MVCC, locking, isolation anomalies, and safe retry behavior.
- Design indexes from concrete predicates, ordering, cardinality, and write cost.
- Interpret query plans and distinguish logical query complexity from physical execution cost.
- Plan online schema changes, backfills, partitioning, replication, and archival.
- Recognize when a relational database should remain the source of truth even if other stores serve derived views.
Mental model
Treat a relational database as an invariant-preserving state machine backed by an ordered recovery log. A client proposes a transaction. The database checks constraints, coordinates concurrent readers and writers, records enough information to recover, and exposes committed state according to an isolation contract. SQL describes the result you want; the optimizer chooses an execution plan using schema metadata and statistics.
Core mechanics
Relational modeling and constraints
Give every durable entity a stable primary key. Natural keys can encode a real invariant—such as an ISO code or tenant-scoped external identifier—but surrogate keys often simplify references and changes. Use UNIQUE, NOT NULL, CHECK, and foreign-key constraints for invariants the database can enforce. Constraints provide protection across every writer, including scripts and future services; application-only checks have a race between “check” and “write.”
Normalize data to remove update anomalies and make ownership clear. Denormalize deliberately when a measured access path needs fewer joins or an immutable snapshot is required. For example, an order should often retain the product description and price agreed at purchase rather than read the current product row. That is domain history, not careless duplication.
Transactions and atomic workflows
A transaction groups changes that must commit or abort together. Atomicity is not the same as exactly-once business execution: clients can lose the commit response and retry. Expose an idempotency key or operation identifier, store it under a unique constraint, and make retries return the prior result.
Keep transactions focused. Long transactions retain row versions, delay cleanup, increase lock duration, and widen the chance of conflict. Avoid waiting for remote APIs while holding database locks. A common pattern is: commit local state plus an outbox event, then let a relay perform external work. Compensating actions are useful across boundaries, but they do not restore all observably intermediate states and therefore require domain-specific semantics.
Isolation, MVCC, and anomalies
Multi-version concurrency control lets readers observe a snapshot while writers create newer versions. The exact behavior depends on the database and isolation level. Know the anomalies that matter: dirty reads, non-repeatable reads, phantoms, lost updates, write skew, and serialization failures. Labels such as “repeatable read” are not perfectly portable across products, so state the needed guarantee rather than relying only on the label.
Under weaker isolation, use atomic conditional writes, row locks, version columns, or uniqueness constraints to protect specific invariants. Under serializable isolation, the database may abort a transaction that cannot be safely ordered; the application must retry the entire transaction with bounded backoff. A senior answer distinguishes contention from correctness: stronger isolation can make a design correct yet operationally poor if every request conflicts on one row.
Indexes and physical access
A B-tree index supports equality, ordered range scans, prefix-compatible ordering, and ordered retrieval. Composite index order should reflect predicates and sorting: equality-constrained columns commonly precede range or ordering columns, but verify with plans. Covering or included columns can avoid table lookups at the cost of larger indexes and more write amplification. Partial indexes target a selective subset, such as active jobs, while expression indexes support normalized lookups.
Every index consumes storage, cache, maintenance I/O, and transaction work. Low-cardinality fields alone are often weak indexes unless combined with a selective predicate or partial condition. Indexing every query independently creates redundant prefixes and write cost. Design a minimal portfolio around critical paths, then measure hit rates, scan counts, page reads, and write overhead.
Query planning and execution
The optimizer estimates row counts and costs, then selects scans, join order, join algorithms, aggregation, and parallelism. Nested-loop joins can be excellent for a small outer input with indexed lookups; hash joins suit larger equality joins; merge joins exploit sorted inputs. A query that is fast with ten rows can collapse when estimates, data skew, or parameter values change.
Use EXPLAIN and runtime plans to compare estimated and actual rows, loops, buffer reads, spills, and timing. Common problems include stale statistics, correlated columns, non-sargable predicates, implicit casts, large offsets, accidental fan-out, and selecting far more columns than needed. Keyset pagination using a stable ordered cursor usually scales better than large OFFSET values and behaves more predictably during concurrent inserts.
Locks, deadlocks, and hot rows
Databases use row, page, table, predicate, metadata, and advisory locks depending on operation and engine. A deadlock is a wait cycle; the database breaks it by aborting a participant. Acquire resources in a consistent order, keep transactions short, and retry aborted work safely. Lock timeouts are a guardrail, not a substitute for fixing contention.
Hot counters, global sequences, inventory rows, and work queues can serialize a system. Techniques include atomic increments, bucketed counters, append-only facts plus asynchronous aggregates, reservation tokens, SKIP LOCKED-style worker claiming, or partitioning by tenant/resource. Each changes read semantics or complexity; explain the invariant that remains authoritative.
Partitioning, replication, and lifecycle
Table partitioning can improve pruning, retention, maintenance, and bulk movement when the partition key matches access patterns. It does not automatically accelerate all queries, and too many partitions increase planning and metadata overhead. Time partitions are common for events; hash partitions can spread write load; tenant partitions can aid isolation but create skew.
Read replicas can absorb stale-tolerant queries but introduce replication lag and read-after-write surprises. Route correctness-sensitive reads to the primary or use a session/log-position barrier. Archive or summarize old data according to access and legal requirements. Capacity planning includes index size, write-ahead log volume, vacuum/compaction work, backup windows, restore rate, and failover headroom—not only table bytes.
Online schema evolution
Use expand-and-contract changes: add a compatible column/table/index, deploy code that can handle both forms, backfill in bounded batches, validate, switch reads, and later remove the old representation. Avoid a single transaction that rewrites a huge table or holds a long metadata lock. Throttle backfills against replication lag, I/O, and foreground latency.
Dual writes from application code can diverge when one write succeeds. Prefer a transaction, trigger, change-data-capture pipeline, or repairable migration with explicit reconciliation. Test rollback at each phase and retain observability for old and new paths until the cutover is complete.
Decision table
| Decision | Prefer the first option when… | Prefer the second option when… | Senior caveat |
|---|---|---|---|
| Normalized vs denormalized | Writes and invariants span shared entities; multiple paths update the same facts. | Reads dominate and duplicated fields are immutable or maintained by a defined pipeline. | Name the source of truth, freshness contract, and repair path for every duplicate. |
| Optimistic vs pessimistic concurrency | Conflicts are rare and retry cost is low. | Conflicts are expected or work must reserve a resource before proceeding. | Optimism needs version checks and full-operation retry; locks need bounded hold time and ordering. |
| Read committed vs serializable | The workflow can protect its invariants with atomic statements and constraints. | Arbitrary concurrent transactions must behave as some serial order. | Product semantics differ; serializable usually requires retry and hotspot analysis. |
| Composite index vs separate indexes | A critical query uses a stable combination and order of predicates. | Predicates are independently selective across different query families. | Some engines combine indexes, but one well-shaped index often avoids extra work; verify plans. |
| Offset vs keyset pagination | Datasets are small and random page numbers are essential. | Feeds are large, ordered, and require stable low-latency continuation. | Cursor fields need a total order and encoded filter/version context. |
| Single database vs split stores | Transactions and joins are central and scale fits operational limits. | Distinct workloads need different isolation, search, graph, analytical, or geographic properties. | Splitting creates ownership, duplication, consistency, migration, and incident-response costs. |
Quantitative reasoning
Failure modes and production signals
| Failure mode | What users see | Likely cause | Mitigation / design response | Useful signals |
|---|---|---|---|---|
| Bad query plan | A formerly fast endpoint becomes seconds slow | Estimate error, skew, stale statistics, parameter sensitivity | Inspect runtime plan, refresh/improve statistics, rewrite or index, pin only as last resort | Estimated vs actual rows, buffers, spills, plan hash, query p99 |
| Lock convoy | Requests pile up behind one transaction | Long transaction, hot row, metadata lock | Cancel/limit blocker, shorten work, partition hotspot, consistent lock order | Lock waits, blocker graph, transaction age, queue depth |
| Deadlock | Some transactions abort intermittently | Inconsistent resource acquisition order | Retry idempotently, standardize order, reduce transaction scope | Deadlock reports, aborted tx rate, involved statements |
| Replica lag | Users do not see recent writes or failover loses freshness | Apply capacity below write rate, long query, network fault | Route critical reads, reduce workload, catch up/reseed, expose staleness | Replay lag bytes/time, apply rate, oldest snapshot |
| Connection storm | Database refuses sessions or spends CPU scheduling | Unbounded pools, deploy/failover reconnect burst | Pool/proxy limits, jittered reconnect, admission control, reserved admin access | Connections, auth rate, queue wait, memory per backend |
| Migration overload | Latency and lag rise during backfill/index build | Unthrottled scan/write amplification | Batch and throttle, online operations, pause on SLO signals, validate incrementally | Backfill rate, WAL, I/O, lag, lock duration |
| Table/index bloat | Storage and cache misses grow despite stable live rows | Old versions not reclaimed, churn, long snapshots | Fix long transactions, tune cleanup, rebuild selectively, reduce churn | Dead tuples/versions, page density, vacuum lag, disk growth |
Senior-level lenses
Put invariants at the narrowest authoritative boundary
A uniqueness rule enforced only by two application replicas is not atomic. Use a database constraint or a single serialized owner where possible. When an invariant spans shards or external systems, explicitly redefine it through reservations, ledgers, escrow, or asynchronous reconciliation rather than claiming a global transaction that does not exist.
A ledger is different from a mutable balance
For money, quota, inventory, and entitlements, append immutable movements with unique operation IDs and derive balances. A cached balance may speed reads, but the ledger supports audit and repair. Double-entry or conservation checks catch classes of corruption that a lone mutable number cannot.
Isolation is a workload decision
Do not recite ACID and move on. Walk through two concurrent transactions and the exact invariant. Show what can interleave, which statement detects conflict, what is locked, and how retry works. Then discuss whether the chosen contention point can meet throughput.
Indexes are part of the write path
An index is a materialized ordering maintained synchronously with every relevant write. Include its cache footprint, split/maintenance cost, uniqueness locking, replication volume, and migration time. Query latency gains can shift the bottleneck to ingest and recovery.
Failover changes semantics unless clients participate
Promotion can expose lost acknowledged writes under asynchronous replication, stale DNS/pools, duplicate retries, and old-primary split brain. Define fencing, durability mode, recovery point, read/write routing, and how clients recognize retryable outcomes.
The backup is only useful at restore speed
Report recovery point objective and recovery time objective separately. Test full and point-in-time restore, permissions, encryption keys, schema compatibility, and application reconciliation. A multi-terabyte backup with a 100 MiB/s restore path may violate a four-hour objective regardless of backup success rate.
Interview question ladder
What does a transaction provide?
Show strong-answer signals
Atomic commit/abort, consistency through declared invariants, isolation of concurrent work according to a level, and durability according to the engine/configuration; distinguish ACID from business exactly-once.
Why does an index speed reads but slow writes?
Show strong-answer signals
It provides a searchable ordered/hashed structure, but every insert/update/delete may maintain pages, logs, cache, and constraints.
What is normalization?
Show strong-answer signals
Structuring relations to reduce duplication and update anomalies; denormalize only with explicit ownership and synchronization semantics.
Design indexes for tenant_id, status, created-time feed queries.
Show strong-answer signals
Start from exact filters/order, likely composite (tenant_id, status, created_at, id) or partial active index, cursor pagination, selectivity, coverage, and write cost; verify plan.
How do you prevent overselling the last item?
Show strong-answer signals
Atomic conditional decrement or locked inventory row, reservation expiry, unique operation ID, transaction boundaries, contention and sharding plan.
Explain read-after-write failure with a read replica.
Show strong-answer signals
Async lag means a subsequent read can hit an older log position; use primary/session stickiness, commit-position wait, or expose eventual freshness.
Design a payment ledger schema and posting transaction.
Show strong-answer signals
Immutable entries, transaction/account IDs, balanced postings, unique idempotency key, currency/precision, states, constraints, reconciliation, read model, audit and reversal rather than deletion.
A table reaches five billion rows. What do you do?
Show strong-answer signals
First inspect access/retention/indexes; then partition/archive, keyset scans, replicas, derived stores, maintenance and backup; shard only when measured single-node limits or isolation demand it.
Plan a zero-downtime primary-key migration.
Show strong-answer signals
Expand schema, stable mapping, dual-compatible code, transactional/CDC backfill, verification, foreign-key/index migration, switch reads/writes, rollback checkpoints, cleanup.
Serializable transactions show rising aborts. Diagnose and redesign.
Show strong-answer signals
Find conflicting predicates/rows, verify retry correctness, shorten transactions, partition hot invariants, use commutative updates/reservations, bound admission, and preserve semantics.
Create a relational data platform for hundreds of teams.
Show strong-answer signals
Golden schemas/ownership, tenancy and isolation tiers, migration tooling, connection budgets, query governance, backups/restore tests, CDC contracts, SLOs, cost allocation, and escape hatches.
When should a service not own its own database?
Show strong-answer signals
Balance autonomy against cross-domain invariants, operational maturity, shared reporting, coupling, compliance, and migration cost; reject both a universal shared schema and dogmatic database-per-service.
Design drills
Design seat holds and purchases for a popular event. Include schema, indexes, hold expiry, idempotency, contention, payment boundary, and reconciliation.
What the interviewer is testing
Transactions under hotspot contention and ambiguous external outcomes.
Model posts and follows, then compare fan-out reads, materialized timelines, keyset pagination, partitioning, and replica use.
What the interviewer is testing
Relational source of truth plus scalable derived views.
A 3 TiB table needs a new non-null canonical customer ID. Produce a phased rollout, validation queries, throttles, and rollback plan.
What the interviewer is testing
Operational safety rather than only final schema elegance.
Common weak answers and how to improve them
“SQL is vertically scalable; NoSQL is horizontally scalable.”
Show the stronger answer
Modern relational systems can partition and replicate; choose by invariants, access patterns, ecosystem, and operational cost.
“Use ACID transactions.”
Show the stronger answer
Name the exact transaction boundary, isolation behavior, conflicts, retries, and external side effects.
“Add an index to every filtered column.”
Show the stronger answer
Design a minimal index portfolio from predicates/order/selectivity and account for write/recovery cost.
“Read replicas solve scale.”
Show the stronger answer
They solve some read capacity, while adding staleness, lag, routing, failover, and operational concerns.
“Shard when the table is large.”
Show the stronger answer
Size alone is not the trigger; identify the exhausted resource, access locality, key, cross-shard operations, and resharding path.