PostgreSQL Page Internals
>>
PostgreSQL Page Internals
PostgreSQL Page Representation
TOAST’ing
COMP9315 21T1 ♢ PG Page Internals ♢ [0/8]
∧ >>
❖ PostgreSQL Page Representation
Functions: src/backend/storage/page/*.c
Definitions: src/include/storage/bufpage.h
Each page is 8KB (default BLCKSZ) and contains:
header (free space pointers, flags, xact data)
array of (offset,length) pairs for tuples in page
free space region (between array and tuple data)
actual tuples themselves (inserted from end towards start)
(optionally) region for special data (e.g. index data)
Large data items are stored in separate (TOAST) files (implicit)
Also supports ~SQL-standard BLOBs (explicit large data items)
COMP9315 21T1 ♢ PG Page Internals ♢ [1/8]
<< ∧ >>
❖ PostgreSQL Page Representation (cont)
PostgreSQL page layout:
COMP9315 21T1 ♢ PG Page Internals ♢ [2/8]
<< ∧ >>
❖ PostgreSQL Page Representation (cont)
Page-related data types:
// a Page is simply a pointer to start of buffer
typedef Pointer Page;
// indexes into the tuple directory
typedef uint16 LocationIndex;
// entries in tuple directory (line pointer array)
typedef struct ItemIdData
{
unsigned lp_off:15, // tuple offset from start of page
lp_flags:2, // unused,normal,redirect,dead
lp_len:15; // length of tuple (bytes)
} ItemIdData;
COMP9315 21T1 ♢ PG Page Internals ♢ [3/8]
<< ∧ >>
❖ PostgreSQL Page Representation (cont)
Page-related data types: (cont)
typedef struct PageHeaderData
{
XLogRecPtr pd_lsn; // xact log record for last change
uint16 pd_tli; // xact log reference information
uint16 pd_flags; // flag bits (e.g. free, full, …
LocationIndex pd_lower; // offset to start of free space
LocationIndex pd_upper; // offset to end of free space
LocationIndex pd_special; // offset to start of special space
uint16 pd_pagesize_version;
TransactionId pd_prune_xid;// is pruning useful in data page?
ItemIdData pd_linp[1]; // beginning of line pointer array
} PageHeaderData;
typedef PageHeaderData *PageHeader;
COMP9315 21T1 ♢ PG Page Internals ♢ [4/8]
<< ∧ >>
❖ PostgreSQL Page Representation (cont)
Operations on Pages:
void PageInit(Page page, Size pageSize, …)
initialize a Page buffer to empty page
in particular, sets pd_lower and pd_upper
OffsetNumber PageAddItem(Page page,
Item item, Size size, …)
insert one tuple (or index entry) into a Page
fails if: not enough free space, too many tuples
void PageRepairFragmentation(Page page)
compact tuple storage to give one large free space region
COMP9315 21T1 ♢ PG Page Internals ♢ [5/8]
<< ∧ >>
❖ PostgreSQL Page Representation (cont)
PostgreSQL has two kinds of pages:
heap pages which contain tuples
index pages which contain index entries
Both kinds of page have the same page layout.
One important difference:
index entries tend be a smaller than tuples
can typically fit more index entries per page
COMP9315 21T1 ♢ PG Page Internals ♢ [6/8]
<< ∧ >>
❖ TOAST’ing
TOAST = The Oversized-Attribute Storage Technique
handles storage of large attribute values (> 2KB) (e.g. long text)
COMP9315 21T1 ♢ PG Page Internals ♢ [7/8]
<< ∧ ❖ TOAST'ing (cont) Large attribute values are stored out-of-line (i.e. in separate file) "value" of attribute in tuple is a reference to TOAST data TOAST'd values may be compressed TOAST'd values are stored in 2K chunks Strategies for storing TOAST-able columns ... PLAIN ... allows no compression or out-of-line storage EXTENDED ... allows both compression and out-of-line storage EXTERNAL ... allows out-of-line storage but not compression MAIN ... allows compression but not out-of-line storage COMP9315 21T1 ♢ PG Page Internals ♢ [8/8] Produced: 23 Feb 2021