Postgres relation files, filed under base/

The buffers article followed an 8KB page from disk into shared memory and back. What it didn't do is name the file. Every page that passes through the buffer pool is block number N of some file under the data directory, and the file has a naming scheme, a size limit, and companion files that share its name. Before the next article reads the inside of a page, this one finds it on disk with ls and od, and shows that the bytes are the same ones.

Nothing here needs more than a scratch database and shell access to its data directory. The examples were captured on PostgreSQL 18.6 with data checksums on, which is the default since 18.

Setup

CREATE EXTENSION IF NOT EXISTS pageinspect;

CREATE TABLE files_demo (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    value numeric(10,2)
);

INSERT INTO files_demo (title, value)
SELECT 'row_' || i, i * 1.5 FROM generate_series(1, 1000) AS i;

A thousand short rows and a primary key. The text column is there so a TOAST table gets created.

From table name to file name

The function that answers "where is this table on disk" ships with the server:

SELECT pg_relation_filepath('files_demo');

 pg_relation_filepath
----------------------
 base/5/16431
(1 row)

The path is relative to the data directory (SHOW data_directory prints it). Three parts: base is the directory for the default tablespace, 5 is the OID of the database, and 16431 is the file's name. That last number is the relfilenode, and it comes from pg_class:

SELECT oid, relfilenode, relname
FROM pg_class
WHERE relname LIKE 'files_demo%';

  oid  | relfilenode |      relname
-------+-------------+-------------------
 16431 |       16431 | files_demo
 16430 |       16430 | files_demo_id_seq
 16438 |       16438 | files_demo_pkey
(3 rows)

Three relations, three files. The identity column's sequence and the primary key index each get their own, because to the storage layer a sequence is one-page relation and an index is a relation like any other. The oid and relfilenode columns are equal for all three, which stays true only until the table is first rewritten (see OID versus relfilenode below).

The middle part of the path, 5, is the OID of the current database from pg_database. Each database gets its own subdirectory under base/, and a table's file lives in the directory of the database it belongs to. That is the whole reason the database OID is part of the path: relfilenodes are only unique within a tablespace and database, so two databases can each have a file called 16431 without colliding.

On disk, the directory looks like the catalog:

$ ls -l base/5/16431 base/5/16438 base/5/16430
-rw------- 1 postgres postgres  8192 Sep 11 20:44 base/5/16430
-rw------- 1 postgres postgres 57344 Sep 11 20:44 base/5/16431
-rw------- 1 postgres postgres 40960 Sep 11 20:44 base/5/16438

pg_relation_size gets its numbers the same way: it does nothing more than stat the files.

The base/5 directory holds a few hundred files on a fresh database before any of ours, almost all of them system catalogs and their indexes, and every one follows the same scheme.

The files you didn't create

There is a function that goes the other way, from a file number back to a name. Point it at the numbers between and around ours:

SELECT n, pg_filenode_relation(0, n)
FROM unnest(ARRAY[16430, 16431, 16436, 16437, 16438]) AS n;

   n   |     pg_filenode_relation
-------+-------------------------------
 16430 | files_demo_id_seq
 16431 | files_demo
 16436 | pg_toast.pg_toast_16431
 16437 | pg_toast.pg_toast_16431_index
 16438 | files_demo_pkey
(5 rows)

The first argument is the tablespace OID, where 0 means the database's default. Two relations we didn't ask for appear: a TOAST table and its index. Because files_demo has a text column, PostgreSQL created a place to put values too large for a page. They're ordinary files, 16436 and 16437, sitting next to the main table, and nothing has been toasted yet, so the TOAST heap has no pages. The catalog links them:

SELECT oid, relname, relkind, relfilenode, reltoastrelid
FROM pg_class
WHERE oid IN (16431, 16436, 16437);

  oid  |       relname        | relkind | relfilenode | reltoastrelid
-------+----------------------+---------+-------------+---------------
 16431 | files_demo           | r       |       16431 |         16436
 16436 | pg_toast_16431       | t       |       16436 |             0
 16437 | pg_toast_16431_index | i       |       16437 |             0
(3 rows)

So one CREATE TABLE with a primary key produced five relations and five files. pg_table_size covers the heap, its TOAST relations, and the forks described next; pg_indexes_size covers the indexes; pg_total_relation_size is the sum of those two. The sequence is its own relation and is counted by none of them.

Forks

Each relation has a main file and up to three companion files that share its relfilenode and differ by a suffix. PostgreSQL calls them forks. The one with no suffix is the main fork, the table's actual pages. After the INSERTs and a VACUUM, files_demo has three of the four:

VACUUM files_demo;

SELECT fork,
       pg_relation_size('files_demo', fork) AS bytes,
       pg_relation_size('files_demo', fork) / 8192 AS pages
FROM unnest(ARRAY['main', 'fsm', 'vm', 'init']) AS fork;

 fork | bytes | pages
------+-------+-------
 main | 57344 |     7
 fsm  | 24576 |     3
 vm   |  8192 |     1
 init |     0 |     0
(4 rows)

$ ls -l base/5/16431*
-rw------- 1 postgres postgres 57344 Sep 11 20:44 base/5/16431
-rw------- 1 postgres postgres 24576 Sep 11 20:44 base/5/16431_fsm
-rw------- 1 postgres postgres  8192 Sep 11 20:44 base/5/16431_vm

16431_fsm is the free space map, a small tree that records roughly how much room each heap page has left so an INSERT can find a page without scanning them all. It's three pages here for a seven-page table, which looks disproportionate until you know the map is a three-level tree and each level needs at least one page. 16431_vm is the visibility map, two bits per heap page: all-visible, so a scan can skip visibility checks, and all-frozen, so VACUUM can skip the page for wraparound purposes. The VACUUM article reads both of these with pg_freespacemap and pg_visibility.

The fourth fork, init, is empty for this table because files_demo is a normal, logged table. An unlogged table gets one:

CREATE UNLOGGED TABLE files_unlogged (id int, note text);
INSERT INTO files_unlogged VALUES (1, 'scratch');

SELECT pg_relation_filepath('files_unlogged');

 pg_relation_filepath
----------------------
 base/5/16444
(1 row)

$ ls -l base/5/16444* base/5/16447* base/5/16448*
-rw------- 1 postgres postgres 8192 Sep 11 20:44 base/5/16444
-rw------- 1 postgres postgres    0 Sep 11 20:44 base/5/16444_init
-rw------- 1 postgres postgres    0 Sep 11 20:44 base/5/16447
-rw------- 1 postgres postgres    0 Sep 11 20:44 base/5/16447_init
-rw------- 1 postgres postgres 8192 Sep 11 20:44 base/5/16448
-rw------- 1 postgres postgres 8192 Sep 11 20:44 base/5/16448_init

The init fork is the state the relation is reset to after a crash. An unlogged table skips the write-ahead log, so after unclean shutdown its contents can't be trusted and PostgreSQL replaces the main fork with a copy of the init fork. For a heap that copy is an empty file, which is why 16444_init is zero bytes. 16447 and 16448 are the unlogged table's TOAST heap and TOAST index, created the same way 16436 and 16437 were, and the pattern repeats: the two heaps get a zero-byte init fork, while the TOAST index at 16448 gets a full 8192 bytes, because an empty B-tree still needs its metapage.

The same bytes, two ways

pageinspect's get_raw_page returns a page as it sits in shared buffers. The file on disk is supposed to contain the same page at the same offset. Read the first 24 bytes of block 0 both ways, starting with the buffer:

SELECT lsn, checksum, flags, lower, upper, special, pagesize, version, prune_xid
FROM page_header(get_raw_page('files_demo', 0));

    lsn    | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
 0/186E9B0 |        0 |     4 |   680 |   712 |    8192 |     8192 |       4 |         0
(1 row)

SELECT encode(substring(get_raw_page('files_demo', 0) from 1 for 24), 'hex');

                      encode
--------------------------------------------------
 00000000b0e9860100000400a802c8020020042000000000
(1 row)

Now the file. od prints bytes from an offset, and block 0 starts at offset 0:

$ od -A d -t x1 -N 24 base/5/16431
0000000 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
0000016 00 00 00 00 00 00 00 00
0000024

Nothing. The file has the right length, because the relation was extended with zeroed space, but the INSERT and VACUUM only dirtied pages in shared memory and nobody has written them back. The buffers article's point, seen from the filesystem side. Force it:

CHECKPOINT;

$ od -A d -t x1 -N 24 base/5/16431
0000000 00 00 00 00 b0 e9 86 01 dd f2 04 00 a8 02 c8 02
0000016 00 20 04 20 00 00 00 00
0000024

Now the bytes match the buffer, with one exception picked apart below. Two fields are worth reading by hand:

• Bytes 0 to 7, 00 00 00 00 b0 e9 86 01, are pd_lsn. It is stored as two 32-bit little-endian halves, so the high half is 0 and the low half is 0x0186E9B0, which is how page_header printed 0/186E9B0. That value is the write-ahead log position of the last change to this page.
• Bytes 8 and 9, dd f2, are pd_checksum, little-endian 0xF2DD.

The remaining 14 bytes match page_header field for field.

Except the checksum. The buffer says 0, the file says f2dd. PostgreSQL computes the checksum when it writes a page out, into the copy it hands to the kernel, and never touches the shared buffer. (Pages written outside the pool, by index builds or the VACUUM FULL rewrite, get theirs in place.) A page that was initialised in memory and has never been read from disk carries a zero checksum in the buffer; one that was read and then modified carries whatever checksum the file had when it was read. Read this page back after it's been evicted and reloaded and the buffer will show the on-disk value, which page_header prints as signed smallint: 0xF2DD comes out as -3363, not 62173.

OID versus relfilenode

Back to the pair of numbers in pg_class that started out equal. The OID is the relation's identity, the number every catalog reference points at, and it never changes. The relfilenode is just a file name. Most of the time they match, because CREATE TABLE assigns the same number to both. Operations that rewrite the table break the tie:

SELECT oid, relfilenode, pg_relation_filepath('files_demo')
FROM pg_class WHERE relname = 'files_demo';

VACUUM FULL files_demo;

SELECT oid, relfilenode, pg_relation_filepath('files_demo')
FROM pg_class WHERE relname = 'files_demo';

  oid  | relfilenode | pg_relation_filepath
-------+-------------+----------------------
 16431 |       16431 | base/5/16431
(1 row)

  oid  | relfilenode | pg_relation_filepath
-------+-------------+----------------------
 16431 |       16449 | base/5/16449
(1 row)

VACUUM FULL writes a compacted copy of the table into a brand new file and then swaps the catalog entry to point at it. The OID stays 16431 so nothing that references the table notices; the relfilenode moves to 16449. The primary key was rebuilt too, and moved from 16438 to 16454. Before the next checkpoint, the old file is still there:

$ ls -l base/5/16431* base/5/1645*
-rw------- 1 postgres postgres     0 Sep 11 20:45 base/5/16431
-rw------- 1 postgres postgres     0 Sep 11 20:45 base/5/16452
-rw------- 1 postgres postgres  8192 Sep 11 20:45 base/5/16453
-rw------- 1 postgres postgres 40960 Sep 11 20:45 base/5/16454

16452 and 16453 are the rebuilt TOAST table and its index; the gap before them is OIDs the rewrite consumed for its transient relation. The old 16431 is still there at zero bytes, and its _fsm and _vm aren't: PostgreSQL unlinks the companion forks and any extra segments immediately, but truncates the main fork's first segment to nothing and keeps the empty name until the next checkpoint, which unlinks it for good.

TRUNCATE does the same swap, to an empty file:

TRUNCATE files_demo;

SELECT oid, relfilenode, pg_relation_filepath('files_demo')
FROM pg_class WHERE relname = 'files_demo';

  oid  | relfilenode | pg_relation_filepath
-------+-------------+----------------------
 16431 |       16455 | base/5/16455
(1 row)

Assigning a fresh file rather than emptying the old one is what makes TRUNCATE transactional. If the transaction rolls back, the catalog row reverts to the old relfilenode and the old file is still there, untouched. CLUSTER, REINDEX, ALTER TABLE ... SET TABLESPACE, and any ALTER TABLE that rewrites rows all take the same route, because each of them builds a new copy of the relation.

Tablespaces

Everything so far lived under base/, the home of the pg_default tablespace. A tablespace is a directory somewhere else, with the same layout inside:

CREATE TABLESPACE fast LOCATION '/var/lib/postgresql/fast';
CREATE TABLE files_ts (id int, note text) TABLESPACE fast;

SELECT pg_relation_filepath('files_ts');

          pg_relation_filepath
-----------------------------------------
 pg_tblspc/16459/PG_18_202506291/5/16460
(1 row)

$ ls -l pg_tblspc/
lrwxrwxrwx 1 postgres postgres 24 Sep 11 20:45 16459 -> /var/lib/postgresql/fast

pg_tblspc holds one symlink per tablespace, named by its OID. Under the link sits PG_18_202506291, the server and catalog version, so two major versions can share a location during an upgrade; below that it's <database OID>/<relfilenode> again, exactly like base/. Tablespace OID, database OID and relfilenode together name a relation file uniquely in the cluster, and pg_waldump identifies every block it touches by that triple.

Segment files

A single file is capped at 1GB (the --with-segsize build default; packaged builds use it). When a relation grows past that, PostgreSQL starts a second file with the same name and a .1 suffix, then .2, and so on. Each holds 131072 pages of 8192 bytes. The cap exists so the server works on platforms with file-size limits.

To see one, the table has to grow past 1GB:

CREATE TABLE files_big (
    id integer GENERATED ALWAYS AS IDENTITY,
    payload text
);

INSERT INTO files_big (payload)
SELECT repeat('x', 1000) FROM generate_series(1, 1200000);

SELECT pg_size_pretty(pg_relation_size('files_big')) AS size,
       pg_relation_size('files_big') / 8192 AS pages,
       pg_relation_filepath('files_big');

  size   | pages  | pg_relation_filepath
---------+--------+----------------------
 1339 MB | 171429 | base/5/16466
(1 row)

$ ls -l base/5/16466*
-rw------- 1 postgres postgres 1073741824 Sep 11 20:45 base/5/16466
-rw------- 1 postgres postgres  330604544 Sep 11 20:45 base/5/16466.1
-rw------- 1 postgres postgres     368640 Sep 11 20:46 base/5/16466_fsm

The first segment is exactly 1073741824 bytes, 131072 pages. The remaining 171429 minus 131072 is 40357 pages, times 8192 is 330604544, the size of .1. pg_relation_filepath still returns the base name; the segment suffix is added by the storage manager when it computes which file holds a given block. pg_relation_size sums all segments.

Block numbers don't know about segments. Block 131072 is simply the first page of 16466.1:

SELECT encode(substring(get_raw_page('files_big', 131072) from 1 for 24), 'hex');

                      encode
--------------------------------------------------
 00000000304b1e3c63ad00003400c8030020042000000000
(1 row)

$ od -A d -t x1 -N 24 base/5/16466.1
0000000 00 00 00 00 30 4b 1e 3c 63 ad 00 00 34 00 c8 03
0000016 00 20 04 20 00 00 00 00
0000024

Same bytes, checksum included this time. This table is ten times larger than this instance's 128MB shared_buffers, so the page was evicted after being written and read back from disk before get_raw_page saw it, and it carries the checksum the write computed.