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='siva';

 relpages 

----------

     2703

(1 row)

yum install postgresql10-contrib

pearl=# create extension pageinspect ;

CREATE EXTENSION

pearl=# select * from page_header(get_raw_page('siva',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:

Query Options:

Find current transaction id:

pearl=# select txid_current();

 txid_current 

--------------

          620

(1 row)

pearl=# select xmin,xmax,ctid,* from siva 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('siva') 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 = 'siva';

-[ RECORD 1 ]-------+---------------------------------

relid                             | 24626

schemaname                | public

relname                        | siva

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('siva');

-[ 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