5 Hidden Components of Postrges Page

5 Hidden Components of Postgres Page

In this article, we are ditching the theory diagrams. Instead, we will put on our lab coats, use PostgreSQL’s built-in “X-ray” tools, and surgically inspect a live data page to reveal the 5 physical components that make it work.

The Lab Setup

To see the raw bytes on the disk, standard SQL isn’t enough. We need the pageinspect extension. Run these commands to set up our experiment:

-- 1. Install the X-Ray tool (Safe for production, read-only)
CREATE EXTENSION IF NOT EXISTS pageinspect;

-- 2. Create a dummy table
CREATE TABLE medium_demo (
    id serial PRIMARY KEY,
    content varchar(50)
);

-- 3. Insert 2 rows to populate a single 8KB page
INSERT INTO medium_demo (content) VALUES ('PostgreSQL is Awesome!');
INSERT INTO medium_demo (content) VALUES ('Block Architecture');

Now we have a single 8KB page on the disk containing two rows. Let’s dissect it.

The 5 Anatomical Parts of a Page

A PostgreSQL page isn’t just a random bag of bytes. It is a highly organized structure divided into five distinct sections.

1. The Header (The ID Card)

Every page starts with a fixed 24-byte header. This contains the page’s vital signs: its version, checksums, and free space tracking.

Let’s read the header of block 0:

SELECT * FROM page_header(get_raw_page('medium_demo', 0));

Key Takeaway: Look at the lsn (Log Sequence Number). This tracks the last time this page was modified. It is crucial for crash recovery—if the server dies, PostgreSQL uses this number to replay the WAL (Write-Ahead Log) and restore your data.

2. Line Pointers (The Directory)

Immediately after the header come the Line Pointers. Here is the trick: These do NOT contain your data.

They are simply 4-byte “shortcuts” or addresses that point to where the actual data resides later in the page.

• Unique behavior: The list of pointers grows from the top of the page downwards.

-- View pointers (lp) and their offsets (lp_off)
SELECT lp, lp_off, lp_len, t_xmin 
FROM heap_page_items(get_raw_page('medium_demo', 0));

3. The “Hole” (Free Space)

This is the smartest part of the design.

• Pointers grow from top to bottom.
• Actual Data grows from bottom to top.

The empty space sandwiched between them is called the Free Space (or Hole). In your page_header output, look at pd_lower and pd_upper. The difference between them is exactly how many bytes of room you have left. When they meet, the page is full, and PostgreSQL must allocate a new 8KB page.

4. The Items (Heap Tuples)

This is where your actual data (“PostgreSQL is Awesome!”) lives. PostgreSQL stacks these rows starting from the very end of the page, growing upwards.

Each item also has its own mini-header containing t_xmin and t_xmax. Why it matters: These fields power MVCC (Multi-Version Concurrency Control). They tell the database which transaction created the row and which one deleted it, allowing multiple users to read/write simultaneously without locking each other out.

5. Special Space (The Navigator)

At the very end of the 8KB block sits the Special Space.

• In standard tables (Heaps): It’s usually empty.
• In Indexes (B-Trees): It is critical. It stores pointers to the “left” and “right” sibling pages. This allows the database to traverse the index tree horizontally at lightning speed.

SELECT pd_special FROM page_header(get_raw_page('medium_demo', 0));
-- Returns 8192 for our standard table (meaning no special space reserved).

Why Should You Care?

Understanding these 5 components isn’t just trivia; it changes how you optimize:

• The Cost of UPDATEs: An UPDATE in PostgreSQL is actually a DELETE + INSERT. The new version of the row is written into the "Hole" (Section 3). If there is no space left, PostgreSQL has to perform costly I/O to create a new page.
• Fillfactor Magic: When you set fillfactor = 70, you are telling PostgreSQL: "Only fill the page up to 70%, leave 30% as a Hole." This reserves space for future updates, keeping them on the same page and boosting performance significantly.