Relational databases
Storage and durability
A database can confirm a change while the table file still holds the earlier value. Durability is the promise that the confirmed change survives the failures covered by the database's commit settings. To keep it, the engine needs more than a changed row in memory: it needs a durable record from which it can recover.
Follow one separate experiment in the shop. The blue mug, P7, starts with a catalogue price of 20.00. A transaction changes it to 22.00. Order 103 already exists and stays pending, with no payment. Its agreed mug price stays 20.00 and its two lines still total 29.00. Changing the catalogue does not reprice the order.
BEGIN;
UPDATE products SET price = 22.00
WHERE product_id = 'P7';
COMMIT;We will follow that change through pages, memory, old row versions and the recovery log. The example follows page-based storage. PostgreSQL supplies the log and recovery path; InnoDB illustrates another way to retain old values. Page names, record letters and crash cuts are illustrative. They are not measured addresses, log positions or traces.
Pages and buffers
A page is a fixed-size block of stored data. PostgreSQL commonly uses 8 kB pages for tables and indexes. A page holds several rows together, plus information the engine needs to find and interpret them. Reading one row can therefore require reading a whole page.
The engine works on cached pages in memory, in a buffer pool; PostgreSQL calls it shared buffers. Suppose P7, P3 and P9 share a page. The first query for P7 obtains that page. A later query for P3 can use the same cached copy.
Figure as text
The first query for the mug misses shared buffers and obtains page A. The later tea query finds its row on that cached page. The file read may itself be served by the operating-system cache.
That reuse is a cache hit. A miss requires obtaining the page, but it need not reach the physical drive: PostgreSQL also uses the operating system's file cache. A query's page visits, buffer misses and drive reads are different counts.
The repeatedly used pages form the workload's working set. They share finite memory with other table and index pages. When a new page needs a slot, the engine may have to evict an existing one. A correct database after restart can still be slow while useful pages are loaded again; surviving data does not imply a warm cache.
Rows, indexes and space
Ten million narrow rows take fewer pages than ten million wide ones. The stored cost includes row headers, pointers, alignment and free space as well as column values. Large values can be compressed or stored outside the main row; PostgreSQL's TOAST mechanism does this for supported types. Estimating storage from the visible characters alone misses these costs.
The table's layout matters too. PostgreSQL stores ordinary tables as a heap, where a row can occupy any suitable page. An index entry can lead to a stored row location. InnoDB stores its table rows in the leaves of a clustered index, normally ordered by the primary key; another index carries that key to find the row. Indexes and access paths develops the lookup and key choices.
An index has pages of its own. Inserting a product maintains its indexes, and an update may change them too. In PostgreSQL, a new version that fits on the same page can avoid new ordinary index entries when the update does not change indexed columns. This is a heap-only tuple, or HOT, update. It is an optimisation with physical conditions, not a promise that every one-row update writes one page. Inside PostgreSQL follows the native layout.
Row versions and cleanup
A snapshot describes which committed versions a reader may see. It does not copy the whole database. Suppose a report establishes its snapshot while P7 costs 20.00. The writer then changes the price and commits. A later query uses a snapshot after that commit and reads 22.00, while the earlier report still needs 20.00.
The engine must keep enough information to answer both readers. Different storage designs retain that earlier price in different places.
Figure as text
After a writer commits £22, an established earlier snapshot still reads £20. PostgreSQL retains the old row version in the table. InnoDB reconstructs the older value from undo. Neither can discard required history yet.
PostgreSQL writes another row version into the table. Visibility metadata distinguishes the earlier version from the later one. InnoDB changes the current clustered record and retains undo information from which an earlier version can be reconstructed. In both designs, a report that retains an old view can make the engine retain old material while newer changes accumulate.
The snapshot's lifetime comes from the reader's isolation and commands. For this PostgreSQL example, a read-only Repeatable Read transaction has already run its first ordinary query before the update. Merely sending BEGIN would not establish that view. At Read Committed, a later ordinary statement can instead see the newer committed price. Transactions and isolation develops those rules.
Space reuse and file size
There are several different states between “old value” and “smaller file”:
| State | What the space can do |
|---|---|
| Still required | Keep the information for a snapshot or another retention requirement. |
| No longer required | Eligible for cleanup; that work may not have happened yet. |
| Cleaned up | Reusable space inside the existing storage. |
| Returned to the operating system | No longer part of the allocated file; requires an operation that can release it. |
PostgreSQL's ordinary VACUUM reclaims dead-version space for reuse. It generally keeps the table file's size, though it can release completely empty pages at the end under suitable conditions. Compacting with VACUUM FULL rewrites the table, needs room for the new copy and takes an exclusive lock. A large file alone is insufficient reason to pay that cost: it may contain useful rows, required versions or space that new writes can reuse.
Cleanup has its own work and can fall behind. Investigate what is being retained and why before choosing a rewrite. PostgreSQL vacuum and transaction age owns that diagnosis and the other maintenance obligations.
These physical versions are not a price-history service. Cleanup may remove 20.00 once the engine no longer needs it. It carries neither a promised business retention period nor the meaning of when a price became effective. Keep agreed prices on order lines and model any required history explicitly, as History, correction and deletion explains. Neither those records nor retained row versions replace a recoverable backup.
Dirty pages and the write-ahead log
After the update, the buffered page has changed while its stored copy may still be older. The buffer is dirty: it contains changes not yet written to the data file. A power failure would lose the memory copy. Replying successfully with no durable way to reconstruct it would lose the confirmed 22.00.
Writing every changed table and index page before replying would put scattered page writes on the commit path. A write-ahead log, or WAL, records recoverable changes in an append-only history instead. The update generates log records as it runs; commit adds the transaction’s outcome. Making that history durable can preserve 22.00 while the stored table page still contains 20.00.
Figure as text
An update changes the buffered price to £22. Its change record and commit record reach durable WAL before success is returned. The table page may still hold £20 and can be written later, after its WAL is durable.
Two orders matter. Before writing a changed data page, WAL describing its changes must already be durable: the write-ahead rule. Before giving a fully durable local commit acknowledgement, the transaction's required WAL, including its commit record, must be durable too. The page itself can follow later because recovery can fill the gap.
A log sequence number, or LSN, identifies a byte position in PostgreSQL's WAL. A page records how far its changes have reached in that history. Generating a record, writing it and durably flushing it are distinct progress points. An LSN is neither a business timestamp nor proof that a transaction committed.
Writing a data page also does not establish commit. A stored page can contain changes made by an unfinished transaction. Recovery and visibility rules must still distinguish that work from committed work. Even if an update's physical changes are replayed, its uncommitted version must not become an ordinary reader's value.
Writing dirty pages
Dirty pages eventually need writing so buffers can be reused and restart work can move forwards. PostgreSQL spreads this work across its checkpointer, background writer and processes serving queries. If a query needs a buffer slot whose page is dirty, it may have to perform write work before using the slot for its read. A read can therefore wait behind earlier writes.
Background writing can move that wait out of a request's path; it does not remove the storage work or the write-ahead requirement. This is why a cache full of dirty pages differs from one holding the same number of clean pages. Native writer counters and settings belong in PostgreSQL WAL, checkpoints and durability.
What commit waits for
A successful file write may mean that the operating system accepted bytes into a volatile cache. Durability requires the appropriate synchronisation request, such as fsync, to carry the required data through the storage stack under its documented contract. Controller and drive write caches matter too: their contents must survive the promised failure, or be written safely before success is reported.
The local promise therefore assumes reliable surviving storage. A drive or virtual volume that reports a flush complete while the only copy remains in unprotected volatile memory breaks that assumption. A process crash, operating-system failure and power loss can destroy different layers. Killing a database process does not establish that an acknowledged change would survive loss of power.
Group commit and batching
Several transactions can share one WAL flush. This is group commit: each retains its own commit outcome, while one synchronisation makes the required records durable for several waiters. Throughput can rise without making an individual flush faster.
Consider an arithmetic model in which a flush takes 5 ms and all other costs are ignored. One client that waits for every reply before sending its next transaction can finish at most 200 such commits per second. If one flush carries eight ready commits, it makes eight durable in those 5 ms. That does not promise eight times lower request latency: a transaction may wait for an in-progress flush before joining a later one. These are model inputs, not measured rates for your storage.
Application batching is a separate choice. Putting a thousand changes in one transaction can reduce the number of commit waits, but now the thousand changes share one outcome. It can hold locks and retain uncommitted work for longer. Choose a batch that has a sensible failure and retry boundary, rather than maximising its size solely to remove flushes.
Asynchronous commit
An asynchronous commit can acknowledge before the required log flush. PostgreSQL supports that trade: a crash can lose recent reported commits, while recovery still produces a consistent database. It preserves its write-order safeguards. Disabling those safeguards is a different risk and can allow corruption, not merely a window of lost recent work.
The distinction matters outside the database. An application might send a confirmation after an early reply, then lose the database record on which it relied. PostgreSQL does not preserve a dependent later database transaction while losing the earlier transaction it depends on; the dangerous surviving effect here can be an external message. Decide whether loss is acceptable and whether the input can be replayed before choosing early acknowledgement. Native settings and failure drills remain with the PostgreSQL durability article.
Crash recovery and checkpoints
Crash recovery uses surviving files and durable log history to restore a consistent database. Replay supplies changes that the data pages are missing. It is not another execution of UPDATE products SET price = price + 2: re-running an application command could apply the business change twice.
In PostgreSQL, redo routines use the records and page progress to apply the necessary physical work. Replayed row versions still need the correct transaction outcome. Work without a completed commit does not become a committed price merely because a change record survived.
Now suppose the server fails during the price update. The recovered database and the caller’s knowledge depend on different events: whether the commit record survived, and whether its reply reached the application.
Figure as text
Before a durable commit survives, the recovered committed price is £20. After its durable commit it is £22, even if the reply was lost. A normal durable reply preserves £22; an early asynchronous reply can be followed by recovery to £20.
Before a durable commit survives, the new price is not recovered as committed. After it survives, 22.00 can return even if the reply never reached the caller. Both can look like a broken connection to the application. Retries and uncertain commit outcomes follows one request identity through that uncertainty; a blind new request is not evidence that the first failed.
Unfinished physical work is handled according to the engine's design. PostgreSQL can retain replayed versions that ordinary readers cannot see. An engine using undo can also need to roll unfinished work back. Logical atomicity does not require data files to have contained only committed changes at every instant.
Checkpoints and restart work
Recovery need not replay from the beginning of the database’s life. A completed checkpoint establishes a safe redo starting point: earlier required page changes have reached the data files. The checkpoint’s record lets restart locate that point. Later durable history can still need replay; a volatile tail beyond it may be lost.
A conceptual history in order, not a scale of elapsed time or WAL volume. A checkpoint can take time to complete; the safe redo point is not simply the instant its final record is written.
More frequent checkpoints can reduce the history restart must examine, while increasing page-write work. PostgreSQL normally also logs a full-page image on the first page modification after a checkpoint. This protects against a page write interrupted partway through: a few redo changes alone cannot reliably repair an arbitrary mixture of old and new page contents.
A checkpoint is not a backup, and it is not a fixed outage-time guarantee. Restart includes failure detection, starting the server, reading and applying history and other engine work. The cache can then need warming. Log volume, storage and replay work all affect the result. Old WAL may also be needed for backups or replicas after it is no longer required for local crash recovery; removing it requires satisfying those obligations too.
Write amplification
The logical change was small: replace P7’s catalogue price with 22.00. Its physical work can be much larger. Write amplification describes that extra writing relative to the logical change. The update can generate recovery records, change table or index pages, and leave obsolete material for later cleanup. None of those categories represents one fixed physical write.
A work map for logged, page-based storage. Which indexes change and how much WAL is generated depend on the engine, schema, free space and current storage state. The boxes are not additive page counters.
Several updates to the same dirty page can share a later page write, while an index split or full-page image can add substantial work to one update. Retained versions consume space and create later cleanup. Consequently neither “one row changed” nor “once to the log and once to the page” predicts the bytes written by a workload.
Other engines organise the work differently. A log-structured merge-tree, or LSM, writes sorted immutable files and later merges them through compaction. Compaction creates replacement files and removes old ones, which can write the same surviving data again. CockroachDB's local storage uses this family of design; PostgreSQL's ordinary heap is not compacted that way. Inside CockroachDB develops its native storage and replicated ownership.
Judge storage work over the useful operation and its later maintenance, under representative load. More free space or another index can trade read work against write work; postponing maintenance can move cost into the future. A commit reply says that the selected durability condition was met. It does not say every physical consequence of the change has finished.
What restart recovery cannot fix
The price model kept the storage containing the files and WAL. Losing that storage removes the material from which local restart works. A different mistake needs a different answer: suppose a separate transaction successfully sets the mug's price to 2.20. Crash recovery should preserve that committed value. It has no business rule saying that the operator meant 22.00.
| Situation | What to establish next |
|---|---|
| Database process stopped; storage survives | Restart and engine recovery; distinguish this test from operating-system or power loss. |
| Power lost; storage survives | Which history really became durable under the storage and commit contract? |
| Storage lost or irreparably damaged | Which surviving replica or recoverable backup contains the required history? |
| Unwanted committed price 2.20 | Should the business correct the value, or restore an earlier history and reconcile later valid work? |
A replica keeps another live copy, but the required change must have reached the copy under the promised acknowledgement rule. An asynchronous replica may be behind, and any replica can copy the same mistaken 2.20. Receipt, durable storage and replay are different progress points. Replicas, lag and read routing follows those promises; Failover and fencing owns the transfer of write authority.
A backup preserves a recoverable copy. For physical point-in-time recovery, a suitable base backup and a complete required log chain can restore to a chosen point. Replaying all the way through the mistaken commit keeps the mistake. Stopping earlier also omits valid work after that point, which must be considered alongside external effects.
Correcting the current price can be the right business action when the rest of the history is sound. Restoring an earlier database is a wider choice with a different loss boundary. Backup, restore and reconciliation develops how to choose and test the recoverable history, and account for valid later work. First identify the loss; then choose the copy or correction that actually addresses it.