Quite some time ago I’ve written about relation files in PostgreSQL and when they get created. In that post we talked about the files containing the actual data, the free space map (fsm) and the visibility map (vm). But this is not all, there is another kind of file you might spot if you take a look into the directory of a database.
The directory of the postgres database of a fresh initialized PostgreSQL cluster looks like this:
postgres@debian11pg:/u02/pgdata/PGDEV/base/13540/ [pgdev] oid2name
All databases:
Oid Database Name Tablespace
----------------------------------
13540 postgres pg_default
13539 template0 pg_default
1 template1 pg_default
postgres@debian11pg:/u02/pgdata/PGDEV/base/13540/ [pgdev] ls
112 1259 13366 13377 2337 2603 2607_fsm 2611 2617 2651 2662 2675 2688 2704 2833 2840 3085 3381 3455 3541 3600 3603_vm 3766 4151 4164 5002 6175
113 1259_fsm 13367 1417 2579 2603_fsm 2607_vm 2612 2617_fsm 2652 2663 2678 2689 2753 2834 2840_fsm 3118 3394 3456 3541_fsm 3600_fsm 3604 3767 4152 4165 548 6176
1247 1259_vm 13368 1418 2600 2603_vm 2608 2612_fsm 2617_vm 2653 2664 2679 2690 2753_fsm 2835 2840_vm 3119 3394_fsm 3456_fsm 3541_vm 3600_vm 3605 3997 4153 4166 549 826
1247_fsm 13358 13368_fsm 16387 2600_fsm 2604 2608_fsm 2612_vm 2618 2654 2665 2680 2691 2753_vm 2836 2841 3164 3394_vm 3456_vm 3542 3601 3606 4143 4154 4167 6102 827
1247_vm 13358_fsm 13368_vm 174 2600_vm 2605 2608_vm 2613 2618_fsm 2655 2666 2681 2692 2754 2836_fsm 2995 3256 3395 3466 3574 3601_fsm 3607 4144 4155 4168 6104 828
1249 13358_vm 13371 175 2601 2605_fsm 2609 2615 2618_vm 2656 2667 2682 2693 2755 2836_vm 2996 3257 3429 3467 3575 3601_vm 3608 4145 4156 4169 6106 pg_filenode.map
1249_fsm 13361 13372 2187 2601_fsm 2605_vm 2609_fsm 2615_fsm 2619 2657 2668 2683 2696 2756 2837 3079 3258 3430 3468 3576 3602 3609 4146 4157 4170 6110 pg_internal.init
1249_vm 13362 13373 2224 2601_vm 2606 2609_vm 2615_vm 2619_fsm 2658 2669 2684 2699 2757 2838 3079_fsm 3350 3431 3501 3596 3602_fsm 3712 4147 4158 4171 6111 PG_VERSION
1255 13363 13373_fsm 2228 2602 2606_fsm 2610 2616 2619_vm 2659 2670 2685 2701 2830 2838_fsm 3079_vm 3351 3433 3502 3597 3602_vm 3764 4148 4159 4172 6112
1255_fsm 13363_fsm 13373_vm 2328 2602_fsm 2606_vm 2610_fsm 2616_fsm 2620 2660 2673 2686 2702 2831 2838_vm 3080 3379 3439 3503 3598 3603 3764_fsm 4149 4160 4173 6113
1255_vm 13363_vm 13376 2336 2602_vm 2607 2610_vm 2616_vm 2650 2661 2674 2687 2703 2832 2839 3081 3380 3440 3534 3599 3603_fsm 3764_vm 4150 4163 4174 6117
You can see the files containing actual data (the numbers with any suffix), free space and visibility maps and three other files will not look at in this post (pg_filenode.map,pg_internal.init,PG_VERSION).
Lets assume the cluster is running for the time, the application got deployed and users are already working on it. For whatever reason you check the directory of the postgres database once more and now it looks like this:
postgres@debian11pg:/u02/pgdata/PGDEV/base/13540/ [pgdev] ls 112 1259 13366 13377 2224 2601_vm 2606 2609_vm 2615_vm 2619_fsm 2658 2669 2684 2699 2757 2838 3079_fsm 3350 3431 3501 3596 3602_fsm 3712 4147 4158 4171 6111 PG_VERSION 113 1259_fsm 13367 1417 2228 2602 2606_fsm 2610 2616 2619_vm 2659 2670 2685 2701 2830 2838_fsm 3079_vm 3351 3433 3502 3597 3602_vm 3764 4148 4159 4172 6112 1247 1259_vm 13368 1418 2328 2602_fsm 2606_vm 2610_fsm 2616_fsm 2620 2660 2673 2686 2702 2831 2838_vm 3080 3379 3439 3503 3598 3603 3764_fsm 4149 4160 4173 6113 1247_fsm 13358 13368_fsm 16387 2336 2602_vm 2607 2610_vm 2616_vm 2650 2661 2674 2687 2703 2832 2839 3081 3380 3440 3534 3599 3603_fsm 3764_vm 4150 4163 4174 6117 1247_vm 13358_fsm 13368_vm 16388 2337 2603 2607_fsm 2611 2617 2651 2662 2675 2688 2704 2833 2840 3085 3381 3455 3541 3600 3603_vm 3766 4151 4164 5002 6175 1249 13358_vm 13371 16388_init 2579 2603_fsm 2607_vm 2612 2617_fsm 2652 2663 2678 2689 2753 2834 2840_fsm 3118 3394 3456 3541_fsm 3600_fsm 3604 3767 4152 4165 548 6176 1249_fsm 13361 13372 16391 2600 2603_vm 2608 2612_fsm 2617_vm 2653 2664 2679 2690 2753_fsm 2835 2840_vm 3119 3394_fsm 3456_fsm 3541_vm 3600_vm 3605 3997 4153 4166 549 826 1249_vm 13362 13373 16391_init 2600_fsm 2604 2608_fsm 2612_vm 2618 2654 2665 2680 2691 2753_vm 2836 2841 3164 3394_vm 3456_vm 3542 3601 3606 4143 4154 4167 6102 827 1255 13363 13373_fsm 174 2600_vm 2605 2608_vm 2613 2618_fsm 2655 2666 2681 2692 2754 2836_fsm 2995 3256 3395 3466 3574 3601_fsm 3607 4144 4155 4168 6104 828 1255_fsm 13363_fsm 13373_vm 175 2601 2605_fsm 2609 2615 2618_vm 2656 2667 2682 2693 2755 2836_vm 2996 3257 3429 3467 3575 3601_vm 3608 4145 4156 4169 6106 pg_filenode.map 1255_vm 13363_vm 13376 2187 2601_fsm 2605_vm 2609_fsm 2615_fsm 2619 2657 2668 2683 2696 2756 2837 3079 3258 3430 3468 3576 3602 3609 4146 4157 4170 6110 pg_internal.init
In addition to the files we already know, there are two “_init” files. What are they used for? To answer that lets check the names of those relations using oid2name:
postgres@debian11pg:/u02/pgdata/PGDEV/base/13540/ [pgdev] oid2name -f 16388
From database "postgres":
Filenode Table Name
----------------------
16388 t1
postgres@debian11pg:/u02/pgdata/PGDEV/base/13540/ [pgdev] oid2name -f 16391
From database "postgres":
Filenode Table Name
----------------------
16391 t2
Asking pg_class what type of relation those are, tells as both are ordinary tables:
postgres=# select relkind from pg_class where relname in ('t1','t2');
relkind
---------
r
r
(2 rows)
But ordinary tables do not have any "*_init" files, do they? They have, as long as they are created as "unlogged" tables:
postgres=# d t1
Unlogged table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
postgres=# d t2
Unlogged table "public.t2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Each unlogged table automatically gets a so-called initialization fork:
postgres=# create unlogged table t3 ( a int );
CREATE TABLE
postgres=# select pg_relation_filepath('t3');
pg_relation_filepath
----------------------
base/13540/16394
(1 row)
postgres=# ! ls -l $PGDATA/base/13540/16394*
-rw------- 1 postgres postgres 0 Aug 24 13:17 /u02/pgdata/PGDEV/base/13540/16394
-rw------- 1 postgres postgres 0 Aug 24 13:17 /u02/pgdata/PGDEV/base/13540/16394_init
What are those forks used for? What you need to know with unlogged tables is, that they are re-initialized when PostgreSQL crashes. First lets add some data to the unlogged table:
postgres=# insert into t3 select * from generate_series(1,1000); INSERT 0 1000 postgres=# select count(*) from t3; count ------- 1000 (1 row)
Now lets crash PostgreSQL:
postgres@debian11pg:/home/postgres/ [pgdev] ps -ef | grep "postgres -D" postgres 35443 1 0 13:22 ? 00:00:00 /u01/app/postgres/product/DEV/db_0/bin/postgres -D /u02/pgdata/PGDEV postgres@debian11pg:/home/postgres/ [pgdev] kill -9 35443
Starting up and checking the content of the tables confirms that it is empty:
postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl start
postgres@debian11pg:/home/postgres/ [pgdev] psql -c "select count(*) from t3" postgres
count
-------
0
(1 row)
What happened is, that PostgreSQL replaced the data file of that unlogged table with it’s initialization fork.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
Hari
25.03.2024Great article, What about pg_internal.int file?
Daniel Westermann
26.03.2024This is the internal relation cache file to speedup backend startup