PostgreSQL Internals: Tables, Indexes, and Tuples

Understanding these abstractions is essential for comprehending how the database operates.

The discussion aims to clarify the terminology and the underlying structure of data storage on disk within the PostgreSQL system.

Metadata maps page numbers to specific file offsets, allowing the database to treat each file as an array of fixed-size 8KB units. By default, each of these pages is 8KB in size.

When data needs to be stored, PostgreSQL allocates new 8KB pages as required, expanding the table's footprint on disk.

The database system uses metadata to efficiently map page numbers to specific file offsets, allowing for rapid access to any part of a table's data.

In PostgreSQL, a row is not a static entity but a versioned entry known as a tuple. Each tuple within a page is managed by a line pointer, which helps organize the data internally.

Every tuple is uniquely identified by its Current Tuple ID (CTID), which combines the page number and the tuple's index within that specific page.

This design choice, while offering distinct advantages in certain scenarios, is noted by the presenter as a feature that can be both praised for its elegance and criticized for its complexity, depending on the context.

By combining the page number and the offset, the tuple ID enables the system to pinpoint the exact physical location of a data entry. A file descriptor is then utilized to quickly find the specific page on disk, enabling immediate file seeks.

Once identified, these data pages are loaded into a shared buffer in memory. This process enables the system to efficiently scan for and retrieve specific rows, with new pages only being allocated when the table grows and more storage space is needed.

This hierarchical organization efficiently guides the system to the desired data.

Traversing the B-tree leads to a leaf page that contains the target tuple ID. This tuple ID serves as a precise pointer, directing the database to the exact page and offset where the physical row data resides.

This design ensures that PostgreSQL can quickly locate the physical storage location of a row based on an indexed key, thereby enhancing query performance.

When a row is updated, the system finds free space within a data page and creates a new tuple to reflect the change.

This approach necessitates an update to the index to ensure it points to the newly created tuple ID. The process of modifying the index, particularly within a B-tree structure, often requires rebalancing the tree.

Such rebalancing operations add a layer of complexity to the system, as the tree's structure must be maintained for efficient searching after each update.

To query an item by its ID, PostgreSQL traverses its B-tree index, specifically a B+ tree structure, which organizes nodes in a chained manner for efficient access.

Items are stored in the leaf pages of this tree, which are kept sorted. This sorted arrangement ensures that searches for specific items are highly efficient.

By default, PostgreSQL clusters its indexes, which means that related entries are kept together, further optimizing the retrieval process.

Multiple versions of the same row can exist simultaneously within a PostgreSQL database, for example, a row with a price of $10 and a newer version with $20.

The specific tuple version returned to a user depends entirely on the active transaction and its isolation level. This mechanism ensures data consistency across concurrent operations.

Visibility is determined by whether the transaction initiating the query started before or after a particular update was committed, ensuring that transactions operate on a consistent snapshot of the data.

Every row in PostgreSQL includes hidden, system-maintained headers: Xmin and Xmax. These headers are crucial for Multi-Version Concurrency Control (MVCC).

Xmin records the transaction ID that created the tuple, while Xmax notes the transaction ID that updated or logically deleted the tuple.

These 32-bit (4-byte) numbers empower the database engine to accurately determine which version of data is visible to any given active transaction, ensuring consistent views of data.

This system allows transactions to read data without locking, as they simply refer to versions of data that were committed before their transaction started.

The heap is the primary storage area where the actual row data resides in PostgreSQL. While indexes help locate data, they do not always contain all requested columns.

In such cases, a heap lookup becomes necessary to retrieve the complete row information. This heap data can be located either on disk or in memory within the shared buffers.

PostgreSQL transactions must filter through various tuple versions, applying specific visibility rules to determine which version of a row is valid for their context. The Transaction ID (XID) plays a critical role in this process, indicating whether a particular row version is visible or obsolete.

For example, if a transaction with ID 10 is active, it would ignore a tuple identified as 0:0 but would select 0:2, resulting in a returned price of 20. This is because the visibility rules dictate that tuple 0:2 is relevant to transaction 10, whereas tuple 0:0 is not.

Snapshot isolation, a key feature of MVCC, guarantees that a transaction only perceives data that is consistent with the database state at the moment the transaction began, preventing inconsistencies from concurrent updates.

For instance, if a transaction with ID 5 remains active for an extended period, it continues to see data versions that would otherwise be considered obsolete by newer transactions.

The 'vacuum' process, designed to clean up dead tuples and reclaim space, is unable to process and remove tuples that are currently pinned by the active snapshots of these long-running transactions.

Storage efficiency declines when obsolete data persists, creating table bloat that consumes space otherwise available for active transactions. Effectively defining what constitutes a 'dead' tuple is crucial for maintaining a healthy and efficient database.

By knowing the precise offset of a tuple, the system can retrieve a specific 8KB page directly into memory with a single I/O operation.

This targeted retrieval ensures that performance remains consistent and largely unaffected by the total size of the table, even if it is as large as 7GB. The direct link from the index to the tuple ID enables this swift and efficient data access.