Skip to content
KEDBYTE
Site navigation
How Data Works
Chapter
27

Pages, Buffers and the Storage Engine

Part E · Storage and Recovery|3,804 words|about 17 min read|Volume E

27.0 What this chapter gives you#

  1. SQL describes logical records, but a storage engine moves and manages physical units. This chapter connects rows to pages, memory buffers, free space and the work triggered by a small update.
  2. You will estimate page occupancy under stated assumptions, distinguish a database cache hit from a device read, and inspect storage metadata without pretending a toy page is a real PostgreSQL or SQLite file.
  3. The arithmetic layouts below are teaching designs. Product-specific sizes and behaviours are labelled separately, and the labs operate only on synthetic data.

27.1 Logical rows and physical pages#

27.1.1 PLAIN — in simple words#

  1. A row is a logical record. A page is a physical storage unit used by many database engines to organize records and indexes.
  2. Several small rows can share a page. A large value may require an overflow or separate-storage mechanism. The relationship is not simply one row equals one file or one disk sector.
  3. Reading one requested field can involve bringing a larger unit into memory. That is one reason data layout and access patterns affect performance even when the SQL result is tiny.

27.1.2 PLAIN — a picture in your head#

  1. Think of records as individual forms and pages as folders that carry several forms together. Fetching one form may require retrieving its folder.
  2. A folder also has labels and indexing information, leaving less than its full capacity for form contents.
  3. Where the comparison breaks: database pages have exact binary layouts, and the device can have different physical transfer units. A folder analogy does not define page atomicity or hardware write guarantees.

27.1.3 PLAIN — a worked example#

  1. Design a toy page of 4,096 bytes with a 64-byte header. Each stored row uses 96 payload bytes plus a 4-byte slot entry.
  2. Available space is 4,096 − 64 = 4,032 bytes. At 100 bytes per row-plus-slot, the page holds at most 40 such rows, leaving 32 bytes unused under this simplified layout.
  3. One thousand rows therefore need at least 25 data pages if all pages achieve that occupancy. Real engines may use more because of metadata, free space, variable-length values and update history.
  4. These are not SQLite’s or PostgreSQL’s actual tuple-overhead figures. They are a transparent model whose assumptions can be changed and recalculated.

27.1.4 PLAIN — what is really happening inside#

  1. A page can contain a header, an array of item references, free space and record bytes. The engine interprets those bytes using its file-format and schema information.
  2. An index page may organize keys and child pointers rather than business rows. A query can visit both index pages and table pages to produce one result.
  3. Physical storage can change while logical identity stays the same. Compaction, updates and maintenance should not force the application to identify customers by their current byte offsets.

27.1.5 TECHNICAL — the engineer’s version#

  1. PostgreSQL’s standard heap layout commonly uses 8 kB pages, with a compile-time page-size choice. The documented page header is 24 bytes and item identifiers use 4 bytes each. These are implementation facts, not universal database constants. [S128]
  2. SQLite’s database page size is a power of two between 512 and 65,536 bytes under its file format. Header encoding treats the value 1 specially to represent 65,536. [S133]
  3. Separate logical key, page identifier, slot reference and device block address in a design. Each names a different layer and may have different stability guarantees.

27.1.6 WORDS — remember these#

  1. Database page: a unit in the engine’s physical layout — a fixed-size storage block containing records, index entries or other engine structures. Slot entry: a small reference locating an item within a page — metadata mapping an item identifier to its stored position and length. Page occupancy: how much of a page is usefully filled — the proportion consumed by payload and required structures under a stated layout.

27.2 Buffer pools#

27.2.1 PLAIN — in simple words#

  1. A buffer pool keeps database pages in memory so repeated work can avoid fetching them again from lower storage layers.
  2. Memory is limited. The engine must decide which pages to retain, which to replace and how to coordinate pages currently being used or changed.
  3. A database-cache miss does not necessarily mean a physical device read. The operating system may already have a copy in its own cache.

27.2.2 PLAIN — a picture in your head#

  1. Mira keeps frequently used folders on her desk and retrieves others from a cabinet. A desk hit is faster than a cabinet trip.
  2. The cabinet assistant may also have a trolley of recently fetched folders. Missing from Mira’s desk does not mean the folder must travel all the way from the distant archive.
  3. Where the comparison breaks: the layers contain copies and coordination metadata, not one movable physical folder. Cache coherence, pinning and dirty-state handling are more exact than ordinary desk tidying.

27.2.3 PLAIN — a worked example#

  1. A toy buffer pool has three slots and uses least-recently-used replacement. Read page IDs 1, 2, 3, 1, 4, 2.
  2. The first three reads miss and fill the pool. Reading 1 hits and makes it most recent. Reading 4 evicts 2; the later read of 2 misses again.
  3. The model records one hit and five misses. It demonstrates a replacement policy, not the policy or device-read count of a named engine.
  4. A different access sequence or replacement algorithm can change the result. A single global hit ratio cannot tell you which important request suffered the expensive miss.

27.2.4 PLAIN — what is really happening inside#

  1. The engine locates the requested page in its buffer mapping or chooses a buffer to load it into. It coordinates concurrent access and prevents a page from being evicted while an operation still requires it.
  2. A dirty page cannot be discarded as though it were merely an unchanged cached copy. Its modifications need the engine’s recovery and flushing protocol.
  3. Large scans can compete with frequently reused pages for cache capacity. Engines may use specialized strategies to avoid letting one scan disrupt every other workload.

27.2.5 TECHNICAL — the engineer’s version#

  1. PostgreSQL I/O statistics distinguish operations at the database/kernel boundary but do not by themselves distinguish actual device reads from data already in the kernel’s page cache. Combine them with appropriate operating-system evidence. [S144]
  2. Cache metrics require a scope and interval. A counter since process start can hide a recent regression; ratios aggregated across workloads can hide a slow critical query.
  3. The companion LRU exercise is a deterministic model with no real I/O, locks or dirty-page writeback. Do not use its hit count as a measured PostgreSQL buffer-pool result.

27.2.6 WORDS — remember these#

  1. Buffer pool: the database’s in-memory page workspace — a managed cache of pages and metadata used by the storage engine. Cache hit: the needed item is already in the named cache — an access resolved at a specified cache layer. Eviction: remove a cached item to make room — replacement subject to usage, dirty-state and recovery constraints.

27.3 Row layout#

27.3.1 PLAIN — in simple words#

  1. A stored row contains more than the visible values returned by SELECT. The engine needs information about field boundaries, missing values, versions and other implementation details.
  2. Variable-length text cannot always be located by multiplying a column number by a fixed width. Earlier fields, alignment and length information can affect where it begins.
  3. Large values may be compressed or stored elsewhere with a reference in the main row. Reading only small fields can therefore avoid some large-value work, depending on the engine and query.

27.3.2 PLAIN — a picture in your head#

  1. An application form has boxes of different sizes and a note saying that the attached document is in another folder. The visible answers are not the whole filing structure.
  2. A short reference can stand in for a large attachment until someone actually needs it.
  3. Where the comparison breaks: binary length headers, alignment and compression are not handwritten annotations. Reading raw bytes safely requires the exact format and bounds, not guesswork based on how a screen displays the row.

27.3.3 PLAIN — a worked example#

  1. Define a toy record with an 8-byte header, a 4-byte ID, a 2-byte text length and five UTF-8 bytes of text. Without alignment or other fields, its size is 8 + 4 + 2 + 5 = 19 bytes.
  2. Replace the text with ten UTF-8 bytes and the record becomes 24 bytes. The number of displayed characters need not equal the encoded byte count.
  3. If a format rounds total size up to a multiple of eight bytes, both 19 and 24 occupy 24 bytes, while 25 occupies 32. That rule belongs to the toy format only.
  4. The exercise links Chapter 3’s representations to physical space: units and encoding remain important after values enter a database.

27.3.4 PLAIN — what is really happening inside#

  1. Row metadata tells the engine how to interpret fields under the relevant schema and storage representation. Missing values may use bitmap information rather than a full ordinary payload value.
  2. Schema evolution adds another layer: old and new row representations may coexist while the engine supplies the logical view promised to SQL.
  3. Do not infer storage savings solely from a column’s declared maximum length. Actual representation, compression, nulls and engine rules determine the physical result.

27.3.5 TECHNICAL — the engineer’s version#

  1. PostgreSQL heap rows have a fixed header, optional null bitmap and aligned user data under the documented layout. Physical interpretation depends on type lengths and alignment metadata. [S128]
  2. PostgreSQL TOAST handles eligible large values through compression and/or out-of-line storage. Ordinary tuples do not simply span arbitrary heap pages as one contiguous record. [S143]
  3. SQLite records use their own header and serial-type representation. Treating a PostgreSQL tuple decoder as an SQLite parser would be a format error, even when both systems return similar SQL values. [S133]

27.3.6 WORDS — remember these#

  1. Row header: metadata around the visible field values — implementation information needed to locate, interpret or manage a stored row version. Alignment: place values at required byte boundaries — padding and layout rules supporting an implementation’s representation or access requirements. Out-of-line value: a large value stored separately from its main row — payload reached through a reference rather than fully embedded in the ordinary tuple.

27.4 Dirty pages and flushing#

27.4.1 PLAIN — in simple words#

  1. A dirty page is a memory copy containing changes that have not yet been written to the corresponding main data-file location.
  2. Dirty does not mean corrupt. It means that the in-memory page and its lower-level stored copy differ in a way the engine is managing.
  3. A transaction can commit before every changed data page reaches its final data-file location if the recovery protocol has durably recorded enough information to reconstruct the accepted changes.

27.4.2 PLAIN — a picture in your head#

  1. Mira corrects a working folder on her desk and records the correction in a protected journal. The cabinet copy can be updated later because the journal tells the recovery clerk what changed.
  2. The order is essential: losing the desk copy must not also lose the only description of an accepted correction.
  3. Where the comparison breaks: a recovery log uses engine-specific records, ordering and commit rules. A casual handwritten note is not enough to recover every possible partial write.

27.4.3 PLAIN — a worked example#

  1. A toy database page on persistent storage contains stock 5. A transaction changes its buffered version to 2 and creates sufficient recovery information for that accepted update.
  2. If the log and required commit evidence are durable while the data page still contains 5, recovery can redo the committed change under the toy protocol.
  3. If the engine overwrites the data page before making the required recovery information durable, a crash can leave an incomplete state with no reliable reconstruction path.
  4. Chapter 28 makes the toy log rules explicit. This chapter does not claim that merely writing the text “stock=2” implements a real WAL engine.

27.4.4 PLAIN — what is really happening inside#

  1. The engine schedules writeback and coordinates it with recovery records. A background writer or checkpoint can move dirty pages toward their data-file locations.
  2. A page may be modified again while flushing is in progress. Correct engines track the relevant synchronization and dirtiness state rather than assuming one write makes every future version clean.
  3. Flushing to the operating system and forcing data through lower caches are distinct boundaries. A successful function call must be interpreted under its actual contract.

27.4.5 TECHNICAL — the engineer’s version#

  1. Write-ahead logging requires relevant log records to be persisted before the corresponding data-file modifications are allowed to become durable under the engine’s protocol. This permits redo-based recovery without forcing every data page at each commit. [S129]
  2. PostgreSQL checkpoints bound the WAL region needed for crash recovery while creating their own I/O work. WAL archiving and other retention requirements can keep older log material relevant beyond local checkpoint needs. [S135] [S131]
  3. A database buffer flush is not a universal proof against device loss or dishonest hardware acknowledgements. Chapter 30 follows the persistence chain explicitly. [S130]

27.4.6 WORDS — remember these#

  1. Dirty page: a changed cached page awaiting corresponding writeback — a buffer whose managed contents differ from its lower stored representation. Flush: push pending changes toward a specified lower layer — an operation whose persistence guarantee depends on the API and storage contract. Redo: reconstruct an accepted change from recovery information — replay that restores data not fully reflected in the main data files before failure.

27.5 Free space and fragmentation#

27.5.1 PLAIN — in simple words#

  1. Deleting a row does not necessarily make the database file smaller immediately. Its space may become reusable within the file while the file retains its allocated size.
  2. Free space can be scattered across pages. A large new record may need a suitable contiguous region or an overflow mechanism even when total free bytes look sufficient.
  3. Maintenance can reorganize storage, but it consumes resources and may require locks or extra temporary space. Rebuilding a file is not a harmless cosmetic operation.

27.5.2 PLAIN — a picture in your head#

  1. Removing forms from several folders creates spare room without removing any folder from the cabinet. A new thick form may still not fit in any one folder.
  2. Repacking the folders can help, but someone must read, move and rewrite the contents while preserving their identities.
  3. Where the comparison breaks: engines have exact slot and free-space rules, and some can reuse fragmented regions efficiently. “Fragmented” is a diagnosis requiring measurements, not a general explanation for every slow query.

27.5.3 PLAIN — a worked example#

  1. Four toy pages each have 100 free bytes. The total is 400 bytes, but a 250-byte item cannot fit on any one page if the format requires the item to fit entirely within one page.
  2. A compaction or overflow strategy might change the result. The sum of free bytes alone cannot tell you which strategy exists.
  3. Now delete half the rows from a 20-page toy file. The logical row count halves, but the file can remain 20 pages while its internal free space becomes available for later inserts.
  4. Measure logical rows, allocated pages and reusable space separately rather than expecting all three to shrink together.

27.5.4 PLAIN — what is really happening inside#

  1. Engines keep metadata about pages with reusable space and may compact items within a page or recycle entirely free pages.
  2. Old row versions and long snapshots can delay when space becomes reusable. A DELETE’s commit is not always the end of its storage consequences.
  3. Rebuilding or vacuuming operations differ by product. A command with the same name can have different locking, file-size and operational effects in two engines.

27.5.5 TECHNICAL — the engineer’s version#

  1. PostgreSQL ordinary VACUUM and SQLite VACUUM are not interchangeable operations. PostgreSQL routine vacuuming commonly reclaims reusable space, while SQLite VACUUM rebuilds the database file and has its own space and transaction constraints. [S123] [S145]
  2. Include indexes and large-value storage when measuring total footprint. A compact heap with oversized indexes is not a small database merely because one table-size metric looks low.
  3. The lab’s page arithmetic and SQLite metadata observations do not justify production maintenance commands. Capture workload, free-space evidence, backup/restore readiness and acceptable lock duration before a real intervention.

27.5.6 WORDS — remember these#

  1. Reusable space: allocated storage available for later records — free capacity inside an existing database structure rather than necessarily returned to the filesystem. Fragmentation: useful space is distributed inconveniently — a layout condition whose performance effect depends on the engine and access pattern. Rebuild: write a reorganized physical representation — maintenance that can improve layout while requiring I/O, temporary capacity and coordination.

27.6 Reading engine-specific evidence#

27.6.1 PLAIN — in simple words#

  1. A useful storage investigation begins with a question: are we reading too much, missing a cache, retaining old versions, waiting for writes or running out of space?
  2. Choose observations that distinguish those possibilities. A single file size, hit ratio or elapsed time rarely identifies the cause by itself.
  3. Observe through supported interfaces first. Editing a live database file with a hex editor is not a reasonable first diagnostic step.

27.6.2 PLAIN — a picture in your head#

  1. A mechanic first checks the dashboard, service record and accessible measurements. They do not drill a hole into a running engine because a noise sounds unfamiliar.
  2. Storage metadata is the instrument panel, not a complete explanation of every failure.
  3. Where the comparison breaks: database files can contain private records and credentials. Diagnostic copies, logs and screenshots need access controls as well as technical care.

27.6.3 PLAIN — a worked example#

  1. In a fresh SQLite teaching database, record PRAGMA page_size, PRAGMA page_count and PRAGMA freelist_count before and after a bounded synthetic workload.
  2. Multiply page size by page count to describe the logical main-file page allocation under that observation. Do not add a claim that this equals every physical byte consumed by WAL, journals, temporary files and backups.
  3. Compare row counts and expected totals too. A smaller file containing missing records is not an optimization success.
  4. The companion records actual runtime and query outputs; no page count is invented in the prose because it can vary with the engine and exact fixture.

27.6.4 PLAIN — what is really happening inside#

  1. Statistics can be cumulative, delayed, reset or sampled. Their observation interval and reset point are part of their meaning.
  2. Product documentation can also change. The official SQLite WAL page consulted for this edition discloses a 2026 WAL-reset bug affecting older releases under specific concurrent WAL write/checkpoint conditions.
  3. The historical lab runtime is SQLite 3.46.1. Its successful bounded tests are not a recommendation to deploy that release or a test of the disclosed concurrent WAL bug. The new exercises do not attempt that workload. [S132]

27.6.5 TECHNICAL — the engineer’s version#

  1. Record engine version, journal/recovery mode, relevant settings, connection count, exact statements and measurement interval. Use supported metadata and a disposable copy for low-level experiments. [S75] [S144]
  2. As consulted on 25 September 2026, SQLite documents the WAL-reset fix in 3.51.3 and later, with named backports. Verify the current supported release and applicable advisories before real deployment; do not infer safety from this book’s historical runtime. [S132]
  3. Corruption investigations require evidence preservation. Keep the original data and associated recovery files intact under a reviewed acquisition procedure; Chapter 32 explains why an attempted repair can destroy the information needed to diagnose the problem. [S139]

27.6.6 WORDS — remember these#

  1. Storage observation: a measured fact about one physical configuration — metadata or instrumentation interpreted with version, scope and timing. Freelist: storage recorded as available for reuse — an engine-managed collection of free pages or blocks. Runtime provenance: which software actually produced the result — the recorded engine and interpreter versions and relevant configuration of an experiment.

27.97 Practice and worked answers#

  1. Count toy rows per page. A 4,096-byte page has 64 header bytes and 100 bytes per row-plus-slot. Answer: floor(4,032/100)=40 rows, with 32 bytes left.
  2. Trace the three-slot LRU cache. Read 1,2,3,1,4,2. Answer: one hit and five misses under the stated toy policy, not five proven device reads.
  3. Apply alignment. Round record lengths 19,24,25 up to multiples of eight. Answer: 24,24,32 bytes under that chosen layout.
  4. Interpret dirty. A buffered page differs from its main-file copy. Answer: this is managed pending writeback, not necessarily corruption.
  5. Find the space trap. Four pages each have 100 free bytes; a 250-byte item must fit on one page. Answer: total free space is insufficiently localized; no page can hold the item without another mechanism.
  6. Interpret a DELETE. Row count halves but file size does not. Answer: storage may be reusable internally, retained for old versions or still allocated for engine structures.
  7. Name the cache boundary. PostgreSQL reports an I/O read. Answer: that does not by itself distinguish a device access from an operating-system cache hit.
  8. Limit the runtime claim. The old SQLite runtime passes the educational tests. Answer: this does not establish current production suitability or exercise the disclosed concurrent WAL-reset bug.

27.98 Common wrong ideas#

  1. Wrong: one row occupies one page. Right: pages can contain many rows, while large values can use separate storage.
  2. Wrong: page size is a universal database constant. Right: formats and builds differ.
  3. Wrong: a database cache miss proves a disk read. Right: lower caches may satisfy it.
  4. Wrong: dirty means damaged. Right: it normally means changed and awaiting managed writeback.
  5. Wrong: COMMIT must flush every changed data page. Right: a correct recovery protocol can persist log evidence and redo later.
  6. Wrong: deleted rows immediately shrink the file. Right: allocated and reusable space are different measurements.
  7. Wrong: all VACUUM commands have the same semantics. Right: consult the specific engine and operation.
  8. Wrong: passing old-runtime tests certifies current deployment safety. Right: version advisories and real workload validation remain necessary.

27.99 Chapter summary in 20 lines#

  1. Logical records and physical storage units belong to different layers.
  2. Pages organize rows, index entries and engine metadata.
  3. Headers and slot entries reduce space available for payload.
  4. Toy occupancy calculations must state their layout assumptions.
  5. Large values can use compression or out-of-line storage.
  6. Physical row locations should not replace stable business keys.
  7. Buffer pools retain pages in memory for reuse.
  8. Cache replacement depends on workload and engine policy.
  9. A database-cache miss is not necessarily a physical-device read.
  10. Row representations include metadata, lengths, null information and alignment.
  11. Dirty pages contain managed changes awaiting corresponding writeback.
  12. Recovery ordering connects log persistence to later data-page flushing.
  13. A committed transaction need not have every data page at its final location immediately.
  14. Checkpoints and background writeback have measurable resource costs.
  15. Free space inside a file differs from space returned to the filesystem.
  16. Scattered free bytes do not guarantee room for one large item.
  17. Maintenance commands differ in semantics across products.
  18. Supported metadata is the starting point for storage diagnosis.
  19. Record runtime, settings and observation intervals with every experiment.
  20. Preserve recovery evidence and verify current advisories before real deployment.

Return to contents