Infrastructure at your Service

Daniel Westermann

When does PostgreSQL create the table and index files on disk?

A question that pops up from time to time is: When we create a table or an index in PostgreSQL are the files on disk created immediately or is this something that happens when the first row is inserted? The question mostly is coming from Oracle DBAs because in Oracle you can have deferred segment creation. In PostgreSQL there is no parameter for that so lets do a quick test.

We start with a simple table:

postgres=# create table t1 ( a int );
CREATE TABLE

To get the real file name we can either use the pg_relation_filepath function:

postgres=# select pg_relation_filepath('t1');
 pg_relation_filepath 
----------------------
 base/33845/33933
(1 row)

… or we can use the oid2name utility:

postgres@pgbox:/home/postgres/ [PG10] oid2name -d postgres -t t1
From database "postgres":
  Filenode  Table Name
----------------------
     33933          t1

Now we can easily check if that file is already existing:

postgres@pgbox:/home/postgres/ [PG10] ls -la $PGDATA/base/33845/33933
-rw-------. 1 postgres postgres 0 Jul 24 07:47 /u02/pgdata/10/PG103/base/33845/33933

It is already there but empty. The files for the visibility map and the free space map are not yet created:

postgres@pgbox:/home/postgres/ [PG10] ls -la $PGDATA/base/33845/33933*
-rw-------. 1 postgres postgres 0 Jul 24 07:47 /u02/pgdata/10/PG103/base/33845/33933

What happens when we create an index on that empty table?

postgres=# create index i1 on t1 (a);
CREATE INDEX
postgres=# select pg_relation_filepath('i1');
 pg_relation_filepath 
----------------------
 base/33845/33937
(1 row)
postgres=# \! ls -la $PGDATA/base/33845/33937
-rw-------. 1 postgres postgres 8192 Jul 24 08:06 /u02/pgdata/10/PG103/base/33845/33937

The file is created immediately as well but it is not empty. It is exactly one page (my blocksize is 8k). Using the pageinspect extension we can confirm that this page is just for metadata information:

postgres=# create extension pageinspect;
CREATE EXTENSION
postgres=# SELECT * FROM bt_metap('i1');
 magic  | version | root | level | fastroot | fastlevel 
--------+---------+------+-------+----------+-----------
 340322 |       2 |    0 |     0 |        0 |         0
(1 row)
postgres=# SELECT * FROM bt_page_stats('i1', 0);
ERROR:  block 0 is a meta page
postgres=# 

The remaining questions is: When will the free space map and the visibility map be created? After or with the first insert?

postgres=# insert into t1 (a) values (1);
INSERT 0 1
postgres=# \! ls -la $PGDATA/base/33845/33933*
-rw-------. 1 postgres postgres 8192 Jul 24 08:19 /u02/pgdata/10/PG103/base/33845/33933

Definitely not. The answer is: vacuum:

postgres=# vacuum t1;
VACUUM
postgres=# \! ls -la $PGDATA/base/33845/33933*
-rw-------. 1 postgres postgres  8192 Jul 24 08:19 /u02/pgdata/10/PG103/base/33845/33933
-rw-------. 1 postgres postgres 24576 Jul 24 08:22 /u02/pgdata/10/PG103/base/33845/33933_fsm
-rw-------. 1 postgres postgres  8192 Jul 24 08:22 /u02/pgdata/10/PG103/base/33845/33933_vm

Hope that helps …

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure