Variable-Length Records and Slotted Page Design in Databases

Added:

PAX (Partition Attributes Across) and Columnar Page Layouts

While traditional slotted page design is highly effective for write-heavy transactional workloads (OLTP), it suffers from poor CPU cache utilization and complex fragmentation management during analytical queries (OLAP). As an alternative, the PAX (Partition Attributes Across) layout organizes data within a page by partitioning attributes vertically rather than horizontally. Instead of using a slot directory to point to entire variable-length records, PAX groups values of the same column together in contiguous 'minipages' within a single disk page. This architecture significantly improves cache locality and CPU instruction pipelining, as scanning a specific attribute no longer requires loading irrelevant record data into the cache. Furthermore, by isolating variable-length attributes into dedicated column zones within the page, PAX simplifies free space management and reduces the need for the frequent, expensive in-page defragmentation and pointer-reordering typical of traditional slotted pages.

The concept of a database 'Page' or 'Block' as the fundamental unit of disk I/O and memory allocation.
The distinction between fixed-length and variable-length data types (e.g., INT vs. VARCHAR).
Basic record representation in binary format, including how schema metadata maps to raw bytes.
The general concept of a Heap File organization, where records are stored in no particular logical order.
Page fragmentation and compaction strategies, including how databases handle record deletion and space reclamation.
The structure of Record Identifiers (RIDs) or Tuple Identifiers (TIDs) as [Page ID, Slot Number] pairs and why this indirection is crucial for physical data independence.
How B+ Tree indexes reference slotted page records, specifically looking at index leaf node structure.
Alternative storage layouts, such as Columnar Storage (Decomposition Storage Model) versus the row-oriented (N-ary Storage Model) slotted page design.
15K views127likes8:00@CS186BerkeleyOriginal Release: 2018-09-01

Slotted pages are a general-purpose data structure for storing records on heap file pages that handles variable length records by placing metadata (slot directory) at the page footer, containing a free space pointer and entries with record lengths and offsets; this design enables efficient record insertion, deletion, and reorganization while supporting both variable and fixed length records, though it may be slightly inefficient for purely fixed-length records.