Database — What a 5-Year Engineer Should Explain

Six answers a backend engineer is expected to give without notes — indexes, transactions and isolation, deadlocks, slow-query diagnosis, the SQL/NoSQL decision, and where Redis earns its place

Indexes17 comparisons vs 99,999 — and a write cost on every insert
Transactionsatomicity is your crash; isolation is everyone else
Deadlocksa wait-for cycle from opposite-order locking
Slow queriesindex, then N+1, then SELECT *, then EXPLAIN
SQL vs NoSQLcount the questions, not the features

1.What an index is, and when it hurts

An index is a second, sorted copy of one or more columns, kept alongside the table and pointing back at the rows. It turns "look at every row until you find it" into "jump straight to it". It hurts when the database has to maintain it on every write, or when the query it is meant to help matches most of the table anyway.

Key features

An index stores the indexed column sorted, with a pointer back to the row — the table itself stays in whatever order rows were written
A sorted structure finds one row in about log2(n) comparisons: roughly 17 for 100,000 rows, against 100,000 for a full scan
Every index is written on every INSERT, UPDATE of an indexed column, and DELETE — five indexes means six structures touched per insert instead of one
An index only helps a predicate that is selective: a condition matching 95% of the table is cheaper to answer by scanning, and the planner will scan
A composite index on (a, b) can serve a query filtering on a, or on a and b — but not one filtering on b alone, because b is only sorted within each a
The index also has to be kept in memory to be fast; indexes you never query still consume cache the ones you do query wanted

Signature

index = sorted(column) -> row pointer · log2(n) seek · one write per index per row change

An index is a sorted copy of a column that turns a full scan into a seek — about 17 comparisons instead of 100,000 on a 100k-row table. It costs a write on every insert, update and delete, and it buys nothing for a predicate matching most of the table. Index from the slow-query list, not from the column list.

2.What a transaction is, and what isolation means

A transaction is a group of statements that either all take effect or none of them do. Isolation is the separate question of what one transaction can see of another one that is running at the same time. The first protects you from crashing halfway; the second protects you from other people.

Key features

A transaction is atomic: every statement in it commits together, or the whole thing rolls back and the database is as if none of it ran
Rollback is not just for errors you raise — a crashed process, a lost connection, or a deadlock abort all end the transaction the same way
Isolation levels are named by the anomalies they forbid: dirty reads, non-repeatable reads, phantom reads
PostgreSQL's default is Read Committed, which prevents dirty reads and still allows the same query to return different rows twice within one transaction
The classic bug is a lost update: two transactions read the same value, each computes a new one from it, and the second write silently overwrites the first
A relative update (`SET balance = balance - 30`) or a conditional one avoids that read-modify-write race without any change of isolation level

Signature

atomic = all or nothing · isolation = what you see of concurrent transactions

A transaction is all-or-nothing; isolation is what you can see of everyone else's. Handle the exception outside the block or the rollback never happens, keep network calls out of open transactions, and prefer a relative or conditional update to read-modify-write — that shape loses updates at every isolation level PostgreSQL uses by default.

3.What causes database deadlocks

A deadlock happens when two transactions each hold a lock the other one needs, and each is waiting for the other to release it first — neither can proceed, so the database detects the cycle and kills one transaction (rolling it back) to break it. The classic cause is two transactions locking the same two rows in opposite order.

Key features

A deadlock requires a CYCLE: transaction A waits on a lock B holds, and B waits on a lock A holds (directly, or through a longer chain)
The classic trigger is two transactions updating the same two rows in OPPOSITE order — A updates row 1 then row 2, B updates row 2 then row 1
PostgreSQL detects the cycle automatically and aborts one transaction (its choice of which is not guaranteed) with a deadlock_detected error — it does not hang forever
The fix is almost always consistent lock ordering: every transaction that touches rows 1 and 2 always locks them in the SAME order (e.g. always by ascending id)
A deadlock is different from ordinary lock contention (one transaction simply waiting its turn) — contention resolves on its own once the first transaction commits; a deadlock never resolves without one side being aborted

Command

python

def has_deadlock_cycle(waits_for: dict) -> bool:
    """waits_for: {tx_id: tx_id_it_is_blocked_on}. A deadlock exists if
    following the chain from any transaction leads back to itself."""
    for start in waits_for:
        seen, current = set(), start
        while current in waits_for:
            if current in seen:
                return True
            seen.add(current)
            current = waits_for[current]
    return False

A deadlock is a genuine cycle — A waits on a lock B holds while B waits on a lock A holds — almost always caused by two transactions locking the same rows in opposite order; the fix is consistent lock ordering, not blind retries.

4.How to optimize a slow query

Before touching a query planner, run through a fixed checklist in order: is there an index on the columns in WHERE/JOIN/ORDER BY, is the query actually a disguised N+1 (one query per row instead of one query total), is it selecting more columns/rows than it needs (SELECT * with no LIMIT), and only after ruling those out does reading an actual execution plan become the next step.

Key features

Check index existence first: is there an index covering the columns actually used in WHERE, JOIN, and ORDER BY — a full table scan on a large table is the single most common cause of a slow query
Check for a disguised N+1: is this "one slow query" actually N nearly-identical queries run in a loop (one per row of an earlier result) rather than a single query with a JOIN
Check what is actually being selected: SELECT * pulls every column (including large/unused ones) and a query with no LIMIT pulls every matching row, both costing more than the caller needs
Check whether the WHERE clause is sargable — wrapping an indexed column in a function (e.g. LOWER(email) = ...) usually prevents the index from being used at all, unless a matching expression index exists
Only after those four are ruled out does reading an actual EXPLAIN ANALYZE plan become the next real step — it answers "what is the database actually doing," not "what could be wrong in general"

Command

python

CHECKLIST = [
    "1. Index on WHERE/JOIN/ORDER BY columns?",
    "2. Disguised N+1 (query-per-row instead of one query)?",
    "3. SELECT * / missing LIMIT pulling more than needed?",
    "4. WHERE clause sargable (no function wrapping an indexed column)?",
    "5. Only now: read EXPLAIN ANALYZE",
]

Check in order: index on the WHERE/JOIN/ORDER BY columns, a disguised N+1 (query count, not just total time), SELECT * / missing LIMIT, a non-sargable WHERE clause — only then read an actual EXPLAIN ANALYZE plan.

5.SQL vs NoSQL

The real question is not relational versus document, it is whether your data has one dominant access pattern or many. A document store keeps everything one screen needs in a single object, which makes that read fast and every other read awkward. A relational store keeps each fact once and assembles it per query, which makes any question answerable and no single question free.

Key features

A document store colocates related data, so fetching one aggregate is one read instead of a join across tables
The same colocation duplicates any value that appears inside many documents, and every copy has to be updated when the value changes
A relational store keeps each fact in one place, so an update touches one row and a cross-cutting aggregate reads one table
"Schemaless" means the schema is enforced by the application instead of the database, not that there is no schema
Both families now overlap heavily — PostgreSQL has JSONB and MongoDB has transactions — so the decision is rarely about a feature that only one of them has
The honest default for a system whose questions are not yet known is relational, because it is the shape that makes an unforeseen query cheap

Signature

document = assemble at write time · relational = assemble at read time

Count the questions, not the features. One dominant access pattern that reads an aggregate whole favours documents; many different questions over the same facts favour relational. Duplicated data is the real cost of embedding — 200 copies against one row for the same rename — and "schemaless" only moves the schema into your application.

6.When would you use Redis?

Redis earns its place when a use case needs speed the primary database cannot give at the same cost — a cache for expensive/repeated reads, a place for ephemeral data that does not need durability (a session, a rate-limit counter), or a data structure the primary database has no native equivalent for (a sorted set for a leaderboard, a stream for a lightweight queue). It is the wrong choice as the primary, durable system of record for data that must never be lost.

Key features

Good fit: caching an expensive or frequently-repeated read (see python.caching) — the primary DB is still the source of truth, Redis just avoids re-computing/re-fetching it every time
Good fit: ephemeral data that can tolerate loss — a session token, a short-lived rate-limit counter, a distributed lock with a TTL
Good fit: a data structure Redis natively supports that the primary DB does not as cleanly — a sorted set for a real-time leaderboard, a list for a simple queue, HyperLogLog for approximate unique counts
Bad fit: the sole, durable system of record for data that must never be lost — Redis persistence (RDB/AOF) exists but is not the guarantee a relational database with WAL and replication gives by default
Bad fit: complex relational queries (multi-table joins, ad-hoc reporting) — Redis has no query planner or join support; that is squarely a relational database's job

Command

python

def should_use_redis(needs_durability, needs_relational_queries):
    if needs_durability or needs_relational_queries:
        return False
    return True   # ephemeral, or a native Redis structure, or a cache in front of the real DB

Redis fits caching, ephemeral data (tolerates loss), and its own native data structures (sorted sets, streams) — not the sole durable system of record for data that must never be lost, and not complex relational queries.

Spotted a mistake on this page? Tell us.

Read Sheet