Understanding PostgreSQL Page (Block) Structure

PostgreSQL stores data on disk in fixed-size units called pages or blocks. In this post, we’ll explore how PostgreSQL organizes data…

PostgreSQL Page (Block) Structure

PostgreSQL stores data on disk in fixed-size units called pages or blocks. In this post, we’ll explore how PostgreSQL organizes data within these pages and demonstrate how to inspect their contents using the pg_filedump tool.

What Is a Page?

In PostgreSQL, a page is the smallest unit of storage for reading and writing data from disk.

Every table and index is made up of these 8 KB pages.

Page Layout Overview

A PostgreSQL page has three main sections:

1. Page Header

Contains metadata about the page, including:

Number of tuples
Offset of free space
LSN (Log Sequence Number), checksum, flags, etc.

Approximate size: 24 bytes

2. Line Pointers (Item Identifiers)

Each tuple stored in the page has a corresponding line pointer, which:

Points to the tuple’s offset within the page
Occupies 4 bytes per pointer
Grows downward from the top of the page

3. Tuples (Row Data)

The actual data is stored at the bottom of the page and grows upward.

This structure ensures:

Line pointers grow from the top
Tuples grow from the bottom
Free space is between them and dynamically adjusted

Tuple Insertion Process

When a new row is inserted:

PostgreSQL checks for available space on the page.
A line pointer is added to the page header.
The tuple is inserted at the bottom of the page.

Thus, a tuple’s location is represented by the ctid, which is a combination of:

Block number
Line pointer offset

Example: ctid = (0,1) refers to block 0, tuple 1.

Inspecting Physical Data with `pg_filedump`

pg_filedump is a powerful tool for inspecting the physical layout of PostgreSQL data files at the page level.

Installation (PostgreSQL 13 on RHEL-based systems)

rpm -ivh https://ftp.postgresql.org/pub/repos/yum/13/redhat/rhel-9.3-x86_64/pg_filedump_13-17.3-1PGDG.rhel9.x86_64.rpm

Usage Example

/usr/pgsql-13/bin/pg_filedump -f &amp;amp;lt;file_path&amp;amp;gt;

Practical Example

Step 1: Create a Table

CREATE TABLE test_wal (id serial, val text);

Initially, the table is empty, and pg_filedump won’t show meaningful content yet.

Step 2: Insert a Row

INSERT INTO test_wal (val) VALUES (&amp;amp;#x27;deneme&amp;amp;#x27;);

The data is inserted, but PostgreSQL doesn’t immediately flush it to disk.

Step 3: Find the File Path

SELECT pg_relation_filepath(&amp;amp;#x27;test_wal&amp;amp;#x27;);
-- Example output: base/50654/57581

Step 4: Check the Page (Before Checkpoint)

/usr/pgsql-13/bin/pg_filedump -f /var/lib/pgsql/13/data/base/50654/57581

You may see something like:

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /var/lib/pgsql/13/data/base/50654/57581
* Options used: -f
*******************************************************************
Notice: Block size determined from reading block 0 is zero, using default 8192 instead.
Hint: Use -S &amp;amp;lt;size&amp;amp;gt; to specify the size manually.

Block    0 ********************************************************
&amp;amp;lt;Header&amp;amp;gt; -----
 Block Offset: 0x00000000         Offsets: Lower       0 (0x0000)
 Block: Size    0  Version    0            Upper       0 (0x0000)
 LSN:  logid      0 recoff 0x00000000      Special     0 (0x0000)
 Items:    0                      Free Space:    0
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 24

 Error: Invalid header information.

  0000: 00000000 00000000 00000000 00000000  ................
  0010: 00000000 00000000                    ........

&amp;amp;lt;Data&amp;amp;gt; -----
 Empty block - no items listed

&amp;amp;lt;Special Section&amp;amp;gt; -----
 Error: Invalid special section encountered.
 Error: Special section points off page. Unable to dump contents.

This happens because the changes haven’t been flushed to disk yet.

Step 5: Force Checkpoint and Retry

CHECKPOINT;

Then run pg_filedump again:

/usr/pgsql-13/bin/pg_filedump -f /var/lib/pgsql/13/data/base/50654/57581

Sample output:

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /var/lib/pgsql/13/data/base/50654/57581
* Options used: -f
*******************************************************************

Block    0 ********************************************************
&amp;amp;lt;Header&amp;amp;gt; -----
 Block Offset: 0x00000000         Offsets: Lower      28 (0x001c)
 Block: Size 8192  Version    4            Upper    8152 (0x1fd8)
 LSN:  logid      0 recoff 0x31c787a8      Special  8192 (0x2000)
 Items:    1                      Free Space: 8124
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 28

  0000: 00000000 a887c731 00000000 1c00d81f  .......1........
  0010: 00200420 00000000 d89f4600           . . ......F.

&amp;amp;lt;Data&amp;amp;gt; -----
 Item   1 -- Length:   35  Offset: 8152 (0x1fd8)  Flags: NORMAL
  1fd8: 4c200000 00000000 09000000 00000000  L ..............
  1fe8: 01000200 02081800 01000000 0f64656e  .............den
  1ff8: 656d65                               eme



*** End of File Encountered. Last Block Read: 0 ***

Now you can see the actual tuple and how it’s stored physically.

Checking the `ctid`

SELECT ctid FROM test_wal WHERE id=1;
-- Output: (0,1)

0: Block number
1: Line pointer index

Additional Notes

PostgreSQL doesn’t immediately flush data to disk upon insert.
You can influence write behavior using parameters like:
checkpoint_timeout
bgwriter_delay
max_wal_size

Summary

Understanding PostgreSQL’s internal page structure is essential for performance tuning, debugging, and in-depth database analysis. In this article, we covered:

How data is organized on disk in 8 KB pages
Tuple layout within a page
How to inspect physical storage using pg_filedump

This low-level insight gives you the tools to better understand what’s really happening inside your PostgreSQL instance.