dbaguide.com — Modern Database Architecture, Operations & Tools Hub

▸1. The Core Physics: Disk Mechanics & Memory Hierarchy

Database indexing revolves around a single physical axiom: Disk I/O is slow; RAM is fast. Even on modern NVMe drives, random 4KB or 8KB block accesses generate substantial CPU cache churn, controller queue stalls, and latency overhead compared to sequential block sweeps.

An engine's indexing architecture directly balances two fundamental engineering goals:

1*How do we read the absolute minimum number of pages?* (Read Amplification minimization)
2*What is the exact storage, latching, and journal penalty per write?* (Write Amplification minimization)

▸2. B-Tree vs B+Tree: PostgreSQL & MySQL Approaches

Comparison Dimension	PostgreSQL (B-Tree)	MySQL InnoDB (B+Tree)
Table Storage Layout	Heap Table (Rows appended across unordered 8KB pages)	Clustered Index (The table itself IS the Primary Key B+Tree)
Primary Key Lookup	Traverses B-Tree from root to leaf ➔ fetches row via Heap Pointer (TID: page/offset) (2 stages)	Traverses B+Tree directly to leaf page containing all row columns (1 stage)
Secondary Index Lookup	Points directly to Heap Tuple ID (TID) coordinates	Stores the Primary Key value; requires a secondary traversal on the clustered index (Bookmark Lookup)
Range Scan Efficiency	Bidirectionally linked leaf blocks; requires TID stream sweeps	Sequentially linked clustered leaves; exceptional physical locality and range throughput
HOT (Heap-Only Tuple) Optimization	Supported: Updates to non-indexed columns avoid index leaf writes	Unsupported: Row mutation modifies or splits clustered B+Tree nodes

▸3. PostgreSQL Heap Architecture & HOT (Heap-Only Tuples)

PostgreSQL stores tabular rows in unstructured 8KB Heap pages. A B-Tree index maintains indexed search keys mapped to exact physical disk coordinates (ctid: page number + page offset).

[!NOTE] The HOT (Heap-Only Tuple) Advantage: In PostgreSQL, when a row is updated without modifying any indexed column—and if sufficient free space exists within the same 8KB page—the engine writes the new version onto that identical page and updates an internal pointer chain. Not a single index block is touched! This reduces write amplification by up to 80%. Tuning fillfactor to 80–90% on write-heavy tables is essential to enable consistent HOT updates.

sql

-- PostgreSQL: Configure fillfactor to maximize HOT update efficiency
ALTER TABLE orders SET (fillfactor = 85);
VACUUM FULL orders; -- Reorganize physical pages

▸4. MySQL InnoDB Clustered Index & Secondary Index Penalties

In MySQL InnoDB, there is no detached heap storage; the physical table organization is strictly determined by the Primary Key B+Tree (Clustered Index).

•Advantage: Queries like SELECT * FROM users WHERE id = 1042; resolve with a single tree traversal directly onto the leaf block containing all column data, requiring zero secondary lookups.
•Secondary Index Penalty: When creating a secondary index (e.g. on email), InnoDB stores the row's Primary Key value in the leaf, not a physical disk address. Consequently, every secondary index search performs a secondary clustered index descent (Bookmark Lookup).

[!WARNING] The Wide Primary Key Trap: If a wide type like a 36-character UUID string is chosen as the Primary Key in MySQL, all 36 bytes are duplicated across the leaf nodes of EVERY secondary index on that table. In a 10-million row table with 5 indexes, this wastes gigabytes of RAM in the buffer pool and inflates I/O. Primary keys in MySQL must always be compact, monotonically increasing integers (BIGINT AUTO_INCREMENT).

▸5. Specialized Index Topologies: BRIN & GIN

Beyond standard B-Trees, PostgreSQL provides purpose-built indexing engines engineered for massive production scales:

1BRIN (Block Range Index): Engineered for naturally ordered datasets such as timeseries logs and sequential IDs. Rather than indexing every row, it stores only the minimum and maximum values across 128 disk pages (1MB block ranges). A 100GB table can be indexed with only 5MB of RAM!
2GIN (Generalized Inverted Index): Designed for composite data structures including JSONB, full-text documents, and integer arrays, mapping individual elements to the set of rows where they appear.

▸6. Senior DBA Golden Rules (Index Philosophy Summary)

1Every Index Is a Write Penalty: Every INSERT, UPDATE, and DELETE must acquire locks, traverse index trees, and generate journal records (WAL / Redo log). Unused indexes represent direct waste of CPU cycles and write IOPS.
2Leverage Covering Indexes (Index-Only Scans): Append projected columns to index leaves using INCLUDE clauses or composite keys so queries can be fully satisfied directly from the index without reading the heap table.
3Respect Cardinality: Low-cardinality flags (such as boolean is_active columns) are typically rejected by query optimizers when indexed via standard B-Trees; replace them with targeted Partial Indexes instead.