Skip to content

Storage Engine Architecture: PostgreSQL Heap vs. MariaDB InnoDB Clustered Tables

Created: 2026-11-08  ·  Last updated: 2026-11-08  ·  Nejat Hakan

Executive Summary: The ultimate determinant of database performance under heavy read and write pressure is its physical storage engine architecture. PostgreSQL and MariaDB (InnoDB) make diametrically opposed design decisions: PostgreSQL organizes data as an unordered Heap File of 8 KiB pages where all indexes are secondary pointer maps (ctid), whereas InnoDB organizes data as a Clustered Index (Index-Organized Table) of 16 KiB pages where row data is embedded directly inside the primary key B-Tree leaf nodes. This guide dissects page anatomy, double-buffering, tuple headers, and low-level page inspection forensics.

  • PostgreSQL 8 KiB Heap Anatomy

    Inspect the internal page header, line pointer array (ItemIdData), free space gap, and 23-byte tuple headers (xmin, xmax, ctid).

  • InnoDB 16 KiB Clustered Index

    Analyze Index-Organized Tables: why primary key lookups skip heap hops and how secondary indexes perform double B-Tree lookups.

  • Heap Append vs. Undo Logs

    Understand MVCC write amplification: PostgreSQL's append-only new tuple versions vs. InnoDB's in-place updates with historical Undo logs.

  • Low-Level Forensic Lab

    Examine raw physical on-disk binary pages using the PostgreSQL pageinspect extension without taking the database offline.

1. Physical Memory & Storage Hierarchy

When the database engine reads or writes data, it interacts with the Linux kernel virtual memory subsystem through two distinct caching layers:

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    APP["Client Application Query"]
    DB_POOL["1. Engine Buffer Pool<br/>(PostgreSQL shared_buffers /<br/>MariaDB innodb_buffer_pool)"]
    OS_CACHE["2. Linux Kernel Page Cache<br/>(Buffered File I/O in Host RAM)"]
    NVME["3. Physical NVMe / SSD Storage<br/>(8 KiB / 16 KiB Blocks on Disk)"]

    APP --> DB_POOL
    DB_POOL <-->|"Buffer Cache Miss / Dirty Flush"| OS_CACHE
    OS_CACHE <-->|"read() / write() / fsync()"| NVME
  • Double-Buffering Phenomenon: In PostgreSQL, data pages read from NVMe reside both in the Linux kernel page cache and inside shared_buffers. For high-throughput servers, total available RAM is typically divided: 25% allocated to shared_buffers and 75% left for the Linux page cache and connection working memory (work_mem).

2. PostgreSQL 8 KiB Page Anatomy & Heap Storage

A PostgreSQL table is a Heap: an append-only collection of fixed 8 KiB blocks (BLCKSZ = 8192). Tuples have no intrinsic ordering on disk; rows are inserted into whichever block possesses adequate free space.

+-----------------------------------------------------------------------+
| PageHeaderData (24 bytes): LSN, checksum, pd_lower, pd_upper, flags   |
+-----------------------------------------------------------------------+
| Line Pointer Array: ItemIdData [1] [2] [3] ... (4 bytes each) ------> |
| (Grows downwards toward page bottom)                                  |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
|                        FREE SPACE GAP                                 |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| <---------------- Tuple 3 Data (HeapTupleHeader + User Payload)       |
| <---------------- Tuple 2 Data                                        |
| <---------------- Tuple 1 Data (Grows upwards from page bottom)       |
+-----------------------------------------------------------------------+

The Tuple Identifier (ctid)

Every row in a PostgreSQL heap is addressed by a physical coordinate called ctid, formatted as (block_number, line_pointer_index). - For example, (0, 3) means: Block 0, Line Pointer 3. - When an index finds a matching key, it yields a ctid. The engine then jumps to Block 0 and reads Line Pointer 3 to locate the physical tuple bytes.

The 23-Byte Heap Tuple Header

Every row stored on disk carries a mandatory 23-byte metadata header: - t_xmin (4 bytes): The transaction ID that inserted this row version. - t_xmax (4 bytes): The transaction ID that deleted or updated this row version (0 if active). - t_cid (4 bytes): Command identifier inside the transaction. - t_ctid (6 bytes): Points to itself, or forwards to a newer row version created by an UPDATE. - t_infomask (2 bytes): Bitflags indicating transaction commit status (HEAP_XMIN_COMMITTED, HEAP_XMAX_INVALID). - NULL Bitmap: Dynamic bitmask recording which nullable columns are NULL.

3. MariaDB / MySQL InnoDB 16 KiB Clustered Index (IOT)

In MariaDB’s InnoDB engine, tables are not heap files. A table is implemented as an Index-Organized Table (IOT): the table data is physically sorted and stored directly inside the Primary Key B-Tree index.

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    subgraph PG_MODEL["PostgreSQL Model (Heap + Secondary)"]
        PG_IDX["Secondary B-Tree Index<br/>(Stores Key -> ctid pointer)"]
        PG_HEAP["Table Heap File<br/>(8 KiB Unordered Pages)<br/>Row data read via ctid hop"]
        PG_IDX -->|"1. Extract ctid"| PG_HEAP
    end

    subgraph INNODB_MODEL["MariaDB InnoDB Model (Clustered Index)"]
        INNO_SEC["Secondary B-Tree Index<br/>(Stores Secondary Key -> PK)"]
        INNO_CLUS["Clustered B-Tree Index<br/>(Root & Branch Pages)"]
        INNO_LEAF["Clustered Leaf Pages (16 KiB)<br/>Row Data Embedded Directly!"]
        INNO_SEC -->|"1. Extract PK"| INNO_CLUS
        INNO_CLUS -->|"2. Traverse to Leaf"| INNO_LEAF
    end

The Double-Lookup Cost of InnoDB Secondary Indexes

  • Primary Key Read: In InnoDB, querying by primary key (WHERE id = 42) traverses the clustered B-Tree directly to the leaf page. It requires zero secondary hops, reading the full row payload immediately.
  • Secondary Index Read: When querying by an unclustered column (WHERE email = 'user@example.com'), InnoDB searches the secondary index, extracts the primary key value, and then performs a second B-Tree traversal on the clustered index to retrieve the actual row data.

4. MVCC Storage Mechanics: Heap-Append vs. Undo Logs

When a transaction executes UPDATE users SET status = 'ACTIVE' WHERE user_id = 10;, how does the engine store the previous value for concurrent transactions?

Operational Metric PostgreSQL (Heap Append) MariaDB InnoDB (In-Place + Undo)
Update Execution Writes a brand new tuple into the 8 KiB page; marks old tuple's t_xmax. Overwrites the row in-place in the 16 KiB leaf page; writes old bytes to Undo Log.
Secondary Indexes Must update all secondary indexes to point to new ctid (unless HOT applies). Secondary indexes remain unchanged if indexed columns are not modified.
Bloat & Cleanup Dead tuples accumulate in heap pages; requires autovacuum to reclaim space. Undo segments are freed automatically when older read transactions commit.
Rollback Cost Instant (O(1)): engine marks transaction aborted in pg_xact. Slower: engine must read Undo logs and reverse in-place mutations sequentially.

Heap-Only Tuple (HOT) Optimization in PostgreSQL

To mitigate index-update overhead during updates, PostgreSQL implements HOT (Heap-Only Tuples). If an UPDATE does not modify any indexed columns and the target heap page has sufficient free space: 1. The new tuple is written to the same 8 KiB page. 2. The old tuple's line pointer links directly to the new tuple within the page. 3. Zero secondary indexes are touched. Index scans continue reading the original line pointer and follow the local pointer chain inside the block.

5. Low-Level Forensic Lab: Inspecting Raw Pages with pageinspect

PostgreSQL provides the official pageinspect contrib module, enabling engineers to inspect physical 8 KiB block headers and tuple flags directly via SQL.

-- File: /opt/db-ops/inspect_page_forensics.sql
BEGIN;

-- 1. Enable the pageinspect forensic extension
CREATE EXTENSION IF NOT EXISTS pageinspect;

-- 2. Create a test table and insert a row
DROP TABLE IF EXISTS forensic_test;
CREATE TABLE forensic_test (
    id INT,
    payload TEXT
);

INSERT INTO forensic_test VALUES (1, 'Storage Engine Internals');

-- 3. Inspect the 24-byte Page Header of Block 0
SELECT lsn, checksum, lower, upper, special, pagesize
FROM page_header(get_raw_page('forensic_test', 0));

-- 4. Inspect line pointers and tuple headers in Block 0
SELECT 
    lp AS line_pointer,
    lp_off AS byte_offset,
    lp_len AS tuple_length,
    t_xmin AS insert_tx_id,
    t_xmax AS delete_tx_id,
    t_ctid AS physical_coordinate,
    t_data AS raw_tuple_data
FROM heap_page_items(get_raw_page('forensic_test', 0));

-- 5. Perform an UPDATE to observe MVCC tuple versioning
UPDATE forensic_test SET payload = 'Updated Payload';

-- Re-inspect: Observe that t_xmax on line pointer 1 is now set,
-- and line pointer 2 contains the new active version!
SELECT 
    lp, t_xmin, t_xmax, t_ctid 
FROM heap_page_items(get_raw_page('forensic_test', 0));

COMMIT;