The physical layout

This chapter explores the physical storage. We'll start from the data files moving deep into the data blocks and finally to the tuples. After looking to the tablespaces we'll complete the outline started in 5.7 with the MVCC.

Data files

Wherever the database stores the files, the $PGDATA/base or a different tablespace, those files are named, initially, after the relation's object identifier, a 4 byte unsigned integer. The word initially means there's no guarantee the file will stay the same in the future. Some file altering operations like the REINDEX or the VACUUM FULL change the file name leaving the relation's object identifier unchanged. The maximum size allowed for a datafile is 1 GB, then a new segment with the same name and a sequential suffix is created. For example if the relation 34554001 reaches the upper limit a new file named 34554001.1 is added, when this one reaches 1 GB then a 34554001.2 is added and so on.
Alongside the main data files there're some additional forks needed for the database activity.

Free space map

The free space map segment is present for the index and table's data files . It's named after the relation's filenode with the suffix _fsm and is used to track the free space in the relation.

Visibility map

The table's file are also called heap files. Alongside those files there is a second fork called visibility map. Like before this file is named after the relation's filenode with the suffix _vm. Its usage is for tracking the data pages having all the tuples visible to all the active transactions. This fork is used also for the index only scans where the data is retrieved from the index page only.

Initialisation fork

The initialisation fork is an empty table or index page, stored alongside the unlogged relation's data file. As seen in 5.3.2 when the database performs a crash recovery the unlogged relations are zeroed. The initialisation fork is used to reset them and all the relation's accessory forks are deleted.

pg_class

All the current database's relations are listed in the pg_class system table. The field relfilenode shows the relation's filename.
The oid field, hidden if wildcard is used in the select list, is the internal object identifier. PostgreSQL is shipped with plenty of useful functions to get informations from the relation's OID. For example the function pg_total_relation_size(regclass) returns the space used by the table plus the indices, the additional forks and the eventual TOAST table. The function returns the size bytes. Another function, the pg_size_pretty(bigint), returns a human readable format for better reading.

The field relkind is used to track the relation's kind

Table 6.1: Relkind possible values