SQL gives us an intentionally clean fiction. We ask for rows; the database finds them. We update a value; the value changes. We commit; the data is safe.
Under that interface, the database is managing fixed-size pages, caches, indexes, row versions, locks, logs and a cost-based planner. Most production surprises happen when the clean fiction collides with one of those physical mechanisms.
The details differ between engines. PostgreSQL makes a useful concrete example, and the mental models transfer well to other relational databases.
The database reads pages, not individual values
PostgreSQL stores tables and indexes as arrays of fixed-size pages, usually 8 KB. A table page contains a header, an array of item pointers, free space and the row tuples themselves. A row is located by a page number and an item identifier on that page.
This changes how you should think about I/O. Asking for one column from one row may require bringing an entire page into memory. Reading nearby rows can be cheap once their page is cached. Jumping through many unrelated pages can turn a small-looking query into random I/O.
Variable-length values add another layer. A short string may live directly in the row. A large value may be compressed or moved into PostgreSQL’s TOAST storage, leaving a reference in the main tuple. Selecting a wide payload that the screen never displays can therefore cause extra page reads, decompression and memory allocation.
“Only return the columns you need” is not merely a style preference.
The buffer manager decides whether storage is slow
Before reading from storage, PostgreSQL looks in its shared buffer pool. If the page is already present, the database can work with the memory copy. If not, it must load the page, potentially evicting another one. The operating system may cache file data as well, so observed performance depends on both layers and the working set of the whole server.
A benchmark that runs the same query ten times is often measuring a warm cache, not the first request after a deployment or failover. A query that is fast on a small development dataset may become unstable when its useful pages no longer fit in memory.
Think in working sets: which table and index pages must remain hot for the critical path, and what else is competing for that space?
A B-tree trades write work for ordered lookup
The default PostgreSQL index is a B-tree. Its root page points to internal pages, which lead to leaf pages containing ordered keys and references to table rows. Because each page holds many entries, a large index usually needs only a few page visits to reach the relevant leaf.
That shape makes equality, range and ordered queries efficient. It also explains why an index is not free.
Every insert may add an entry. Every relevant update may add another. If a leaf page has no room, it splits, moves part of its contents to a new page and updates its parent. More indexes mean more pages to cache, more WAL to generate and more work on every write.
Column order matters because the tree is sorted lexicographically. An index on
(tenant_id, created_at) naturally supports a tenant’s time range. It is not
equally useful for a query that filters only by created_at across every tenant.
Indexes should be designed from real predicates, joins and ordering — then verified with the planner — rather than added one column at a time as performance charms.
An update usually creates a new row version
PostgreSQL uses multiversion concurrency control, or MVCC. Readers observe a snapshot of the database rather than blocking every writer. To make that possible, an update normally creates a new tuple version and marks the old one as no longer visible to future transactions.
For a while, both versions exist physically. An old transaction may still need the previous version, while a new transaction sees the replacement. Deletes work similarly: the tuple becomes invisible before its space is reclaimed.
This is why a write-heavy table can grow even when its logical row count stays flat. It is also why long-running transactions are dangerous. An old snapshot can prevent the system from removing row versions that would otherwise be dead.
PostgreSQL can sometimes perform a heap-only tuple update when indexed columns do not change and the new version fits on the same page. That avoids new entries in every index, but it is an optimization, not a contract the application should assume.
VACUUM is part of normal operation
VACUUM identifies row versions that no active transaction can see and makes their space reusable. It also maintains visibility information that can enable index-only scans and supports transaction ID housekeeping.
Routine autovacuum is therefore not optional cleanup after the “real” database work. It is part of the database’s concurrency design.
When autovacuum falls behind, symptoms can appear far from the cause: larger tables and indexes, more cache misses, slower scans and increasing write amplification. The answer is rarely to disable it. Inspect which tables create dead tuples, whether workers have enough time and I/O budget, and whether long transactions are holding back cleanup.
VACUUM FULL is a different, much more invasive rewrite. It should not be the
routine response to ordinary MVCC churn.
WAL makes commit cheaper and recovery possible
Changing every data page on durable storage before returning from a transaction would make commits expensive and scattered. PostgreSQL instead uses a write-ahead log.
The database records the change in WAL before the corresponding modified data page is allowed to reach durable storage. Under normal synchronous commit, it can flush the sequential WAL records and acknowledge the transaction while the dirty table and index pages are written later.
After a crash, PostgreSQL starts from a checkpoint and replays WAL records needed to bring data pages back to a consistent state. The same ordered log also powers physical replication and point-in-time recovery.
This explains several operational facts. A tiny logical update may generate WAL for multiple indexes and page changes. Checkpoint behavior can affect I/O latency. Replication delay can be measured as distance between log sequence numbers. A backup without the required WAL is not a complete recovery story.
The planner chooses costs, not truths
For each SQL statement, the planner considers candidate paths: sequential scan, index scan, bitmap scan, join algorithms and different join orders. It estimates their cost using table statistics, value distributions, estimated row counts and configured assumptions about I/O and CPU.
The cheapest estimated plan wins. If the estimates are wrong, a reasonable planner can choose a terrible real plan.
This is why “the database ignored my index” is not yet a diagnosis. A sequential scan may genuinely be cheaper when a query needs a large fraction of the table. The useful question is whether the estimated row counts resemble the actual row counts at each step.
Use EXPLAIN to inspect the plan and EXPLAIN ANALYZE carefully to execute and
measure it. Look for the first large divergence between estimated and actual
rows, unexpected repeated loops, disk spills and expensive heap fetches. Then
fix the information or query shape that produced the decision: fresh statistics,
better indexes, a different predicate or more accurate data modeling.
Transactions define visibility and conflict
A transaction is not just a wrapper that makes several statements succeed or fail together. Its isolation level defines which committed changes are visible and which anomalies the application must handle.
MVCC reduces read-write blocking, but writes can still conflict. Row locks, unique checks, foreign keys and schema changes create waits. Two transactions that acquire resources in different orders can deadlock; PostgreSQL detects the cycle and aborts one participant.
Keep transactions short, acquire shared resources in a consistent order and be prepared to retry errors that the isolation contract permits. Never hold a database transaction open while waiting for a human, a remote API or a long queue operation.
Translate internals into application habits
A practical database review asks:
- Which pages and indexes form the hot working set?
- Does each index serve a measured query pattern, and what does it cost writes?
- Can a wide or external value be loaded only when needed?
- Are transactions short, bounded and safe to retry?
- Are old snapshots or idle transactions preventing cleanup?
- Do estimated and actual plan row counts agree?
- How much WAL does the workload produce, and can replicas and backups keep up?
- Has recovery been tested, not merely configured?
You do not need to become a storage-engine author to write better application code. You need enough of the physical model to predict where abstractions leak.
SQL describes the result you want. Database internals explain the work required to produce it.
For deeper reference, explore PostgreSQL’s documentation on physical storage, B-tree indexes, routine vacuuming, WAL and EXPLAIN.