In my previous post, I looked at what happens when PostgreSQL executes a SELECT query.
One part of that journey caught my attention:
PostgreSQL doesn't simply read "rows" from disk. It works with pages.
So I wanted to go one level deeper.
What is actually inside a PostgreSQL page?
Today, we're going to inspect one.
Not just with a diagram.
We'll use PostgreSQL itself to look inside its storage.
🧠 First: What Is a PostgreSQL Page?
PostgreSQL stores table and index data in fixed-size blocks called pages.
In a standard PostgreSQL build, a page is:
8 KB
So instead of imagining a table like this:
users
Row 1
Row 2
Row 3
Row 4
Row 5
...
Think about it more like:
users table
┌──────────────────┐
│ Page 0 - 8 KB │
├──────────────────┤
│ Page 1 - 8 KB │
├──────────────────┤
│ Page 2 - 8 KB │
├──────────────────┤
│ Page 3 - 8 KB │
└──────────────────┘
Each page can contain multiple tuples (PostgreSQL's internal term for stored row versions).
🔍 Why Does PostgreSQL Use Pages?
Imagine PostgreSQL had to read every individual row directly from disk.
That would create a huge amount of I/O.
Instead, PostgreSQL works with blocks of data.
If it needs a particular piece of data, it can bring the relevant page into memory.
A simplified view is:
Disk
│
│ 8 KB page
▼
PostgreSQL Buffer
│
▼
Executor
│
▼
Tuple
This is one reason database performance is heavily influenced by I/O.
🧱 What's Inside a Page?
A simplified PostgreSQL heap page looks something like this:
┌──────────────────────────────┐
│ Page Header │
├──────────────────────────────┤
│ Line Pointers │
│ │
│ │
│ Free Space │
│ │
│ │
│ Tuples / Rows │
└──────────────────────────────┘
There are several important pieces here.
1️⃣ Page Header
Every page has a header containing metadata about the page.
Among other things, PostgreSQL tracks information such as:
- page state
- free space boundaries
- LSN-related information
- checksum-related information when enabled
The important idea is:
The page needs metadata so PostgreSQL knows how to manage the data inside it.
2️⃣ Line Pointers
This is one of the most interesting parts.
PostgreSQL doesn't simply say:
Row 1 starts at byte 100
Row 2 starts at byte 250
Instead, the page contains line pointers (also called item identifiers).
Conceptually:
Line Pointer 1 → Tuple 1
Line Pointer 2 → Tuple 2
Line Pointer 3 → Tuple 3
This becomes particularly important when PostgreSQL updates rows.
We'll come back to this when we discuss MVCC.
3️⃣ Free Space
A page needs somewhere to put new tuples.
So there is free space between the line-pointer area and tuple storage.
Conceptually:
┌─────────────────────┐
│ Page Header │
├─────────────────────┤
│ Line Pointers │
├─────────────────────┤
│ │
│ Free Space │
│ │
├─────────────────────┤
│ Tuple 3 │
│ Tuple 2 │
│ Tuple 1 │
└─────────────────────┘
The exact physical layout is more nuanced, but this mental model is useful.
4️⃣ Tuples
Finally, we have the actual stored row versions.
For example:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT
);
Insert some data:
INSERT INTO users (name)
VALUES
('Ahmed'),
('John'),
('Sarah'),
('Mike');
These rows eventually occupy storage inside heap pages.
🧪 Let's Inspect One
Now let's get our hands dirty.
PostgreSQL provides an extension called:
pageinspect
It provides functions for inspecting the internal contents of database pages.
⚠️ This is primarily a learning/debugging tool. You generally don't need it for normal application development.
Step 1: Enable pageinspect
Run:
CREATE EXTENSION pageinspect;
You may need appropriate privileges to create the extension.
Step 2: Create a Test Table
Let's create a small table:
CREATE TABLE page_demo (
id SERIAL PRIMARY KEY,
name TEXT
);
Insert some rows:
INSERT INTO page_demo (name)
VALUES
('Ahmed'),
('John'),
('Sarah'),
('Mike'),
('David');
Step 3: Find the Table's Physical Relation
PostgreSQL stores table data in a physical relation.
We can ask PostgreSQL for the relation's file node:
SELECT pg_relation_filenode('page_demo');
You'll get a number similar to:
12345
This is an internal identifier used by PostgreSQL's storage system.
Step 4: Inspect the Page Header
Now let's inspect the first page:
SELECT *
FROM page_header(
get_raw_page('page_demo', 0)
);
You'll see information about the page.
For example, you'll encounter fields such as:
lsn
checksum
flags
lower
upper
special
pagesize
version
prune_xid
The exact values depend on your PostgreSQL version and table.
🧐 What Does pagesize Tell Us?
You should see a value corresponding to the page size, typically:
8192
That's:
8192 bytes
÷ 1024
=
8 KB
So we can actually inspect the page and see its size.
Step 5: Inspect the Line Pointers
Now let's inspect the items stored on the page:
SELECT *
FROM heap_page_items(
get_raw_page('page_demo', 0)
);
This is where things start getting interesting.
You'll see information associated with tuples and their positions on the page.
Some of the columns you'll encounter include things such as:
lp
lp_off
lp_flags
lp_len
t_xmin
t_xmax
t_ctid
Don't worry if these look strange.
They're the building blocks of PostgreSQL's storage engine.
🔥 One Column You Should Remember: t_ctid
You'll see something like:
(0,1)
(0,2)
(0,3)
These values are related to the tuple's physical location.
The general idea is:
(block number, offset number)
For example:
(0,3)
can be thought of as:
Page 0
Line Pointer 3
This is called a CTID.
🧪 Let's Look at CTID From SQL
We can query:
SELECT
ctid,
id,
name
FROM page_demo;
You might see:
ctid | id | name
--------+----+-------
(0,1) | 1 | Ahmed
(0,2) | 2 | John
(0,3) | 3 | Sarah
(0,4) | 4 | Mike
(0,5) | 5 | David
The exact values aren't guaranteed to look exactly like this on every table/database state, but the important concept is the same.
🤯 Here's Where It Gets Interesting
Let's update a row.
UPDATE page_demo
SET name = 'Ahmed Updated'
WHERE id = 1;
Now check:
SELECT
ctid,
id,
name
FROM page_demo
WHERE id = 1;
You may notice that the ctid has changed.
Why?
Because PostgreSQL uses MVCC.
An UPDATE isn't simply:
Old row
↓
Overwrite
↓
New row
Internally, PostgreSQL creates a new row version and marks the old version as no longer visible to appropriate transactions.
This is one of the reasons understanding pages is so important.
We'll explore this properly in the next article.
🧠 So What Have We Learned?
A PostgreSQL table isn't simply a collection of rows.
A simplified mental model is:
Database
│
▼
Table
│
▼
Pages
│
▼
Line Pointers
│
▼
Tuples
And the page itself contains structures that help PostgreSQL manage those tuples.
🚨 Why Should Backend Developers Care?
You might be thinking:
"I'm a Laravel/Go developer. Why should I care about 8 KB pages?"
Because these internals explain things you encounter at the application level.
For example:
Why does UPDATE sometimes create table bloat?
MVCC + tuple versions.
Why does VACUUM exist?
Because old tuple versions eventually need cleanup.
Why can an UPDATE cause more I/O than expected?
Because PostgreSQL may need to create a new tuple version and update related structures.
Why do indexes matter?
Because indexes can reduce the amount of table data/pages PostgreSQL needs to visit.
Why can queries become slower as a table grows?
Because the number of pages that need to be processed can increase.
These aren't random PostgreSQL behaviors.
They come from the storage architecture.
🧩 The Bigger Picture
We're slowly connecting the dots:
PostgreSQL
│
┌───────────┴───────────┐
│ │
Query Storage
Layer Layer
│ │
Planner Pages
│ │
Executor Tuples
│ │
└───────────┬───────────┘
│
MVCC
│
VACUUM / WAL
Understanding one piece makes the next piece easier.
🔥 Final Takeaway
The next time you write:
SELECT *
FROM users
WHERE id = 10;
remember that PostgreSQL isn't just "looking for a row."
It is working through:
Query
↓
Execution Plan
↓
Relation
↓
Page
↓
Line Pointer
↓
Tuple
↓
Visibility
↓
Result
And that is just the beginning.
🚀 What's Next?
Now that we know what a PostgreSQL page looks like, the next question becomes much more interesting:
What happens internally when we UPDATE a row?
That's where we'll get into:
- MVCC
xminxmax- tuple versions
- dead tuples
- visibility
- why PostgreSQL doesn't overwrite rows in place
That's where PostgreSQL internals really start to click.
I'm learning PostgreSQL internals by going below the SQL layer and documenting what I find along the way.
If you're learning PostgreSQL too, follow along. 🚀
Top comments (0)