16389.1, and 16389.2 files

In PostgreSQL, database objects like tables and indexes are stored in files called segments. Each segment has a predetermined size, and a table may span multiple segments as its data volume increases. By default, if the –with-segsize option isn’t specified during PostgreSQL’s source code compilation, the segment size is usually set to 1GB. When the size of tables and indexes surpasses this limit, PostgreSQL generates additional files named after their respective relfilenode.1, relfilenode.2, and so forth, to accommodate the excess data. This behavior can be seen in example bellow

postgres=# CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    number INT
);
CREATE TABLE
postgres=# INSERT INTO my_table (number)
SELECT generate_series(1, 50000000);
INSERT 0 50000000
postgres=# \dt+ my_table 
                                     List of relations
 Schema |   Name   | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+----------+-------+----------+-------------+---------------+---------+-------------
 public | my_table | table | postgres | permanent   | heap          | 1729 MB | 
(1 row)
postgres=# select oid,relname,relfilenode from pg_class where relname = 'my_table';
  oid  | relname  | relfilenode 
-------+----------+-------------
 16385 | my_table |       16385
(1 row)
postgres@9e7108914f4c:~$ ls -lhrt data/base/5/16385 (Tab for auto-complete)
16385      16385.1  

Free Space Map(FSM) 

Each table and index relation, except for hash indexes, has a Free Space Map (FSM) to keep track of available space in the relation. It stores all free space-related information alongside primary relation

Visibility Map(VM)

The Visibility Map (VM) in PostgreSQL is a system designed to monitor pages within tables and indexes, identifying those that exclusively contain tuples visible to all active transactions. Each table possesses its own visibility map, storing the visibility status of individual pages within the table file. This information determines whether a page contains any dead tuples. During vacuum processing, pages without dead tuples can be efficiently bypassed using the associated Visibility Map (VM).

Source: interdb

In the below diagram, we can see a table has 2 pages and Page 0 contains four tuples. Tuple-2 is determined to be a dead tuple, PostgreSQL will remove it and rearrange the remaining tuples to address fragmentation or bloat. Subsequently, it updates both the Free Space Map (FSM) and Visibility Map (VM) associated with this page. PostgreSQL repeats this process until it reaches the final page.

Source: ashnik

Note: Indexes only have individual free space maps and do not have visibility maps.

Tablespaces 

Tablespace in PostgreSQL is a physical storage area separate from the default data directory. It allows organizing database objects on different storage devices for performance or administrative purposes. We can create a new tablespace with the following query

CREATE TABLESPACE my_tablespace
  OWNER postgres
  LOCATION '/var/lib/postgresql/tbs';
CREATE TABLESPACE

Upon creating a new tablespace on a specified directory, two key actions occur

Firstly, A database and system catalog version-specific directory gets created under the desired location i.e, PG_DATABASE_MAJORVERSION_CATALOGVERSION

ls -lhrt /var/lib/postgresql/tbs/PG_16_202307071/
total 0

The catalog version number in PostgreSQL is an internal mechanism for maintaining data directory compatibility and preventing inconsistencies. Its format is YYYYMMDDN representing the date the number was changed, with N representing a simple counter to accommodate for more than one change on the same date.

We can check the catalog version by 

postgres=# SELECT catalog_version_no FROM pg_control_system();
 catalog_version_no 
--------------------
      202307071
(1 row)

Secondly, A symlink to the tablespace directory gets created inside the original data/pg_tbspc directory

postgres@9e7108914f4c:~$ ls -lhrt data/pg_tblspc/
total 0
lrwxrwxrwx 1 postgres postgres 23 Mar 15 01:53 16386 -> /var/lib/postgresql/tbs

Creating a table within a tablespace associated with the PostgreSQL database will result in the creation of a new directory inside the PG_16_202307071 directory, bearing the same OID as the PostgreSQL database, within the data/base directory.

postgres=# CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
) TABLESPACE my_tablespace;
CREATE TABLE

The OID of the PostgreSQL database is regenerated and remains unchanged.

postgres@9e7108914f4c:~$ ls -lhrt tbs/PG_16_202307071/5/16392 
-rw------- 1 postgres postgres 0 Mar 15 01:57 tbs/PG_16_202307071/5/16392

In this first part of our series, we’ve explored fundamental concepts of database clusters, databases, and tables.

Stay tuned for the second part, where we’ll delve deeper into internal layout of a table and how PostgreSQL performs read and write on a tuple.