Understanding OID to Filenode Mapping

In a standard PostgreSQL instance, each table and index corresponds to a physical file on disk. This relationship is primarily managed through the relfilenode identifier found in the pg_class catalog. For most user-defined tables, the Object Identifier (OID) aligns directly with this filenode value.

However, critical internal system catalogs behave differently. Certain high-frequency read relations, such as pg_type, pg_proc, and pg_class, show a relfilenode of 0. According to official documentation, this zero value signifies that the actual disk filename is not static but derived from runtime state. Accessing these objects directly via other catalog lookups would introduce performance overhead; therefore, they rely on a dedicated mapping mechanism for efficient resolution.

Locating the Mapping Files

These mappings are stored in binary files named pg_filennode.map. Their locations are determined by whether the catalog is database-specific or shared across the cluster:

• Database-Specific Mappings: Found in the base/\<database_oid\>/ directory within the data cluster.
• Cluster-Wide Mappings: Located in the global/ directory at the root of the data cluster.

Each pg_filennode.map file maintains a fixed size of 512 bytes, matching a standard OS disk sector. This format restricts the file to storing a maximum of 62 OID-to-Filenode pairs per file.

Inspecting Mappings Programmatically

To examine the internal mappings without relying solely on SQL queries against pg_class, administrators can utilize the pg_filedump utility. The -m flag specifically targets map files.

For example, extracting the mapping entries from a local database directory yields structured output similar to this:

pg_filedump -m base/13008/pg_filennode.map | head -n 20

Sample Output:

Magic Number: 0x592717 (CORRECT)
Num Mappings: 17
Detailed Mappings list:
OID: 1259       Filenode: 41317
OID: 1249       Filenode: 1249
OID: 1255       Filenode: 1255
OID: 1247       Filenode: 1247
... (omitted for brevity)

Verifying Relations via OIDs

Once the mappings are extracted, one can verify which logical object names correspond to specific internal identifiers. Below is an alternative method to correlate OIDs returned from the map file with their relation names using a dynamic query construction.

Instead of hardcoding a massive IN clause, a script might generate the filter condition dynamically:

SELECT relname, oid, relfilenode 
FROM pg_class 
WHERE relfilenode = 0 
  AND oid IN (
    1259, 1249, 1255, 1247, 2836, 2837, 
    4171, 4172, 2690, 2691, 2703, 2704,
    2658, 2659, 2662, 2663, 3455
  );

Resulting Correlation:

Distinction Between Shared and Local Catalogs

A crucial distinction exists between non-shared "Nail" tables and shared global tables. This separation is visible through the reltablespace column in pg_class.

• Local Tables (reltablespace = 0): These reside under the base/ directory. Examples include pg_namespace and indexes associated with specific databases.
• Shared Tables (reltablespace = 1664): These reside under the global/ directory. Critical examples include pg_database, pg_authid, and replication origins.

Inspecting the global/pg_filennode.map reveals mappings for these shared structures:

ls -l global/pg_filennode.map
# Result shows file size consistent with 512-byte sector limit

Extracting shared OID mappings allows verification of cluster-wide integrity:

SELECT relname, oid, reltablespace 
FROM pg_class 
WHERE relfilenode = 0 
  AND reltablespace = 1664;

Typical entries returned for the shared space include pg_database (OID 1262), pg_authid (OID 1260), and various toast indices, confirming their centralized storage location while maintaining the relfilenode alias mechanism.