In Part 1, we explored the fundamentals of PostgreSQL page structure, including page introduction, page size, and the Page Header Data. We also looked at the different fields within the page header and understood how they help PostgreSQL manage and organize data at the page level.
In this part, let’s move one step further and explore what gets stored after the Page Header Data.
We will focus on three important components of a PostgreSQL page:
- Item Identifiers (Item ID Data)
- Items (Rows / Tuples)
- Free Space and Special Space
Let’s understand how PostgreSQL manages these components internally.
1. Item Identifiers (Item ID Data)
Immediately following the Page Header Data are the Item Identifiers, also known as the Item ID Array or Line Pointers.
Each Item Identifier occupies 4 bytes and acts as a pointer to the actual row (tuple) stored within the page.
Instead of storing the actual row data directly in the Item Identifier, PostgreSQL maintains metadata that tells it where the corresponding tuple is located.
Each Item Identifier contains three important pieces of information:
- Offset: The starting byte position of the tuple within the page.
- Length: The size of the tuple in bytes.
- Flags: Attribute bits that describe the status and interpretation of the item.
How does it work during an INSERT?
When a new row is inserted into a PostgreSQL page, PostgreSQL allocates a new Item Identifier from the beginning of the available free space.
The pd_lower value in the Page Header Data is updated accordingly to reflect the new boundary of the Item Identifier array.
This is important because Item Identifiers grow forward, from the beginning of the page towards the end.
Think of Item Identifiers as an index or directory that helps PostgreSQL locate the actual row data inside a page.
2. Items (Rows / Tuples)
Now that we understand how Item Identifiers point to the actual data, let’s look at where PostgreSQL stores the actual rows.
The actual row data, also known as tuples, is stored towards the end of the page.
While Item Identifiers grow forward from the beginning of the page, tuple data is stored backward, starting from the end of the page.
This creates an interesting structure where both Item Identifiers and tuple data grow towards each other, leaving free space in between.
Each tuple generally consists of two major components:
- HeapTupleHeader: Contains metadata associated with the tuple.
- Column Data: Contains the actual values stored for the table columns.
How does MVCC relate to tuple storage?
As we discussed in my previous blog on PostgreSQL MVCC, every tuple contains important transaction-related metadata.
Some of the important fields include:
xmin– Identifies the transaction that inserted the tuple.xmax– Identifies the transaction that deleted or updated the tuple, when applicable.ctid– Identifies the physical location of a tuple version within the table.
These fields are part of the tuple’s internal header and help PostgreSQL manage multiple row versions using its Multi-Version Concurrency Control (MVCC) mechanism.
If you haven’t read my previous blog on MVCC, I recommend checking it out to understand how PostgreSQL manages row versions internally.
3. Free Space
Free space in a PostgreSQL page is the area between the Item Identifier array and the actual tuple data. When new rows are inserted, PostgreSQL uses this available space to allocate new Item Identifiers and store tuple data. The Item Identifier array grows from the beginning of the page towards the end, while the tuple data grows from the end of the page towards the beginning. As more rows are inserted, both areas gradually move towards each other, reducing the available free space. When there is insufficient free space to store a new tuple and its Item Identifier, PostgreSQL may allocate another page to store the new row.
4. Special space
At the end of a PostgreSQL page, there is an area called Special Space, which is reserved for access-method-specific information. Heap table pages generally do not use this area, whereas index pages use it to store additional metadata required by their respective index access methods, such as B-tree or GiST. This allows different PostgreSQL access methods to maintain their own specific information without changing the overall page structure.
Conclusion
In this section, we explored how PostgreSQL organizes and manages data inside a database page beyond the Page Header Data.
We understood the key components of page management:
- Item Identifiers: Act as pointers to the actual tuple data stored within the page.
- Tuple Data: Stores the actual table rows towards the end of the page.
- Free Space: Provides available space between the Item Identifier array and tuple data for future insertions.
- Special Space: Is reserved for access-method-specific information, primarily used by index pages.
One interesting aspect of PostgreSQL page management is that the Item Identifier array grows from the beginning of the page, while tuple data grows from the end. This opposite-direction approach allows PostgreSQL to utilize available page space efficiently.
