Databases, oriented

A session is two cooperating processes: your client, and a server process that owns the files. For each connection the supervisor forks a whole backend process, so a connection costs far more than a socket.

The unit of work is a page — 8 kilobytes by default — and a row may not span one. The usual assumption is that a narrow row means a cheap read. Say you select one boolean from a 40-byte row: the engine still reads the whole 8-kilobyte page holding it. Every row version also carries a fixed 23-byte header of transaction ids, which on a two-column table outweighs the data. A value pushing a row past roughly 2 kilobytes is compressed or moved out of line, and is not read unless a query selects it.

Memory splits in two, and that is where settings get misread. shared_buffers is one pool for the whole server (default 128MB; documented guidance is about 25% of system memory on a dedicated machine, with skepticism past 40%). work_mem, default 4MB, is charged per sort or hash operation rather than per server, so fifty concurrent sessions can multiply it many times over.

Rule: before putting a cache in front of the database, check whether the working set still fits the cache it has.

Indexes speed retrieval but, as the documentation puts it, add overhead to the system as a whole. The mistake is treating an index as pure upside: every insert and update maintains every index on the table, so an index no query uses is not neutral — it is pure cost.

Structure decides what one can answer. A balanced tree serves =, ranges, sorted retrieval, and patterns only when constant and anchored at the start — LIKE 'foo%' can use it, LIKE '%bar' cannot. A hash index answers equality alone. An inverted index handles multi-component values, which is how arrays and documents become searchable. A block-range index stores cheap per-block summaries, worthless unless the column correlates with physical row order.

Then the traps. On a multi-column tree, equality on the leading columns plus one inequality limits how much index is scanned; a constraint on a later column alone is checked but need not reduce that scan — though newer versions can skip-scan when it has few distinct values. Wrapping the column defeats it outright: WHERE lower(email) = ? needs an index on lower(email).

Columns are not free either. A query reading only indexed columns can be answered from the index alone, via a visibility map. Say you add one unindexed column: every row costs a table visit again.

Rule: index the expression the query really uses, select only the columns you need, and drop indexes nothing scans.

For one table the planner always considers a sequential scan, plus index scans where an index matches the query's restrictions. For joins it picks between a nested loop (rescan the inner side per outer row — good when that side is indexed, very slow otherwise), a merge join (both inputs sorted, each scanned once, the sort often free from an index), and a hash join (hash one input; equality only).

All of it rests on statistics gathered by sampling, which the documentation calls approximate even when freshly updated; row and page counts are not live. So the common mistake is reading a slow query as a missing index. More often the index exists and the estimate was wrong — the planner chose a shape suiting the row count it expected.

EXPLAIN prints the estimates; EXPLAIN ANALYZE runs the statement and prints real rows beside them — so wrap it around a write:

Two reading traps. Costs are arbitrary units anchored to one sequential page fetch, not milliseconds, so they never match measured times. And inside a nested loop the inner node's rows and times are averages per loop — multiply by the loop count.

Rule: compare estimated against actual rows first; the largest ratio in the plan is where to look.

A transaction makes several steps atomic: nobody sees a half-finished state, and a failure leaves nothing behind. If you never write BEGIN, each statement gets its own implicit transaction — so a script that dies halfway leaves its earlier statements committed forever.

Isolation is the harder half. The standard names four levels; PostgreSQL implements three, and asking for read-uncommitted silently gives you read-committed. Under that default each command takes a fresh snapshot, so two SELECTs in one transaction can return different data and both be correct. Repeatable-read takes one snapshot, at the transaction's first statement.

The trap is assuming repeatable-read therefore means safe. Suppose rows carry a class and a value. One transaction sums class 1 and inserts that total as class 2; concurrently another sums class 2 and inserts it as class 1. Neither sees the other, neither breaks a constraint, both commit — and no order of running them one at a time produces that result. Only serializable catches it, by monitoring read/write dependencies and aborting one with a serialization failure your code must catch and retry. Snapshot isolation was formalized in a paper written because the standard's phenomena fail to characterize the levels engines ship.

Rule: choose the isolation level deliberately, and assume anything above read-committed can fail and need replaying.

Multiversion concurrency control (MVCC) is why a long report does not stall the writes underneath it: every statement sees the database as of an earlier moment, and reading never blocks writing.

The price is that an update or delete cannot overwrite anything: the old version must stay readable by snapshots that still need it — which is what those transaction ids in the row header are for. Reclaiming it is a later job.

Here is the misconception that costs real money. Say you delete half a large table and watch the disk: nothing is returned, and queries get no faster. Plain VACUUM makes that space reusable inside the file, returning it to the operating system only when trailing pages happen to be entirely free; the gaps in between are still paged in and scanned. VACUUM FULL does compact it, by rewriting the table under a lock that blocks everything and needing roughly a second copy's worth of disk.

Autovacuum fires at a base threshold plus a fraction of the table's size, so the biggest tables tolerate the most garbage before it triggers. And one long-open transaction, a forgotten prepared transaction, or a replication slot for a dead replica holds the cleanup horizon back for the whole database.

Rule: dead rows are the normal state, not an anomaly — watch the oldest open transaction before you watch disk.

Table locks come in modes that conflict pairwise. The one to know is the strongest: it conflicts with every other mode, is the only one that blocks an ordinary SELECT, and is taken by DROP TABLE, TRUNCATE, REINDEX, VACUUM FULL and most forms of ALTER TABLE. Row locks are narrower — they block writers of the same row, not readers — and SELECT ... FOR UPDATE takes one while reading, making a read-then-write sequence safe.

Deadlocks come from ordering. Suppose two transactions each update rows A and B, one in that order and one in reverse: each holds what the other needs. The server detects the cycle and aborts one unpredictably, so retrying is a fallback rather than a fix. The documented defense is that every application acquires locks in a consistent order.

The mistake in an incident is tuning the query you can see. The live activity view names what each backend is waiting on, and waiting on a lock, on disk, and on the client are three unrelated problems — including the session idle inside an open transaction, which causes waits rather than suffering them. Index-usage counters, by contrast, are cumulative since the last statistics reset: a zero means never, since some past restart.

Rule: in an incident, follow the waits to the root transaction instead of tuning whatever is stuck behind it.

The rule is an ordering rule: a change may not reach the data files until the record describing it has been flushed to durable storage. That is what makes recovery possible — after a crash, anything missing from the data pages is redone from the log.

It also explains commit latency: only the write-ahead log (WAL) must be flushed to declare a transaction committed, not every data file it touched. The log is written sequentially, so that flush is far cheaper than scattering page writes, and under load one flush can commit many small transactions.

Backups inherit the idea, and this is where a comfortable assumption goes wrong. Imagine the backup job has been green for a year and someone drops a table. A file-system copy is one moment, and one taken from a running server holds internal inconsistencies. A base backup plus the archived log stream fixes both: replay repairs the inconsistencies, so an ordinary archiving tool suffices, and you can recover forward to a chosen timestamp or named restore point. Recovering to a specific transaction id is possible but, the documentation notes, not practically usable.

One sequencing catch: the archive must already work before the first base backup, or there is no stream to replay.

Rule: a green backup job proves nothing — a backup is not a backup until a restore has been rehearsed at real data volume.

A standby tracks changes from the primary: a warm one accepts no connections until promoted, a hot one answers read-only queries while still following.

The choice that shapes everything is when the primary considers a transaction done. Asynchronous replication allows a delay between commit and propagation: the cost is minimal, and in exchange a failover can lose whatever had not shipped, while load-balanced servers return slightly stale results. Synchronous replication treats a transaction as committed only once the other servers have committed it too — no data loss on failover, consistent reads, and a cost the documentation puts at potentially more than halving performance over a slow network.

"Eventually consistent" is usually heard as "briefly, slightly out of date". What it means is worse: a user can write something and then not see their own write. Say you post a comment, the page reloads, and the read goes to a replica a few hundred milliseconds behind — your comment is missing. Nothing failed; the system did what asynchronous means. Either pin that session's reads to the primary or make the interface tolerate it.

Multi-primary setups do not escape the trade: servers accepting writes independently identify conflicts after the fact, resolved by rules you write.

Rule: decide per read whether provably stale data is acceptable, and design the write-then-read path before adding the replica.

A partitioned table is divided by range, by an explicit list of values, or by a hash when there is no natural boundary. The planner then prunes: it excludes any partition that cannot contain matching rows, both when planning and, for run-time values, during execution.

The benefit that never disappoints is bulk removal. Dropping or detaching an old partition is a catalog operation; deleting the same rows creates dead versions, work for vacuum, and a table that stays large.

The rest is conditional, and the documentation is unusually blunt: never assume more partitions are better than fewer. Suppose your table is partitioned by month but your hot queries filter by customer: pruning cannot help, every query touches every partition, and you have added planning cost for nothing. Too many partitions also lengthen planning and grow per-session memory; planning copes with a few thousand.

One constraint surprises people mid-migration. A unique or primary key on a partitioned table must include every partition key column, because indexes are per-partition and nothing enforces uniqueness across them. A table keyed by a random identifier but partitioned by month cannot keep it unique.

Rule: take the partition key from the predicate your hottest queries already use, never from how the data feels organized.

Normalization is usually taught as tidiness, which is the wrong reason to care. Copy a value into a second place and you have built a way for the two to disagree. For example, a customer's city stored on the customer row and on every order: the first address change makes some orders wrong, and no query can say which is true. Duplication for read speed is a legitimate later trade; arriving at it by accident is how a system ends up with two answers to one question.

Constraints are how the rule survives reality: they apply to the migration script and the console session too, not just your code path. Two behave counter-intuitively, both because of unknown values. A CHECK passes when its expression is true or unknown, so CHECK (price > 0) accepts a null price — only NOT NULL stops that. And a UNIQUE column treats two unknowns as different, so "one active subscription per user" written as a nullable unique column permits any number of them.

Changing the shape is the other half: ALTER TABLE takes the strongest lock by default and holds it for the whole command. Adding a column with a constant default is now a fast metadata change; a volatile default, a stored generated column or an identity column rewrites the whole table and its indexes.

Rule: put the invariant in a constraint, and check what a schema change locks before it runs in production.

Storing a document is more flexible than a fixed set of typed columns, and a database with a document type offers that without a second system to run and back up.

The misconception is that this means no schema. It means an unenforced one. Everything from the previous chapter — required values, allowed ranges, references that must exist — applies to columns, not to a document's contents. Imagine one service starts writing status where another still reads state: both succeed, nothing errors, and the disagreement surfaces later as missing data.

The mechanics carry their own traps. A text-exact document type keeps whitespace, key order and duplicate keys, and is reparsed on every operation. The decomposed binary form is slightly slower to write, much faster to process, and is the only one that can be indexed — but it preserves neither key order nor duplicates, so a value read back is not byte-identical to the one written. And any update locks the whole row, so one large, frequently-touched document serializes writers touching unrelated parts of it.

The documented test is good: a document should be an atomic datum business rules say cannot sensibly be split into independently modifiable pieces.

Rule: reach for a document when the structure is genuinely unknown, not to postpone designing one.

Because the server forks a backend per connection, a connection is an operating-system process, not a cheap handle. The ceiling is fixed at server start and sizes several resources — shared memory among them — so raising it charges every connection more.

That is why the answer to connection exhaustion is a pool rather than a bigger ceiling: many short application checkouts multiplexed over a small, stable set of server connections, turning a stampede into a queue. The documentation reaches for the same tool when tuning serializable workloads.

The misconception worth killing is that pool exhaustion means the database is overloaded. Suppose one slow query holds ten pool slots for thirty seconds: every other request queues, times out, and the application reports the database as down — while the database sits nearly idle. The symptom is indistinguishable from an outage; the cause is one query.

The defenses are limits, and every one is off by default. A per-statement ceiling aborts a runaway query. A lock-wait ceiling aborts a statement that is only queueing. A ceiling on sessions left idle inside an open transaction terminates them — the documentation ties that directly to holding locks and blocking the cleanup of dead rows, the bloat mechanism from earlier.

Rule: cap concurrency at a pool, set a statement timeout and an idle-in-transaction timeout, and read "database is down" as "something holds connections".