An overview of the

All of the data in PostgreSQL is stored in the corresponding file, which is the file you’ll see most often. The files used to store the data together make up the entire PostgreSQL database cluster, which is the name given to a collection of databases in PostgreSQL. Logically, all of PostgreSQL’s databases belong to a tablespace, and a single database cannot span tablespaces, while a tablespace can contain multiple databases. The relationship between a table space and a database is many-to-many. So how is the data in the database stored in the data file? Let’s explore.

Understand the relationship between OID and RelFileNode

In PostgreSQL, the OID is called the Object Identifier and the Object Identifier. In PostgreSQL, the internal primary key data type used to assign each Object is alias RegClass, and the OID can be converted to an integer. RelFileNode is the physical access information for objects in the PostgreSQL database. RelFileNode is associated with three corresponding OIDs, namely, the OID of the tablespace, the OID of the database, and the OID of the object. By default, when an object is created, it is mapped to a RelFileNode number using the OID. Internal mapping is carried out by RelMapping. Therefore, in PostgreSQL, the management of all objects is managed by OID to manage internal objects, while external files are managed by RelFileNode.


postgres=# CREATE TABLE tab_test(id int,name varchar);
CREATE TABLE
postgres=# INSERT INTO tab_test VALUES(1,'PostgreSQL');
INSERT 0 1
postgres=# SELECT relname,oid,relfilenode FROM pg_class WHERE relname = 'tab_test';
 relname  |  oid  | relfilenode 
----------+-------+-------------
 tab_test | 16384 |       16384
(1 row)

From the above example, we can observe that the new object OID created is the same as the RelFileNode. The default first OID is 16384


#define FirstNormalObjectId             16384

The question then arises, if in a concurrent multi-transaction environment, whether the objects created will conflict.

-- transaction 1 postgres=# BEGIN; BEGIN postgres=*# CREATE TABLE tab_test(id int,name varchar); CREATE TABLE postgres=*# SELECT relname,oid,relfilenode FROM pg_class WHERE relname = 'tab_test'; Relname | | oid relfilenode -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- tab_test | 16384 | 16384 row (1) - transaction 2 postgres = # BEGIN; BEGIN postgres=*# CREATE TABLE tab_t(id int,name varchar); CREATE TABLE postgres=*# SELECT relname,oid,relfilenode FROM pg_class WHERE relname = 'tab_t'; relname | oid | relfilenode ---------+-------+------------- tab_t | 16390 | 16390

In fact, no, a new object is assigned a new OID. If a transaction is rolled back, the OID will not be reused and the OID value will increase.

The relFileNode value will be reset when the relFileNode and OID are inconsistent

1, VACUUM FULL

 postgres=# SELECT relname,oid,relfilenode
    FROM pg_class
    WHERE relname ~ 'tab_test';
     relname  |  oid  | relfilenode 
    ----------+-------+-------------
     tab_test | 16384 |       16384
    (1 row)

    postgres=# VACUUM tab_test;
    VACUUM
    postgres=# SELECT relname,oid,relfilenode
    FROM pg_class
    WHERE relname ~ 'tab_test';
     relname  |  oid  | relfilenode 
    ----------+-------+-------------
     tab_test | 16384 |       16384
    (1 row)
    postgres=# VACUUM FULL tab_test;
    VACUUM
    postgres=# SELECT relname,oid,relfilenode
    FROM pg_class
    WHERE relname ~ 'tab_test';
     relname  |  oid  | relfilenode 
    ----------+-------+-------------
     tab_test | 16384 |       16390

The VACUUM FULL operation resets the RELFILENODE for both the table and index

2, REINDEX INDEX


postgres=# CREATE INDEX idx_tab_test_id ON tab_test USING btree(id);
CREATE INDEX
postgres=# SELECT relname,oid,relfilenode
FROM pg_class
WHERE relname ~ 'idx_tab_test_id';
     relname     |  oid  | relfilenode 
-----------------+-------+-------------
 idx_tab_test_id | 16401 |       16401
(1 row)

postgres=# REINDEX INDEX idx_tab_test_id ;
REINDEX
postgres=# SELECT relname,oid,relfilenode
FROM pg_class
WHERE relname ~ 'idx_tab_test_id';
     relname     |  oid  | relfilenode 
-----------------+-------+-------------
 idx_tab_test_id | 16401 |       16402
(1 row)

RelFileNode resets only for the index

3, CLUSTER table_name USING index_name


postgres=# SELECT relname,oid,relfilenode
FROM pg_class
WHERE relname ~ 'tab_test|idx_tab_test_id';
     relname     |  oid  | relfilenode 
-----------------+-------+-------------
 tab_test        | 16384 |       16390
 idx_tab_test_id | 16401 |       16402
(2 rows)
postgres=# CLUSTER tab_test USING idx_tab_test_id;
CLUSTER
postgres=# SELECT relname,oid,relfilenode
FROM pg_class
WHERE relname ~ 'tab_test|idx_tab_test_id';
     relname     |  oid  | relfilenode 
-----------------+-------+-------------
 tab_test        | 16384 |       16403
 idx_tab_test_id | 16401 |       16409
(2 rows)

The CLUSTER operation resets the relfileNode for both the table and index

RelFileNode refers to the function

pg_relation_filonode()


postgres=# SELECT pg_relation_filenode('tab_test');
 pg_relation_filenode 
----------------------
                16410
(1 row)

pg_relation_filepath()


postgres=# SELECT pg_relation_filepath('tab_test');
 pg_relation_filepath 
----------------------
 base/13580/16410
(1 row)

conclusion

In PostgreSQL, in addition to using the OID as the object identifier, another type of identifier is XID, or transaction (abbreviated as Xact) identifier, which in Relation corresponds to the hidden columns Xmin and Xmax. Of course, CID is used as the command identifier in the Relation object to hide the presence of the columns CMIN and CMAX. The tuple identifier tid is used to identify the physical location of tuples (i.e., rows) in the table. Ctid is used in relation objects, which is also a hidden attribute.