02 - Physical Storage & MVCC

# 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 | By grouping your heaviest types together at the start of your table, you can shrink your storage requirements by **25%** without losing a single bit of information. --- ### 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. #### The Setup 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); ``` #### The Task 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. Over millions of rows, reordering columns from largest to smallest is a free optimization that reduces table bloat and improves scan times. ```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 build a separate metadata subsystem. It built a **relational model for itself**. The catalog tables reference each other through OID foreign keys, exactly as your application tables reference each other through integer foreign keys. The engine queries itself using the same planner, the same executor, and the same index infrastructure that it uses to answer your queries. > [!IMPORTANT] > **The Recursive Insight**: When Postgres needs to plan a query against `animals`, it first queries `pg_class` to find the table, `pg_attribute` to find the columns, and `pg_statistic` to read the distribution statistics. Those catalog queries are themselves planned and executed by the same engine. It is turtles — or rather, 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. #### The Task 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. Any subsequent queries for other rows on that same page will be instant cache hits in memory. > **The Takeaway**: Pages—not rows—are PostgreSQL's fundamental unit of disk and memory transfer. ### Why 8KB? The Goldilocks Compromise Postgres chooses 8KB as the optimal balance for modern hardware: 1. **Hardware Alignment**: Most SSDs use 4KB physical sectors. An 8KB page fits cleanly into two sectors, avoiding slow and risky partial-sector writes. 2. **OS Harmony**: Linux manages memory in 4KB pages; an 8KB rhythm keeps the buffer pool and storage stack in lockstep. 3. **B-Tree Efficiency**: 8KB is large enough for high "fan-out," keeping search trees shallow—often just three or four hops to find a row in a billion-row table. In short: most trips to the disk are sized to align with the hardware's optimal I/O unit. ### 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 rows are deleted, the engine simply marks their Item Identifiers as "dead" and does **not** shift the surrounding tuples in memory. The page remains fragmented. Only when a new tuple arrives that is *larger* than the current free space—but would fit if the dead space were reclaimed—does Postgres execute an on-page defragmentation. It copies the active tuples to the very end of the page, sliding them together in a single, lazy sweep to restore a single contiguous gap in the middle. By defragmenting lazily rather than eagerly, Postgres avoids the CPU tax of memory copying on every single write or delete. The indirection of Item Identifiers allows the engine to shuffle tuples around on the page without changing their external logical IDs (`ctid`), keeping index references perfectly stable. ### 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 of indirection is vital for performance. It allows Postgres to "defragment" the page (moving tuples around to reclaim space) without changing the tuple's logical ID (the Item Identifier index). The outside world (indexes and table scans) continues to reference the Item ID, oblivious to the tuple's physical migration within the page. 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 is considered full. No further tuples can be inserted until the **Autovacuum** worker reclaims space by removing tuples marked as dead. **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 a page is written to disk, the engine computes a CRC over its contents and stores it in `pd_checksum`. When the page is read back, the engine recomputes the checksum and compares. 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. Postgres prevents this with **Full Page Writes (FPW)**. The first time a page is modified after a **[[Manuscript/05 - Durability & Transactions/5.2 - Crash Recovery (The Recovery Parade)|Checkpoint]]**, Postgres copies the *entire 8KB block* into the WAL. > [!TIP] > While FPW prevents corruption, it causes significant **Write Amplification**. A 1-byte change to a tuple can trigger an 8KB write to the WAL. Tuning the `checkpoint_timeout` is the primary way to manage this overhead. ### 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. #### The Task 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 logical pointer (`t_ctid`)**: Pointing to page `0`, index `1`, `2`, and `3` respectively. #### 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 When you execute an `UPDATE`, Postgres performs a three-step logical swap: 1. **Expire the current version**: The physical **Tuple** remains in place, but its header is modified. Postgres stamps `xmax` with the current Transaction ID. This signals that future transactions should treat the row as deleted. 2. **Write the new version**: A fresh tuple is placed in available heap space. Its `xmin` is set to the current Transaction ID. 3. **Physical Linkage**: The `ctid` (physical address) of the old tuple is updated to point to the new one. This creates a version chain that the engine can walk to find the latest record. ```mermaid graph LR Tuple1["Tuple 1 (Old version) ctid: (73,73) xmin: 846 xmax: 847"] -->|t_ctid pointer| Tuple2["Tuple 2 (New version) ctid: (73,74) xmin: 847 xmax: 0"] ``` ```mermaid sequenceDiagram participant W as Transaction 847 (Writer) participant DB as Postgres Heap participant R as Transaction 848 (Reader) Note over DB: "[Tuple 1] xmin: 800, xmax: 0" W->>DB: UPDATE animals SET name = 'Gilly' Note over DB: "[Tuple 1] xmax: 847 (Marked)" Note over DB: "[Tuple 2] xmin: 847 (New)" R->>DB: SELECT * FROM animals Note right of R: Snapshot: [Active: 847] DB-->>R: Returns Tuple 1 Note right of R: Visibility: xmax (847) is in Active Set So Tuple 1 is still visible. W->>DB: COMMIT Note over R: Snapshot persists. Further reads still see Tuple 1. ``` Concurrent transactions see whichever row version satisfies their isolation-level snapshot and command visibility rules. PostgreSQL evaluates transaction status and snapshot boundaries; comparing XIDs as ordinary integers is not a safe visibility algorithm because transaction IDs wrap and `xmax` has several meanings. ### 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." #### The Investigation 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 Diagnosis 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." #### The Investigation 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 ``` #### The Diagnosis 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. #### The Lazy Fix 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 Below are four rows extracted from a corrupted database page during a post-mortem incident review. As the Database Investigator, you are given the current transaction context and a snapshot of the transaction log (CLOG) states. You must determine: 1. Which row versions are visible to your current transaction. 2. Which row versions are logically dead (safe for autovacuum cleanup). 3. Which row version represents the active, current state of the record. #### The Context - **Your Current Transaction ID (XID)**: `1200` - **CLOG status**: - Transaction `1050`: Committed - Transaction `1100`: Committed - Transaction `1150`: Aborted - Transaction `1250`: In-Progress (running concurrently) #### The Evidence (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` | --- #### The Investigation Use your understanding of MVCC visibility rules to answer the questions before reading the solutions. ##### 1. Is Row A visible to your transaction (XID 1200)? * **The Physical Reality**: **No**. Row A was created by `1050` (committed, so it became visible) but was later deleted/updated by `1100` (also committed). Because the deleting transaction `1100` is older than your active XID (`1200`), the deletion is fully visible to you. Row A is a dead tuple and ready for Autovacuum cleanup. ##### 2. Is Row B visible to your transaction (XID 1200)? * **The Physical Reality**: **Yes**. Row B was created by `1100` (committed, visible to you) and deleted by `1150`. However, transaction `1150` aborted. Since the deletion was aborted, the deletion never happened logically. Row B remains fully active and visible to your transaction. ##### 3. Is Row C visible to your transaction (XID 1200)? * **The Physical Reality**: **No**. Row C was created by transaction `1150`. Since `1150` aborted, any rows it attempted to insert are invisible to all other transactions. Row C is dead. ##### 4. Is Row D visible to your transaction (XID 1200)? * **The Physical Reality**: **Yes**. Row D was created by `1100` (committed, visible). It has a deleter `t_xmax` of `1250`. However, transaction `1250` is still in-progress (active). Under the standard *Read Committed* isolation level, you cannot see modifications from uncommitted transactions. Therefore, the deletion is not yet visible to you, and the row remains visible. #### The Forensic Verdict - **Visible Rows**: Row B and Row D. - **Dead Rows (Vacuum candidates)**: Row A and Row C. - **Active Current State**: Row B. (Row D is also active but currently locked for modification by transaction 1250). --- ### 📝 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. ### Sources & Further Reading - [PostgreSQL 18: Database Page Layout](https://www.postgresql.org/docs/18/storage-page-layout.html) - [PostgreSQL 18: Transaction Isolation](https://www.postgresql.org/docs/18/transaction-iso.html) - [PostgreSQL 18: Visibility Map](https://www.postgresql.org/docs/18/storage-vm.html) - [PostgreSQL 18: `pageinspect`](https://www.postgresql.org/docs/18/pageinspect.html) - Source trail: `src/include/access/htup_details.h`, `src/backend/access/heap/`, and `src/backend/storage/ipc/procarray.c`. <div style="page-break-after: always;"></div>