Chapter # 01:

Knowing PostgreSQL — Database Clusters, Databases and Tables

Purpose of writing this blog is to introduce all of you with Database Clusters and summarizes basic knowledge of PostgreSQL that is actually example of database cluster.

The following topics are covered in this article:

• A database cluster’s logical structure
• A database cluster’s physical structure
• A heap table file’s internal structure
• The procedures for writing and reading data into a table

1. Logical Structure of Database Cluster:

A database cluster is a collection of databases managed by a single PostgreSQL server.

Fig 1: Presents logical structure of Database Cluster:

• Database: A database is a collection of database objects.
• Database Objects: A database object is a data structure used either to store or to reference data.A table (heap) is a good example and there are many other tables like indexes, sequences, views, functions, etc.
• PostgreSQL,the database itself is also a database object and is logically separated from one another.
• All database objects in PostgreSQL are internally managed by their respective Object Identifiers (OIDs), which are 4-byte unsigned integers.
• The relationships between database objects and their respective OIDs are stored in appropriate system catalogues.

2. Physical Structure of Database Cluster:

• A DB cluster is basically a directory called base directory and it contains sub-directories and lots of files.

Fig2: An example of database cluster.

• The database is a subdirectory within the base subdirectory, and each table and index is (at least) a file stored in the subdirectory of the database it belongs to. There are also a number of subdirectories containing configuration files and specific data.
• Table spaces in PostgreSQL: A tablespace in PostgreSQL is one directory that contains some data outside of the base directory.

2.1: Layout of a Database Cluster:

• Describing some file and sub-directories and their description. For example:

Files:

PG_VERSION :A file containing the major version number of PostgreSQL

postgresql.conf: A file to set configuration parameters

postgresql.auto.conf: A file used for storing configuration parameters that are set in ALTER SYSTEM (version 9.4 or later)

Sub-directories:

base/: Subdirectory containing per-database subdirectories.

global/: Subdirectory containing cluster-wide tables, such as pg_database and pg_control.

pg_stat/: Subdirectory containing permanent files for the statistics subsystem.

pg_tblspc/: Subdirectory containing symbolic links to tablespaces.

2.2: Layout of a Databases:

• A database is a sub-directory under the base sub-directory. The database directory names are the same as their respective OIDs.

2.3. Layout of Files Associated with Tables and Indexes:

Tables and indexes as database objects are managed internally by individual OIDs, but their data files are managed by relfilenode, variables.

• Some commands are used to change the relfilenode values of tables and indexes (e.g., TRUNCATE, REINDEX, CLUSTER).
• For table and index file sizes greater than 1GB, PostgreSQL creates and uses a new file named relfilenode.1. When the new file is full, the next new file is created with a name such as relfilenode.2.

• Each table has two associated files suffixed respectively with ‘_fsm’ and ‘_vm’.
• These are called free space maps and visibility maps, and each page in the spreadsheet stores information about free space capacity and visibility, respectively.
• Indexes only have individual free space maps and don’t have visibility map.
• They can also be referred to internally as forks of each relationship. The free space map is the first branch of the table/index data file(branch number is 1), the visibility map is the second branch of the table data file(branch number is 2&# 41; The fork number of the data file is 0.

2.4. Tablespaces:

A tablespace in PostgreSQL is an additional data area outside the base directory.

• A tablespace is created under the directory specified when issuing the CREATE TABLESPACE statement, and a version-specific subdirectory (for example, PG_14_202011044) is created under that directory.
• If you create a new table which belongs to the database created under the base directory, first, the new directory, whose name is the same as the existing database OID, is created under the version specific subdirectory, and then the new table file is placed under the created directory.

3. Internal Layout of a Heap Table File:

• The data file (heap table and index, as well as the free space map and visibility map) is divided into fixed-length pages (or blocks), the default being 8192 bytes (8 KB).
• Block numbers: Pages within each file are numbered sequentially from 0.
• If the file has been filled up, PostgreSQL adds a new empty page to the end of the file to increase the file size.

Fig 3:Page layout of a heap table file.

A page within a table contains three kinds of data described as follows:

• heap tuple(s): A heap tuple is itself a record. They are in order from the bottom of the page.
• line pointer(s): A row pointer is 4 bytes long and contains a pointer to each heap tuple. Also called an element pointer.
• header data: It is 24 byte long and contains general information about the page.

1. Free space or hole: An empty space between the end of line pointers and the beginning of the newest tuple.
2. Tuple identifier (TID): A TID comprises a pair of values: the block number of the page that contains the tuple, and the offset number of the line pointer that points to the tuple. A typical example of its usage is index.
3. Heap tuple whose size is greater than about 2 KB (about 1/4 of 8 KB) is stored and managed using a method called TOAST (The Oversized-Attribute Storage Technique).

4. The Methods of Writing and Reading Tuples:

4.1. Writing Heap Tuples:

Description:

Suppose a table consists of one page containing only one heap tuple. The pd_lower on this page points to the first row pointer, and both the row pointer and pd_upper point to the first heap tuple.

When the second tuple is inserted, it will be placed after the first tuple. A second row pointer is pushed into the first tuple and points to the second tuple. pd_lower is changed to point to the second row pointer and pd_upper is changed to point to the second heap tuple. Other headers in that page (pd_lsn, pg_checksum, pg_flag, etc.) are also rewritten with appropriate values.

4.2. Reading Heap Tuples:

Two typical access methods are outlined below: sequential scans and B-tree index scans.

• Sequential Scan — All tuples on all pages are read sequentially by scanning all row pointers on each page. Shown in below fig.
• B-tree index scan — The index file contains index tuples, each consisting of an index key and a TID pointing to the target heap tuple. When an index tuple with the searched key is found, PostgreSQL uses the obtained TID value to read the desired heap tuple.

The free space map is the first branch of the table/index data file(branch number is 1), the visibility map is the second branch of the table data file(branch number is 2&# 41; The fork number of the data file is 0.

For More Information: https://www.interdb.jp/pg/