An Introduction to PostgreSQL

This chapter provides an overview of PostgreSQL's database cluster structure, which includes both logical and physical aspects. A database cluster is a collection of databases managed by a PostgreSQL server, and it does not refer to a group of database servers.
The logical structure consists of database objects such as tables, indexes, sequences, views, and functions. PostgreSQL internally manages these objects using object identifiers (OIDs), which are unsigned 4-byte integers. The relationships between these objects and their OIDs are stored in system catalogs based on the object type.
The physical structure of a database cluster is primarily composed of one base directory containing subdirectories and files. Each database is a subdirectory under the base subdirectory, and tables and indexes are stored as files within their respective database subdirectories. PostgreSQL also supports tablespaces, which are directories containing data outside the base directory.
Files associated with tables and indexes are managed by the relfile node variable. When a table or index file size exceeds 1GB, PostgreSQL creates new files with incremented numbers (e.g., relfilenode.1, relfilenode.2) to accommodate the larger size. Tables have associated free space maps and visibility maps, while indexes only have free space maps.
Tablespaces in PostgreSQL are additional data areas outside the base directory, created under a specified directory when a CREATE TABLESPACE statement is issued. The tablespace directory is addressed by a symbolic link from the pg_tblspc subdirectory, and its link name is the same as the tablespace's OID value.
Heap table file in PostgreSQL is divided into fixed-length pages or blocks of 8192 bytes (8 KB). Each page has a sequential block number, and the internal layout of these pages depends on the data file types. In a heap table file, a page contains three types of data: heap tuples, line pointers, and header data.
Heap tuples are the record data itself, line pointers are 4-byte long pointers to each heap tuple, and header data contains general information about the page. To identify a tuple within the table, a tuple identifier (TID) is used, which consists of the block number and the offset number of the line pointer pointing to the tuple.
The content also explains the writing and reading methods of heap tuples. When writing, the tuple is placed after the previous one, with line pointers and header data updated accordingly. Reading tuples can be done either through a sequential scan, where all tuples in all pages are sequentially read, or through a B-tree index scan, which uses index tuples to find the target heap tuple without unnecessary scanning.