[SOLVED] CS计算机代考程序代写 PostgreSQL Page Internals

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 uint16LocationIndex;

// 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
{
XLogRecPtrpd_lsn;// xact log record for last change
uint16pd_tli;// xact log reference information
uint16pd_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
uint16pd_pagesize_version;
TransactionId pd_prune_xid;// is pruning useful in data page?
ItemIdDatapd_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 dataTOAST’d values may be compressedTOAST’d values are stored in 2K chunksStrategies 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 storageCOMP9315 21T1 ♢ PG Page Internals ♢ [8/8]Produced: 23 Feb 2021