# Chapter 2: Physical Storage & MVCC ## 2.0 - Storage Foundations (The Building Blocks of Storage) <img src="assets/chap_1_storage_hierarchy.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> In the clean, abstract world of application logic, a query is just information. But to Postgres, data is a physical reality. It has a location on a disk, a specific byte size, and an associated cost to retrieve. ### What You'll Learn - How Postgres physically organizes data into **Tuples**, **Pages**, and **Relations** - Why an `UPDATE` creates a new heap tuple version instead of overwriting the old user payload - How **MVCC** lets ordinary reads and writes avoid blocking over row visibility - What **TOAST** does when data exceeds the 8KB page limit Because storage I/O is often expensive, PostgreSQL uses buffers, access methods, and a page-oriented layout to avoid or combine unnecessary work. A standard build uses 8KB **pages** (`BLCKSZ` is configurable at build time), which move between PostgreSQL buffers, the operating-system cache, and storage. ### The Logical vs. The Physical A common misconception is that the database operates directly on "rows" and "columns," as if managing a giant in-memory spreadsheet. In reality, a row is a logical abstraction—a clean, version-less representation of data used by the application. Deep in the storage layer, Postgres manages **Tuples** (the physical records on disk) packed into **Pages** (8KB blocks of memory and disk). > [!NOTE] Rows Are Logical; Tuples Are Physical > **Concept**: A row is logical; a tuple is physical. > **Payoff**: A row is the version-less abstraction humans see; a heap tuple is one physical version with transaction metadata. An `UPDATE` creates a new heap tuple instead of replacing the old row payload, so one logical row can leave several physical versions behind. ### Versioned Heap Updates When you run an `UPDATE`, PostgreSQL creates a new tuple version in the **heap** and marks how the old version retires and links onward. The new tuple may fit on the same page—enabling a HOT chain—or land on another page. Pruning and vacuum also make old space reusable, so the heap is a busy warehouse, not an immutable append-only log. This is the physical basis of **Multi-Version Concurrency Control (MVCC)**. Ordinary reads and writes generally do not block one another merely to decide which version is visible. Real conflicts still require coordination; `SELECT ... FOR UPDATE`, DDL, and Serializable conflict detection deliberately step outside the slogan. > [!NOTE] Where the suitcase metaphor stops > A new tuple version is like packing a new suitcase instead of replacing the contents of the old one. Unlike a real suitcase, PostgreSQL can change transaction metadata and line-pointer state around the old tuple, and it can reuse the space later. ### The Storage Hierarchy To minimize the cost of fetching data, the engine organizes storage into a disciplined hierarchy: - **[[Manuscript/02 - Physical Storage & MVCC/2.1 - Data Types (Knicks, knacks, bits, and bobs)|Data Types]]** — The bits and alignment of individual facts. - **[[Manuscript/02 - Physical Storage & MVCC/2.2 - Tuple (The Suitcase)|Tuples]]** — The physical realization of a row, wrapped in headers. - **[[Manuscript/02 - Physical Storage & MVCC/2.3 - The Page (The Shipping Container)|Pages]]** — The 8KB "shipping containers" that move between disk and memory. - **[[Manuscript/02 - Physical Storage & MVCC/2.4 - Relation (The Table)|Tables]]** — The collection of pages that form a logical entity. - **[[Manuscript/02 - Physical Storage & MVCC/2.5 - MVCC (The Sharpie Ledger)|MVCC]]** — The visibility rules that govern concurrent access. - **[[Manuscript/02 - Physical Storage & MVCC/2.6 - TOAST (The Separate Trailer)|TOAST]]** — The overflow valve for data that cannot fit in a page. ### The Result Set: Unpacking the Suitcase When you submit a query, the engine scans these physical pages and produces a temporary projection called a **[[Structures/Result Set|Result Set]]**. This is where physical tuples are finally unpacked and presented back to you as weightless, logical rows, stripped of their bureaucratic headers. Dead tuples are left behind in the heap to be cleaned up later by the **Autovacuum** process. --- ## 2.1 - Data Types (Knicks, knacks, bits, and bobs) <img src="assets/bits_types_collection.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Postgres refuses to store abstract concepts. It needs to know exactly what size "box" to put things in before it can start packing. In Postgres, every field is strictly defined by a **Data Type**. A type is essentially a very practical architectural contract that specifies two things: 1. **Storage Footprint**: How many bytes Postgres must set aside for this value in the physical record. 2. **Valid Operations**: What mathematical or logical actions can be performed on the data (for example, you can't multiply a `date` by `saffron`). --- ### The Architectural Contract Think of data types as physical boundaries. If you try to force an 8-byte payload into a 2-byte slot, Postgres will reject it. If you put a 1-byte payload into an 8-byte slot, Postgres will shake its head at the waste. Imagine storing a single `boolean` (1 byte) in a `bigint` column (8 bytes). This isn't just a waste of space—it significantly increases the I/O cost of every query. Because Postgres is "Strongly Typed," once a column is defined as an `integer`, it strictly rejects any input that does not conform to that contract. This structure allows the engine to optimize data layout and ensure high-speed retrieval. To maintain performance, you must be aware of the storage overhead. If you frequently specify 8-byte types for data that fits in 2 bytes, you are unnecessarily bloating your records and reducing the number of records that fit within a single **[[Manuscript/02 - Physical Storage & MVCC/2.3 - The Page (The Shipping Container)|Data Page]]**. --- ### Standard Inventory | Category | Technical Name | Physical Size | | :--- | :--- | :--- | | **Logic** | `boolean` | 1 byte | | **Numeric** | `smallint` | 2 bytes | | **Numeric** | `integer` | 4 bytes | | **Numeric** | `bigint` | 8 bytes | > [!NOTE] > **The `text` vs. `varchar` implementation**: In Postgres, `text` and `varchar` use the same underlying storage representation (**`varlena`**). Both use a dynamic structure preceded by a small header (the "tag") that tells Postgres the content length. The only difference is that `varchar(n)` forces Postgres to perform an additional length check before storing; `text` skips this check. --- ### Alignment Padding Physical records aren't packed perfectly end-to-end. To ensure high-speed memory access, the CPU relies on alignment. Modern processors fetch memory in standardized chunks (typically 32-bit or 64-bit words). If a data type like an 8-byte `bigint` is unaligned and spans across two words, the CPU is forced to execute two separate memory reads and combine the fragments using bit-shifts—a silent CPU latency tax. To avoid this, Postgres forces data types to start at specific byte boundaries corresponding to their size (e.g., a `bigint` must start on a multiple of 8). If you pack a 1-byte `boolean` followed by an 8-byte `bigint`, Postgres inserts **7 bytes of alignment padding** so the `bigint` can align comfortably on its boundary. You can observe this overhead directly using `pg_column_size()`: ```sql -- A single boolean and a bigint: 1 + 7 (padding) + 8 = 16 bytes SELECT pg_column_size(row(true, 1::bigint)); -- Two bigints and two booleans: 8 + 8 + 1 + 1 = 18 bytes (no padding between columns) -- Note: the total row size will still be aligned to the MAX alignment of the types. SELECT pg_column_size(row(1::bigint, 2::bigint, true, false)); ``` #### Optimal Column Ordering: Optimizing for Space Because of padding, the **order** of your columns determines the final physical weight of your data. | Order Style | Example Sequence | Final Size | | :--- | :--- | :--- | | **Bad Packing** | `bool, bigint, bool, bigint` | 32 bytes | | **Good Packing** | `bigint, bigint, bool, bool` | 24 bytes | In this deliberately awkward example, grouping the wider types first reduces the illustrated record from 32 bytes to 24 bytes—a **25% saving for this row shape**. Real savings depend on the exact types, null bitmap, tuple header, and column sequence; many tables save less or nothing. --- ### OIDs: Postgres's Secret Labels Postgres doesn't identify types by their names internally. To the engine, "integer" or "timestamp" are just human-readable aliases. Internally, every object in the database is assigned a unique, 4-byte numeric label called an **OID (Object Identifier)**. Think of an OID as the **Internal Serial Number** for code objects. Strings are heavy and expensive to compare in C; integers are light and constant. When the query planner needs to pass a type into a function, it doesn't pass the word "integer"—it passes the number **23**. #### The Unified Catalog Almost everything in Postgres has an OID: - **Tables** (Relations) in `pg_class`. - **Functions** in `pg_proc`. - **Indexes** in `pg_index`. - **Types** in `pg_type`. By labeling every concept with a consistent numeric ID, Postgres can use the same indexing machinery to manage its own internal blueprints as it uses to manage your actual data. > [!NOTE] > **The Limit of the ID**: OIDs are "Cluster-Global" 32-bit integers, meaning they wrap around at ~4.2 billion. While this seems infinite, the risk of "Wraparound" is why Postgres retired OIDs for identifying user records, reserving them safely for the internal catalog blueprints. --- ### 🧪 Observation Lab: Column Alignment Padding To see the physical cost of alignment padding, we will build two tables storing the exact same data, but with different column orders, and inspect their physical tuple layouts using the `pageinspect` extension. #### Build Aligned and Padded Rows Connect to the database and run the following statements: ```sql CREATE EXTENSION IF NOT EXISTS pageinspect; -- A poorly aligned table: boolean (1 byte), bigint (8 bytes), boolean (1 byte) CREATE TABLE pad_bad ( a BOOLEAN, b BIGINT, c BOOLEAN ); -- An optimally aligned table: bigint (8 bytes), boolean (1 byte), boolean (1 byte) CREATE TABLE pad_good ( b BIGINT, a BOOLEAN, c BOOLEAN ); -- Insert identical values into both INSERT INTO pad_bad VALUES (true, 1, false); INSERT INTO pad_good VALUES (1, true, false); ``` #### Read Both Tuple Lengths from Page Zero Query the physical storage size of the newly written records by reading the line pointers directly from the underlying page: ```sql -- Check the size of the tuple in pad_bad SELECT lp, lp_len FROM heap_page_items(get_raw_page('pad_bad', 0)); -- Check the size of the tuple in pad_good SELECT lp, lp_len FROM heap_page_items(get_raw_page('pad_good', 0)); ``` #### Bad Column Order Costs Seven Bytes per Row Notice the physical sizes of the tuples: - `pad_bad` returns a tuple length (`lp_len`) of **41 bytes**. - `pad_good` returns a tuple length (`lp_len`) of **34 bytes**. #### Largest-First Packing Saves Seven Bytes per Row By placing the heaviest column (`bigint`) at the beginning, we saved 7 bytes per row. This is because Postgres was able to pack the two 1-byte booleans consecutively, requiring no padding. In the bad table, Postgres had to insert 7 bytes of padding before the `bigint` column to align it to an 8-byte boundary. For a new schema, column order can be a useful design-time space optimization on sufficiently large or wide tables. For a populated production table, reordering columns generally requires a table rewrite or migration. Measure the bytes saved against migration risk, schema-evolution cost, and readability before deciding that this particular packing trick is worth it. ```sql -- Clean up DROP TABLE pad_bad; DROP TABLE pad_good; ``` --- ## 2.1.1 - System Catalogs (The Engine Queries Itself) <img src="assets/arch_system_catalogs.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Up until now, we've described the engine as a machine with hidden gears — tuples packed into pages, types enforced by contract, OIDs assigned as secret labels. But Postgres has a strange architectural habit: it stores the blueprints for the machine *inside the machine itself*. Tables, types, indexes, functions, schemas, and roles are not magical C-struct internals buried in the engine's source code. They are rows in system tables — tables that live in the `pg_catalog` schema and are queryable with ordinary SQL. This is not a convenience feature. It is a foundational design decision. The engine manages its own infrastructure using the same relational model it uses to manage your data. The same indexing, the same visibility rules, the same query planner. Postgres does not have a separate metadata engine. It *is* its own metadata engine. --- ### The Mystery Consider what happens when you run: ```sql CREATE TABLE capybaras ( id INT, name TEXT ); ``` You just created a thing. But where did Postgres *remember* this? Not philosophically — literally. There is no configuration file that was appended to. No XML registry. No hidden binary blob. The answer: ```sql SELECT relname, relkind, reltuples FROM pg_class WHERE relname = 'capybaras'; ``` ```text relname | relkind | reltuples ------------+---------+----------- capybaras | r | 0 ``` The table you created became a **row**. In another table. That is the click. --- ### `pg_class` — The Ledger of Things `pg_class` is the central registry of *every named object* in the database. Every table, index, sequence, view, materialized view, and TOAST table is a row in `pg_class`. ```sql -- What kinds of objects live in pg_class? SELECT relkind, count(*) FROM pg_class GROUP BY relkind ORDER BY count DESC; ``` The `relkind` column tells you what category of object the row represents: | `relkind` | Meaning | | :--- | :--- | | `r` | Ordinary table (relation) | | `i` | Index | | `S` | Sequence | | `v` | View | | `t` | TOAST table | | `m` | Materialized view | Every object that has a physical presence on disk — a file in `base/` — has a corresponding row here. The column `relfilenode` tells you which file it maps to. The column `relpages` tells you how many 8KB pages it currently occupies. The column `reltuples` is the planner's estimate of how many rows are in the table — the number the **Query Planner** uses to decide between a Sequential Scan and an Index Scan. > [!NOTE] > **This is why `ANALYZE` matters.** When you run `ANALYZE`, Postgres samples the table and updates `pg_class.reltuples` and `pg_class.relpages`. If these numbers are stale, the planner's cost model is working with outdated physics, and it will choose bad plans. --- ### `pg_attribute` — The Column Registry The table exists. Fine. But where are its columns? ```sql SELECT attname, atttypid::regtype, attnum FROM pg_attribute WHERE attrelid = 'capybaras'::regclass AND attnum > 0 -- exclude system columns AND NOT attisdropped -- exclude dropped columns ORDER BY attnum; ``` ```text attname | atttypid | attnum ---------+----------+-------- id | integer | 1 name | text | 2 ``` Every column in every table in the database is a row in `pg_attribute`. The `attrelid` foreign key points back to `pg_class.oid`. The `atttypid` foreign key points to `pg_type.oid`. The physical column ordering — `attnum` — is what determines the byte layout inside each tuple. When the engine needs to extract column 2 from a tuple, it reads the type's `typlen` and `typalign` from `pg_type` to calculate the exact byte offset. This is why column ordering affects alignment padding, as we saw in **[[Manuscript/02 - Physical Storage & MVCC/2.1 - Data Types (Knicks, knacks, bits, and bobs)|2.1 Data Types]]**. > [!TIP] > **System columns live here too.** Remember `ctid`, `xmin`, `xmax` from the tuple header? They are rows in `pg_attribute` with negative `attnum` values. Try removing the `attnum > 0` filter and you'll see the engine's own bookkeeping columns appear. --- ### `pg_type` — The Type Registry Why does `text` mean anything? Because every type in Postgres — built-in and user-defined — is a row in `pg_type`: ```sql SELECT typname, typlen, typalign, typcategory FROM pg_type WHERE typname IN ('int4', 'text', 'bool', 'timestamptz'); ``` ```text typname | typlen | typalign | typcategory --------------+--------+----------+------------- bool | 1 | c | B int4 | 4 | i | N text | -1 | i | S timestamptz | 8 | d | D ``` The `typlen` column is the physical footprint: `4` means four bytes, `-1` means variable-length (a `varlena`), `-2` means null-terminated C string. The `typalign` column (`c`, `s`, `i`, `d`) tells the engine what byte boundary the type requires — the same alignment rules we covered in the padding discussion. This is the table the OIDs from **[[Manuscript/02 - Physical Storage & MVCC/2.1 - Data Types (Knicks, knacks, bits, and bobs)|2.1]]** point to. When the engine sees `atttypid = 23` in `pg_attribute`, it looks up OID 23 in `pg_type` and finds `int4` — four bytes, integer alignment. The entire type system is a lookup table. --- ### `pg_namespace` — The District Map Schemas in Postgres are organizational boundaries — they separate objects with the same name into distinct namespaces. Every schema is a row in `pg_namespace`: ```sql SELECT nspname, nspowner::regrole FROM pg_namespace WHERE nspname NOT LIKE 'pg_toast%' AND nspname NOT LIKE 'pg_temp%'; ``` ```text nspname | nspowner ----------------+------------ pg_catalog | postgres public | pg_database_owner information_schema | postgres ``` When you write `SELECT * FROM animals`, Postgres resolves `animals` by searching your `search_path` — a list of schemas to check in order. The default is `"$user", public`, which means Postgres first looks for a schema matching your role name, then falls back to `public`. This is why `public.animals` and `pg_catalog.pg_class` can coexist without collision. They live in different neighborhoods. --- ### `pg_proc` — The Recipe Cabinet Functions and procedures are also rows. Every built-in function (`now()`, `count()`, `pg_relation_size()`) and every function you define lives in `pg_proc`: ```sql SELECT proname, pronargs, prorettype::regtype FROM pg_proc WHERE proname = 'pg_relation_size'; ``` This surprises people. They assume built-in functions are hard-coded C function calls. They are — but the *dispatch table* is relational. When the parser encounters `pg_relation_size('animals')`, it looks up the function by name in `pg_proc`, reads its argument types, return type, and implementation language (`prolangs`), and dispatches accordingly. User-defined SQL and PL/pgSQL functions use the same catalog row — the only difference is the `prolang` column. --- ### The Graph Now step back and see the structure that has emerged: ```mermaid erDiagram pg_namespace ||--o{ pg_class : "oid = relnamespace" pg_class ||--o{ pg_attribute : "oid = attrelid" pg_type ||--o{ pg_attribute : "oid = atttypid" pg_class ||--o{ pg_index : "oid = indrelid" pg_class ||--o| pg_class_TOAST_row : "reltoastrelid = oid" ``` `pg_class_TOAST_row` is a role label, not a separate catalog. It represents another row in `pg_class`, reached through `reltoastrelid`, whose `relkind` is `t`. Postgres did not hide its metadata behind an alien object model. It built a **relational model for itself**. Catalog rows point to one another through OIDs, and SQL clients can traverse those relationships with the same joins they use against application tables. > [!IMPORTANT] > **The Recursive Insight**: Postgres stores table definitions in tables—and those catalog tables describe themselves. `pg_class` contains a row for `pg_class`; `pg_attribute` contains rows for the columns of `pg_attribute`; `pg_type` describes the types used by those descriptions. The model folds back upon itself. PostgreSQL bootstraps the foundational catalogs and uses specialized caches to navigate them, so it does not vanish into an infinite hall of hidden SQL. The recursion lives in the model, not the call stack. Tuples all the way down. --- ### The Punchline When you type `\d animals` in `psql`, you might assume the client is calling a proprietary internal API. It is not. It is running SQL. A simplified version of what `\d` actually executes: ```sql -- 1. Find the table SELECT c.relname, c.relkind, n.nspname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 'animals'; -- 2. Get the columns SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull FROM pg_attribute a WHERE a.attrelid = 'animals'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum; -- 3. Get the indexes SELECT c2.relname AS index_name, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i JOIN pg_class c2 ON c2.oid = i.indexrelid WHERE i.indrelid = 'animals'::regclass; ``` There is no magic. Every `\d`, every `\dt`, every `\df` in `psql` is an ordinary SQL query against `pg_catalog`. The engine does not have a secret API for introspection — it has tables. > [!TIP] > **See the queries yourself.** Run `psql` with the `-E` flag (`psql -E`) and every backslash command will print the SQL it executes before showing the result. This is the single fastest way to learn the catalog schema. --- ### The Remaining Catalogs Two catalogs that we will encounter in later chapters deserve a brief mention: - **`pg_constraint`**: every `PRIMARY KEY`, `FOREIGN KEY`, `UNIQUE`, and `CHECK` constraint is a row. We will use this in **[[Manuscript/03 - Access Paths & Indexing/3.5 - Constraints & Triggers (The Integrity Layer and the Chain Reaction)|Chapter 3.5]]**. - **`pg_authid`**: every role (user, group) in the cluster is a row. We will use this extensively in **[[Manuscript/09 - Identity & Access Control/9.1 - Roles & Privileges (The Name Tags)|Chapter 9]]**. The pattern is always the same: if Postgres knows about something, there is a catalog table where that knowledge lives as a row. --- ### 🧪 Observation Lab: Scavenger Hunting the Catalogs To understand the relational design of Postgres's internal system catalogs, we will write a query that joins `pg_class`, `pg_attribute`, and `pg_type` to find information about the `animals` table—without using `psql` backslash shortcuts or online documentation. #### Join the Catalogs to Reconstruct `animals` Write a query that displays: 1. The table name (`relname`) 2. The column name (`attname`) 3. The column's relative position number (`attnum`) 4. The column's data type (`typname`) Connect your query from `pg_class` to `pg_attribute` (matching `pg_class.oid` to `pg_attribute.attrelid`), and then to `pg_type` (matching `pg_attribute.atttypid` to `pg_type.oid`). ```sql SELECT c.relname AS table_name, a.attname AS column_name, a.attnum AS column_index, t.typname AS data_type FROM pg_class c JOIN pg_attribute a ON c.oid = a.attrelid JOIN pg_type t ON a.atttypid = t.oid WHERE c.relname = 'animals' AND a.attnum > 0 -- exclude system columns AND NOT a.attisdropped -- exclude dropped columns ORDER BY a.attnum; ``` #### A Catalog Join Reconstructs the Table Examine the results returned by the engine: ``` table_name | column_name | column_index | data_type ------------+-------------+--------------+------------- animals | id | 1 | int4 animals | name | 2 | text animals | species_id | 3 | int4 animals | created_at | 4 | timestamptz ``` Notice that Postgres does not store human-friendly names like `integer` or `timestamp with time zone` inside `pg_attribute`; it stores them as `int4` and `timestamptz` inside `pg_type`. The client `psql` uses formatting helper functions like `format_type()` to render those names nicely for humans. #### Metadata Is Just Queryable Data Introspection is not a separate engine subsystem. If you write an ORM, a migration runner, or a monitoring dashboard, you do not need special APIs—you simply query standard catalog tables. Because Postgres handles metadata relationally, the catalog benefits from the same indexing, optimization, and query planning machinery as your application's data. --- ## 2.2 - Tuple (The Suitcase) <img src="assets/arch_tuple_suitcase.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> When you query a database, you deal in the logical realm of tables and rows. You issue a query, and a row appears. But databases do not store rows. They store raw bytes on disk, organized by strict physical boundaries. To understand how Postgres manages these bytes under intense concurrency, we must examine the smallest physical unit of storage in the engine: the **Tuple**. ### The Versioned Update Imagine a busy table where readers are constantly scanning a row while a writer tries to update the name of an animal patron. In most environments, if you edit a file while someone is reading it, they see garbage or get blocked. If Postgres wrote updates directly to disk, how could concurrent readers view the old name without waiting for the writer to finish? > [!IMPORTANT] Predict Where an Updated Row Version Goes > If Postgres updates a row, what does it do to the existing bytes on disk? If it does not overwrite them, where does the new name go, and how does the engine know which version of the row is the right one? Pause and formulate a guess. It is tempting to expect that Postgres locks the row, edits the bytes in place, and writes a temporary rollback segment to satisfy concurrent readers. While this sounds simple, it has a devastating flaw: readers would constantly block writers, and writers would block readers, grinding performance to a halt under high traffic. PostgreSQL does not overwrite the old row payload with the new payload. When you run an `UPDATE`, it packs the new values into a new **Tuple**—a physical **Suitcase** in the heap—and changes metadata on the old tuple to retire and link it. Heap pages can later reuse free space, so this is versioned heap storage rather than an immutable append-only log. Rather than erasing the old tuple: 1. The engine records the updating transaction in the old tuple's header (`xmax`) and updates tuple metadata. 2. It places a new tuple in available heap space, which may be on the same page or another page. 3. It links the old version toward the new version through the tuple's `t_ctid` field; HOT updates can use this chain to avoid new entries in unchanged indexes. Because the old payload remains available, an ordinary MVCC reader can continue to see the version selected by its snapshot while a writer creates another version. Ordinary reads and writes therefore usually avoid blocking one another *for visibility*, although row locks, explicit locking reads, DDL, and other coordination still block when their lock modes conflict. Obsolete tuple versions consume space until pruning or vacuum can reclaim it. > [!NOTE] > **In PostgreSQL Terms** > * **Tuple**: The physical realization of a row. > * **Heap**: The unordered physical storage area where tuples occupy and later reuse page space. > * **MVCC**: The visibility model that allows concurrent reading of old versions. ### Tuple Anatomy Standard heap tuples follow a fixed physical layout. They are divided into two sections: the **Header** (metadata) and the **User Data** (your columns). #### 1. The Header (The Metadata) Standard heap tuples begin with a fixed-size header (**23 bytes**). This header contains the status bits and transaction IDs that allow Postgres to manage concurrency without locking. #### 2. The User Data (The Payload) This is where your actual data lives. It follows the header and is laid out in the order defined in your `CREATE TABLE` statement. To understand how Postgres makes these decisions, we must look at the physical anatomy of a heap tuple: ```text +---------------------------------+ <-- Tuple Start | HEADER (23 bytes) | (xmin, xmax, ctid, flags) +---------------------------------+ | NULL BITMAP (Optional) | (Null Presence map) +---------------------------------+ | [ ALIGNMENT PADDING ] | (Memory Alignment) +---------------------------------+ | | | USER DATA | (Payload) | [Slot 1] [Slot 2] ... | | | +---------------------------------+ <-- Tuple End ``` ### Lab: Inspecting the Physical Reality To see the physical reality of a tuple, we can peek at the "system columns" that Postgres attaches to every row. Let's welcome **Cashew the Capybara** to the cafe: ```sql INSERT INTO animals (name, species_id) VALUES ('Cashew', (SELECT id FROM species WHERE name = 'Capybara')); SELECT ctid, xmin, xmax, name FROM animals WHERE name = 'Cashew'; ``` **STOP.** Let's inspect a real tuple. ```text ctid | xmin | xmax | name ---------+------+------+-------- (73,75) | 850 | 0 | Cashew ``` Look: - **`xmin`** = creator (Transaction 850 created the row) - **`xmax`** = nobody (0 means no transaction has deleted or updated it yet) - **`ctid`** = address (Page 73, Slot 75 is where Cashew lives) That's it. You can now read tuple headers. > [!NOTE] PostgreSQL Stores Tuples, Not Logical Rows > **Concept**: Rows aren't what Postgres stores. Tuples are. > **Payoff**: Rows are what humans see; tuples are what the engine manipulates. That's why MVCC, vacuum, and visibility all live on tuples—not rows. By inspecting these headers, the engine determines precisely where the tuple is located and whether it is "visible" to your current transaction. How the engine performs this visibility math efficiently without checking a central database registry on every single read is one of PostgreSQL's greatest optimizations, which we cover in detail in **[[Manuscript/02 - Physical Storage & MVCC/2.5 - MVCC (The Sharpie Ledger)|Chapter 2.5]]**. This allows Postgres to manage history without erasers, though it eventually requires a "Vacuum" to reclaim space from obsolete versions. --- --- ### The Tuple Lifecycle Because a tuple is the physical realization of a row, its lifecycle is governed by two major architectural disciplines: 1. **[[Manuscript/02 - Physical Storage & MVCC/2.2.1 - Visibility & System Columns|Visibility & System Columns]]**: How the engine uses hidden headers (`xmin`, `xmax`, `ctid`) to decide which transactions can see which version of a row. 2. **[[Manuscript/02 - Physical Storage & MVCC/2.2.2 - Storage Optimizations|Storage Optimizations]]**: how the engine packs these tuples efficiently using Null Bitmaps, Alignment, and specialized techniques like **HOT** and **TOAST**. --- ## 2.2.1 - Visibility & System Columns <img src="assets/arch_visibility_passport.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Every tuple in the **Heap** carries a set of hidden markers that determine if a specific query is allowed to see it. These markers are the engine's primary tool for managing concurrency without locking. In the Elephant Cafe, we call these the **tuple attributes**. They tell the engine where the tuple came from, who owns it, and whether it has been logically retired. ### The System Columns There are three critical "System Columns" embedded in every tuple's header. You cannot see them in a `SELECT *`, but they are always there, guiding the engine's visibility logic. | Field | Size | Role | | :--- | :--- | :--- | | **`xmin`** | 4 bytes | The Transaction ID (XID) that **inserted** this tuple. | | **`xmax`** | 4 bytes | The Transaction ID that **deleted** or updated this tuple. | | **`ctid`** | 6 bytes | The physical location **(Page, Offset)** of this tuple. | To truly understand visibility, you must see it in action. Let's look at the physical identity of Babu the Elephant. Even though he looks like a weightless row in your application, he has a very specific physical coordinate and history. ```sql SELECT ctid, xmin, xmax, name FROM animals WHERE name = 'Babu'; ``` | ctid | xmin | xmax | name | | :---- | :--- | :--- | :--- | | (0,1) | 501 | 0 | Babu | #### Decoding the Attributes - **`ctid (0,1)`**: This tuple lives on **Page 0**, at **Slot 1**. The `ctid` is the physical address the engine uses to jump directly to the data. - **`xmin 501`**: This version of Babu was created by **Transaction 501**. - **`xmax 0`**: The `xmax` is empty. This means no transaction has deleted or updated this tuple yet. It is currently "Alive." ### The Multi-Version Reality Because PostgreSQL keeps multiple heap tuple versions, an `UPDATE` is a two-step physical dance: 1. The engine sets the `xmax` of the old tuple to your current Transaction ID (marking it as dead). 2. The engine writes a brand-new tuple with a new `ctid` and sets its `xmin` to your current Transaction ID. > [!IMPORTANT] > **The Visibility Checkpoint**: Each statement or transaction uses a **snapshot** under the rules of its isolation level. The snapshot stores transaction visibility boundaries and the transactions that were in progress; PostgreSQL combines that information with tuple headers, transaction status, and the current transaction's own commands to decide visibility. `xmax` can also represent locking or multitransaction state, so it is not merely a Boolean “deleted” field. That compact transaction metadata allows one patron to see the old price of Saffron while another patron is updating it. The ordinary read does not need a conflicting row lock merely to preserve its view; PostgreSQL evaluates which tuple version is visible to that snapshot. > [!NOTE] Snapshots Store Transaction Boundaries, Not Pages > **Concept**: A snapshot is not a copy of database pages. > **Payoff**: A snapshot is not a copy of database pages. It is compact transaction metadata: visibility boundaries plus the set of transactions that were in progress when the snapshot was taken. Its size depends on transaction activity, not on the number of rows or total database size. --- ## 2.2.2 - Storage Optimizations <img src="assets/arch_storage_opts.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Once you understand the anatomy of a tuple and its visibility attributes, the question becomes one of efficiency. How does Postgres pack these suitcases as tightly as possible, and what happens when the data is simply too large to fit? ### Efficiency: Nulls and Alignment Postgres is designed to minimize storage overhead within each tuple. To do this, it uses two primary techniques: the **Null Bitmap** and **Memory Alignment**. #### The Null Bitmap (Compact Absence) If a column is NULL, PostgreSQL stores no payload bytes for that attribute. A tuple containing any NULLs includes a bitmap with one bit per column, rounded to whole bytes and followed by alignment as needed. - **NULL payload is cheap, not free**: Ninety NULL attributes avoid ninety values' payloads, but a wide table still pays for tuple metadata, the null bitmap, alignment, and catalog/schema complexity. #### Memory Alignment (The 8-Byte Rhythm) The CPU is most efficient when reading data that starts on a "Natural Boundary" (usually 8 bytes). If a column ends on an odd byte, Postgres adds **Padding Bytes** to ensure the next column starts at the correct interval. - **The Optimization**: By grouping fixed-width columns of the same size together (e.g., placing all `bigint` and `timestamp` columns at the start of the table), you can reduce the amount of padding required, potentially saving megabytes of space on a large table. --- ### Advanced Optimizations For the extreme edges of performance, Postgres employs two specialized techniques to handle updates and large data. #### 1. Heap-Only Tuples (HOT) High-frequency updates can lead to "Index Bloat" as the engine creates new physical versions of rows. To mitigate this, Postgres uses **HOT Updates** to link new versions together on the same page without updating every index. We will explore the mechanics and performance implications of this in **[[Manuscript/06 - Resource Management & Processes/6.5 - Tuple Bloat (Garbage Collection)|Chapter 6.5 - Tuple Bloat (Garbage Collection)]]**. #### 2. TOAST (The Separate Trailer) One physical heap tuple cannot cross a heap-page boundary. But what if one logical row contains a 10MB JSON document or a high-resolution olfactory map? The engine uses **TOAST** (The Oversized-Attribute Storage Technique). When a tuple exceeds its target size, PostgreSQL considers compressing and/or moving eligible variable-length attributes out of line according to each column's storage strategy. The heap tuple then contains a compact external datum reference, and PostgreSQL fetches and reconstructs the value when an operation actually needs it. > [!TIP] > Moving a large attribute out of line lets a scan that does not reference that attribute avoid fetching its TOAST chunks. A query that projects, sorts, filters, hashes, transmits, or otherwise evaluates the value may have to fetch or decompress it. --- ## 2.3 - The Page (The Shipping Container) <img src="assets/arch_page_container.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> If Postgres requested tuples individually from disk, the I/O overhead would be catastrophic. Physical disks excel at moving large contiguous blocks, but they struggle with tiny, random requests. To solve this, the engine organizes storage into fixed-size **Pages** (or Blocks). By default, every page is **8KB**. Think of it as a **Shipping Container**. The engine moves containers, not individual items. Postgres reads and writes these 8KB chunks regardless of whether they hold one record or a thousand. The engine does not fetch individual rows from disk; it fundamentally fetches the 8KB page that contains the row. > [!NOTE] > **In PostgreSQL Terms** > * **Tuple**: The physical row record. > * **Page**: The 8KB physical block that holds multiple tuples. > * **I/O Unit**: Postgres fundamentally reads and writes in discrete 8KB blocks. > [!NOTE] Storage I/O Moves Pages, Not Individual Rows > **Concept**: Postgres never reads a row from disk. It reads the page. > **Payoff**: If you query a single row from a table with a cold cache, Postgres fetches the entire 8KB page containing that row. A subsequent query for another row on that page may avoid storage I/O while the page remains cached, although it still pays CPU, visibility, locking, and executor costs. Memory is quick. “Instant” has submitted no benchmark. > **The Takeaway**: Pages—not rows—are PostgreSQL's fundamental unit of disk and memory transfer. ### Why 8KB? A Build-Time Bargain PostgreSQL's default block size is **8KB**, chosen at compile time as `BLCKSZ`. A server build uses one block size throughout the cluster; changing it requires a custom build and a newly initialized cluster. The size is a durable engineering compromise rather than a universal hardware optimum. Larger blocks can hold more tuples or index entries per page and increase B-tree fan-out, but a miss fetches more unrelated bytes and makes each full-page image larger. Smaller blocks reverse those trade-offs. The operating system and storage device may cache, combine, or split PostgreSQL's requests according to their own page and sector sizes; `8KB` does not promise lockstep alignment with either layer. In short: 8KB is PostgreSQL's default unit of page identity and buffer management. The hardware beneath it remains free to be complicated. ### The Anatomy of Compaction If you delete several rows from the middle of an 8KB page, you create physical "holes" in the page. If Postgres leaves these holes fragmented, the page will quickly run out of usable contiguous space for new, large rows. But if Postgres slides the remaining rows together on every deletion to keep the free space contiguous, the CPU will spend massive cycles copying bytes back and forth in memory. How does Postgres keep its storage compact without wasting CPU cycles shuffling rows on every delete? > [!IMPORTANT] Predict When PostgreSQL Compacts a Page > If you delete a row, does Postgres immediately reclaim the space? If it leaves holes, how does a new, large insert ever fit without causing fragmentation? Pause and formulate a guess. You might expect that Postgres either does instant defragmentation on every delete (wasting CPU) or leaves fragmented holes indefinitely, accepting that pages will grow bloated and sparse. Neither of these models is acceptable for a high-throughput relational engine. Instead, Postgres resolves this by organizing the page into a dual-directional growth structure (think of the page as a **Shipping Container** with a smart packing layout): 1. **Item Identifiers** (pointers) grow forward from the front of the page (`pd_lower`). 2. **Tuples** (raw data) grow backward from the end of the page (`pd_upper`). 3. They grow toward each other, leaving a single, contiguous block of **Free Space** in the middle. When a row version becomes removable, pruning can mark its Item Identifier unused or redirect it as part of a HOT chain. Postgres does **not** slide surrounding tuples together at the moment of `DELETE`. When pruning or insertion needs contiguous room, the page can compact its live tuple bytes in one pass and restore a single gap in the middle. By defragmenting lazily rather than eagerly, Postgres avoids the CPU tax of copying bytes on every write or delete. The indirection of Item Identifiers lets the engine move a tuple *within the same page* without changing its TID, so existing index entries remain valid. A `ctid` is still a physical locator, not a logical identifier: an `UPDATE` normally creates a new TID, and a table rewrite such as `VACUUM FULL` can change many of them. ### The Schematic ```text +---------------------------------+ <-- Page Start (Offset 0) | PageHeaderData (24 bytes) | (Control metadata) +---------------------------------+ <-- pd_lower | Item Identifiers (4 bytes ea)| (Line pointers) | [1] [2] [3] [4] ... | (Grow DOWN ↓) +---------------------------------+ | | | FREE SPACE | (Contiguous space) | | +---------------------------------+ <-- pd_upper | | | TUPLES (Data) | (Physical records) | ... [4] [3] [2] [1] | (Grow UP ↑) | | +---------------------------------+ | Special Space (Optional) | (Index internal pointers) +---------------------------------+ <-- Page End (8,192 bytes) ``` ### The Parts of the Page 1. **The Page Header (`PageHeaderData`):** At the beginning of a standard page is a fixed 24-byte header. It records the page version, a checksum, and the memory offsets (`pd_lower` and `pd_upper`) that define the boundaries of the free space. The header also contains a marker tying the physical page to the database's recovery history. This helps the engine determine precisely which WAL records have already been applied to this specific page. 2. **The Item Identifiers (`ItemIdData`):** These act as an indirection layer. Each 4-byte identifier points to the physical offset of a tuple within the page. As new tuples are added, identifiers grow forward, moving the `pd_lower` pointer deeper into the page. > [!NOTE] > **Why the Indirection?**: This layer allows Postgres to compact a page without changing the tuple's physical TID: the block number and Item Identifier remain the same while the byte offset behind that identifier moves. Indexes keep referencing the TID. The guarantee ends at the page boundary and at table rewrites. 3. **The Tuples:** The actual data records are persisted starting from the end of the page and moving toward the front. As new tuples arrive, they are placed in the next available space, shifting the `pd_upper` pointer backward. 4. **The Special Space:** At the end of the page is an optional area used primarily by **Index Pages** to store metadata like sibling pointers (to the left and right pages in a B-Tree), enabling fast transversal without returning to the index root. 5. **The Free Space:** This is the contiguous gap between `pd_lower` and `pd_upper`. When these two pointers meet, the page has no immediately usable room. An insert can choose another page or extend the relation; pruning, manual vacuum, or autovacuum may later make this page reusable. **The rule of thumb:** The Item Identifiers (pointers) and the Tuples (data) grow toward each other from opposite ends of the page. The page is "full" when they finally meet in the middle. ### Page Checksums To detect silent data corruption — bit-rot in the storage layer, incomplete writes, cosmic rays — Postgres can maintain a checksum on its pages. When checksums are enabled, the engine computes PostgreSQL's page-checksum algorithm (derived from FNV-1a and mixed across parallel sums), stores the result in `pd_checksum`, and verifies it when the page is read. A mismatch raises a data-corruption error rather than silently returning garbage to the user. If the power dies while the OS is in the middle of writing an 8KB page, the result is a **Torn Page**. This occurs because the underlying hardware often writes in smaller sectors (e.g., 512 bytes or 4KB). A crash leaves the page in a physically corrupted state, half-new and half-old. With `full_page_writes` enabled, Postgres protects crash recovery from this with **Full Page Images (FPI)**. The first WAL record that modifies a page after a **[[Manuscript/05 - Durability & Transactions/5.2 - Crash Recovery (The Recovery Parade)|Checkpoint]]** normally includes the page image, allowing recovery to replace a torn page before replaying later changes. WAL compression can reduce the stored image; the logical protection remains page-sized. > [!TIP] > Full-page images can cause significant **Write Amplification**: a tiny first change after a checkpoint can log an almost page-sized image. The rate depends on checkpoint frequency, how many distinct pages are dirtied, `max_wal_size`, workload shape, and WAL-compression settings. Tune the system from measured WAL volume rather than treating `checkpoint_timeout` as a solitary dial. ### Inspecting a Page (`pageinspect`) You can peek at the actual physical layout of a page using the `pageinspect` extension: ```sql CREATE EXTENSION IF NOT EXISTS pageinspect; SELECT * FROM page_header(get_raw_page('animals', 0)); -- lsn | checksum | flags | lower | upper | special | pagesize -- ------------+----------+-------+-------+-------+---------+---------- -- 0/16A5E88 | 0 | 0 | 28 | 8160 | 8192 | 8192 ``` The output exposes the `lsn` (the last WAL record that touched this page), the `pagesize` (typically 8192 bytes), and the `lower`/`upper` pointers that bound the free space. This is the architecture of the page made visible from a SQL session. --- ### 🧪 Observation Lab: Inspecting Raw Page Slots While `page_header()` shows page-level boundaries, you can use `heap_page_items()` to inspect the individual item identifiers and tuple slots inside the page. #### Read the First Three Heap Slots Query the first page of the `animals` table using `heap_page_items()` to see the physical pointers and offsets of the first three tuples: ```sql SELECT lp, lp_off, lp_flags, lp_len, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('animals', 0)) LIMIT 3; ``` #### Pointers Descend While Tuples Climb Examine the returned values from your SQL session: ``` lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_ctid ----+--------+----------+--------+--------+--------+-------- 1 | 8136 | 1 | 56 | 829 | 830 | (0,1) 2 | 8080 | 1 | 56 | 829 | 830 | (0,2) 3 | 8024 | 1 | 56 | 829 | 830 | (0,3) ``` Notice what is happening physically inside the page: 1. **The pointers (`lp`)**: Item 1, 2, and 3. 2. **The physical offsets (`lp_off`)**: The tuple for Item 1 is located at offset 8136. The tuple for Item 2 is at offset 8080. Notice how the offsets are decreasing (`8136 -> 8080 -> 8024`) as new rows grow backward from the end of the page. 3. **The tuple length (`lp_len`)**: Each physical tuple header and data payload occupies 56 bytes. 4. **The visibility headers (`t_xmin`, `t_xmax`)**: The transaction IDs that created and deleted (or updated) the rows, matching the MVCC model. 5. **The tuple identifier (`t_ctid`)**: For these current versions, it points back to their own physical TIDs—page `0`, Item Identifiers `1`, `2`, and `3`. After an update, an old version's header may point toward the newer tuple version instead. #### One 8 KB Page Grows from Both Ends By parsing raw page blocks, you confirm that Postgres does not hide data behind complex storage layers. You are looking directly at the line pointer array growing down and the tuple stack growing up, proving the dual-directional architecture of the 8KB page. --- ## 2.4 - Relation (The Table) <img src="assets/arch_table_depot.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> While the **[[Manuscript/02 - Physical Storage & MVCC/2.3 - The Page (The Shipping Container)|Page]]** is Postgres's physical I/O unit, the **Table** (or Relation) provides the logical organization and schema mapping. ### The Heap: A Study in Disorder In the Elephant Cafe, a table is not an ordered list. It is a **Heap**—a physical file where data is stored with no inherent logical order. When you insert a new animal, Postgres does not try to find "the right spot" alphabetically; it simply looks for the first available gap in its shipping containers and tosses the tuple inside. This "toss it in" approach is why writing to a table is so fast, but it’s also why searching it is so slow. Because tuples are scattered based on arrival time and available space (managed by the **Free Space Map**), the engine can never "guess" where a specific record lives. ### The Logical Blueprint A table is defined by its **Schema**. This is the architectural blueprint that specifies which types of data are permitted in each column. Every tuple that enters the table must strictly adhere to this blueprint, or Postgres will reject it at the gate. This structure is what allows Postgres to be efficient. Because the database knows the exact size and type of every column, it can calculate physical offsets with mathematical precision, jumping directly to the data it needs without having to "read" every byte in between. ```sql -- Defining the schema for the species table CREATE TABLE species ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT UNIQUE NOT NULL, diet_type diet_category NOT NULL ); ``` Once the schema is defined, Postgres allocates the initial data files required to persist incoming 8KB pages onto the disk. ### Physical Segmentation (Segments) A table is physically represented as a series of 8KB pages stored in a file. However, most modern filesystems get grumpy at extreme file sizes. To manage this, Postgres imposes a strict **1GB threshold** for any single physical file. If a table grows beyond 1GB, Postgres simply starts a new **Segment** file directly next door (e.g., `16385`, `16385.1`, `16385.2`). To the end-user, the table is a single logical entity; at the storage layer, it is a multi-file sequence of 1GB segments. You can inspect the literal file path of a relation on disk: ```sql -- Where is the 'animals' relation located on my disk? SELECT pg_relation_filepath('animals'); -- Output: base/13593/16385 ``` ### Table Forks: The Side Rooms A relation is not merely a single list of data pages. For performance and management, Postgres separates different functional requirements into distinct files called **Forks**, or specialized **Side Rooms** in the table structure. The primary data is stored in the **Main Fork**. However, Postgres maintains supplementary structures to optimize access: - **Free Space Map (`_fsm`)**: A quick sketch showing which pages have empty space, allowing Postgres to quickly locate a target for new tuples. - **Visibility Map (`_vm`)**: A bitmask indicating which pages hold ONLY fully alive, completely visible records. This is critical for **Index-Only Scans**; if Postgres knows the whole page is safe, it doesn't even have to read the table data to check visibility rules! > [!TIP] > **Segments vs. Forks**: Segments are continuations of the same file sequence (more space), while Forks are entirely different files serving specific functional requirements (like visibility tracking). ### Access Patterns: The Sequential Scan In the absence of an index, Postgres must perform a **Sequential Scan** (Full Table Scan). It begins reading at the first page of the relation and proceeds linearly through every segment until the predicate is satisfied or the end of the file is reached. For large relations, this results in significant I/O latency and CPU overhead as every page must be loaded into memory and processed. ### Storage Reclamation (The Autovacuum) Because data is abandoned rather than erased, tables naturally accumulate "dead" tuples over time—a state known as **Bloat**. To reclaim this space, the **Autovacuum** background process scans pages to identify dead tuples and mark their space as reusable in the **Free Space Map**. This prevents the physical file size from growing indefinitely. --- ## 2.5 - MVCC (The Sharpie Ledger) <img src="assets/arch_mvcc_sharpie_v3.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> Postgres uses **MVCC** (Multi-Version Concurrency Control). When a record is updated, the engine does not overwrite the old row payload with the new payload. Instead, it modifies the old tuple's metadata and stores a new tuple version in available heap space. Think of it as a **Sharpie Ledger**: an update writes a new line and crosses out an older one; later housekeeping may reclaim crossed-out space. PostgreSQL begins that visibility decision with transaction markers in the tuple header: `xmin` identifies the creating transaction, while `xmax` can record deletion, update, locking, or multitransaction state. By retaining old versions, the engine lets ordinary MVCC reads and writes proceed without blocking one another merely to preserve visibility. Conflicting row locks, explicit locking reads, DDL, and other lock modes still wait as their semantics require. > [!TIP] > **The Key Benefit**: An ordinary reader can keep using a consistent snapshot of an old tuple version while a writer creates and commits a new one. This is a targeted concurrency property, not a promise that all reads and writes are lock-free. > [!NOTE] > **In PostgreSQL Terms** > * **Tuple**: The physical row version. > * **Heap**: The unordered physical table storage. > * **MVCC**: Multi-Version Concurrency Control (the visibility model). --- ### The Mechanics of Versioning An `UPDATE` does not swap one tuple for another in place. It leaves three physical facts behind: 1. **Mark the old version.** Its payload stays put. The updating Transaction ID goes into `xmax`, marking the transaction that may retire it. The transaction's outcome and the observer's snapshot still decide whether it is visible. 2. **Write the successor.** A fresh tuple carries the new payload into an available heap slot, sometimes on another page. Its `xmin` is the updating Transaction ID. 3. **Link the versions.** Every tuple's `ctid` names its own physical address. Inside the old tuple header, `t_ctid` changes from that self-address to the successor's address. A HOT update uses these links as a chain that an index lookup can follow.  **UPDATE creates a successor.** `COMMIT` changes transaction status; it does not return to every heap tuple and crown one copy as *the current row*. That explains storage. Visibility is a separate decision. The same tuple fields can support different answers as the writer commits and different snapshots look through their own windows:  **The heap keeps both; the snapshot chooses one.** The writer sees its own new version. A statement that looks while Transaction 847 is still active sees the old one. Under Read Committed, a later statement begun after the commit can see the successor. A Repeatable Read transaction that took its snapshot before the commit keeps seeing the old version. PostgreSQL evaluates transaction status, snapshot boundaries, and command visibility rules together. Comparing XIDs as ordinary integers is not a safe visibility algorithm: transaction IDs wrap, and `xmax` has several jobs. ### The Select Write Tax Every time you query a tuple, Postgres must check the header's `xmin` and `xmax` to see if the transaction that created or deleted it has committed. The database tracks this in a global status table called the **Commit Log (CLOG)** (stored in `pg_xact`). If Postgres checked this global CLOG table for every single tuple during a table scan, the contention would be massive, destroying query throughput. Yet Postgres reads millions of rows in milliseconds. Even stranger: sometimes, running a read-only `SELECT` query on a cold table forces Postgres to write dirty pages back to disk. Why does reading a database row cause a write to disk? > [!IMPORTANT] Predict Why a Read Can Dirty a Page > How does Postgres avoid looking up transaction status in the global CLOG for every single read? And why would a read-only `SELECT` query ever modify a page on disk? Pause and formulate a guess. You might assume Postgres updates a central, thread-safe memory index of all committed transactions that readers consult, or that it updates the tuple's visibility status on commit. However, changing all modified tuples on `COMMIT` would require the commit command to open and write to every modified page in the database, making commits incredibly slow. Instead, PostgreSQL resolves much of this lazily using **Hint Bits**. Commit records and transaction-status machinery establish the transaction's outcome; `COMMIT` does **not** visit and finalize every tuple it changed. A later backend that reads a tuple can cache the discovered status in that tuple's header: 1. It reads the tuple's `xmin`/`xmax`. 2. If the tuple lacks a usable hint, it resolves the transaction status through PostgreSQL's transaction-status machinery (`pg_xact` and its caches). 3. Once it learns the status, it **stamps** that status directly into the tuple's header flag (`t_infomask` bits) as `COMMITTED` or `ABORTED`. This stamp is a Hint Bit. Subsequent queries reading this tuple see the Hint Bit directly in the header and bypass the CLOG lookup entirely. Because the first scanner stamps the Hint Bit, it modifies the page. This is the Select Write Tax: even a read-only `SELECT` can mark a page as dirty in memory, which eventually forces a write to disk. However, this one-time tax buys O(1) visibility status checks for all future queries, keeping scans extremely fast and locking-free. ### 🧪 Expose the Old Tuple Version — Lab **Prove the Update Left a Ghost**: "Prove that Postgres doesn't update rows in place. Show me the 'Ghost' of the old version." #### Record Glowy's Original Tuple Address First, find a specific animal and note its physical address (`ctid`) and creator (`xmin`). ```sql SELECT ctid, xmin, name FROM animals WHERE name = 'Glowy'; ``` **Result**: ```text ctid | xmin | name ---------+------+------- (73,73) | 846 | Glowy ``` Now, perform an `UPDATE` and check the same columns: ```sql UPDATE animals SET name = 'Glowing Gilly' WHERE name = 'Glowy'; SELECT ctid, xmin, name FROM animals WHERE name = 'Glowing Gilly'; ``` **Result**: ```text ctid | xmin | name ---------+------+--------------- (73,74) | 847 | Glowing Gilly ``` #### The Updated Row Moves to a New Slot The `ctid` moved from `(73,73)` to `(73,74)`. The engine didn't change the data at the old address; it wrote a **brand new tuple** at a new address. #### The Old Tuple Remains as Evidence To see the "Ghost" left behind, we can peek at the hidden `xmax` of the old record (requires a specific query to bypass the normal visibility filters): ```sql -- Peeking at the expired version SELECT ctid, xmin, xmax, name FROM animals WHERE ctid = '(73,73)'; ``` **Result**: ```text ctid | xmin | xmax | name ---------+------+------+------- (73,73) | 846 | 847 | Glowy ``` The old version still exists on disk! Its `xmax` is now set to **847** (the ID of your update transaction). To your transaction, this row is a "Ghost"—it occupies physical space but is logically dead. > [!NOTE] > **Recap**: An ordinary heap `UPDATE` creates a new tuple version and retires the old version through header metadata. The obsolete version remains until page pruning or **Vacuum** can reclaim the space. --- ### The Ghost of a ROLLBACK PostgreSQL creates the tuple version before the transaction commits, and the dirty heap page may reach storage independently of that transaction's eventual outcome. If the transaction rolls back, its tuple version can remain physically present in the heap even though no later transaction treats it as committed. | ctid | xmin | xmax | id | animal_id | status | Visibility | | :--- | :--- | :--- | :--- | :--- | :--- | :--- | | **(0,3)** | **102** | 0 | 2 | 8 | Salad | **Invisible (XID 102 aborted)** | No future transaction will treat this tuple version as committed: PostgreSQL consults transaction status in `pg_xact` (often avoiding repeated lookups through tuple hint bits), and XID 102 is aborted. The bytes can still occupy physical space in the heap page until cleanup. Accumulations of dead tuple versions contribute to **bloat**. > [!NOTE] The Visibility Map has a different job > The Visibility Map stores two conservative page-level facts: whether every tuple on a heap page is visible to all transactions, and whether every tuple is frozen. It does not record whether an individual transaction committed or aborted. This design prioritizes write speed today by deferring the cost of cleanup. However, it leaves a performance debt that must be settled later. We cover the process of reclaiming this space in detail in **[[Manuscript/06 - Resource Management & Processes/6.4 - Vacuum & Freezing (The Housekeepers)|Chapter 6]]**. --- --- ## 2.6 - TOAST (The Separate Trailer) <img src="assets/toast_dinosaur.png" width="250" style="float: left; margin: 0 20px 20px 0;" /> PostgreSQL heap relations normally use 8KB pages in standard builds, and one physical heap tuple cannot span heap pages. Operating systems and storage devices may transfer or cache different-sized units; 8KB is PostgreSQL's default database-block size, not a universal hardware transfer unit. ### The Overflowing Page If the physical tuple must fit inside one page, a logical row containing a large JSON document, image, or text field needs another representation. How does PostgreSQL fit a 10MB peg into an 8KB hole? > [!IMPORTANT] Predict Where an Oversized Attribute Goes > If a heap tuple cannot span pages, where does most of a 10MB attribute go in a standard 8KB-block build, and how does the heap tuple refer to it? Pause and formulate a guess. You might expect that Postgres simply chains pages together for that row, turning the table heap into a slow, linked-list structure. But if the engine did this, a query scanning the table would have to traverse this linked list for every row, destroying the performance of sequential scans for other, normal-sized rows. Instead, Postgres resolves this with a technique called **TOAST** (*The Oversized-Attribute Storage Technique*). Think of it as parking a **Separate Trailer** behind the table to hold oversized cargo. When a tuple exceeds its TOAST target (roughly 2KB under common defaults), PostgreSQL considers eligible variable-length attributes according to their per-column strategy: 1. **Compression**: For strategies that permit it, PostgreSQL can compress an attribute using the column's selected compression method (`pglz` or `lz4` when the build supports it). A sufficiently reduced value can remain inline. 2. **External Storage**: If the tuple still needs shrinking, the engine can split an attribute into chunks sized to keep several chunk rows per page. 3. **The TOAST Table**: It writes these chunks into a hidden, dedicated secondary table (the TOAST table) associated with the main table. 4. **The Claim Check**: It replaces the inline datum with a compact external-reference structure (commonly described as an 18-byte on-disk TOAST pointer) that identifies the stored value and its TOAST relation. When you select the row, Postgres transparently reads the claim check, pulls the chunks from the TOAST table, reassembles them in memory, and hands you the complete 10MB value. Because oversized columns are moved out-of-line, the main table heap remains extremely compact. A `SELECT count(*)` or a scan that does not request the large field can fly through pages at maximum speed, completely ignoring the TOAST tables. However, the trade-off is the **`SELECT *` Penalty**: requesting the oversized fields forces additional random I/O and memory allocations to stitch the chunks back together. ### The Four Storage Strategies Each column declares a TOAST strategy that controls how the engine handles oversized values: | Strategy | Behavior | | :----------- | :------------------------------------------------------------------------------------------------ | | **PLAIN** | Prevents compression and out-of-line storage for that attribute. The physical tuple must still fit on a page. | | **EXTENDED** | Attempts compression first; falls back to external storage if still too large. **Default** for variable-length types. | | **EXTERNAL** | Skips compression entirely and moves the value to external storage. Useful when the data is already compressed (e.g. JPEG, gzipped JSON). | | **MAIN** | Attempts compression but keeps the value inline as long as it possibly fits. | You can inspect (or override) the strategy per column with `ALTER TABLE ... SET STORAGE`. ### 🧪 Follow a Large Value into TOAST — Lab **Prove the Value Moved Out of Line**: "Store a massive amount of text in a single column. Prove that Postgres moves it to external storage when it gets too large." #### Measure Inline and Compressible Values First, let's create a scratch table for our test: ```sql CREATE TABLE toast_test ( biography TEXT ); ``` We can use `pg_column_size()` to see the physical size of a value as it is stored in the tuple. ```sql -- 1. A small name fits comfortably inline SELECT pg_column_size('Cashew'::text) AS size; -- Result: 7 bytes ``` Now, let's create a massive biography. By default, Postgres uses the **EXTENDED** strategy, which attempts to compress the data first. ```sql -- 2. Store a highly compressible 5,000-character biography INSERT INTO toast_test (biography) VALUES (repeat('A', 5000)); SELECT pg_column_size(biography) AS stored_value_size FROM toast_test; -- Result: 69 bytes ``` #### Compression Keeps 5,000 Characters Tiny Why is the stored 5,000-character string only about 69 bytes in this PostgreSQL 18 lab? The repeated input compresses extremely well under the table's default `EXTENDED` strategy. Exact size and selected compression method depend on the server and column configuration. To force the data into **external storage**, we must either exceed the compression limit or disable compression entirely. #### Disable Compression to Force External Storage Disable compression for the column to force external storage: ```sql ALTER TABLE toast_test ALTER COLUMN biography SET STORAGE EXTERNAL; TRUNCATE toast_test; INSERT INTO toast_test (biography) VALUES (repeat('B', 5000)); -- Check the size now SELECT pg_column_size(biography) AS size FROM toast_test; ``` **Result**: ```text size ------- 5000 ``` #### External Chunks Confirm the Move `pg_column_size(biography)` reports the stored external value's uncompressed datum size when PostgreSQL fetches it; it does not display the heap tuple's pointer. Inspect the owning table's TOAST relation to prove that external chunks exist: ```sql SELECT c.reltoastrelid::regclass AS toast_relation, pg_relation_size(c.reltoastrelid) AS toast_heap_bytes FROM pg_class AS c WHERE c.oid = 'toast_test'::regclass; ``` In the clean PostgreSQL 18 lab, `toast_heap_bytes` becomes nonzero (typically one 8KB page after this insert). Relation names and byte counts can vary. > [!WARNING] > **The `SELECT *` Penalty**: Returning an out-of-line attribute can require additional reads, decompression, memory, and network transfer. Avoid projecting large text or JSONB columns when the caller does not need them. --- ## 2.7 - Summary: The Row Has a Body ### Chapter 2 Capstone: Forensic Row Investigation Four tuple headers have survived a decidedly impolite crash test. Your snapshot was taken after transactions `1050` and `1100` committed, while `1250` was still in progress. Transaction `1150` aborted. For this exercise, assume the rows are independent tuple versions; do not infer a version chain from their item pointers alone. #### Transaction Evidence | XID | Status at the snapshot | | :--- | :--- | | `1050` | Committed before the snapshot | | `1100` | Committed before the snapshot | | `1150` | Aborted | | `1250` | In progress | #### Raw Tuple Headers | Tuple ID | `t_ctid` (Logical Pointer) | `t_xmin` (Creator XID) | `t_xmax` (Deleter/Updater XID) | | :--- | :--- | :--- | :--- | | **Row A** | `(0, 1)` | `1050` | `1100` | | **Row B** | `(0, 3)` | `1100` | `1150` | | **Row C** | `(0, 3)` | `1150` | `0` | | **Row D** | `(0, 4)` | `1100` | `1250` | --- #### Your Forensic Report Complete the report without reading ahead: | Tuple | Visible to this snapshot? | Creator evidence | Deleter evidence | | :--- | :--- | :--- | :--- | | Row A | | | | | Row B | | | | | Row C | | | | | Row D | | | | Then answer three harder questions: 1. Which tuples are already logically obsolete because of committed or aborted transaction status? 2. Can this one snapshot prove that vacuum may reclaim Row A immediately? What global evidence is missing? 3. Does Row D's `t_xmax` prove that its deletion will commit? > [!IMPORTANT] File the Report > Classify all four tuples and name the fact you still need before authorizing cleanup. <div style="page-break-after: always;"></div> ### Forensic Debrief: Visibility Is Not Reclamation | Tuple | Visible? | Why | | :--- | :--- | :--- | | **Row A** | **No** | Its creator committed before the snapshot, but its deleting/updating transaction also committed before the snapshot. | | **Row B** | **Yes** | Its creator committed. The attempted deletion belongs to an aborted transaction, so that deletion has no logical effect. | | **Row C** | **No** | Its creator aborted, so the inserted version never became visible to other transactions. | | **Row D** | **Yes** | Its creator committed, while its deleting/updating transaction is still in progress and therefore has not made the version invisible to this snapshot. | Row A is logically superseded or deleted, and Row C is the remnant of an aborted insertion. But this snapshot alone does not authorize physical reclamation. Vacuum must respect the cluster-wide cleanup horizon, including snapshots, replication slots, prepared transactions, and other consumers that may still need older versions. “Invisible to me” is not the same claim as “safe to remove for everyone.” Row D's nonzero `t_xmax` records an attempted delete, update, or lock-related use of that field; transaction status and tuple flags supply the meaning. Because `1250` is still running, the attempt may commit or abort. The header records evidence, not prophecy. --- ### 📝 Summary: The Row Has a Body At the start of this chapter, a row looked like a clean application-level object. Now you can see the physical machinery underneath it: tuples packed into pages, transaction headers marking birth and death, old versions left behind so readers and writers do not block each other. That changes how you interpret everyday operations. An UPDATE is no longer a simple edit. It is the creation of a new physical version. A DELETE is not immediate erasure. It is a visibility change followed by future cleanup. Bloat is not mysterious waste. It is history waiting to be proven irrelevant. The practical achievement is this: Given tuple metadata like `ctid`, `xmin`, and `xmax`, you can now reason about where a row lives, which version is visible, and why vacuum eventually has work to do. > [!NOTE] Rows Tell the Story; Tuples Leave the Evidence > **Concept**: A row is the story the application sees. A tuple is the evidence Postgres leaves behind. <div style="page-break-after: always;"></div>