This blog aims to assist you in understanding the concepts of Chapter:1 [Database Cluster, Databases and Tables] from the book The Internals of PostgreSQL.
So, Let's Start:
Logical Structure of Database Cluster
- In PostgreSQL, a database cluster refers to a collection of databases managed by a single PostgreSQL server running on a host.
- It is important to note that the term database cluster does not imply a group of database servers.
The logical structure of a database cluster is depicted in figure below:
In the context of relational database theory, a database object is a data structure used for storing or referencing data. Examples of database objects include tables, indexes, sequences, views, and functions.
In PostgreSQL, databases themselves are considered as database objects and are logically separated from each other. All other database objects, such as tables and indexes, are associated with their respective databases within the cluster.
All the database objects in PostgreSQL are internally managed by respective object identifiers (OIDs), which are unsigned 4 byte integers.
Physical Structure of Database Cluster
- A database cluster basically is one directory referred to as base directory.
An example of database cluster is depicted in figure below:
Tablespaces
- A tablespace in PostgreSQL is an additional data area outside the base directory
A Tablespace in the Database Cluster is depicted in figure below:
Internal Layout of a Heap Table File
In PostgreSQL, the data file, which includes heap tables, indexes, free space maps, and visibility maps, is divided into fixed-length pages or blocks. The default size of each page is 8192 bytes (8 KB). These pages are sequentially numbered starting from 0 and are referred to as block numbers.
When a file reaches its capacity and is filled up, PostgreSQL extends the file size by adding a new empty page at the end. This allows for additional space to accommodate more data within the file.
A Page layout of a heap table file is depicted in figure below:
- A page within a table contains three kinds of data.
Heap Tuple: A heap tuple is a record data itself. They are stacked in order from the bottom of the page.
Line Pointer: A line pointer is "4 byte" long and holds a pointer to each heap tuple. It is also called an item pointer. Line pointers form a simple array, which plays the role of index to the tuples. Each index is numbered sequentially from 1, and called offset number. When a new tuple is added to the page, a new line pointer is also pushed onto the array to point to the new one.
Header Data: It is 24 byte long and contains general information about the page.
An empty space between the end of line pointers and the beginning of the newest tuple is referred to as free space or hole.
In PostgreSQL, a tuple identifier (TID) is used to identify a specific tuple within a table.
The TID consists of two values: the block number of the page that holds the tuple and the offset number of the line pointer that references the tuple within that page. This combination of block number and offset allows PostgreSQL to locate and retrieve the desired tuple within the table.
The Methods of Writing and Reading Tuples
Writing Heap Tuples
In a table with one page containing only one heap tuple, the page's pd_lower points to the first line pointer, and both the line pointer and pd_upper point to the first heap tuple (See 'a' part in figure below).
When a second tuple is inserted into the table, it is placed after the first tuple. The second line pointer is pushed onto the first line pointer, and it points to the second tuple.
Consequently, the pd_lower is updated to point to the second line pointer, and the pd_upper now points to the second heap tuple (See 'b' part in figure below).
Additionally, other header data within the page, such as pd_lsn, pg_checksum, and pg_flag, are also appropriately rewritten.
Example of Writing of a heap tuple is depicted in figure below:
Reading Heap Tuples
- There are two ways to read heap tuples.
Sequential Scan: All tuples in all pages are sequentially read by scanning all line pointers in each page (See 'a' part in figure below).
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.
- 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 (See 'b' part in figure below).
Example of Sequential Scan and Index Scan is depicted in figure below:
I hope, this blog has helped you in understanding the concepts of Chapter:1 [Database Cluster, Databases and Tables].
Check out summary of Chapter : 2
If you want to understand PostgreSQL In-Depth.
Top comments (0)