PostgreSQL Pages and Tuples

INTERNAL LAYOUT:

• a tuple or an item is a synonym for a row
• a relation is a synonym for a table
• a filenode is an id which represent a reference to a table or an index.
• a block and page are equals and they represent a 8kb segment information the file storing the table.
• a heap refer to heap file. Heap files are lists of unordered records of variable size. Although sharing a similar name, heap files are different from heap data structure.
• CTID represent the physical location of the row version within its table. CTID is also a special column available for every tables but not visible unless specifically mentioned. It consists of a page number and the index of an item identifier.
• OID stands for Object Identifier.
• database cluster, we call a database cluster the storage area on disk. A database cluster is a collection of databases that is managed by a single instance of a running database server.

Page:

        Inside the data file (heap table and index, as well as the free space map and visibility map),

it is divided into pages (or blocks) of fixed length, the default is 8192 byte (8 KB). 

        Those pages within each file are numbered sequentially from 0, and such numbers are called as block numbers. If the file has been filled up, PostgreSQL adds a new empty page to the end of the file to increase the file size. 

Writing of a heap tuple:

Reading Heap Tuples:

Two typical access methods, sequential scan and index scan:

Sequential scan:

All tuples in all pages are sequentially read by scanning all line pointers in each page.

B-tree index scan: 

        An index file contains index tuples, each of which is composed of an index key and a TID pointing to the target heap tuple. If the index tuple with the key that you are looking for has been found, PostgreSQL reads the desired heap tuple using the obtained TID value. 

For example, TID value of the obtained index tuple is ‘(block = 7, Offset = 2)’. 

It means that the target heap tuple is 2nd tuple in the 7th page within the table, so PostgreSQL can read the desired heap tuple without unnecessary scanning in the pages.

pearl=# select relpages from pg_class where relname='emp';

 relpages 

———-

     2703

(1 row)

yum install postgresql10-contrib

pearl=# create extension pageinspect ;

CREATE EXTENSION

pearl=# select * from page_header(get_raw_page('emp',0));

    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid 

————+———-+——-+——-+——-+———+———-+———+———–

 0/18FE12F8 |        0 |     0 |   764 |   792 |    8192 |     8192 |       4 |         0

(1 row)

Tuple:

Heap tuples in table pages are classified as a usual data tuple and a TOAST tuple.

Tuple Structure:

Inserting, Deleting and Updating Tuples:

Inserting:

 

Deleting:

 

Updating:

work scenarios examples

Find current transaction id:

pearl=# select txid_current();
 txid_current
————–
          620
(1 row)

pearl=# select xmin,xmax,ctid,* from emp limit 5;
 xmin | xmax | ctid  | id |  name  
——+——+——-+—-+——–
  617 |    0 | (0,1) |  1 | qwerty
  617 |    0 | (0,2) |  2 | qwerty
  617 |    0 | (0,3) |  3 | qwerty
  617 |    0 | (0,4) |  4 | qwerty
  617 |    0 | (0,5) |  5 | qwerty
(5 rows)

Find Free space map:

pearl=# create extension pg_freespacemap ;
CREATE EXTENSION

pearl=# select * from pg_freespace('emp') limit 5;
 blkno | avail
——-+——-
     0 |     0
     1 |     0
     2 |     0
     3 |     0
     4 |     0
(5 rows)

Find Dead tuple count and size:

pearl=# select * from pg_stat_all_tables where relname = 'emp';
-[ RECORD 1 ]——-+———————————
relid                             | 24626
schemaname                | public
relname                        | emp
seq_scan                      | 2
seq_tup_read               | 500005
idx_scan                  |
idx_tup_fetch         |
n_tup_ins                    | 500000
n_tup_upd                   | 0
n_tup_del                    | 250000
n_tup_hot_upd            | 0
n_live_tup                   | 250000
n_dead_tup                  | 250000
n_mod_since_analyze | 250000
last_vacuum         |
last_autovacuum     |
last_analyze               | 2020-07-31 10:14:03.501667+05:30
last_autoanalyze        |
vacuum_count           | 0
autovacuum_count    | 0
analyze_count           | 1
autoanalyze_count    | 0

pearl=# \x
Expanded display is off.

pearl=# create extension pgstattuple ;
CREATE EXTENSION

pearl=# select * from pgstattuple('emp');
-[ RECORD 1 ]——+———
table_len          | 22142976
tuple_count        | 250000
tuple_len          | 8750000
tuple_percent      | 39.52
dead_tuple_count   | 250000
dead_tuple_len     | 8750000
dead_tuple_percent | 39.52
free_space         | 67292
free_percent       | 0.3

postgres=# select * from pg_class where relname='emp';

postgres=# create extension pageinspect ;
CREATE EXTENSION

postgres=# select * from page_header(get_raw_page('emp',0));
-[ RECORD 1 ]--------
lsn       | 0/304F380
checksum  | 0
flags     | 4
lower     | 764
upper     | 792
special   | 8192
pagesize  | 8192
version   | 4
prune_xid | 0

postgres=# create extension pg_freespacemap ;
CREATE EXTENSION

postgres=# select * from pg_freespace('emp');
 blkno | avail
-------+-------
     0 |     0
     1 |     0
     2 |     0
     3 |     0
     4 |     0
     5 |     0
     6 |     0
     7 |     0
     8 |     0
     9 |     0