# 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 the engine uses an append-only architecture instead of in-place updates - How **MVCC** enables concurrent reads without locking - What **TOAST** does when data exceeds the 8KB page limit Because writing and reading from persistent storage is one of the most computationally expensive operations a database performs, Postgres avoids unnecessary disk I/O at all costs. To achieve this, it relies on a strict physical hierarchy, organizing its data into standardized 8KB blocks known as **Pages**. ### 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] The Click > **Concept**: A row is logical; a tuple is physical. > **Payoff**: A row is the version-less abstraction that humans see. A tuple is a physical byte payload on disk wrapped in transaction headers. Because Postgres never edits data in-place, a single logical row is often represented by multiple physical tuples (old, current, and deleted versions) scattered across different disk pages. ### The Append-Only Engine Postgres uses an **Append-Only** architecture for primary storage. In the physical world, Postgres never updates data in place. When you run an update, the engine marks the old tuple as "dead" (obsolete) and writes a brand new version to a fresh location in the **Heap** (the unordered physical file where table data lives). This Multi-Version Concurrency Control (MVCC) ensures that readers never block writers, and writers never block readers, avoiding the heavy coordination cost of locking. ### 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)); ``` #### The Observation 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**. #### The Payoff 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_class ||--o{ pg_attribute : "has columns" pg_attribute }o--|| pg_type : "has type" pg_class }o--|| pg_namespace : "lives in schema" pg_class ||--o{ pg_index : "has indexes" pg_class }o--|| pg_class : "TOAST table" ``` 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; ``` #### The Observation 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. #### The Payoff 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 Immutable 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] Prediction Checkpoint: The In-Place Edit > 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. Instead, Postgres operates as a strictly **Append-Only** engine. It does not update bytes in place. When you run an `UPDATE`, the engine packs your new data into a brand new **Tuple** (think of it as a physical **Suitcase** packed into the Heap). Rather than erasing the old tuple: 1. The engine stamps the old tuple's header flag (`xmax`) to mark it as obsolete. 2. It appends a completely new tuple to a fresh location in the **Heap** (the physical table file). 3. It creates a physical chain (`ctid` pointer) from the old tuple to the new one. Because the old bytes are left completely untouched on disk, active readers can continue scanning the old version of the row without holding any locks. Readers never block writers, and writers never block readers. However, this non-blocking performance comes at a price: the old, dead tuples remain in the page as "bloat" until they are garbage-collected. > [!NOTE] > **In PostgreSQL Terms** > * **Tuple**: The physical realization of a row. > * **Heap**: The unordered physical storage area where tuples are appended. > * **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 at microsecond scale, we must look at the physical anatomy of the bytes on the wire: ```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] The Click > **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 Postgres uses **Append-Only** storage, an `UPDATE` is actually 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**: Every query you run carries a **Snapshot**—a list of which transactions are finished and which are still in progress. Postgres compares your snapshot to the `xmin` and `xmax` of every tuple. If a tuple's `xmin` is from a finished transaction and its `xmax` is empty (or from a transaction that hasn't finished), you see it. Otherwise, it is invisible to you. This simple integer comparison is what allows one patron to see the "old" price of Saffron while another patron is in the middle of updating it. No locks are required; the engine simply looks at the tuple attributes and decides what's real for you. > [!NOTE] The Click > **Concept**: A snapshot is not a copy of database state. It is just three numbers. > **Payoff**: Because a snapshot is just boundaries (the oldest active XID, the next XID, and active XIDs in between), snapshot creation takes microseconds and occupies almost zero memory, regardless of how large the database is. --- ## 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 (Free Space) If a column is NULL, Postgres doesn't waste any space in the User Data section of the tuple. Instead, it flips a single bit in a specialized map in the header called the **Null Bitmap**. - **The Payoff**: NULLs are virtually free. A table with 100 columns, 90 of which are NULL, takes up almost the same space as a table with only 10 columns. #### 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) Postgres has a hard limit: a tuple cannot exceed the size of a single **8KB Page**. But what if you need to store a 10MB JSON document or a high-resolution olfactory map? The engine uses **TOAST** (The Oversized-Attribute Storage Technique). If a value is too large (>2KB), Postgres "TOASTs" it: 1. The large value is compressed and moved to a separate, hidden "TOAST table." 2. The original tuple is left with an **18-byte "Claim Check"** (a pointer) in its place. 3. When you query the row, the engine automatically fetches the data from the trailer and presents it to you as if it were right there in the suitcase. > [!TIP] > This is why Postgres can handle massive blobs of data without slowing down sequential scans. The engine only "unpacks" the large TOAST data if you explicitly ask for that specific column. --- ## 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] The Click > **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] Prediction Checkpoint: The Lazily Compacted 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; ``` #### The Observation 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. #### The Payoff 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 utilizes **MVCC** (Multi-Version Concurrency Control). When a record is updated, the engine does not overwrite existing data. Instead, it marks the original version as obsolete and appends a new version to the heap. Think of it as a **Sharpie Ledger**: changes are never erased, only crossed out. Postgres enforces this using two transaction-ID markers in the tuple header: `xmin` (the creator) and `xmax` (the eraser). By appending new versions instead of updating in-place, the engine sidesteps the need for heavy table-level locks. This ensures that readers never block writers, and writers never block readers. > [!TIP] > **The Key Benefit**: In Postgres, **readers never block writers, and writers never block readers.** A reader holds onto its consistent snapshot of the old version while a writer is busy committing a new one. > [!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 appended to the next available space in the heap. 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)<br>ctid: (73,73)<br>xmin: 846<br>xmax: 847"] -->|t_ctid pointer| Tuple2["Tuple 2 (New version)<br>ctid: (73,74)<br>xmin: 847<br>xmax: 0"] ``` ```mermaid sequenceDiagram participant W as Transaction 847<br/>(Writer) participant DB as Postgres Heap participant R as Transaction 848<br/>(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<br/>So Tuple 1 is still visible. W->>DB: COMMIT Note over R: Snapshot persists.<br/>Further reads still see Tuple 1. ``` Concurrent transactions see whichever version of the row matches their own visibility snapshot. They skip anything with an `xmin` greater than their own ID or an `xmax` that has already committed. ### 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] Prediction Checkpoint: The Read-Only Write > 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, Postgres resolves this lazily using **Hint Bits**. When a transaction commits, it only writes a single status bit to the CLOG to declare itself committed. It does **not** touch the tuples it updated. Instead, the first query that comes along and scans the page does the heavy lifting: 1. It reads the tuple's `xmin`/`xmax`. 2. It looks up the status of that transaction in the CLOG (a slow, thread-safe memory check). 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. ### 🧪 Lab Challenge: The Ghost in the Machine (Update Mechanics) **The Request**: "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 Reward 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**: `UPDATE` = `INSERT` + `DELETE`. Every update creates a new physical record. The old one remains as "Bloat" until the **Autovacuum** reclaims the space. --- ### The Ghost of a ROLLBACK Because Postgres is append-only, it writes the physical tuple to disk *before* the transaction officially commits. If a transaction creates a new row and then encounters a `ROLLBACK`, that physical data still lives in the heap. | ctid | xmin | xmax | id | animal_id | status | Visibility | | :--- | :--- | :--- | :--- | :--- | :--- | :--- | | **(0,3)** | **102** | 0 | 2 | 8 | Salad | **Invisible (XID 102 aborted)** | No future transaction will ever see this record—the engine's visibility map knows that XID 102 aborted—but the bytes still occupy physical space in the 8KB page. These dead records are known as **Bloat**. 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;" /> Postgres is incredibly stubborn about its 8KB page limit. In fact, a database page is the absolute unit of disk transfer, and Postgres does not allow a single tuple to span across page boundaries. ### The Overflowing Page If a tuple must fit inside an 8KB page, then a table should reject any row that is larger than 8KB. Yet you can easily insert a 10MB JSON blob, a high-resolution image, or a massive text field into a single row. The database accepts the write instantly without raising an error. How does Postgres fit a 10MB peg into an 8KB hole? > [!IMPORTANT] Prediction Checkpoint: The Overflowing Row > If a row cannot span pages, and a page is strictly 8KB, where does the remaining 9.99MB of a 10MB row go, and how does the table point 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 row exceeds a threshold of roughly **2KB**, Postgres intervenes: 1. **Compression**: It first attempts to compress the large columns using a fast algorithm (LZ4 or PGLZ). If the compressed data fits within the page, it stays inline. 2. **External Storage**: If the data is still too large, the engine chops the value into a series of smaller chunks (usually 2KB each). 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 large data in the main tuple with a tiny **18-byte pointer** (a "claim check") referencing the TOAST table OID and the chunk identifiers. 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** | TOAST disabled entirely. If the row doesn't fit in 8KB, Postgres throws an error. | | **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`. ### 🧪 Lab Challenge: TOASTing a Large Record **The Request**: "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. A 5,000 character biography is highly compressible SELECT pg_column_size(repeat('A', 5000)) AS size; -- Result: 69 bytes ``` #### The Diagnosis Wait—why is the 5,000-character string only 69 bytes? Postgres used its default compression (PGLZ). Because the compressed result (69 bytes) fits easily in the 8KB page, it stays **Inline**. 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; 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 ``` #### The Reward At 5,000 uncompressed bytes, this attribute has outgrown the ~2KB threshold and is moved to a dedicated TOAST table in the `pg_toast` schema, leaving only an 18-byte pointer in the main table. > [!WARNING] > **The `SELECT *` Penalty**: Out-of-line TOAST values must be reassembled. A `SELECT *` on a table with large text or JSONB fields forces additional reads and allocations even if you never use the values. --- ## 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] The Click > **Concept**: A row is the story the application sees. A tuple is the evidence Postgres leaves behind. <div style="page-break-after: always;"></div>