CS计算机代考程序代写 2021/4/28 PostgreSQL Page Internals

2021/4/28 PostgreSQL Page Internals
PostgreSQL Page Internals
PostgreSQL Page Representation TOAST’ing
>>
COMP9315 21T1 ♢ PG Page Internals ♢ [0/8]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pg-pages/slides.html
1/10

2021/4/28 PostgreSQL Page Internals
❖ PostgreSQL Page Representation
Functions: src/backend/storage/page/*.c Denitions: src/include/storage/bufpage.h Each page is 8KB (default BLCKSZ) and contains:
header (free space pointers, ags, 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)
Largedataitemsarestoredinseparate(TOAST)les (implicit) Alsosupports~SQL-standardBLOBs (explicitlargedataitems)
COMP9315 21T1 ♢ PG Page Internals ♢ [1/8]
∧ >>
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pg-pages/slides.html
2/10

2021/4/28 PostgreSQL Page Internals
<< ∧ >> ❖ PostgreSQL Page Representation (cont)
PostgreSQL page layout:
COMP9315 21T1 ♢ PG Page Internals ♢ [2/8]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pg-pages/slides.html
3/10

2021/4/28 PostgreSQL Page Internals
<< ∧ >> ❖ 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,
lp_flags:2,
lp_len:15;
// tuple offset from start of page
// unused,normal,redirect,dead
// length of tuple (bytes)
} ItemIdData;
COMP9315 21T1 ♢ PG Page Internals ♢ [3/8]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pg-pages/slides.html
4/10

2021/4/28 PostgreSQL Page Internals
<< ∧ >> ❖ PostgreSQL Page Representation (cont)
Page-related data types: (cont)
typedef struct PageHeaderData
{
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]
// xact log record for last change
// xact log reference information
// flag bits (e.g. free, full, …
// offset to start of free space
// offset to end of free space
XLogRecPtr
uint16
uint16
LocationIndex pd_lower;
LocationIndex pd_upper;
LocationIndex pd_special; // offset to start of special space
uint16 pd_pagesize_version;
pd_lsn;
pd_tli;
pd_flags;
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pg-pages/slides.html
5/10

2021/4/28 PostgreSQL Page Internals
<< ∧ >> ❖ 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,
size, …)
Item item, 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]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pg-pages/slides.html
6/10

2021/4/28 PostgreSQL Page Internals
<< ∧ >> ❖ 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 t more index entries per page
COMP9315 21T1 ♢ PG Page Internals ♢ [6/8]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pg-pages/slides.html
7/10

2021/4/28 PostgreSQL Page Internals
<< ∧ >>
❖ 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]
https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pg-pages/slides.html
8/10

2021/4/28 PostgreSQL Page Internals
<< ∧ ❖ TOAST'ing (cont) Large attribute values are stored out-of-line (i.e. in separate le) "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] https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pg-pages/slides.html 9/10 2021/4/28 PostgreSQL Page Internals Produced: 23 Feb 2021 https://cgi.cse.unsw.edu.au/~cs9315/21T1/lectures/pg-pages/slides.html 10/10