Physical layout on disk

For DBAs and operators

Map a table to its files

SELECT pg_relation_filepath('sales.orders');      -- base/16384/16402
SELECT oid, datname FROM pg_database;              -- 16384 = appdb
SELECT relfilenode, reltablespace FROM pg_class
 WHERE oid = 'sales.orders'::regclass;
-- the other way round: which relation owns file 16402?
SELECT pg_filenode_relation(0, 16402);

SELECT pg_size_pretty(pg_relation_size('sales.orders'))        AS main_fork,
       pg_size_pretty(pg_relation_size('sales.orders', 'fsm')) AS fsm,
       pg_size_pretty(pg_relation_size('sales.orders', 'vm'))  AS vm,
       pg_size_pretty(pg_total_relation_size('sales.orders'))   AS with_indexes_toast;

What each entry is

Segments and blocks

Each file is a sequence of 8 kB blocks (block_size). After 131072 blocks (segment_size = 1 GB) a new segment file .1, .2 … starts. Both are fixed at compile time. Block n lives in segment n / 131072 at offset (n % 131072) × 8192.

Configuration files

postgresql.conf is read first, then postgresql.auto.conf, which ALTER SYSTEM writes and which therefore wins. Debian and Ubuntu packages keep the config files in /etc/postgresql/18/main/; check SHOW config_file and SHOW hba_file, and SELECT * FROM pg_file_settings WHERE error IS NOT NULL for typos.

postmaster.pid

The lock file holds the postmaster PID, data directory, start time, port, socket directory, listen address, shared memory key and status (starting, ready, standby, stopping). A stale file after a crash is detected automatically; never delete it while a postmaster might still be running, or two servers can open the same files.

relfilenode is not always the OID: it changes after TRUNCATE, VACUUM FULL, CLUSTER, REINDEX and table-rewriting ALTER TABLE. Mapped catalogs (like pg_class itself) have relfilenode 0 in pg_class and are found via pg_filenode.map.

PostgreSQL 18 source trail: tablespace.c documents pg_tblspc, default/global tablespaces and the on-disk path; rel.h defines mapped relations.