Internals of PostgreSQL - Chapter 1 Quick summary
Tags: postgres, database
Published: 2023-04-24 08:12:40

Section 1 : **Logical Structure**  

**Database Cluster ( in PostgreSQL )**
- is a collection of databases managed by a PostgreSQL server. 
- does not mean a group of database servers. 

**PostgreSQL server**
- it runs on a single host and manages a single database cluster.


**Database Objects**
- PostgreSQL is a Object-Relational model Database 
- a database is a collection of database objects.
- it is a data structure used either to store or to reference data.

- database objects are : 
   - Databases itself 
   - Tables ( Heap table )
   - Indexes
   - Sequences
   - Views 
   - Functions 

- database itself is a database object. 
- database ( as an object ) are logically separated , which means doesn't belong to any other object.
- other objects belongs to their respective databases.

**object identifiers**
- also known as OIDs
- all the database objects in PostgreSQL are internally managed by them , where each object has it's unique OID that represent/refer it.
- each OID is represented as unsigned 4-byte integers.
- system catalogs is the place where you can find more about the relation between database objects and their OID.



Section 2 : **Physical Structure**


**Database cluster**
- it's basically one directory referred to as base directory.
- it contains some subdirectories and lots of files.
- the path of the base directory is usually set to the environment variable PGDATA

**Database**
- it's is a subdirectory under the base subdirectory.
- those subdirectories of databases ( database directories ) are named identical to the respective OIDs.

- **Tables / indexes** : 

    - each table/index relative to the Database has at least one file stored under this subdirectory.
    - once the table/index size exceeds 1 GB , it will have more than one file related to it , as PostgreSQL creates a new file ( for this extra needed space )

     **Note :** 
     - extra files naming is "relfilenode_value.x" , where x starts at 1.
     - maximum size of table/index can be changed using the configuration.


     - **Refilenode**
           - it's a variable that manages data files of tables/indexes.
           - value of this variable not always matches OIDs.
           - it's changeable for tables/indexes , can be changed by some commands , which means commands can assign new refilenode value for tables/indexes it's not fixed. 


- **Free space map**
   - also referred to as FSM
   - storing the information of the free space capacity on each page within the table file.

- **Visibility map**
   - also referred to as VM
   - storing the information of the visibility on each page within the table file.


**N.B** 
Tables have both free space map(FSM) and visibility map (VM).
Indexes have only free space map (FSM).
both Free space map and Visibility map known as **forks**.





**Tablespace**
- PostgreSQL supports tablespaces.
- here PostgreSQL defines tablespace as it is one directory that contains some data outside of the base directory.
- is an additional data area outside the base directory.



**Data File**
- data file it is divided into pages (or blocks) of fixed length
- default size of data file is 8 KB
- pages are enumerated sequentially ( starting from page 0 ) 
- number of page known as block / page number 
- once file has been filled up , PostgreSQL add new page to end of file ( extend / increase file size )
- Internal layout of pages depends on the data file types.
- examples of data files : Table , index , FSM ( Free space map ) , VM ( visibility map )



**Page layout ( specific of Heap Table data file )**
- each page contain 3 kinds of data 
   - heap tuple(s) 
   - line pointer(s)
   - header data

**heap tuple**
- it represent a record of data itself 
- heap tuples of a page stacked in order from bottom to up ( top-most is the latest/newest record/tuple )

**Line pointers**
- each line pointer is 4 byte. 
- each line pointer holds a pointer to specific heap tuple. 
- line pointer also known as item pointer.
- line pointers together forms a simple array
- this array numbered sequentially ( start from 1 ).
- the index/number of line pointer is called offset number.

Notes : 
- heap tuples and line pointers grows towards each other 
- line pointers start exactly after the header data and grow downward towards heap tuples.  
- heap tuples start from the end of the page and grow upward towards line pointers.
- for each new added heap tuple , a line pointer ( item pointer ) is created and added to line pointers to point to this newly added tuple.
- the free space that both grow to is called the free space or hole.
 



**Header Data**
- has a fixed predefined structure.
- allocated in the beginning of the page.
- it's 24 byte long.
- it contains general information about the page.


**Tuple identifier**
- also known as TID.
- identifies a tuple within the table.
- TID structure : <block/page number , offset number of line pointer>


Notes :
- type of page we mention is called a slotted page.
- the line pointers correspond to a slot array.

**TOAST**
- known as The Oversized-Attribute Storage Technique.
- a technique used to manage the heap tuple of size greater than 2 KB 



**pd_lower**
- one of Header Data fields of the page.
- points to the latest added line pointer. 

- **pd_upper**
- one of Header Data fields of the page. 
- points to the latest added heap tuple.  


**Writing Heap Tuples**

- on adding new Heap tuple 
   - a new line pointer is created at end of line pointers it 
   - this line pointer , points to the added Heap tuple
   - pd_lower changes it's value to point to this line pointer ( newly created one )
   - pd_upper changes it's value to point to this new Heap tuple.
   - also other header data within this page are rewritten to appropriate values.



**Reading Heap Tuples**
- Types of access methods : 
   - Sequential scan. 
   - B-tree index scan.
   - TID-Scan.
   - Bitmap-Scan.
   - Index-Only-Scan.


**Sequential scan**
- All tuples in all pages are sequentially read by scanning all line pointers in each page


**Index File**
- contains index tuples.
- each index tuple is composed of index key and a TID pointing to the target heap tuple.


**B-tree index scan**
- it make use of Index File.
- not explained in this chapter.






Book is Internals of PostgreSQL 
Author Hironobu SUZUKI.


links : 
https://www.interdb.jp/pg/pgsql01.html