Page Layout
The page layout is illustrated below:
It consists of the following five parts:
- Page Header:
PageHeaderDataoccupies 24 bytes, storing page metadata. - Line Pointer Array: Each line pointer
ItemIdDataoccupies 4 bytes, storing pointers to heap tuples, containing<offset, length>information. - Free Space: Unused page space.
- Heap Tuples: The data records themselves, stacked in reverse order from the bottom of the page.
- Special Space: The
special space, used in certain index pages; for ordinary tables (non-index), this area is empty. (The special space is a special data area used only by indexes, containing specific data whose content varies by index type.)
The page layout is as follows:
/* PageHeaderData 24 bytes Line pointer array, 4 bytes each
* +----------------+---------------------------------+
* | PageHeaderData | linp1 linp2 linp3 ... |
* +-----------+----+---------------------------------+
* | ... linpN | |
* +-----------+--------------------------------------+
* | ^ pd_lower (free space start) |
* | middle area is free space |
* | v pd_upper (free space end) |
* +-------------+------------------------------------+
* | | tupleN ... |
* +-------------+------------------+-----------------+
* | ... tuple3 tuple2 tuple1 | "special space" |
* +--------------------------------+-----------------+
* ^ pd_special (pd_special in page header
* points to the start of special space)
*/Note: In PostgreSQL, besides table (including TOAST tables) and index pages, there are other page types. Table and index pages follow the layout above, while pages such as FSM pages and VM pages have different layouts.
Page Header
The page header stores metadata about the page. Through the page header, we can calculate the current page’s free space size, the number of tuples in the page, etc.
typedef struct PageHeaderData
{
PageXLogRecPtr pd_lsn; /* LSN of the XLOG record written by
* the last modification to this page */
uint16 pd_checksum; /* page checksum */
uint16 pd_flags; /* flag bits */
LocationIndex pd_lower; /* start of free space */
LocationIndex pd_upper; /* end of free space */
LocationIndex pd_special; /* start of special space */
uint16 pd_pagesize_version; /* page size, page layout version */
TransactionId pd_prune_xid; /* oldest prunable XID on the page,
* or 0 if none. If set, some records
* on the page can be reclaimed by vacuum */
ItemIdData pd_linp[FLEXIBLE_ARRAY_MEMBER]; /* line pointer array */
} PageHeaderData;
typedef PageHeaderData *PageHeader;Let’s look at a practical example:
-- truncate the table
postgres=# truncate table t1;
TRUNCATE TABLE
-- insert one tuple
postgres=# insert into t1 values(1,1);
INSERT 0 1
-- view page header info. At this point, the table has 1 page with one tuple,
-- the tuple is 32 bytes. Since it's a regular table, special space is empty.
postgres=# select * from page_header(get_raw_page('t1',0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
------------+----------+-------+-------+-------+---------+----------+---------+-----------
3/20467CA0 | 0 | 0 | 28 | 8160 | 8192 | 8192 | 4 | 0
(1 row)
-- view the tuple: tuple size 32 bytes, header 24 bytes, data 8 bytes,
-- tuple starts at offset 8160
postgres=# SELECT * FROM heap_page_item_attrs(get_raw_page('t1', 0), 't1'::regclass);
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_attrs
----+--------+----------+--------+---------+--------+----------+--------+-------------+------------+--------+--------+-------+-------------------------------
1 | 8160 | 1 | 32 | 4027950 | 0 | 0 | (0,1) | 2 | 2048 | 24 | | | {"\\x01000000","\\x01000000"}
(1 row)The pd_flags flag bits are as follows:
#define PD_HAS_FREE_LINES 0x0001 /* are there any unused line pointers? */
#define PD_PAGE_FULL 0x0002 /* set if an UPDATE cannot find enough
* free space on the page for its new
* tuple version */
#define PD_ALL_VISIBLE 0x0004 /* all rows are visible */The LSN is used by the buffer manager to enforce the fundamental WAL rule: “WAL must be written before data.” A dirty buffer page cannot be flushed to disk until the xlog flush position has advanced past this page’s LSN.
Line Pointers
Line pointers indicate the position of tuples within a page. Through line pointers, tuples can be located. Additionally, the number of tuples in a page can be determined from the length of the line pointer array.
typedef struct ItemIdData
{
unsigned lp_off:15, /* tuple offset from the start of the page */
lp_flags:2, /* status bits */
lp_len:15; /* tuple length */
} ItemIdData;
typedef ItemIdData *ItemId;Getting the number of rows in a page, calculated as: (pd_lower - page header size) / line pointer size
#define PageGetMaxOffsetNumber(page) \
(((PageHeader) (page))->pd_lower <= SizeOfPageHeaderData ? 0 : \
((((PageHeader) (page))->pd_lower - SizeOfPageHeaderData) \
/ sizeof(ItemIdData)))Getting a tuple from a line pointer (using lp_off and lp_len to locate a tuple on the page):
#define PageGetItem(page, itemId) \
( \
AssertMacro(PageIsValid(page)), \
AssertMacro(ItemIdHasStorage(itemId)), \
(Item)(((char *)(page)) + ItemIdGetOffset(itemId)) \
)lp_flags has the following states; only LP_UNUSED line pointers can be reused immediately:
#define LP_UNUSED 0 /* unused (lp_len=0) */
#define LP_NORMAL 1 /* in use (lp_len>0) */
#define LP_REDIRECT 2 /* HOT redirect (lp_len=0),
* redirects to another line pointer */
#define LP_DEAD 3 /* dead line pointer, may or may not
* have storage */HOT (Heap Only Tuple): When updating a row, the new row can be placed in the same data page as the old row with relevant flag bits set. The main purpose is to reduce index updates when updating table tuples.
Heap Tuples
Tuple Identifier (TID)
A record in a table corresponds to a tuple on a page, but due to Multi-Version Concurrency Control (MVCC), multiple versions of the same record may exist, each being a separate tuple. Which version is used is determined by visibility rules.
To identify a tuple, the database internally uses the TID (Tuple Identifier) as its identifier. The TID consists of the page number where the tuple resides and the offset number of the line pointer within that page.
// Tuple identifier definition:
typedef struct ItemPointerData
{
BlockIdData ip_blkid; // block number
OffsetNumber ip_posid; // row number
}
/* If compiler understands packed and aligned pragmas, use those */
#if defined(pg_attribute_packed) && defined(pg_attribute_aligned)
pg_attribute_packed() // force compact layout, no padding bytes
pg_attribute_aligned(2) // specify 2-byte alignment for the struct
#endif
ItemPointerData;
// Through the definition above, it occupies 6 bytes, saving 2 bytes
// (with default alignment padding it would occupy 8 bytes)
typedef ItemPointerData *ItemPointer;
// BlockNumber storage format; defined this way to save space
typedef struct BlockIdData
{
uint16 bi_hi;
uint16 bi_lo;
} BlockIdData;During an index scan, a tuple can be located via its TID.
Tuple Layout
In addition to storing actual data, a tuple also stores some extra system columns used for transaction processing and various flag bits. All database operations — INSERT, DELETE, UPDATE, SELECT — operate at the tuple level. Pages, files, and other structures can to some extent be seen as containers for tuples. Tuples are the core data.
The tuple layout is as follows:
+------------------------+
| HeapTupleHeaderData | -- fixed header (23 bytes)
+------------------------+
| NULLs bitmap (optional) | -- null bitmap, present if HEAP_HASNULL
| | is set in t_infomask
+------------------------+
| padding bytes | -- ensures user data is MAXALIGN-aligned,
| | typically a multiple of 8
+------------------------+ <- t_hoff starts here, user data offset
| user data fields |
| - field1 |
| - field2 |
| - ... | -- alignment padding may also be needed
+------------------------+ between user data fieldsThe tuple header fields are as follows:
- t_xmin: Transaction ID that created this tuple.
- t_xmax: Transaction ID that deleted this tuple.
- t_cid: Command ID within the inserting/deleting transaction.
- t_infomask2: Flag bits; the lower 11 bits indicate the number of attributes, and the remaining bits are used for HOT and tuple visibility flags.
- t_infomask: Flag bits.
- t_hoff: Size of the tuple header (including bitmap and padding), also the start offset of user data. It must always be a multiple of
MAXALIGN. - t_bits: Null bitmap, indicating which fields of the tuple are NULL. If no attributes are NULL, this field is empty. It exists only when
HEAP_HASNULLint_infomaskis set. When present, the bitmap must be large enough to hold one bit per data column (the number of bits equals the attribute count int_infomask2). In the bitmap, 1 means non-null, 0 means null.
t_infomask2 flag bits:
#define HEAP_NATTS_MASK 0x07FF /* lower 11 bits indicate attribute count */
#define HEAP_KEYS_UPDATED 0x2000 /* marks that the tuple's index key columns
* were updated or the tuple was deleted */
#define HEAP_HOT_UPDATED 0x4000 /* marks that the tuple was updated via HOT */
#define HEAP_ONLY_TUPLE 0x8000 /* marks the tuple as a Heap Only Tuple */For example: t_infomask2 lower 11 bits are 00000000010, indicating 2 attributes.
postgres=# SELECT t_xmin,t_xmax,t_ctid,t_infomask2::bit(16),t_infomask::bit(16),t_hoff,t_bits,t_attrs FROM heap_page_item_attrs(get_raw_page('t1', 0), 't1'::regclass);
t_xmin | t_xmax | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_attrs
---------+--------+--------+------------------+------------------+--------+--------+-------------------------------
4027950 | 0 | (0,1) | 00000,00000000010 | 0000,1001,0000,0000 | 24 | | {"\\x01000000","\\x01000000"}
(1 row)t_infomask flag bits:
#define HEAP_HASNULL 0x0001 /* contains NULL values; determines
* whether null bitmap is needed */
#define HEAP_HASVARWIDTH 0x0002 /* contains variable-length fields */
#define HEAP_HASEXTERNAL 0x0004 /* has fields stored externally via TOAST */
#define HEAP_HASOID_OLD 0x0008 /* contains an OID field (deprecated) */
#define HEAP_XMAX_KEYSHR_LOCK 0x0010 /* xmax lock type: for key share */
#define HEAP_COMBOCID 0x0020 /* t_cid is a combo command ID,
* used for complex intra-transaction ops */
#define HEAP_XMAX_EXCL_LOCK 0x0040 /* xmax lock type: exclusive (for update) */
#define HEAP_XMAX_LOCK_ONLY 0x0080 /* xmax is only a lock */
#define HEAP_XMIN_COMMITTED 0x0100 /* the transaction in t_xmin has committed */
#define HEAP_XMIN_INVALID 0x0200 /* the transaction in t_xmin is invalid
* or aborted; the tuple is invisible
* to all transactions */
#define HEAP_XMAX_COMMITTED 0x0400 /* the transaction in t_xmax has committed,
* meaning the deleting transaction committed */
#define HEAP_XMAX_INVALID 0x0800 /* the transaction in t_xmax is invalid
* or aborted */
#define HEAP_XMAX_IS_MULTI 0x1000 /* t_xmax is a MultiXactId, not a regular
* transaction ID; used when multiple
* transactions lock the same row */
#define HEAP_UPDATED 0x2000 /* this tuple was produced by an UPDATE;
* the new version */
#define HEAP_MOVED_OFF 0x4000 /* deprecated since 9.0; kept only for
* binary upgrade compatibility */
#define HEAP_MOVED_IN 0x8000 /* deprecated since 9.0; kept only for
* binary upgrade compatibility */Example:
-- the page currently has one tuple
postgres=# select * from t1;
a | b
---+---
1 | 1
(1 row)
-- update
postgres=# update t1 set b = 2;
UPDATE 1
-- view tuple info, observe the flag bit changes
-- old version tuple: t_infomask flag: HEAP_XMIN_COMMITTED
-- updated new tuple, t_infomask flag: HEAP_UPDATED | HEAP_XMIN_COMMITTED
postgres=# SELECT t_xmin,t_xmax,t_ctid,t_infomask2::bit(16),t_infomask::bit(16),t_hoff,t_bits,t_attrs FROM heap_page_item_attrs(get_raw_page('t1', 0), 't1'::regclass);
t_xmin | t_xmax | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_attrs
---------+---------+--------+------------------+------------------+--------+--------+-------------------------------
4027950 | 4027952 | (0,2) | 0100,0000,0000,0010 | 0000,0001,0000,0000 | 24 | | {"\\x01000000","\\x01000000"}
4027952 | 0 | (0,2) | 1000,0000,0000,0010 | 0010,1000,0000,0000 | 24 | | {"\\x01000000","\\x02000000"}
(2 rows)Note: Tuple visibility flags such as
HEAP_XMIN_COMMITTEDare not set at transaction commit time. Instead, they are set when a subsequent DML or vacuum SQL operation scans the tuple and determines whether the transaction is visible (HeapTupleSatisfiesVisibility). During this process, if the transaction is found to be committed, theSetHintBitsfunction sets the corresponding flag. This is why read-only queries can also generate write I/O.
Kernel implementation:
// Transaction visibility info, occupies 12 bytes
typedef struct HeapTupleFields
{
TransactionId t_xmin; /* transaction ID that inserted the tuple */
TransactionId t_xmax; /* transaction ID that deleted the tuple */
union
{
CommandId t_cid; /* command ID (within the same transaction)
* that inserted or deleted the tuple */
TransactionId t_xvac; /* deprecated since PG 8.4;
* old-style VACUUM FULL xact ID */
} t_field3;
} HeapTupleFields;
// Tuple header
struct HeapTupleHeaderData
{
union
{
HeapTupleFields t_heap; /* on-disk form */
DatumTupleFields t_datum; /* in-memory form */
} t_choice;
ItemPointerData t_ctid; /* current TID of this or newer tuple;
* if the tuple was updated, stores the
* physical location of the new version */
/* Fields below here must match MinimalTupleData! */
#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK2 2
uint16 t_infomask2; /* attribute count (lower 11 bits) + flags */
#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK 3
uint16 t_infomask; /* flag bits */
#define FIELDNO_HEAPTUPLEHEADERDATA_HOFF 4
uint8 t_hoff; /* tuple header size (including bitmap, padding) */
/* ^ - fixed header size 23 bytes - ^ */
#define FIELDNO_HEAPTUPLEHEADERDATA_BITS 5
bits8 t_bits[FLEXIBLE_ARRAY_MEMBER]; /* null bitmap
* (variable size, may be empty) */
/* user data fields follow here */
};We can examine tuple data using the pageinspect extension. The tuple header is byte-aligned; t_hoff is always a multiple of MAXALIGN (typically 8).
postgres=# create table padding4(a1 bool,a2 bool,a3 bool,a4 bool,a5 bool,a6 bool,a7 bool,a8 bool,a9 bool);
CREATE TABLE
postgres=# insert into padding4 values(true);
INSERT 0 1
-- tuple length 33 bytes: header 32 bytes, user data 1 byte,
-- header = 23 + 2 + 7 (padding)
postgres=# select * from heap_page_items(get_raw_page('padding4',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+---------+--------+----------+--------+-------------+------------+--------+------------------+-------+--------
1 | 8152 | 1 | 33 | 4027920 | 0 | 0 | (0,1) | 9 | 2049 | 32 | 1000000000000000 | | \x01
(1 row)In addition to header-level padding, alignment padding may also occur between user data fields:
postgres=# create table padding(a bool, b integer);
CREATE TABLE
postgres=# insert into padding values(true);
INSERT 0 1
-- lp_len: tuple length
-- t_hoff: tuple header length, user data offset
-- t_bits: null bitmap
postgres=# select * from heap_page_items(get_raw_page('padding',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+---------+--------+----------+--------+-------------+------------+--------+----------+-------+--------
1 | 8160 | 1 | 25 | 4027914 | 0 | 0 | (0,1) | 2 | 2049 | 24 | 10000000 | | \x01
(1 row)
postgres=# update padding set b = 1;
UPDATE 1
-- after the update, t_ctid is set to the new tuple's TID;
-- the old tuple's t_xmax is set to the new tuple's transaction ID,
-- indicating the tuple has been deleted.
-- lp_len changed from 25 to 32 bytes: 24 + 1 + 4, with 3 bytes for alignment padding
postgres=# select * from heap_page_items(get_raw_page('padding',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+---------+---------+----------+--------+-------------+------------+--------+----------+-------+--------------------
1 | 8160 | 1 | 25 | 4027914 | 4027915 | 0 | (0,2) | 16386 | 257 | 24 | 10000000 | | \x01
2 | 8128 | 1 | 32 | 4027915 | 0 | 0 | (0,2) | 32770 | 10240 | 24 | | | \x0100000001000000
(2 rows)To avoid space waste from alignment padding, you can optimize the column order in a table:
-- by rearranging column order, reduce tuple size and padding
postgres=# create table padding3(a integer, b bool);
CREATE TABLE
postgres=# insert into padding3 values(1);
INSERT 0 1
-- with one NULL value, tuple size is 23 + 1 + 4 = 28.
-- Note that the null is represented via the null bitmap, with no data written.
postgres=# select * from heap_page_items(get_raw_page('padding3',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+---------+--------+----------+--------+-------------+------------+--------+----------+-------+------------
1 | 8160 | 1 | 28 | 4027917 | 0 | 0 | (0,1) | 2 | 2049 | 24 | 10000000 | | \x01000000
(1 row)
-- update the tuple
postgres=# update padding3 set b = true;
UPDATE 1
-- the updated tuple size is 29 = 23 + 1 + 4 + 1.
-- Compared to the `padding` table, each tuple saves 3 padding bytes.
postgres=# select * from heap_page_items(get_raw_page('padding3',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+---------+---------+----------+--------+-------------+------------+--------+----------+-------+--------------
1 | 8160 | 1 | 28 | 4027917 | 4027918 | 0 | (0,2) | 16386 | 257 | 24 | 10000000 | | \x01000000
2 | 8128 | 1 | 29 | 4027918 | 0 | 0 | (0,2) | 32770 | 10240 | 24 | | | \x0100000001
(2 rows)