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 :
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.
Top comments (0)