skip to content

In PostgreSQL, which files under the data directory hold one table, and what are the _fsm and _vm files beside it?

level: juniorimportance: should knowfreq 34%

answer

  1. one directory per database OID
  2. named by a number, not the table name
  3. 1 GB segments: .1, .2
  4. main, fsm, vm, init forks
  5. pg_relation_filepath()

basics

~20 s

A PostgreSQL table lives in PGDATA/base/<database OID>/ as a file named after its relfilenode, split into 1 GB segments of 8 kB pages. Beside it sit forks: _fsm (free space map), _vm (visibility map) and, for unlogged tables, _init.

solid answer

~40 s

Under `PGDATA/base/` there is one directory per database, named by the database's OID. A table's data, the **main fork**, is a file named after `pg_class.relfilenode`, which usually starts equal to the table's OID but changes whenever the table is written into a fresh file (`TRUNCATE`, `VACUUM FULL`, `CLUSTER`, some `ALTER TABLE` forms). Past 1 GB the file continues as `16391.1`, `16391.2` and so on, and every file is an array of 8 kB pages. Next to it, `16391_fsm` is the **free space map** (roughly how much room each page has) and `16391_vm` is the **visibility map** (two bits per heap page: all-visible, all-frozen). Unlogged tables also have `16391_init`, an empty copy used to reset them after a crash. Wide columns add a separate TOAST table with its own files. `SELECT pg_relation_filepath('articles');` returns the path.

code

sql · 9 lines
sql
-- Where does the staging table live, and does TRUNCATE move it?
SELECT pg_relation_filepath('article_import_staging');
-- e.g. base/16384/16420

TRUNCATE article_import_staging;

SELECT pg_relation_filepath('article_import_staging');
-- a new filenode, e.g. base/16384/16431; the table OID is unchanged
SELECT oid, relfilenode FROM pg_class WHERE relname = 'article_import_staging';

go deeper

for a junior

Remember the shape: base, then a directory per database, then numbered files of 8 kB pages split at 1 GB, with _fsm and _vm beside each table.

for a middle

Explain why the filenode can differ from the OID and which commands change it, and what the free space map and visibility map each record per page.

for a senior

Use the layout in operations: map a huge numbered file back to its table, know why per-table file copies are useless, and what the init fork does after a crash.

for a principal

Connect the layout to policy: why physical backups are whole-cluster, why build-time page and segment sizes are effectively fixed, and how that shapes storage planning.

## The data directory at a glance A PostgreSQL **cluster** keeps its data under one directory, `PGDATA` (for example `/var/lib/pgsql/data`). For table storage, three entries matter: - `base/` holds one subdirectory per database, named after the database's OID in `pg_database`, such as `base/16384/`. - `global/` holds cluster-wide catalogs such as `pg_database` itself. - `PG_VERSION` records the major version the directory belongs to. Other entries (`pg_wal`, `pg_xact`, `pg_tblspc`, `postmaster.pid`) belong to WAL, transaction status, tablespaces and the server lifecycle, which are separate subjects. ## One table, several forks Each table and each index is stored in its own files, named after its **filenode**, the number in `pg_class.relfilenode`. A relation is made of **forks**, each a separate file sharing that number: | Fork | File | What it holds | |---|---|---| | main | `16391` | The data pages: rows for a table, entries for an index | | fsm | `16391_fsm` | The **free space map**: one byte per page approximating its free space, organised as a tree so an `INSERT` can find a page with room quickly | | vm | `16391_vm` | The **visibility map**, tables only: two bits per heap page, all-visible and all-frozen | | init | `16391_init` | Unlogged tables and their indexes only: an empty copy that replaces the main fork after a crash | Every table and index except hash indexes has a free space map; indexes have no visibility map. A table whose columns can hold long variable-length values also has a **TOAST table**, linked from `pg_class.reltoastrelid`, with its own set of files. Temporary tables use a different name, `t<backend number>_<filenode>`, and spill files from large sorts land in `base/pgsql_tmp/`. ## Segments and pages With the default build, a data file does not grow past **1 GB**. A larger relation continues in `16391.1`, `16391.2` and so on. The segment size is a build-time option (`--with-segsize`), not a setting you can change on a running server. Inside every file the unit is the **page** (block), 8 kB by default and also fixed when the server is compiled. A table is an array of such pages, and a row is addressed by its `ctid`: page number plus the number of a line pointer on that page. With the default page size one relation can reach 32 TB, and a single field value can be up to 1 GB. ## The filenode is not the OID `pg_class.oid` identifies a table for its whole life. The filenode usually starts equal to it but changes whenever PostgreSQL writes the relation into a fresh file: 1. `TRUNCATE` swaps in a new, empty file. 2. `VACUUM FULL` and `CLUSTER` write a complete new copy, then drop the old one. 3. `REINDEX` does the same for an index. 4. `ALTER TABLE` forms that rewrite the table, such as most column type changes, also assign a new filenode. So never derive a file name from the OID. For a few system catalogs, `pg_class` among them, `relfilenode` is even `0`, and `pg_relation_filenode()` returns the real number. The simplest tool is `pg_relation_filepath()`, which returns the path of the first segment of the main fork relative to `PGDATA`; append `.N`, `_fsm`, `_vm` or `_init` to find the rest. ```sql SELECT oid, relfilenode, reltoastrelid::regclass AS toast_table FROM pg_class WHERE relname = 'articles'; SELECT pg_relation_filepath('articles'); ``` The fork names are also the vocabulary of the inspection functions: the contrib function `get_raw_page('articles', 'vm', 0)` from `pageinspect` reads block 0 of the visibility map fork, and the names it accepts are exactly `main`, `fsm`, `vm` and `init`. Because the free space map spends one byte and the visibility map two bits per heap page, both forks stay tiny next to the main fork; a 10 GB table has a visibility map of well under a megabyte. ## Why it matters in practice - **Disk diagnosis.** When a database directory fills up, mapping a large numbered file back to its table goes through `relfilenode`; the contrib program `oid2name` does that mapping from the shell. - **No per-table file copying.** The files only make sense together with the cluster's catalogs and transaction status, so copying one table's files into another cluster does not move the table. - **Unlogged tables reset.** After a crash or unclean shutdown, the `_init` fork is copied over the main fork and the other forks are removed, which is why an unlogged table comes back empty. - **Inspection tools.** The contrib modules `pg_freespacemap`, `pg_visibility` and `pageinspect` read the fsm, the vm and raw pages of any fork. Measuring how large each relation is belongs to the size functions covered with the statistics views.

  • Why is copying one table's files into another PostgreSQL cluster not a way to move that table?
    The files hold tuples whose visibility depends on transaction status kept in the source cluster's `pg_xact` and on transaction IDs that mean nothing elsewhere, while the table's definition lives in the source database's catalogs. Recent changes may also still sit in shared buffers. File-level copies only work for a whole cluster; a single table moves with a dump and restore or logical replication.
  • What happens to an unlogged table's files after a crash?
    During recovery PostgreSQL copies the `_init` fork, an empty table, over the main fork and removes the other forks, so the table comes back empty. The same reset follows an unclean shutdown. That is the price of skipping WAL: fast writes, no crash safety, and no replication to standbys.

  • Table files are named after the table, so you can find them by name.
  • A table's file name always equals its OID, so it is safe to hard-code.
  • The _vm file is a vacuum log listing which rows were removed.
  • PostgreSQL keeps each table in one ever-growing file with no segments.
  • Copying one table's files to another cluster is a valid way to move it.