Database fundamentals

Almost always a B+ tree: a shallow, wide, sorted structure whose leaf nodes hold the indexed values in order along with pointers back to the rows. Shallow and wide is the whole point, because each level is one disk page, so finding a value among a hundred million rows takes three or four page reads rather than a scan. The structure being sorted is what makes range queries, prefix matches and ORDER BY on the indexed column cheap as well as equality lookups.

A hash index answers equality in one step and cannot answer anything else — no ranges, no sorting, no prefix matching, no "greater than". A B+ tree gives up a little equality speed to keep the data ordered, and ordering is what most real queries need. The leaves are also linked, so once you have found the start of a range you walk sideways through the leaf pages rather than descending the tree again for each row.

The property that matters is that depth grows logarithmically with a very large base, so the tree is almost flat even at enormous row counts.

flowchart TD
    R[Root page<br/>a few hundred keys] --> I1[Internal page]
    R --> I2[Internal page]
    R --> I3[Internal page]
    I1 --> L1[Leaf: sorted values + row pointers]
    I2 --> L2[Leaf: sorted values + row pointers]
    I3 --> L3[Leaf: sorted values + row pointers]
    L1 -.linked.-> L2
    L2 -.linked.-> L3

Each page is one disk read and holds a few hundred keys, so the fan-out per level is in the hundreds rather than two.

fan-out 300, rows addressed per level
  depth 1        300
  depth 2     90,000
  depth 3 27,000,000
  depth 4  8.1 billion

So a lookup in a 27-million-row table is three page reads, and the upper
levels are almost always already in memory — often one physical read.

Two consequences follow. Adding rows barely deepens the tree, which is why index performance degrades gracefully rather than falling off a cliff — the thing that actually degrades is cache residency once the index stops fitting in memory. And the dotted links between leaves are what make ranges cheap: having descended once to find the start, the engine walks sideways through leaf pages instead of re-descending for every row, so returning ten thousand rows in a range costs barely more than finding the first one.

A clustered index determines the physical order of the rows, so the table is the index and there can be only one. A non-clustered index is a separate structure holding the key plus a reference back to the row. The consequence is that a non-clustered lookup usually costs an extra step to fetch the row — the bookmark or heap lookup — and that step is why the optimiser sometimes prefers a full scan over an index it could have used.

An index on (a, b) is sorted by a first, then by b within each value of a, exactly like a phone book sorted by surname then forename.

INDEX (customer_id, created_at)

  customer_id | created_at
  ------------+------------
  1001        | 2026-01-04     <- sorted by customer, then date within customer
  1001        | 2026-03-19
  1001        | 2026-07-02
  1002        | 2026-02-11
  1002        | 2026-06-30

Now three queries against it:

-- Uses the index fully. Seeks to customer 1001, then walks the date range.
WHERE customer_id = 1001 AND created_at > '2026-02-01';

-- Uses the index. A leading-column-only predicate is a clean prefix.
WHERE customer_id = 1001;

-- Cannot seek. Dates are only ordered *within* a customer, so rows for
-- 2026-06-30 are scattered across the whole index.
WHERE created_at > '2026-06-01';

This is the leftmost prefix rule: an index on (a, b, c) serves predicates on a, on a, b, and on a, b, c, but not on b alone or c alone. Trying to find a surname when you only know the forename means reading the entire book.

Two refinements worth offering unprompted. The first is that the last usable column may be a range — a = ? AND b > ? seeks fine, but a > ? AND b = ? stops seeking at a, because once you are scanning a range of a the ordering of b no longer helps. The second is that the rule kills duplicate indexes: if you have (a, b) you do not also need (a), and noticing that in a review removes write cost for free.

An index containing every column a query needs, so the database answers from the index alone and never touches the table. That removes the row lookup entirely, which on a query returning many rows is often the difference between fast and unusable. Most engines let you append non-key columns for exactly this purpose, which keeps them out of the sorted key — cheaper to maintain — while still making the index self-sufficient for that query.

Because the index stores the column's values, not the function's results. Once you write WHERE lower(email) = 'x' or WHERE year(created_at) = 2026, the database cannot use the sorted order of email or created_at, so it evaluates the function for every row. The fixes are to rewrite the predicate as a range — created_at >= '2026-01-01' AND created_at < '2027-01-01' — or to create an index on the expression itself where the engine supports it.

Because an index only pays for itself when it eliminates most of the table. An index on a boolean matching half the rows means the engine would do a lookup for every second row, which is more random I/O than reading the table sequentially, so the optimiser correctly ignores it. The exception is a skewed distribution — a status where 0.1% of rows are failed — and a partial index covering only those rows is both small and genuinely selective.

Storage, memory, and write amplification. Every insert, delete and update to an indexed column must maintain every affected index, so a table with eight indexes does roughly nine writes per row change, and each one competes for the same buffer cache the table wanted. That is why the correct number of indexes is the smallest set covering the queries you actually run, and why finding unused indexes is one of the cheapest performance wins available.

Atomicity, that a transaction's changes all apply or none do. Consistency, that committed data satisfies declared constraints — the weakest and most often misdescribed letter, since it refers to your constraints rather than to anything the database invents. Isolation, that concurrent transactions do not see each other's partial work, to a degree set by the isolation level. Durability, that a committed transaction survives a crash, which is what the write-ahead log is for.

The levels are defined by which anomalies they permit, so the table is the definition rather than a summary of it.

level              dirty read   non-repeatable read   phantom read
-----------------  -----------  --------------------  ------------
read uncommitted   possible     possible              possible
read committed     prevented    possible              possible
repeatable read    prevented    prevented             possible*
serializable       prevented    prevented             prevented

* the standard permits phantoms here; several engines prevent them anyway.

The three anomalies, concretely. A dirty read sees another transaction's uncommitted change, which may then roll back — you acted on data that never existed. A non-repeatable read is reading one row twice in a transaction and getting different values, because someone committed an update in between. A phantom read is running the same query twice and getting different rows, because someone inserted a row matching your predicate.

-- Non-repeatable read at read committed:
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- 500
                                             -- another txn commits a withdrawal
SELECT balance FROM accounts WHERE id = 1;   -- 300, inside the same transaction
COMMIT;

The trap is defaults. PostgreSQL and Oracle default to read committed, MySQL's InnoDB to repeatable read, SQL Server to read committed — so identical application code behaves differently across engines, and code that was correct on one is subtly wrong on another. Knowing your engine's default is more useful than reciting the table.

Snapshot isolation gives each transaction a consistent view of the database as of its start, so readers never block writers and never see partial work. It is not serializable: two transactions can each read a consistent snapshot, each make a decision valid in isolation, and commit a combined state that no serial order could produce — write skew. The classic case is two doctors both checking that another is on call and both signing off, leaving nobody on call.

Multi-version concurrency control: instead of locking a row for reading, the engine keeps multiple versions of it and shows each transaction the version appropriate to its snapshot. That is why readers do not block writers and writers do not block readers in PostgreSQL or InnoDB, which is the single biggest practical difference from a purely lock-based engine. The cost is that old versions accumulate and must be cleaned up — vacuum in PostgreSQL, the undo log in InnoDB — and neglecting that is a real production failure mode.

The write-ahead log record is flushed to durable storage, and only then is the commit acknowledged. The data pages themselves may still be dirty in memory and written later, because the log is enough to reconstruct them after a crash. This is why commit latency is bound by a sequential log flush rather than by random page writes, why committing in a tight loop is slow, and why grouping work into fewer, larger transactions is usually the first batch-job optimisation.

It holds its locks and its snapshot for its whole duration. The locks block other writers; the snapshot prevents cleanup of every row version created since it started, so table bloat grows and the cleanup process cannot keep up. A transaction left open across a slow HTTP call to a third party is the usual cause, and it degrades the entire database rather than just that request — which is why external calls belong outside transaction boundaries.

Pessimistic locking takes the lock before reading, assuming conflict, so nobody else can interfere — correct and it serialises access, which under contention becomes the bottleneck. Optimistic concurrency reads without locking, then checks at write time that nothing changed, usually via a version column, and retries if it did. Optimistic wins when conflicts are rare, which is most of the time; pessimistic wins when they are common, because a retry loop under heavy conflict is pure waste.

Two users, one row, and nothing in the database complains.

T1: SELECT stock FROM items WHERE id=7      -> 10
T2: SELECT stock FROM items WHERE id=7      -> 10
T1: UPDATE items SET stock = 9  WHERE id=7  (10 - 1)
T2: UPDATE items SET stock = 9  WHERE id=7  (10 - 1)

Two sales, one unit deducted. Both transactions committed successfully.

The read-modify-write in application code is what creates the window. Three fixes, in decreasing order of how much you should like them.

-- 1. Atomic update. Best when the new value is a function of the old one.
UPDATE items SET stock = stock - 1 WHERE id = 7 AND stock > 0;
-- Check the affected row count: zero means it was out of stock.

-- 2. Optimistic, with a version column. Best when a human is editing a form.
UPDATE items SET stock = 9, version = 4 WHERE id = 7 AND version = 3;
-- Zero rows affected means someone else saved first; show them a conflict.

-- 3. Pessimistic. Only when the work between read and write cannot be
--    expressed as an expression and conflicts are frequent.
BEGIN;
SELECT stock FROM items WHERE id = 7 FOR UPDATE;   -- blocks other writers
UPDATE items SET stock = ... WHERE id = 7;
COMMIT;

The first is not merely the fastest, it is the only one that needs no retry logic, so prefer it wherever the update can be written as an expression. The second surfaces the conflict to whoever caused it, which is right for long-lived edits where silently overwriting someone's work is the real bug. The third is correct and it holds a lock for the duration of your transaction, so it inherits every problem of the long transaction above.

Two transactions each holding a lock the other needs, so neither can proceed. The database detects the cycle and kills one, which surfaces as an intermittent error under load and never in testing. The reliable prevention is a consistent lock ordering — always touch rows in the same order, for instance sorted by primary key — because a cycle requires that two paths disagree about order. Keeping transactions short reduces the window; it does not remove the cause.

Two transfers between the same two accounts, running at the same moment, in opposite directions.

sequenceDiagram
    participant T1 as Transfer A to B
    participant T2 as Transfer B to A
    T1->>T1: lock row A
    T2->>T2: lock row B
    T1--xT2: wants row B, held by T2
    T2--xT1: wants row A, held by T1
    Note over T1,T2: cycle detected, the engine kills one

Neither transaction did anything wrong. The cycle exists because the two paths disagreed about which row to touch first, and that disagreement came from the business meaning of the operation — you naturally lock the sender before the receiver.

The fix is to make lock order independent of business meaning:

-- Lock both rows in a fixed order, whichever direction the money moves.
SELECT id, balance FROM accounts
 WHERE id IN (:from_id, :to_id)
 ORDER BY id
   FOR UPDATE;

-- now perform both updates; no cycle is possible because every transaction
-- acquires the lower id first

Sorting by primary key is arbitrary and that is exactly why it works: every transaction in the system derives the same order from the same rule, so no cycle can form. Any total order agreed globally will do.

Three things to say alongside it. Deadlocks are detected and resolved rather than hung, so the application sees a specific error — and must retry, because the transaction it chose to kill did nothing wrong. Retrying is safe here precisely because the whole transaction rolled back. And shortening transactions reduces the frequency but cannot eliminate the cause, so consistent ordering is the actual fix and a shorter transaction is the mitigation.

Some engines convert many row locks into a single table lock once the count crosses a threshold, to save lock-manager memory. The result is that a large update which was blocking a few rows suddenly blocks the whole table, and throughput collapses for reasons invisible in the statement itself. The mitigation is to batch large writes into chunks small enough to stay below the threshold, committing between them — which also keeps the transaction short.

FOR UPDATE takes a write lock on the selected rows for the rest of the transaction, so a concurrent transaction attempting the same rows waits. Adding SKIP LOCKED makes it ignore already-locked rows instead of waiting, which turns a table into a workable job queue: each worker claims the first unlocked pending row and they never contend. It is the standard way to build a queue in a relational database when you do not want a broker.

Read its execution plan, on data of production shape, before changing anything. The plan tells you which access method was chosen, the join order, and where the estimated row counts diverge from actual — and that divergence is usually the whole story, because a plan built on a wrong estimate is a plan optimised for a query you are not running. Guessing at indexes without reading the plan is how tables acquire eight of them.

The shape matters more than the numbers, and one line usually accounts for nearly all the time.

EXPLAIN ANALYZE
SELECT o.id, c.name FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-07-01';

Nested Loop  (cost=0.4..184213  rows=48  actual rows=91204  loops=1)  time=8421ms
  ->  Seq Scan on orders o  (rows=50  actual rows=91204)  time=310ms
        Filter: created_at >= '2026-07-01'
        Rows Removed by Filter: 4108796
  ->  Index Scan using customers_pkey on customers c  (loops=91204)

Three readings, in order of what they tell you.

The estimate is wrong by a factor of 1,900 — 48 rows planned, 91,204 actual. That is the root cause, not a symptom: the planner chose a nested loop because it expected 48 iterations, and a nested loop over 91,204 rows means 91,204 index lookups. With a correct estimate it would have chosen a hash join. Stale statistics or a correlation the planner cannot see are the usual reasons.

The sequential scan discarded 4.1 million rows to keep 91,204, so a created_at index would let it read only what it needs. That is the obvious fix and the smaller one.

The loops count is where the time went. A cheap operation multiplied by 91,204 iterations is not cheap, and loops is the field people skip when reading a plan — per-loop costs are shown per loop, so the displayed number understates the total.

So the fix order is: refresh statistics and re-check the estimate, add the index on created_at, then re-read the plan to confirm the join method changed. Adding the index without fixing the estimate can leave the nested loop in place.

Nothing here reads as a database access, which is why it survives review.

List<Order> orders = orderRepo.findByStatus(PENDING);   // 1 query
for (Order o : orders) {
    // looks like a field read; is a SELECT per iteration
    System.out.println(o.getCustomer().getName());      // N queries
}

At 500 pending orders that is 501 round trips. Each is fast — a primary-key lookup on a warm index, perhaps 0.4ms — so nothing appears in the slow-query log and every individual query is blameless. The endpoint takes 200ms and the database reports itself healthy.

Two fixes, and they are not equivalent:

// Join fetch: one query. Correct when you need the children for every parent.
@Query("select o from Order o join fetch o.customer where o.status = :s")
List<Order> findPendingWithCustomer(@Param("s") Status s);

// Batch load: two queries. Better when the join would multiply rows —
// fetching a collection rather than a single related entity.
Map<Long, Customer> byId = customerRepo.findAllById(customerIds)
        .stream().collect(toMap(Customer::getId, identity()));

Use the join for a to-one relationship, and the two-query batch for a to-many, because join-fetching a collection returns the parent row once per child and the ORM then deduplicates in memory — you have moved the N+1 from round trips to bandwidth.

The durable fix is detection rather than vigilance. Log generated SQL in development, and assert the query count in an integration test for any endpoint that matters: a test that fails when a change turns two queries into fifty-one catches this permanently, and code review does not.

Fetching a list with one query, then issuing a further query per row to load a related entity — one hundred rows becomes one hundred and one round trips. It appears almost exclusively through ORMs and lazy loading, where the code reads as a simple property access. The fixes are a join, an eager-load hint, or a second query fetching all the children by their parent ids at once. It is worth detecting mechanically, by asserting query counts in tests, because it is invisible in code review.

Data volume crossed a threshold where the plan changed — a nested loop that was right at ten thousand rows is wrong at ten million. Or statistics went stale, so the planner is optimising for a distribution that no longer exists. Or the working set stopped fitting in memory, and reads that were cache hits became disk. Or index bloat grew the structure. All four produce the same symptom, and the plan plus the cache hit ratio distinguish them.

The engine builds a plan for a parameterised query using the first parameter value it sees, caches that plan, and reuses it for every subsequent value. If the first call passed a rare value and the next passes one matching a third of the table, the cached plan is badly wrong for it, and vice versa. The symptom is a query that is fast or slow depending on nothing visible. Mitigations are recompile hints, optimising for a representative value, or splitting the query.

Look at the WHERE clause first, then ORDER BY, then the columns selected. The usual shape is equality predicates first in the index, then the range predicate or sort column, then any columns you want covered. That ordering follows from the leftmost prefix rule: equality columns keep the remaining data contiguous, so a range or a sort on the next column is a straight walk rather than a gather.

First normal form asks that each column holds a single value rather than a list. Second asks that every non-key column depends on the whole primary key, which only bites with composite keys. Third asks that no non-key column depends on another non-key column — if city determines postcode, the postcode belongs elsewhere. In practice, aiming for third normal form and deliberately denormalising specific hot paths afterwards is the working answer.

When a join is measurably the bottleneck on a read path that matters, and you have somewhere to put the maintenance burden. The cost is that the duplicated value can now disagree with its source, so you need a mechanism keeping it correct — a trigger, an application invariant, or a scheduled reconciliation — and a way to detect drift. Denormalising without that mechanism does not trade consistency for speed, it trades correctness for speed.

Storing money as a floating-point number, and storing time without a zone.

-- Wrong
CREATE TABLE payments (
  amount     DOUBLE PRECISION,   -- 0.1 + 0.2 = 0.30000000000000004
  created_at TIMESTAMP           -- which zone? nobody knows, including you
);

-- Right
CREATE TABLE payments (
  amount_minor  BIGINT      NOT NULL,   -- pence or cents, an exact integer
  currency      CHAR(3)     NOT NULL,   -- an amount without one is meaningless
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

Floating point cannot represent most decimal fractions exactly, so a sum of a million payments drifts, and a total that fails to reconcile by a few pence is a genuinely hard bug to trace back to its cause. Either integer minor units or a fixed-point decimal type is correct; the integer is harder to misuse because there is no rounding behaviour to get wrong.

The timestamp without a zone is worse because it looks fine for months. It breaks at a daylight-saving transition, when one local hour occurs twice and ordering becomes ambiguous, and it breaks permanently the day a second region starts writing rows. Store instants in UTC with a zone-aware type and convert at the edge; store a zone name separately only when the local wall time is the thing that matters, like a recurring calendar appointment.

The general principle behind both: a column's type is a constraint, and choosing a wider type than the data needs is choosing to enforce nothing.

A surrogate key — a generated integer or UUID — by default, because natural keys turn out to be mutable. Email addresses change, national identifiers get reissued, and a "unique" product code acquires a duplicate after an acquisition. Changing a primary key means updating every foreign key referencing it. Keep the natural key as a unique constraint, which gives you the integrity guarantee without making the whole schema depend on its stability.

A sequential integer is compact, orders naturally and keeps inserts at the end of the index, but it leaks volume and requires the database to allocate it. A random UUID can be generated by the client and is safe across shards, but is larger and inserts randomly into the index, fragmenting it and hurting cache locality. The modern compromise is a time-ordered UUID such as UUIDv7, which keeps the distributed generation while restoring sequential insert behaviour.

Not avoided, but used deliberately, because it means "unknown" and propagates through comparisons in ways that surprise people — NULL = NULL is not true, and a NOT IN against a set containing NULL returns no rows. Where a column is genuinely mandatory, declare NOT NULL and let the database enforce it. Where a nullable column encodes a state rather than an absence, an explicit status column usually models it better and reads correctly in every query.

Because the application is not the only thing that writes to the database. Migrations, backfills, admin tools, a script someone ran once, and a second service all bypass it, and orphaned rows found years later cannot be reconstructed. The constraint costs an index lookup per write and buys a guarantee that no code path can violate. The usual objection is performance at very high write volume, which is a real reason and one to establish by measurement rather than assumption.

Nothing coherent as a category — it groups key-value stores, document stores, wide-column stores and graph databases, which have less in common with each other than any of them has with a relational engine. The useful question is never "SQL or NoSQL" but which access patterns must be fast, whether the data has a shape known in advance, and what consistency the business requires. Answering those picks the store; the label follows.

When the entity is naturally one nested object that is read and written whole, and there are few queries that need to relate it to other entities. A document avoids the join because it stores the aggregate together, which is genuinely faster for that pattern. It goes wrong when the access patterns diversify: you end up duplicating data across documents, and updating a value now means finding every document that embedded it, with no transaction spanning them.

When every access is by a key you already have and the value is opaque to the store — sessions, feature flags, rate-limit counters, cached fragments. The constraint is total: you cannot ask "which sessions belong to this user" unless you maintained that index yourself. That is a fair trade for microsecond lookups, and the failure mode is discovering a second access pattern later and building a secondary index by hand.

The same data, modelled both ways, and the queries each one makes cheap.

Relational                          Document
-------------------------------     ------------------------------------
orders(id, customer_id, total)      {
customers(id, name, address)          "_id": "o-1",
                                      "total": 4200,
Read an order with its customer:      "customer": {
  one join                              "id": "c-9",
                                        "name": "Acme Ltd",
Customer changes address:               "address": "12 Mill Lane"
  one UPDATE, one row                 }
                                    }
                                    Read an order with its customer:
                                      one lookup, no join

                                    Customer changes address:
                                      update every order that embedded it

The document is faster for the read it was designed for and has no answer for the write. Whether that is a good trade depends entirely on whether the embedded data is immutable in context.

And often it is — deliberately. The address on a historical order should be the address at the time of the order, so embedding is not duplication, it is capturing a fact. That is the strongest case for a document model: the aggregate is a snapshot, so there is nothing to keep in sync.

The case where it goes wrong is embedding mutable reference data because the read was convenient. A customer's current name embedded in ten thousand orders means a rename is a ten-thousand-document update with no transaction spanning it, so there is a window where the system disagrees with itself and no constraint that would have prevented it.

The question that settles it: is this value a fact about the moment, or a reference to something that changes? Facts embed; references do not.

Very high write throughput with query patterns known in advance, because you design the table around the query rather than around the entity. The partition key and clustering columns are the whole design, and a query that does not include the partition key is either impossible or a full-cluster scan. That inversion — model the query, not the data — is the actual skill, and it is why these stores punish workloads whose access patterns are still changing.

When traversals of unbounded depth are the primary workload — shortest path, reachability, "who is connected to whom through how many hops". Those are the queries where a relational engine needs a recursive join whose cost grows with depth. If your relationships are two levels deep and known, foreign keys handle it comfortably, and adding a graph store is an operational burden that buys nothing.

If you are storing high-volume append-only measurements and querying them by range with aggregation, yes: the compression from storing similar values together is an order of magnitude, and downsampling and retention are built in rather than hand-rolled. Below that volume a relational table with a good index on time and partitioning by month is simpler and fast enough, and simpler counts for a lot when the alternative is another system to run.

Because a database connection is expensive — a process or thread plus memory on the server — and opening one per request both adds latency and lets a traffic spike exhaust the server's connection limit. A pool keeps a bounded set open and hands them out. The sizing surprise is that the right pool is small: a database with sixteen cores does not go faster with four hundred concurrent connections, it goes slower, because they contend for the same cores and locks.

The intuition is that more concurrency means more throughput. Past the hardware's capacity, the opposite happens, and the shape of the curve is worth carrying. The figures below are illustrative of that shape rather than a measurement of any particular system — the absolute numbers depend entirely on your hardware and workload, and only the direction of each column generalises.

16-core database server, mostly CPU-bound workload

pool size   throughput      p99 latency
---------   -------------   -----------
    16      ~12,000 tps      14ms
    32      ~12,400 tps      28ms      queueing, no more work done
   100      ~11,800 tps      95ms      context switching, lock contention
   400       ~9,200 tps     480ms      worse on every axis

Beyond the point where every core is busy, additional connections do not add capacity — they add a queue. Worse, they add it inside the database, where you cannot see it, instead of in the pool where you could have measured it. The extra connections also each consume memory for sort and hash workspace, so a large pool can turn in-memory sorts into disk spills.

A common starting formula is cores x 2 + effective spindle count, which for sixteen cores and SSD storage lands somewhere near thirty-five, not four hundred. Treat it as a starting point to measure from rather than a rule.

Two consequences for architecture. Queue in the application, where the wait is visible and you can time it out, rather than in the database. And with many application instances the pool is per instance, so twenty pods at fifty connections is a thousand connections to a server sized for forty — which is why a shared external pooler exists, and why "we scaled out the app and the database fell over" is such a common story.

The rule is that the database and both versions of the application must be compatible at every intermediate point, because during a rolling deploy old and new code run simultaneously.

Goal: rename `username` to `handle`.

The wrong way, in one step:
  ALTER TABLE users RENAME COLUMN username TO handle;
  Every running instance of the old code breaks the instant this commits.

The expand/contract way, over three deploys:

  1. EXPAND    add `handle`, nullable; backfill in batches;
               deploy code that writes both columns, reads `username`
  2. MIGRATE   deploy code that reads `handle`, still writes both
               (now old and new code are both correct)
  3. CONTRACT  deploy code that only uses `handle`;
               then drop `username`

Each step is independently deployable and independently revertible, which is the property that makes it safe. Reverting step 2 is just a deploy, because both columns still hold correct data.

Two details that decide whether it works. The backfill must be batched with commits between batches, because a single UPDATE over ten million rows holds locks and bloats the undo log for as long as it runs. And adding a column with a non-constant default rewrites the entire table on older engines — check your version, because ADD COLUMN ... DEFAULT is instant on some and a full rewrite with a table lock on others.

The same shape handles every destructive change: adding a NOT NULL constraint, splitting a column, changing a type. Never make a change that only one deployed version can tolerate.

Replication protects against a machine failing; a backup protects against a mistake. A dropped table replicates to every replica within milliseconds, which is why a replica is not a backup no matter how many there are. What you need is point-in-time recovery — a base backup plus the write-ahead log — so you can restore to the instant before the error, and a restore that has actually been rehearsed, because an untested backup is a hypothesis.

Vacuum reclaims space from dead row versions so it can be reused, keeping the table from growing indefinitely under MVCC; it generally does not return space to the operating system. An index rebuild reconstructs the structure to remove fragmentation and reduce depth. The first is routine and automatic, the second is occasional and worth doing when an index has grown much larger than its data justifies — a symptom of long transactions blocking cleanup.

Correctness in exchange for read capacity. Once reads go to replicas, any read that follows a write may not see it, because replication is asynchronous. So the routing cannot simply be "SELECT goes to a replica": reads within a transaction, and reads immediately after a user's own write, must go to the primary. Systems that adopt splitting as a blanket rule generate a long tail of "I saved it and it disappeared" bugs.

When you have a natural range — usually time — and your queries filter on it, so the engine can skip entire partitions. The larger benefit is often maintenance: dropping last year's data becomes detaching a partition, which is instant, instead of a DELETE of a hundred million rows that bloats the table and runs for hours. It does not help queries that ignore the partition key, and it adds planning overhead when there are very many partitions.

The engine's own statistics view, which aggregates by normalised statement — in PostgreSQL pg_stat_statements, and equivalents elsewhere. Sort by total time rather than mean, because the query that matters is usually a fast one executed ten million times, not the slow report run twice a day. That single reordering changes the answer more often than not, and it is why slow-query logs alone give a misleading picture.

DELETE removes rows one at a time, is transactional, fires triggers and can be rolled back — and leaves the space to be reclaimed later. TRUNCATE deallocates the whole table's storage, which is far faster, usually does not fire row triggers, and is transactional in some engines but not all. DROP removes the table definition entirely. The interview point is the middle one: knowing whether TRUNCATE can be rolled back on your engine is the actual question.

Because UNION removes duplicates, and that requires sorting or hashing the entire combined result before it can return a single row — so it also blocks streaming, which matters when the caller only wants the first page. UNION ALL concatenates and streams as rows arrive. When the inputs cannot produce duplicates, which is most of the time and something you usually know from the predicates, UNION is paying for a deduplication guaranteed to find nothing. On a large result the difference is a sort of millions of rows against no extra work at all, which makes it one of the cheapest wins available in an existing codebase — and one people write by reflex because UNION is the word they learned first.

Both return fifty rows. Only one of them stays fast, and only one is correct while the table is being written to.

-- OFFSET: the engine produces 500,000 rows and throws away 499,950.
SELECT id, title, created_at FROM posts
 ORDER BY created_at DESC, id DESC
 LIMIT 50 OFFSET 499950;

-- Keyset: seeks directly to the position and reads fifty rows.
SELECT id, title, created_at FROM posts
 WHERE (created_at, id) < ('2026-03-11 09:14:22', 88213)
 ORDER BY created_at DESC, id DESC
 LIMIT 50;

The keyset version needs an index on (created_at DESC, id DESC) and the row comparison, which most engines support directly. Its cost is constant: page one and page ten thousand read the same fifty rows.

The correctness argument is the stronger one and gets mentioned less. Under OFFSET, a row inserted while a user pages shifts everything down by one, so they see a row twice; a deletion shifts up and they miss one entirely. Keyset anchors on a value from the last row seen, so concurrent writes cannot shift the window — new rows simply appear where they belong.

The id tiebreaker is not decoration. Without it, two posts sharing a timestamp make the sort order non-deterministic, so a row can be skipped or repeated at exactly the page boundary — a bug that appears in production and never in testing, because test data rarely has ties.

What you give up is jumping to an arbitrary page number, since you need the previous page's last row to compute the next. For infinite scroll and API cursors that is not a loss; for a numbered-page admin table it is, which is the one place OFFSET remains reasonable — bounded to a few pages.

Because the engine must produce and discard every row before the offset. Page one discards nothing; page ten thousand at fifty rows a page discards half a million rows to return fifty, so each page is slower than the last. It is also incorrect under concurrent writes: an insert shifts everything, so a row can appear on two pages or none. Keyset pagination — WHERE (created_at, id) < (?, ?) ORDER BY ... LIMIT 50 — is constant time and stable.

Because under MVCC the engine cannot trust a stored total: which rows are visible depends on your snapshot, so it must check them. That means scanning the table or an index. If you need an exact live count on a huge table, maintain a counter; if approximate is acceptable, the planner's estimate from the statistics is free and usually within a percent. Asking whether the count needs to be exact is the right first response.

The query it generates and how many of them. Lazy loading turns a property access into a round trip, which is the N+1 problem; a default eager configuration fetches columns and joins nobody asked for; and identity-map caching means two reads inside a session may not reflect a concurrent commit. None of this is an argument against ORMs — it is an argument for logging generated SQL in development and asserting query counts in tests.

It defeats covering indexes, because the query now needs columns the index does not carry, so every match becomes a row lookup. It makes the result shape depend on the schema, so adding a column changes what your code receives. And it drags large text and binary columns through the network and the cache for queries that wanted three integers. Naming columns costs nothing and keeps the plan stable when the table changes.

"Show me the plan." Anyone can propose an index; a candidate who asks what the current plan is, what the estimated and actual row counts are, and whether statistics are current is describing how the problem is actually solved. The same instinct shows up as asking about the data distribution before choosing a key, and about the read/write ratio before choosing a store — measurement before prescription, in a domain where intuition is unusually unreliable.