Relations, forks and segments · PostgreSQL

“The orders table is 40 GB” is an incomplete statement, because it does not say which of several things is being measured. A relation is stored as a set of files, each holding something different, and the indexes and TOAST data may be larger than the rows.

The forks

Fork	Filename	Holds
main	`16390`, `16390.1`, …	The rows themselves
fsm	`16390_fsm`	Approximate free space per page
vm	`16390_vm`	Which pages are all-visible and all-frozen
init	`16390_init`	The empty state of an unlogged relation

Read-only / Safethe forks of two real tables— Captured from a live 18.6 cluster. The vm fork is empty because neither table has been vacuumed yet.

$ psql -U postgres -c "SELECT relname, pg_size_pretty(pg_relation_size(oid,'main')) AS main, pg_size_pretty(pg_relation_size(oid,'fsm')) AS fsm, pg_size_pretty(pg_relation_size(oid,'vm')) AS vm FROM pg_class WHERE relname IN ('narrow','wide')"

 relname |  main   |  fsm  |   vm
---------+---------+-------+---------
narrow  | 3544 kB | 24 kB | 0 bytes
wide    | 464 kB  | 24 kB | 0 bytes
(2 rows)

The visibility map being empty is informative rather than a problem: those forks are created and populated by VACUUM, and neither table had been vacuumed. Part VIII returns to what that costs.

Segments

The main fork is split into files of 1 GB. A 3.5 GB table is 16390, 16390.1, 16390.2 and a partial 16390.3.

The historical reason was filesystem limits on maximum file size. The reason it survives is more practical: extending and truncating a relation touches one segment rather than one enormous file, and a filesystem is generally better at managing several large files than one gigantic one.

The operational consequence is that a large table appears in a directory listing as many files with numeric suffixes, and counting them is a rough size estimate. It also means ls -la on a data directory is not a useful way to find large tables, because a 400 GB table is 400 files rather than one obvious entry.

The three size functions

This is the distinction that decides whether a disk investigation finds the right table.

Function	Includes
`pg_relation_size(oid)`	The main fork only, by default
`pg_relation_size(oid, 'fsm')`	One named fork
`pg_table_size(oid)`	Main + fsm + vm + TOAST
`pg_indexes_size(oid)`	All indexes on the relation
`pg_total_relation_size(oid)`	Table size + indexes: everything

Read-only / Safethe same three tables under each measure— Captured live. Note wide_random: a 104 kB main fork and a 16 MB table.

$ psql -U postgres -c 'SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS main_fork, pg_size_pretty(pg_table_size(oid)) AS table_size, pg_size_pretty(pg_total_relation_size(oid)) AS total FROM pg_class WHERE relname IN (...)'

   relname   | main_fork | table_size | total
-------------+-----------+------------+--------
narrow      | 3544 kB   | 3568 kB    | 5776 kB
wide        | 464 kB    | 496 kB     | 560 kB
wide_random | 104 kB    | 16 MB      | 16 MB
(3 rows)

Three readings worth drawing out.

narrow is 3.5 MB of rows and 5.8 MB in total — the primary key index is nearly two-thirds the size of the data. That ratio is entirely normal and is the reason index size belongs in capacity planning.

wide_random has a 104 kB main fork and a 16 MB table. The rows are almost all somewhere else, and pg_relation_size reports the 104 kB. An investigation using that function would rank this table as one of the smallest in the database.

Using pg_relation_size to find what is consuming the disk

It is the shortest name, it is the one that appears first in most examples, and it reports the main fork only.

For a table with large values, most of the data is in an associated TOAST relation with its own OID and its own name, and pg_relation_size excludes it. For a heavily indexed table, the indexes can exceed the rows several times over, and it excludes those too.

So the function most likely to be reached for is the one that most reliably understates. On the capture above it reports 104 kB for a 16 MB table — a factor of 160.

pg_total_relation_size is the one to use when the question is “what is consuming space”. The query worth keeping:

SELECT n.nspname AS schema, c.relname,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
       pg_size_pretty(pg_relation_size(c.oid))       AS heap,
       pg_size_pretty(pg_indexes_size(c.oid))        AS indexes,
       pg_size_pretty(pg_total_relation_size(c.oid)
                    - pg_table_size(c.oid))          AS index_only
  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind IN ('r','m','p')
   AND n.nspname NOT IN ('pg_catalog','information_schema')
 ORDER BY pg_total_relation_size(c.oid) DESC
 LIMIT 20;

Reading total against heap immediately shows whether the space is in the rows, in the indexes, or in TOAST.

Finding the file behind a relation

# The path, relative to the data directory. Correct across tablespaces.
psql -U postgres -c "SELECT pg_relation_filepath('orders')"

# Every fork of that relation, with sizes
psql -U postgres -c \
  "SELECT fork, pg_size_pretty(pg_relation_size('orders', fork)) AS size
     FROM unnest(ARRAY['main','fsm','vm']) AS fork"

# The TOAST relation, if one exists
psql -U postgres -c \
  "SELECT c.relname, t.relname AS toast_relation,
          pg_size_pretty(pg_relation_size(t.oid)) AS toast_size
     FROM pg_class c LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
    WHERE c.relname = 'orders'"

Going the other way — from a large file to the relation responsible — is the disk-full case, and it needs the relfilenode:

SELECT c.relname, c.relkind, n.nspname
  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relfilenode = 16390;

That query returns nothing for a relation whose relfilenode was changed by a rewrite, and nothing for a file left behind by an interrupted operation, which is itself a useful signal.

Why relfilenode is separate from OID, and what that buys

Every relation has an OID, which is its permanent identity in the catalogue, and a relfilenode, which names its files. For a newly created table the two are usually equal, and they diverge the first time the relation is rewritten.

VACUUM FULL, CLUSTER, TRUNCATE and some forms of ALTER TABLE work by building a complete new copy of the relation with a new relfilenode, then switching the catalogue to point at it and dropping the old files. The separation is what makes this possible: the table’s identity does not change, so every foreign key, view and permission that refers to it remains valid, while the files underneath are replaced wholesale.

Two operational consequences follow.

A rewrite needs room for both copies. VACUUM FULL on a 400 GB table requires 400 GB of free space in the tablespace before it can complete, which is a common and unwelcome discovery on a filesystem that was already nearly full. That is one of several reasons Part VIII recommends against reaching for it.

A cached filepath goes stale silently. A monitoring script that records pg_relation_filepath once and then watches that path will, after any rewrite, be watching a file that no longer exists or that belongs to something else. Ask the server each time.

Some catalogue relations have a relfilenode of 0, which means their location is not stored in the catalogue at all — it cannot be, since reading the catalogue requires finding them first. Those are resolved through a mapping file in the data directory, which is why pg_relation_filepath still works for them.

Production discipline

Use pg_total_relation_size for capacity questions. pg_relation_size reports the main fork and understates by a factor that can exceed a hundred.
Compare total against heap to see immediately whether space is in rows, indexes or TOAST.
Ask the server for a relation’s path each time. A rewrite changes the relfilenode and a cached path becomes wrong silently.
Do not size tables from a directory listing. A large table is many 1 GB segments, and TOAST data is under a different name entirely.
Budget free space for rewrites. Any operation that rewrites a relation needs room for a second complete copy.
Remember index size in capacity planning. On the capture above a single primary key was nearly two-thirds the size of the data.

Cross-course references

Linux for Production Sysadmins — Part XIII (Disks) and Part XVIII (Storage) cover the filesystem beneath these files, and Part XLI (Disk performance) covers measuring their I/O.
Ceph & Distributed Storage — Part LXIII (Capacity) covers planning for a workload whose real size is larger than its apparent one, which is the same problem this lesson describes.
Observability for Production Sysadmins — Part LIX (Database observability) covers exporting per-relation sizes so growth is visible before it is urgent.