Contents

filenode

A numeric identifier used to name on-disk files belonging to a relation

A filenode is a unique numeric identifier used to name on-disk files belonging to a relation. It is stored in the relation's pg_class entry in the relfilenode column as an OID datatype.

While the relfilenode is the same as the relation's OID when the relation is created, it will change if the relation is rewritten (e.g. following a VACUUM FULL or TRUNCATE).

See below for functions to assist mapping filenode IDs to relations and vice-versa.

Functions

PostgreSQL provides the following functions to assist with mapping filenode IDs to relations:

  • pg_relation_filenode(relation regclass)
  • pg_filenode_relation(tablespace oid, filenode oid) (PostgreSQL 9.4 and later)

pg_relation_filenode() returns the filenode number of the specified object. Additionally, pg_relation_filepath() will provide the full filenode path (relative to PGDATA).

pg_filenode_relation() returns the OID of the relation associated with the specified tablespace (a value of 0 implies the default tablespace) and filenode.

Additionally, the contrib module oid2name can also be used to map OIDs and filenodes in a data directory to object names.

relfilenode = 0

If a relation's relfilenode column in pg_class is 0, this means it is a "mapped" relation whose on-disk filename is determined at a lower level via pg_filenode.map; in practice this means tables in the schema pg_catalog.

Source code

Internally a RelFileNode struct is used to reference filenodes; see src/include/storage/relfilenode.h:

typedef struct RelFileNode
{
        Oid                     spcNode;                /* tablespace */
        Oid                     dbNode;                 /* database */
        Oid                     relNode;                /* relation */
} RelFileNode;

This enables backends to determine the on-disk location of buffer pages without knowing anything about their underying tables; see e.g. comment in src/include/storage/buf_internals.h.

Examples

postgres=# CREATE TABLE filenode_test (id INT);
CREATE TABLE

postgres=# SELECT oid, relfilenode FROM pg_class WHERE relname='filenode_test';
  oid  | relfilenode 
-------+-------------
 16461 |       16461

Here, oid and relfilenode are identical. This changes after a full rewrite, e.g. with VACUUM FULL:

postgres=# VACUUM FULL filenode_test;
VACUUM
Time: 14.908 ms
postgres=# SELECT oid, relfilenode FROM pg_class WHERE relname='filenode_test';
  oid  | relfilenode 
-------+-------------
 16461 |       16464
(1 row)

Functions

Extracting filenode information from the relation:

postgres=# SELECT oid, relfilenode,
    pg_relation_filenode(relname::regclass),
    pg_relation_filepath(relname::regclass)
  FROM pg_class WHERE relname='filenode_test';

  oid  | relfilenode | pg_relation_filenode | pg_relation_filepath 
-------+-------------+----------------------+----------------------
 16461 |       16464 |                16464 | base/13285/16464
(1 row)

Extracting relation information from the filenode:

postgres=# SELECT pg_filenode_relation(0, 16464)::oid, pg_filenode_relation(0, 16464);
 pg_filenode_relation | pg_filenode_relation 
----------------------+----------------------
                16461 | filenode_test
(1 row)

Determine if a set of filenode IDs belong to relations in a tablespace in the current database:

postgres=# WITH a(x) AS (VALUES (16452), (16468)) SELECT pg_filenode_relation(0, x)::REGCLASS FROM a;

0 is the default tablespace; replace with a non-default tablespace OID as required.

Categories

PostgreSQL internals, Storage

See also

OID, oid2name, mapped catalog, pg_filenode.map