Query-writing rounds and the storage-engine reasoning behind why a query is slow, for backend, data, and DBA roles alike.
13 published across 8 topics.
Database fundamentals58 short answers on one page, for revising rather than studying.Joins, window functions, aggregation, and the analytical queries asked in a shared editor.
Two one-to-many joins from the same parent multiply against each other, so every payment row is counted once per order line. Aggregate each branch to one row per parent before joining, and reach for DISTINCT only where duplicate values are genuinely impossible.
Rank rows inside each department with DENSE_RANK() over a partition and keep rank two. The plausible wrong answers compare against a global MAX, or use ROW_NUMBER, which silently returns a top earner again when two people share the highest salary.
B-tree mechanics, composite-column order, covering indexes, and why an index goes unused.
Order by how the predicate uses the column, not by cardinality: equality columns first, then one range column, then columns needed only for sorting. A range predicate stops the index seeking on everything after it, and the leftmost prefix decides which other queries the index can serve.
Work outward from the plan: confirm the index is actually usable for the predicate as written, then check whether the optimiser is choosing to ignore it because of selectivity, stale statistics, or a type or collation mismatch that makes the predicate non-sargable.
ACID guarantees, isolation levels, the anomalies each permits, and locking versus MVCC.
A deadlock is a cycle in the wait-for graph, so read the engine's own report to get both statements and the locks each held. Nearly all of them come from two code paths taking the same rows in different orders; impose one order, shorten transactions, and retry, because a deadlock is always safe to retry.
Each level is defined by the anomalies it allows: READ COMMITTED permits non-repeatable reads and phantoms, PostgreSQL's REPEATABLE READ is snapshot isolation so it prevents both yet still permits write skew, and only SERIALIZABLE rules out all four.
Reading execution plans, statistics and cardinality estimation, and diagnosing real regressions.
Read the plan tree innermost-node-first, remember that EXPLAIN only estimates while EXPLAIN ANALYZE executes, multiply each node's actual rows by its loops, and treat a large gap between estimated and actual rows as the primary finding.
First establish whether the four seconds are inside the database at all, using pg_stat_statements and auto_explain rather than the client's own timer. If they are, the usual cause is that the application sends bind parameters, so the planner is working without the literal values your ad-hoc run gave it.
Normalisation and deliberate denormalisation, keys, constraints, and schema evolution under load.
Give every entity a surrogate primary key but keep a unique constraint on the business identifier, and model a negotiated price as a row with a validity period rather than a mutable column - then copy the agreed price onto the order line so history cannot be rewritten.
Decide by which attributes are filtered or constrained, not by how many exist. Anything a query filters, sorts or validates on becomes a real typed column; the open-ended remainder goes in a JSONB document with a GIN index, and entity-attribute-value is the fallback that costs you every guarantee.
Document, key-value, wide-column, and graph stores, with honest selection criteria.
Choose on the shape of the reads and writes, not the shape of the data - what the key is, whether ranges are scanned within a partition, and whether traversals have unbounded depth. Scalability is not a differentiator, because the access pattern is what scales.
MVCC and vacuum, WAL, connection handling, and the operational depth senior roles expect.
An UPDATE writes a new row version and leaves the old one visible to older snapshots; vacuum reclaims it only once no snapshot can see it. A long transaction pins that horizon, so dead tuples accumulate as bloat and freezing stalls, which is what leads to transaction-id wraparound.
Backup and recovery, replication and failover, upgrades, capacity planning, and incident response.
Every step is chosen to hold weak locks briefly - add the constraint invalid and validate separately, index concurrently, backfill in committed batches - behind a restore you have rehearsed and timed, and a pool small enough that one blocked DDL statement does not exhaust it.