Reading The Internals of PostgreSQL: Database Cluster, Databases, and Tables — Burak Sen

June 28, 2026I'm delving into Postgres Internals and while doing that I thought it would be better to write my notes to keep me accountable and try to internalize my readings. Thanks Hironobu Suzuki for this great reference and his work on Postgres. Here is the source link https://www.interdb.jp/pg/index.html. Logical Structure of Database Cluster# A database cluster is not a collection of database servers in the context of PostgreSQL (SQL standard uses the term catalog cluster). It means a group of databases managed by a single PostgreSQL instance. That is most probably a correct definition in dictionary terms, but usually when you hear database cluster you would assume multiple nodes/instances of databases that act as a single system. A database is a collection of database objects such as tables, indexes, views, etc. In PostgreSQL, databases are also database objects and they are represented by Oid, which is an unsigned int object identifier. sqlSELECT oid, datname FROM pg_database ORDER BY oid;oiddatname1template14template05postgres(3 rows) Built-in objects (databases in this query) have hardcoded low values. Other user-created tables/objects start having OIDs from 16384 (OIDs 1-16383 are reserved or used by init objects). database cluster · one PostgreSQL instancetemplate1oid 1template0oid 4postgresoid 5shopoid 16384pg_database lists every database (one shared catalog per cluster)objects inside 'shop'orderstableoid 16386orders_pkeyindexoid 16395views, sequences, …more objectsoid …every object is identified by its own Oid These objects and their relations are stored in system catalogs which are just regular tables in PostgreSQL. Some examples are: TableDescriptionpg_classTables and other objects that are similar to tables (views, indexes, TOAST tables etc.)pg_databaseStores information about available databases.pg_indexIndex information...... Some trivia about catalogs:

pg_database is shared across all databases of a cluster (one table per cluster), whereas most system catalogs are created per database pg_class stores indexes and there is a pg_index catalog as well. Well, the reason is that pg_class is for generic relational information.

pg_index and other respective catalogs have their own customized schema. This helps with separating concerns and not having a pg_class2 table in the future (overly exaggerated but remembered the famous merchants2 table https://jimmyhmiller.com/ugliest-beautiful-codebase).

As mentioned above, these are just regular tables you can run queries on (at your own risk!). Many built-in objects such as types, functions, and operators are stored in these tables and user-defined ones are added to them the same way. These OIDs are automatically created when new rows are added to catalog tables. For example, when registering an extension (e.g. pgvector), what happens is that pgvector is added to the pg_extension table with an automatically created OID. This behavior was true for user-defined tables previously. The chronological history is:

PG <= 8.0: All table rows are created with an OID. 8.1 <= PG < 12: Automatic OID generation is an opt-in feature. To enable, users need to create the table with CREATE TABLE foo (...) WITH OIDS; or enable GUC default_with_oids. PG >= 12: The feature is completely removed.

Physical Structure of DB Cluster# A Postgres cluster stores everything in the data directory. Its path is set by the PGDATA environment variable. Common default locations are /var/lib/pgsql/data and /var/lib/postgresql/<version>/main. initdb is responsible for setting up and creating this directory and it is automated by Postgres installers. When brew install postgresql@18 is called, postgres@18.rb runs the line below in its post-install method, after Postgres itself is installed: system bin/"initdb", "--locale=en_US.UTF-8", "-E", "UTF-8", postgresql_datadir unless pg_version_exists? Similar logic is implemented across other Postgres installation methods (EDB for Windows, apt/deb, etc.). Inside $PGDATA there are many subdirectories: $PGDATA/├── base/ # one subdirectory per database│ └── {OID}/ # tables & indexes as files (relfilenode)├── global/ # cluster-wide catalogs (e.g. pg_class)├── pg_wal/ # WAL segment files├── pg_xact/ #

transaction commit status (clog)├── pg_tblspc/ # symlinks to external tablespaces├── PG_VERSION # major version number├── postgresql.conf # main server configuration└── ... # 15+ more The full list is in https://www.postgresql.org/docs/current/storage-file-layout.html. Subdirectory changes seem to be very rare. The table here shows some naming changes and new additions in PG9 and PG10. I also checked the Postgres source code and saw that the current_logfiles subdirectory was added in the PG10 release. It is added in 19dc233 which is included starting from PG10: bashgit tag --contains 19dc233c32f | grep -E '^REL' | sort -V | headREL_10_0 REL_10_1 REL_10_2 ... Database Subdirectory Layout# As said above, each database has its own subdirectory in the base directory, named after its OID (/base/{OID}). Tables and indexes are stored in a single file under the database subdirectory if their size is under 1GB. Similar to OID, physical files are identified by relfilenode and this information is stored in the pg_class row of the table and/or index. Now let's explore these layouts a bit. I'm using a Mac. I've installed PostgreSQL 18 with brew install postgresql@18 and run it as a service. Then, connect in a terminal with psql -d postgres. sqlSHOW data_directory;data_directory/opt/homebrew/var/postgresql@18(1 row) As described above, the data directory path is /opt/homebrew/var/postgresql@{VERSION}.

Now let's create the shop database and explore it. sqlCREATE DATABASE shop;

SELECT oid, datname FROM pg_database WHERE datname = 'shop';CREATE DATABASEoiddatname16384shop(1 row) A new directory with that OID is now on disk: bashls /opt/homebrew/var/postgresql@18/base/1 16384 4 5 One surprise is that the shop directory is not empty.

The reason is that the CREATE DATABASE command creates the database by copying an existing one, and template1 is the default source database for it. Additional details on templates are in the official documentation. bashls /opt/homebrew/var/postgresql@18/base/16384 | head112 113 1247 1247_fsm 1247_vm 1249 1249_fsm 1249_vm 1255 1255_fsm Create a basic table: sql\c shop

CREATE TABLE orders ( id bigserial PRIMARY KEY, customer_id bigint NOT NULL, total_cents bigint NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); Now, this table will have OID for itself and its index for primary key. Let's examine them by querying pg_class: sqlSELECT oid, relname, relfilenode, relkind FROM pg_class WHERE relname IN ('orders', 'orders_pkey');oidrelnamerelfilenoderelkind16386orders16386r16395orders_pkey16395i(2 rows) As you can see, relfilenodes (identifier for physical location) and OIDs are identical. We can use pg_relation_filepath which is a built-in function to compute paths: sqlSELECT pg_relation_filepath('orders'), pg_relation_filepath('orders_pkey');pg_relation_filepathpg_relation_filepathbase/16384/16386base/16384/16395(1 row) database oid16384relfilenode · pg_class16386base/16384/16386base/16384/16386the heap file on diskthe file is named by relfilenode, not oid (they only coincide right after CREATE) Our table and index files are stored in base/{database_oid}/{table|index_relfilenode}.

Insert some rows and trigger VACUUM: sqlINSERT INTO orders (customer_id, total_cents) SELECT (random() * 1000)::bigint, (random() * 100000)::bigint FROM generate_series(1, 1000);

VACUUM orders;INSERT 0 1000 VACUUM VACUUM is mainly used to reclaim storage. Its details can be checked in the documentation, and I'll delve into it in future posts. The resulting files are: bashls -l /opt/homebrew/var/postgresql@18/base/16384/{16386*,16395}-rw-------@ 1 burak admin 65536 Jun 15 10:43 .../base/16384/16386 -rw-------@ 1 burak admin 24576 Jun 15 10:43 .../base/16384/16386_fsm -rw-------@ 1 burak admin 8192 Jun 15 10:43 .../base/16384/16386_vm -rw-------@ 1 burak admin 40960 Jun 15 10:43 .../base/16384/16395 For our table, 16386 is the main relation data; 16386_fsm and 16386_vm are its forks. They are called auxiliary files and are used for tracking free space in the table's pages (FSM) and the visibility status of each page (VM). Now, to demonstrate that OID == relfilenode does not necessarily hold, trigger a VACUUM FULL, which does the following (simplified):

get an exclusive lock on the table copy rows to new files (new relfilenode/s) swap the pointer to the new relfilenode and commit

sqlVACUUM FULL orders;

SELECT oid, relname, relfilenode FROM

pg_class WHERE relname IN ('orders', 'orders_pkey');

SELECT pg_relation_filepath('orders');VACUUMoidrelnamerelfilenode16386orders1639716395orders_pkey16400(2 rows)pg_relation_filepathbase/16384/16397(1 row) The rewrite caused by VACUUM updated the relfilenodes, and they are not equal to the OIDs at the latest state. before VACUUM FULLoid (identity)16386relfilenode16386pathbase/16384/16386file 16386VACUUM FULLrewrite to anew fileafteroid (identity)16386relfilenode16397pathbase/16384/16397file 16397the oid is the table's permanent identity · the relfilenode names the file and changes on rewrite Tablespaces# Postgres also supports tablespaces. With tablespaces, a user can specify other directories to store/use database files. It allows the following:

extending the partition/volume that Postgres was initially configured on. placing frequently-accessed indexes on fast disks and cold tables on slower ones.

We've talked about the base/ and global/ directories. pg_default is the default tablespace for ordinary database objects, and its path is $PGDATA/base, whereas pg_global is the tablespace for cluster-wide objects. Let's create a directory mkdir -p /Users/burak/pg-tblspc-extra and add it as a tablespace. sqlCREATE TABLESPACE extra_space LOCATION '/Users/burak/pg-tblspc-extra'; SELECT oid, spcname FROM pg_tablespace WHERE spcname = 'extra_space';CREATE TABLESPACEoidspcname16401extra_space(1 row) As expected, our tablespace has an OID as well, and it's 16401. Now, instead of going to the raw path of this tablespace, Postgres creates a symlink between the file paths.