Files on Disk — OIDs, relfilenode, Tablespaces

The data directory is not a black box. Every table is a numbered file inside a numbered directory, every file stops at 1 GB and continues in the next one, and two different catalogue numbers connect the name you type in SQL to the bytes on the volume. Knowing which number is which is the difference between an unexplained 96%-full volume and a five-minute answer.

Everything in this topic is diagnostic knowledge. It tells you what a backup has to copy, what a rewrite costs before it starts, and where the space went. It is emphatically not an invitation to edit anything under /var/lib/postgresql/18/main, and the last section explains why the first instinct a filling disk produces is the one that ends the cluster.

The Layout of PGDATA

Inside the data directory, base/ holds one subdirectory per database, named for the database's OID, and inside that sits a file per relation named for its filenode. global/ holds the cluster-wide catalogues such as pg_database, so they are readable from a connection to any database. pg_wal/ holds the write-ahead log, pg_tblspc/ holds a symlink per tablespace, and sorts that overflow work_mem spill into a pgsql_tmp directory inside base/. Any relation can be located from inside psql.

SELECT oid FROM pg_database WHERE datname = 'cartwheel';   -- 16384
SELECT pg_relation_filepath('orders');                     -- base/16384/24576

$ ls -l /var/lib/postgresql/18/main/base/16384/24576*
1073741824  24576        -- segment 0, full
1073741824  24576.1
      ...
 962592768  24576.13     -- the tail of the table
   3850240  24576_fsm    -- free space map
    491520  24576_vm     -- visibility map

Read the path as two numbers. 16384 is the OID of the cartwheel database, so everything in that directory belongs to it and nothing outside it does. Fourteen data files and two maps is what "the orders table" means on the volume: 24576 is the filenode of orders, and the siblings with suffixes are the rest of the same relation — thirteen further gigabyte segments of data, plus the two map forks from the previous topic.

OID and relfilenode

A relation has two numbers and they answer different questions. The OID is its identity in the catalogue — the number every dependency, foreign key and statistics row refers to, and the number that never changes for the life of the object. The filenode is the name of its file. The two usually start out equal, which is why people conflate them, and they diverge the first time anything rewrites the relation: TRUNCATE, REINDEX, CLUSTER, VACUUM FULL and most forms of ALTER TABLE … ALTER COLUMN … TYPE all produce a new file and leave the OID untouched. For a handful of system catalogues pg_class.relfilenode reads zero, because their file names are tracked by low-level state instead; pg_relation_filenode() is the function that gives the right answer in every case.

SELECT oid, relfilenode FROM pg_class WHERE relname = 'inventory';
  oid  | relfilenode
-------+-------------
 24601 |       24601

VACUUM FULL inventory;

  oid  | relfilenode
-------+-------------
 24601 |       31088

A rewrite writes a new file and does not release the old one until it finishes, so VACUUM FULL on the 900 MB inventory needs another 900 MB free before it starts — and the moment that command gets reached for is usually the moment the volume has nothing to spare. Chapter 7 takes that argument the rest of the way. The other consequence is less obvious: any script that caches a mapping from file names to tables is wrong from the next rewrite onward, reporting one table's growth under another table's name, with nothing anywhere logging that the mapping moved.

Segments and the 1 GB Rule

A relation's file grows to 1 GB and then continues as a second file with .1 appended, then .2, and so on for as long as the table keeps growing. The limit is set when the server is built and the server reports it as segment_size, which is 131,072 blocks of 8 KB. It applies to the maps as well, though a free space map would have to describe an implausible relation before it needed a second segment. The historical motive was file size limits on platforms nobody runs any more; the present-day benefit is that dropping or rewriting a huge relation moves in bounded pieces rather than as one enormous file operation.

The practical version is that "the file for orders" is never one file, so any hand-rolled size measurement that globs a single name is wrong by a factor of fourteen here and by an arbitrary factor elsewhere. Do the arithmetic in SQL instead: pg_total_relation_size() already sums the segments, the forks, the TOAST relation and the indexes. And pg_database_size() is not a provisioning number — the WAL, the temporary files and the space a backup needs are all outside it.

Tablespaces

A tablespace is a named directory outside the data directory that relations can be placed in, so a specific table or index can sit on different storage from the rest of the cluster. Postgres reaches it through a symlink in pg_tblspc named for the tablespace's OID, under which sits a version-specific subdirectory and then one directory per database. The two built-in tablespaces do not work that way: pg_default is base and pg_global is global, and neither goes through a link.

Neither of the two costs shows up in the syntax. Moving an existing relation into a tablespace rewrites it under an ACCESS EXCLUSIVE lock, with the same requirement for a second full copy as any other rewrite. The other cost is the restore — a physical backup of a cluster with tablespaces is a backup of a symlink whose target has to exist on the new host, and a host with no /mnt/fast-nvme fails at the worst possible moment, which Chapter 12 covers as a procedure rather than a surprise. temp_tablespaces is the one use that pays for itself with no drama: sort spill goes to a scratch volume, large sorts stay off the disk holding the data, and nothing needs restoring if that volume is lost.

Data Checksums

Since 18, initdb enables data checksums by default, and --no-data-checksums is the flag that turns them off. Each page carries a checksum that is verified when the page is read, so a page corrupted by the I/O system is reported rather than silently handed to a query, and failures are counted in pg_stat_database. SHOW data_checksums answers whether this particular cluster has them, which is a question worth asking of any cluster you did not build yourself — the setting is a property of the cluster from initdb onward, not something a config reload can change.

On a cluster created before 18, or one where that flag was passed at initdb, pg_checksums turns them on offline: the server must be shut down cleanly first, and enabling rewrites every relation block whose checksum changed, which on a multi-terabyte cluster is a long maintenance window to schedule rather than a command to try. There is a small running cost as well, and this chapter has already met it — with checksums on, hint-bit updates are always WAL-logged, because a hint bit changes the page and therefore changes its checksum.

Why You Never Touch the Files

When a volume fills, the instinct is to find the biggest file and move it aside. Inside the data directory that ends the cluster. Relations exist because catalogue rows say they do, WAL replay expects to find the files it wrote records about, and there is no supported way to inform the server that something it owns has disappeared. This holds for files that look obviously orphaned, for old segments in pg_wal — deleting those can break both recovery and the replica that had not read them yet — and for anything in pgsql_tmp while the server is running. The supported ways to free space are all SQL: delete rows, drop objects, run the right maintenance, and in the worst case add a volume and move a tablespace onto it.

What this knowledge is genuinely for is diagnosis, sizing, and understanding what a backup has to copy. Three functions do most of it: pg_relation_filepath() to find something, pg_total_relation_size() to measure it honestly, and pg_size_pretty() to make the answer readable. That closes the physical tour. Five topics ago a row was an abstraction; it is now 24 bytes of header and some columns, in a slot, on a page, in a segment, in a directory named for a database. The next chapter takes the two versions of the strawberries row that are sitting on page 91 and asks the question this one deliberately refused: which of them is a given transaction entitled to see?