Databases & SQL

What this area actually covers

Databases is the study of how data is stored so that it can be found again quickly and changed safely while other people are changing it too. Those two halves — retrieval and concurrency — generate nearly every question you will be asked.

In practice the area splits into two skills that are usually conflated. Using a database well means modelling a schema that constrains what can be wrong, writing queries that let the engine do its job, indexing deliberately, and knowing what a transaction gives you. Operating one means backups you have restored, replication and failover, upgrades without downtime, capacity planning, and being the person on the call when disk fills at three in the morning. Most engineers are hired for the first and quietly judged on how much of the second they understand.

Two things get bundled in wrongly. Analytics and warehousing overlap in language but differ in mechanics — a columnar warehouse optimised for scanning billions of rows behaves nothing like a row store optimised for fetching one, and advice from one context is often actively harmful in the other. Object-relational mappers are a separate topic: knowing an ORM's API is not database knowledge, and the most expensive database bugs come from engineers who only ever spoke to the database through one.

There is a third boundary that decides how deep an interview goes. Knowing SQL and knowing databases are different competencies. SQL is a language for expressing what you want; database knowledge is understanding what the engine will do about it. A candidate can write an elegant seven-table query with three window functions and have no idea why it takes ninety seconds, and another can write plainer SQL that runs in four milliseconds because they knew which access path the engine would pick. Both are useful. Only the second is what senior interviews are looking for.

The eight areas underneath

The section is divided into eight subsections, running from the language upwards to the engine and then out to operations. The first five apply to anyone who writes queries against a relational database; the sixth widens to the non-relational world; the last two go into engine internals and running the thing, which is where senior and DBA-adjacent interviews spend their time.

SQL Query Writing covers joins, window functions, aggregation and the analytical queries that get asked in a shared editor while someone watches. It is a subsection of its own because live SQL is a distinct performance: you are being assessed on whether you reach for the right construct, whether you handle nulls and grain correctly, and whether you can talk through a query while writing it. Window functions in particular separate candidates sharply, because they solve in one pass what people otherwise attempt with a correlated subquery.

Indexing covers B-tree mechanics, the order of columns in a composite index, covering indexes, and the reasons an index you created is being ignored. It exists separately because indexing is where the largest performance wins live and where the most confident wrong beliefs are held — that more indexes are better, that an index per column covers a multi-column predicate, that an index helps a query whose predicate wraps the column in a function.

Transactions & Isolation covers the ACID guarantees, the isolation levels, the specific anomalies each level permits, and locking against multi-version concurrency control. It is separate because it is the concurrency half of the whole subject, and because the guarantees are weaker and more engine-specific than almost anyone assumes before they read them carefully.

Query Optimisation covers reading execution plans, statistics and cardinality estimation, and diagnosing regressions in queries that used to be fast. Its own subsection because it is a diagnostic skill rather than a knowledge area: the material is a method for finding where estimate and reality diverged, and you can only learn it by reading plans.

Data Modelling covers normalisation, deliberate denormalisation, keys, constraints, and changing a schema while the system is under load. It is here because the shape of the schema decides the ceiling on everything above it — a model that permits contradictory states will produce data bugs that no amount of application code fully prevents — and because schema evolution is a genuinely operational skill.

NoSQL Stores covers document, key-value, wide-column and graph engines with selection criteria that do not depend on tribal loyalty. It is a subsection rather than a footnote because these engines are the right answer for real workloads, and because the reasons to choose them are precise: access pattern, consistency requirement, and write scaling characteristics rather than a dislike of migrations.

PostgreSQL Internals covers MVCC and vacuum, the write-ahead log, connection handling and the operational depth senior roles expect. It is engine-specific on purpose. Generic database knowledge stops being enough at a certain level, and PostgreSQL is common enough that its particular mechanisms — why a delete does not free space immediately, why long transactions block cleanup, why connection count matters more than you expect — are worth knowing concretely rather than in the abstract.

DBA & Operations covers backup and recovery, replication and failover, upgrades, capacity planning and incident response. It closes the section because it is the half that turns knowledge into responsibility, and because even engineers who will never hold the title get asked one question from it: when did you last verify a restore.

Where it sits in a real system

The database is almost always the part of your system that cannot be trivially scaled by adding another copy, and that single asymmetry explains its importance. Stateless application servers are cheap to duplicate. State is not: it has to be consistent, durable, and reachable, and those requirements resist the horizontal solution.

This is why performance investigations converge here. A slow endpoint is usually not slow CPU-side. It is one query missing an index; it is the same query run once per row of a result set; it is a lock held while an HTTP call is in flight; it is a connection pool exhausted so requests queue before they even reach the engine. You can spend a week optimising serialisation and win milliseconds, or find the sequential scan and win seconds.

Mechanically, a query goes through a parser, then a planner that chooses among candidate execution strategies using statistics about your data, then an executor.

flowchart TD
    A[SQL text arrives on a connection] --> B[Parse into a syntax tree]
    B --> C[Rewrite: views, rules, simplification]
    C --> D[Plan: cost candidate paths using statistics]
    D --> E[Execute the chosen plan]
    E --> F{Pages in the buffer cache}
    F -- Yes --> G[Return rows at memory speed]
    F -- No --> H[Read from disk, then return]

The branch at the bottom is why the same query has two very different response times with no change to the plan, and why a benchmark run twice tells you less than you think.

The planner is a cost model, not a rule follower, and that is the crux of the area: you do not tell the database how to run a query, you create the conditions — indexes, accurate statistics, a predicate it can reason about — under which it chooses well. Underneath that sits storage, where data lives in fixed-size pages, a write-ahead log makes durability possible without flushing everything immediately, and a buffer cache is the difference between memory speed and disk speed.

The write-ahead log deserves a sentence of its own because it explains several otherwise puzzling behaviours. Durability requires that a committed change survives a crash, and flushing every modified page to its final location at commit time would be intolerably slow, so the engine instead appends a compact record of the change to a sequential log and flushes that. The data pages catch up later. This is why commit latency is dominated by one sequential write rather than many random ones, why a crash recovery takes time proportional to how much log is unapplied, and why the same log can be shipped to a replica to reproduce the primary's state.

Who does this work

Backend engineers are the largest population and the ones whose database skill is most variable. A day includes writing a migration, discovering a query that degraded as a table grew, adding an index and checking the plan changed, and deciding whether a piece of denormalised data is worth the update cost.

Data engineers work the movement and modelling side: pipelines, batch and streaming loads, dimensional models in a warehouse, and the correctness problems that come with late-arriving or duplicated data. Their tuning instincts are scan-oriented rather than seek-oriented, and the difference matters.

DBAs and database reliability engineers own the running system. The day is replication lag, vacuum and bloat, failover drills, restore tests, upgrade planning, and reviewing other people's migrations before they lock a busy table. This role was widely declared dead when managed services arrived; what actually happened is that provisioning and patching went away while tuning, capacity and incident work did not.

Analytics engineers and BI developers write the heaviest SQL of anyone — window functions, deep aggregations, careful handling of nulls and grain — and are specified to rather than specifying.

The row worth noticing is the DBA's. Everyone else asks about speed or correctness; the person who has been paged asks what lock the change takes, because that is the question whose wrong answer takes the site down during business hours.

Demand, adoption and how that is changing

Demand is high and unusually stable, because the underlying need has not changed in decades while the surface keeps being repackaged. Every application stores data; someone has to decide how.

The specific pressures are worth naming. Managed and serverless databases removed a lot of operational toil, which reduced pure administration roles but increased the number of engineers expected to understand what they are now responsible for themselves. Cost pressure made query efficiency a finance conversation, because in a consumption-priced service a bad query has an invoice attached. Data volumes at ordinary companies now routinely exceed what a naive schema tolerates, so problems that used to appear at large scale appear at medium scale. And the relational core has absorbed features that once justified a separate store: PostgreSQL now handles JSON documents, full-text search and vector similarity search competently enough that the default answer to "which database" is more often "the one we already have".

That last point is the honest shape of the NoSQL question, and it deserves to be answered without tribalism. Document, key-value, wide-column and graph stores are not a fad and not a mistake; they are engines with different trade-offs, and each earns its place where the trade matches the workload.

What has changed is the framing: the mid-2010s claim that relational databases could not scale was mostly a claim about the hardware and the products of the time, and it did not survive. The mature position is that the interesting question is your access pattern and consistency requirement, and "we chose a document store because we did not want to write migrations" is the answer that ages worst — partly because a schema does not disappear when you stop declaring it, it simply moves into application code where nothing enforces it.

What makes it hard

The difficulty is that the database's behaviour is emergent. Nothing in your code says "use a sequential scan"; that decision comes from the planner's estimate of how many rows a predicate will match, which comes from statistics that may be stale, on a data distribution that changes underneath you. So the same query is fast for months and then is not, and nothing in the diff explains it.

Three things carry most of the interview weight, and they are the same three that carry most of the production weight.

Indexes are ordered structures, and every property follows from the ordering. A B-tree index supports equality and range lookups and lets you read rows already sorted; it cannot help a predicate that hides the column inside a function, and it cannot skip the leading column of a composite index to use a later one. That last rule is why the column order in a multi-column index is a design decision rather than a detail, and why adding an index per column is a common and ineffective reflex.

The practical consequences are enumerable, which makes them good interview material:

Every index also has a cost that nobody sees in a benchmark of reads: it must be updated by every insert, update and delete that touches its columns, it consumes space and cache that the table itself wanted, and it lengthens the planner's search. Indexes are a read subsidy paid for by writes, and the correct number is the smallest set that serves the queries you actually run.

Query plans are the only honest source of truth about why something is slow. Reading one means comparing what the planner estimated with what actually happened, and the gap between the two is the diagnosis.

-- ANALYZE runs the query; BUFFERS shows how much was read from cache versus disk.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders WHERE customer_id = 4210 AND status = 'shipped';

-- The signal is the mismatch, not the node type:
--   Seq Scan on orders  (cost=... rows=1 width=..) (actual rows=284000 loops=1)
-- Estimated one row, found 284,000. The plan was chosen for a query that does
-- not exist, which is a statistics problem, not an index problem.

Two more habits make plan reading productive. Read the plan inside out, because the innermost nodes execute first and a slow parent is often just carrying a slow child. And watch the loops counter: a node reporting a fast actual time with fifty thousand loops is the same query executed fifty thousand times, which is the plan-level signature of the N+1 problem that an ORM produced somewhere above.

Transactions are where confidence outruns understanding. ACID is easy to recite and its guarantees are weaker than most people assume, because isolation is a spectrum you choose.

Under PostgreSQL's default Read Committed, each statement sees a fresh snapshot, so two statements in one transaction can see different data. Repeatable Read gives you a single snapshot for the transaction, and PostgreSQL will abort a transaction rather than let it write over a change it could not see. Serializable is the only level that guarantees the outcome matches some serial order, and because PostgreSQL implements it optimistically, your application must be prepared to catch a serialisation failure and retry the whole transaction. Defaults differ between engines, so "the database handles it" is not a portable claim.

The anomaly that catches good engineers is the lost update, because it needs no exotic conditions at all.

sequenceDiagram
    participant A as Session A
    participant D as Database
    participant B as Session B
    A->>D: Read balance, gets 100
    B->>D: Read balance, gets 100
    A->>D: Write balance 90
    B->>D: Write balance 80
    D-->>B: Committed, A's deduction is gone

What to notice is that neither session did anything wrong and no error was raised. The fix is either to make the write relative rather than absolute, or to take the read with SELECT ... FOR UPDATE, or to add a version column and reject a write whose version has moved.

The part where experience is not substitutable is production change. Anyone can write a migration; knowing which migration takes a lock that blocks every write on your busiest table, and how to achieve the same result without it, comes from having got it wrong once.

Why a deleted row does not free space

One mechanism explains a cluster of PostgreSQL behaviours that look unrelated until you know it, and it is worth the five minutes because senior interviews reach for it. To let readers proceed without blocking writers, PostgreSQL does not overwrite a row when you update it. It writes a new version and marks the old one as no longer visible to transactions starting after a certain point. A delete does the same thing without the new version. Nothing is reclaimed at that moment.

stateDiagram-v2
    [*] --> Live
    Live --> Dead: updated or deleted
    Dead --> Reclaimable: no transaction can still see it
    Reclaimable --> Free: vacuum marks the space reusable
    Free --> Live: a later insert reuses the page

The transition that stalls is the middle one. A single long-running transaction holds the visibility horizon back, so dead rows across the whole database stay unreclaimable for as long as it is open.

That one fact explains why a table can grow while its row count falls, why an idle transaction left open by an application bug causes bloat somewhere else entirely, why autovacuum exists and why it sometimes cannot keep up on a heavily updated table, and why counting rows is not free in this engine. It also explains a rule of thumb worth carrying: keep transactions short, and never hold one open across a network call to something you do not control.

Changing a schema without stopping the system

Migrations are the most consequential thing most backend engineers do to a database, and they are examined precisely because the failure is public. The underlying rule is simple: some operations need only a brief lock, and some hold an exclusive lock for as long as they take to rewrite the table. On a table with a hundred rows the distinction is invisible. On a table with two hundred million rows, the second kind is an outage.

The safe pattern for almost every change is the same three-phase shape, sometimes called expand and contract. First expand: add the new structure in a form that is compatible with the code currently running, which usually means nullable and without a default that forces a rewrite. Then migrate: deploy code that writes both old and new, and backfill the existing rows in small batches with a pause between them so that replication and vacuum can keep up. Then contract: once nothing reads the old structure, drop it, and only then add the constraint that assumes the new one is fully populated.

-- Phase 1, cheap: a nullable column takes a brief lock and no table rewrite.
ALTER TABLE orders ADD COLUMN currency text;

-- Phase 2, backfill in batches rather than one statement over 200M rows.
UPDATE orders SET currency = 'GBP'
WHERE currency IS NULL AND id BETWEEN 1 AND 50000;

-- Phase 3, validate separately so the long check does not block writes.
ALTER TABLE orders ADD CONSTRAINT orders_currency_not_null
  CHECK (currency IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_currency_not_null;

The two-step constraint at the end is the detail worth carrying into an interview. Adding a constraint normally verifies every existing row while holding a lock; adding it as NOT VALID and validating afterwards splits that into a brief lock and a long non-blocking scan. The same instinct applies to index creation, where building an index concurrently avoids blocking writes at the cost of taking longer and being able to fail and leave an invalid index behind that you must clean up.

One more habit separates people who have done this from people who have read about it: they set a short lock timeout before running the migration. If the statement cannot acquire its lock within a couple of seconds it fails immediately rather than queueing, and that matters because a queued exclusive lock request blocks every subsequent reader behind it. A migration that waits politely for a long-running query can take down a site more thoroughly than one that rewrites a table.

Why study it

If you are a backend engineer who wants to be conspicuously better than your peers, this is the highest-leverage area available, and the reasoning is concrete rather than motivational. The knowledge compounds because the fundamentals barely move — B-trees, plans, isolation and normalisation have outlived several complete turnovers of framework fashion. It is unusually diagnostic in interviews, because the gap between engineers who can read a plan and engineers who add indexes hopefully is wide and immediately visible. And it pays off on the problems that get attention: when something is slow enough that management notices, the fix is usually in this layer, and being the person who finds it is career-visible in a way that refactoring is not.

There is a quieter argument too. Database knowledge is the most portable thing you can learn, because it survives changing language, framework, company and decade. An engineer who understands isolation levels understands them in Java, in Go, in Python and in whatever replaces them, and the same is not true of almost anything else you could spend the same hours on.

Skip it, or rather deprioritise it, if you are heading for frontend, mobile, embedded or graphics work — a working knowledge is enough there, and depth is better spent elsewhere. And if you want to work on databases rather than with them, that is a systems-programming path involving storage engines and consensus protocols, which is a different and much narrower job market.

Your first hour

Do not read about indexes. Create a slow query and fix it, because the mechanism only becomes intuitive once you have watched a plan change.

In a local PostgreSQL, make a table with enough rows that scanning hurts:

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id int NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO orders (customer_id, status, created_at)
SELECT (random() * 50000)::int,
       (ARRAY['pending','shipped','cancelled'])[1 + (random() * 2)::int],
       now() - (random() * interval '365 days')
FROM generate_series(1, 2000000);

ANALYZE orders;  -- without this the planner is guessing about your data

Now run EXPLAIN (ANALYZE, BUFFERS) on a lookup by customer_id and read the timing and the node type. Add an index on customer_id, re-run, and compare. Then run the same query with status added to the predicate and see whether the index still helps. Replace it with a composite index on (customer_id, status) and confirm the improvement. Then query on status alone and watch the composite index be ignored — that is the leading-column rule, felt rather than memorised. Finally wrap the column in a function, as in WHERE lower(status) = 'shipped', and watch the index stop being used at all.

If you have appetite for one more experiment, do the statistics one, because it teaches the lesson that the plan is chosen from estimates rather than facts. Insert a large batch of rows all with the same customer_id, do not run ANALYZE, and re-run the lookup. The planner will still believe the old distribution and may choose an index scan for a predicate that now matches a substantial fraction of the table, which is exactly the shape of a real production regression. Run ANALYZE and watch the plan change with no change to the query or the indexes.

You end the hour with a handful of saved plans, before and after, and a demonstrated understanding of column order and predicate shape. That is more than most candidates bring to a senior interview.

What this is not

It is not ORM proficiency. An ORM generates SQL, and the skill is being able to see what it generated and judge it; an engineer who cannot is at the mercy of a query they never wrote. Turning on query logging in a local environment for one afternoon is the fastest cure, because the number of statements a single page load produces is usually the surprise that fixes the habit.

It is not data science or analytics. Writing SQL to answer a business question is a valuable adjacent skill, but it is about grain, definitions and correctness of interpretation, not about storage and concurrency.

It is not the same as distributed systems, though they meet at replication. Partitioning, quorums and consensus are systems topics that happen to be implemented inside databases, and confusing "I understand replication lag" with "I understand consensus" is a common overreach.

It is not a contest between SQL and NoSQL, either, and treating it as one signals that you learned the subject during the argument rather than after it. The engines have different shapes and the question is always which shape your access pattern needs.

And a managed service is not an absence of database work. The provider handles the machine; the schema, the indexes, the locks, the plans and the bill are still yours.

Nobody remembers the engineer who tuned the JSON serialiser. They remember the one who found the sequential scan.