Difference between oid and relfilenode

Question

I am reading Internals of postgreSQL chp 1 and I am unable to understand the difference between object identifier and relfilenode.

Tables and indexes as database objects are internally managed by individual OIDs, while those data files are managed by the variable, relfilenode. The relfilenode values of tables and indexes basically but not always match the respective OIDs

I get that both these are the attributes of the system catalog 'pg_class' and OID can be thought of as the primary key of the table, so what is the purpose of relfilenode and how is it different from OID?

Answer 1

relfilenode is the prefix for the name of the files that make up the table. Initially it is identical to the immutable object ID (oid), but SQL statements that rewrite the table will modify it (for example VACUUM (FULL), CLUSTER, TRUNCATE or the variants of ALTER TABLE that rewrite the table).

Answer 2

PostgreSQL assigns OIDs (object identifiers), which are distinctive identifiers, to database objects. They act as internal primary keys that are used to refer to and identify things in various system catalogues.

The relfilenode attribute, on the other hand, is exclusive to tables and indexes in the pg_class system catalogue table. It symbolises the actual on-disk file connected to a specific table or index. Each table or index has a distinct relfilenode value associated with it, which aids the storage engine in identifying the data's actual file.

Answer 3

• OID can be described as the primary key for uniquely identification of rows of tables in the system catalog.

• While relfilenode represents a name that is used to locate the file on disk. It is related to the physical storage of file in the disk.

Answer 4

OIDs and relfilenodes are both identifiers used by Postgres but for different purposes.

• OIDs are used to identify table rows. They can also be used to establish relationships between tables such as foreign keys.
• relfilnodes are used at system level to identify the table and/or index. It's used by Postgres under the hood to manage physical files that contain data and indexes.

Hope that helps.

Answer 5

• oid stands for object identifier and is an immutable value assigned to an object such as a table.
• relfilenode value specifies the file name of the table on disk

Initially, both oid and relfilenode are the same for a table but with operations that rewrite the table on the disk, relfilenode gets updated but oid is not.

Operations like truncate, vacuum full updates the relfilenode, but insert, update does not.

Answer 6

• OID is used as an internal row identifier for every row in a table (deprecated as a primary key for user-created tables), and it can also be used for specific purposes like large objects or system catalogs if enabled.

• relfilenode is an identifier for the underlying file that stores the data or index associated with a table or index. It is used internally by PostgreSQL to manage the physical storage of tables and indexes.

Answer 7

In PostgreSQL, OIDs (Object Identifiers) are unique internal identifiers for database objects that were previously used for referencing system catalog items included.

'relfilenode' values, on the other hand, can be utilized internally to handle the physical storage of table and index data on the disc, referring to the related data files.

OIDs are becoming less significant in current PostgreSQL versions, but'relfilenode' is still important for organizing database file structure.

Answer 8

• In PostgreSQL OID's are distinctive identifiers to database objects. They act internal primary keys that are used to refer to and identify things in various system catalogues.
• The relfilenode attribute on the other hand is exclusive to tables and indexes in the pg_class system catalogue table. It symbolizes the actual on-disk file connected to a specific table or index. Each table or index has a distinct relfilenode value associated with it, which aids the storage engine in identifying the data's actual file.