PostgreSQL stores data internally in fixed-size pages (usually 8KB). While normal SQL queries allow us to work with tables and indexes logically, PostgreSQL developers and database researchers sometimes need to inspect the physical storage layer itself.
The pageinspect extension provides low-level visibility into:
- Heap table pages
- Index pages (BTREE, GIN, GiST, HASH, BRIN)
- Free Space Map (FSM)
- Page headers and checksums
- Raw tuple structure
If you were working deeply with PostgreSQL internals and customization or working as a database administrator, this extension would be one of the most important tools for real database investigation.
Installing the pageinspect Extension
First check whether the extension is available:
SELECT * FROM pg_available_extensions
WHERE name = 'pageinspect';
Install it:
CREATE EXTENSION pageinspect;
Verify installed objects:
\dx+ pageinspect
You will see many inspection functions like this
Objects in extension "pageinspect"
Object description
-------------------------------------------------------------------
function brin_metapage_info(bytea)
function brin_page_items(bytea,regclass)
function brin_page_type(bytea)
function brin_revmap_data(bytea)
function bt_metap(text)
function bt_multi_page_stats(text,bigint,bigint)
function bt_page_items(bytea)
function bt_page_items(text,bigint)
function bt_page_stats(text,bigint)
function fsm_page_contents(bytea)
function get_raw_page(text,bigint)
function get_raw_page(text,text,bigint)
function gin_leafpage_items(bytea)
function gin_metapage_info(bytea)
function gin_page_opaque_info(bytea)
function gist_page_items_bytea(bytea)
function gist_page_items(bytea,regclass)
function gist_page_opaque_info(bytea)
function hash_bitmap_info(regclass,bigint)
function hash_metapage_info(bytea)
function hash_page_items(bytea)
function hash_page_stats(bytea)
function hash_page_type(bytea)
function heap_page_item_attrs(bytea,regclass)
function heap_page_item_attrs(bytea,regclass,boolean)
function heap_page_items(bytea)
function heap_tuple_infomask_flags(integer,integer)
function page_checksum(bytea,bigint)
function page_header(bytea)
function tuple_data_split(oid,bytea,integer,integer,text)
function tuple_data_split(oid,bytea,integer,integer,text,boolean)
These functions operate directly on raw disk pages.
Important Concept Before Starting
The most important function in this extension is:
get_raw_page()
It extracts the physical page as a binary (bytea) value.
Almost all other functions require this raw page as input.
Example:
SELECT get_raw_page('res_users', 0);This reads page 0 from the table.
Result :
Inspecting Heap Table Pages
Reading Page Header Information
To view metadata stored at the top of a page:
SELECT *
FROM page_header(get_raw_page('res_users', 0));
Result :
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
------------+----------+-------+-------+-------+---------+----------+---------+-----------
0/31433408 | 0 | 4 | 204 | 272 | 8192 | 8192 | 4 | 0
(1 row)
- lsn ( log sequence number ) - Stores the WAL log position showing the last modification made to this page.
- checksum - Holds the page checksum value used to verify data integrity when checksums are enabled.
- flags - Contains internal status bits that describe the current state or hints related to the page.
- lower - Marks the end of the line pointer array and indicates where free space begins from the top.
- Upper - Marks where tuple data starts from the bottom and shows the upper boundary of free space.
- special - Points to the start of the special area reserved mainly for index metadata; heap pages usually do not use it.
- pagesize - Shows the physical size of the page in bytes, which is typically 8192 in PostgreSQL.
- version - Indicates the internal layout version of the page structure used by PostgreSQL.
- prune_xid - Stores a transaction hint used by PostgreSQL to decide when dead tuples on the page can be pruned safely.
Purpose:
Helps understand how PostgreSQL organizes page storage internally.
Viewing Heap Tuples
To see raw tuples inside a page:
SELECT *FROM heap_page_items(get_raw_page('res_users', 1));Result :
-[ RECORD 1 ]lp | 1lp_off | 8016lp_flags | 1lp_len | 171t_xmin | 769t_xmax | 0t_field3 | 0t_ctid | (0,1)t_infomask2 | 6t_infomask | 2306t_hoff | 24t_bits | t_oid | t_data | \x010000000f757365725f3100060000001b01850500ca16ca1cf50a98087b73616d706c655f746578745f73616d706c655f746578745f73616d706c655f746578745f73616d706c655f746578745f73616d706c655f746578745f7301000000000000001900000004000000010000001800000070670000180000006462000024000000696e6465780000001400000031000000
- lp - Line pointer number that identifies the tuple’s slot position inside the page.
- lp_off - Offset value showing where the tuple data starts within the page.
- lp_flags - Status flag describing whether the line pointer is used, unused, or redirected.
- lp_len - Length of the tuple data stored in bytes.
- t_xmin - Transaction ID that inserted the tuple.
- t_xmax - Transaction ID that deleted or locked the tuple; zero means no delete or lock.
- t_field3 - Internal field used for command or visibility tracking depending on tuple state.
- t_ctid - Current tuple identifier showing the block and offset location of the row.
- t_infomask2 - Secondary bitmask storing extra tuple metadata such as attribute count and update hints.
- t_infomask - Primary bitmask containing visibility and structural flags for the tuple.
- t_hoff - Header offset indicating where actual column data begins after the tuple header.
- t_bits - Bitmap showing which columns contain NULL values.
- t_oid - Object identifier stored with the tuple if OIDs are enabled.
- t_data - Raw binary representation of the tuple’s column data stored inside the page.
Purpose :
Useful for studying MVCC and tuple visibility rules.
Inspecting BTREE Index Pages
BTREE Meta Page
SELECT * FROM bt_metap('idx_btree_age');Result :
magic | version | root | level | fastroot | fastlevel | last_cleanup_num_delpages | last_cleanup_num_tuples | allequalimage --------+---------+------+-------+----------+-----------+---------------------------+-------------------------+--------------- 340322 | 4 | 1 | 0 | 1 | 0 | 0 | -1 | t(1 row)
- magic - A fixed identifier value that verifies the page belongs to a valid B-Tree index structure.
- version - Shows the internal format version used by the B-Tree metadata layout.
- root - Indicates the block number of the current root page of the index tree.
- level - Represents the height of the root page in the tree, where zero means the root is also a leaf page.
- fastroot - Stores a cached root block used to speed up index searches.
- Fastlevel - Shows the tree level of the fastroot page.
- last_cleanup_num_delpages - Records how many index pages were removed during the most recent cleanup or vacuum process.
- last_cleanup_num_tuples - Displays the number of tuples processed during the last cleanup operation, with negative one meaning no recent value is stored.
- allequalimage - Indicates whether all index entries use an equal image representation, allowing certain storage optimizations.
Purpose:
Shows tree level, root block, and metadata.
BTREE Page Statistics
SELECT *FROM bt_page_stats('idx_btree_age', 1);Result :
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo_level | btpo_flags -------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------------+------------ 1 | l | 51 | 0 | 77 | 8192 | 3976 | 0 | 0 | 0 | 3(1 row)
- blkno - Block number of the index page being inspected.
- type - Indicates the page type where l means a leaf page that stores actual index entries.
- live_items - Number of active index tuples currently present on the page.
- dead_items - Number of obsolete or removable index tuples that may be cleaned during vacuum.
- avg_item_size - Average size in bytes of index tuples stored on this page.
- page_size - Total size of the page in bytes, usually 8192 for PostgreSQL.
- free_size - Amount of unused free space available inside the page.
- btpo_prev - Block number of the previous sibling page in the B-Tree structure.
- btpo_next - Block number of the next sibling page in the B-Tree structure.
- btpo_level - Tree level of the page where zero represents a leaf level.
- btpo_flags - Internal flags describing the state or special properties of the B-Tree page.
BTREE Page Items
SELECT *FROM bt_page_items('idx_btree_age', 1);Result :
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids ------------+-----------+---------+-------+------+-------------------------+------+--------+---------------------------------------------------------------- 1 | (16,8199) | 64 | f | f | 00 00 00 00 00 00 00 00 | f | (4,13) | {"(4,13)","(5,45)","(6,4)","(8,3)","(8,36)","(9,1)","(10,25)"}(1 row)- itemoffset - Position number of the index tuple within the B-Tree page.
- ctid - Pointer to either a heap tuple or posting list location, depending on the index entry type.
- itemlen - Size of the index tuple in bytes stored on the page.
- nulls - Indicates whether the indexed value contains NULL attributes.
- vars - Shows whether the indexed value includes variable-length data.
- data - Raw binary representation of the indexed key value stored in the index.
- dead - Indicates whether the index entry is marked as dead and eligible for cleanup.
- htid - Heap tuple identifier representing the main referenced row location.
- tids - List of heap tuple locations stored as a posting list when multiple rows share the same indexed key value.
You can also use raw page input:
SELECT *
FROM bt_page_items(get_raw_page('idx_btree_age', 1));
Useful for scanning multiple index pages.
Inspecting GIN Index Pages
GIN Meta Page
SELECT *
FROM gin_metapage_info(get_raw_page('idx_gin_tags', 0));
Result :
pending_head | pending_tail | tail_free_size | n_pending_pages | n_pending_tuples | n_total_pages | n_entry_pages | n_data_pages | n_entries | version --------------+--------------+----------------+-----------------+------------------+---------------+---------------+--------------+-----------+--------- 4294967295 | 4294967295 | 0 | 0 | 0 | 5 | 4 | 0 | 503 | 2(1 row)
In a GIN index, the pending list is a temporary storage area where new index entries are collected before they are merged into the main index structure.
- pending_head - Block number of the first page in the pending list where new GIN entries are temporarily stored before being merged into the main index.
- pending_tail - Block number of the last page in the pending list used to append incoming index entries.
- tail_free_size - Amount of remaining free space available in the last pending page.
- n_pending_pages - Total number of pages currently used by the pending list.
- n_pending_tuples - Number of index tuples waiting inside the pending list to be processed.
- n_total_pages - Total number of pages allocated for the entire GIN index.
- n_entry_pages - Number of pages that store the main GIN entry tree structure.
- n_data_pages - Number of pages used to store posting lists or posting trees for indexed values.
- n_entries - Total count of distinct indexed keys stored in the index.
- version - Internal storage format version of the GIN index metadata.
GIN Opaque Page Info
SELECT *
FROM gin_page_opaque_info(get_raw_page('idx_gin_tags', 2));
Result :
rightlink | maxoff | flags
-----------+--------+--------
4 | 0 | {leaf}
(1 row)
- rightlink - Block number of the next sibling page in the GIN index structure, allowing PostgreSQL to move sequentially to the following page during scans.
- maxoff - Maximum item offset stored on this page, which indicates how many entries are present; zero means no items exist on this page.
- flags - Describes the role of the page inside the GIN index, where leaf means the page is a leaf node that holds actual index entries rather than internal tree pointers.
Purpose:
Shows internal GIN storage details and flags.
Inspecting GiST Index Pages
GiST Page Items
SELECT *
FROM gist_page_items_bytea(
get_raw_page('idx_gist_salary', 1)
);
Result :
itemoffset | ctid | itemlen | dead | key_data
------------+--------+---------+------+----------------------------------------------------
1 | (8,34) | 24 | f | \x00000800220018401f38000000008666008d05e121031200
2 | (1,19) | 24 | f | \x00000100130018401f380000000086e500b301a813ad2300
(2 rows)
- ctid - Reference to the heap tuple or child page that this GiST entry points to.
- itemlen - Size of the index tuple stored in the page measured in bytes.
- dead - Indicates whether the GiST entry is marked as invalid and eligible for cleanup.
- key_data - Binary representation of the indexed key value
GiST Page Opaque Info
SELECT *
FROM gist_page_opaque_info(
get_raw_page('idx_gist_salary', 1)
);
Result :
lsn | nsn | rightlink | flags
------------+-----+-----------+--------
0/3154B450 | 0/1 | 4 | {leaf}
(1 row)
- lsn - WAL log sequence number showing the last modification applied to this GiST page.
- nsn - Node sequence number used by GiST to maintain consistency during page splits and concurrent operations.
- rightlink - Block number of the next sibling page, allowing traversal to the adjacent page in the GiST structure.
- flags - Indicates the role of the page inside the GiST tree, where leaf means the page stores actual index entries.
Inspecting HASH Index Pages
Hash Meta Page
SELECT *
FROM hash_metapage_info(get_raw_page('idx_hash_name', 0));
Result :
[ RECORD 1
magic | 105121344
version | 4
ntuples | 500
ffactor | 307
bsize | 8152
bmsize | 4096
bmshift | 15
maxbucket | 1
highmask | 3
lowmask | 1
ovflpoint | 1
firstfree | 0
nmaps | 1
procid | 400
spares | {0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
mapp | {3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
- magic - Identifier value that confirms the page belongs to a valid PostgreSQL hash index.
- version - Internal format version used by the hash index metadata structure.
- ntuples - Estimated number of index tuples currently stored in the hash index.
- ffactor - Fill factor that defines the expected number of tuples per bucket before expansion occurs.
- bsize - Size of each hash bucket page available for storing index tuples.
- bmsize - Size of the bitmap page used to track free overflow pages.
- bmshift - Bit shift value used internally for bitmap addressing calculations.
- maxbucket - Highest bucket number that currently exists in the hash index.
- highmask - Bitmask used to compute bucket numbers during hash lookups when the index grows.
- lowmask - Secondary bitmask used along with highmask to determine correct bucket mapping.
- ovflpoint - Indicates the split point level where overflow pages begin to be used.
- firstfree - Block number of the first reusable overflow page available.
- nmaps - Number of bitmap pages used for tracking overflow page allocation.
- procid - Identifier of the hash support procedure used by the index operator class.
- spares - Array showing how many overflow pages are reserved for each split level.
- mapp - Array containing block numbers of bitmap pages that manage overflow page tracking.
Hash Page Items
SELECT *
FROM hash_page_items(get_raw_page('idx_hash_name', 1));
Result :
itemoffset | ctid | data
------------+---------+----------
1 | (0,40) | 10098342
2 | (5,37) | 15090668
3 | (10,27) | 48135276
4 | (4,13) | 50489306
(4 rows)
- itemoffset - Position number of the hash index entry inside the page.
- ctid - Heap tuple location that the hash index entry points to, represented by block number and offset.
- data - Hash value generated from the indexed column, which PostgreSQL uses to determine the bucket placement.
Hash Page Stats
SELECT *
FROM hash_page_stats(get_raw_page('idx_hash_name', 1));
Result :
live_items | dead_items | page_size | free_size | hasho_prevblkno | hasho_nextblkno | hasho_bucket | hasho_flag | hasho_page_id
------------+------------+-----------+-----------+-----------------+-----------------+--------------+------------+---------------
236 | 0 | 8192 | 3428 | 1 | 4294967295 | 0 | 2 | 65408
(1 row)
- live_items - Number of active hash index tuples currently stored on this page.
- dead_items - Number of obsolete index entries that can be removed during cleanup.
- page_size - Total size of the page in bytes.
- free_size - Amount of unused space remaining on the page.
- hasho_prevblkno - Block number of the previous page in the overflow chain.
- hasho_nextblkno - Block number of the next overflow page, where 4294967295 indicates no next page.
- hasho_bucket - Bucket number to which this hash page belongs.
- hasho_flag - Internal flag describing the role or state of the hash page.
- hasho_page_id - Identifier used internally by PostgreSQL to verify the page type and integrity.
Hash Page Type
SELECT *
FROM hash_page_type(get_raw_page('idx_hash_name', 1));
Result :
hash_page_type
----------------
bucket
(1 row)
- hash_page_type - Indicates the role of the inspected page within the hash index structure, where bucket means the page stores primary hash index entries belonging to a specific bucket.
Free Space Map Inspection
SELECT *
FROM fsm_page_contents(
get_raw_page('res_users', 'fsm', 0)
);
Result :
fsm_page_contents
-------------------
0: 227 +
1: 227 +
3: 227 +
7: 227 +
15: 227 +
31: 227 +
63: 227 +
127: 227 +
255: 227 +
511: 227 +
1023: 227 +
2047: 227 +
4095: 227 +
fp_next_slot: 0 +
(1 row)
Purpose:
- fsm_page_contents - Shows the free space information stored in the Free Space Map for different heap pages, where each number represents the approximate amount of available free space recorded for a page.
The pageinspect extension is not meant for daily application queries. Instead, it is designed for advanced users who want to understand PostgreSQL storage internals.
Key benefits:
- Understand how PostgreSQL stores rows physically
- Learn index structure deeply
- Debug storage anomalies
- Study MVCC visibility
- Validate index layout during research
- Explore PostgreSQL source-code behavior using real page data
For developers working on PostgreSQL internals or database optimization, this extension bridges the gap between SQL-level operations and storage-level implementation.
The pageinspect extension opens a window into PostgreSQL’s internal storage engine. By combining get_raw_page with specialized inspection functions, you can analyze heap pages, index structures, and free space management directly from SQL.