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 );

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

postgres=# select pg_relation_filepath('t1');
(1 row)

… or we can use the oid2name utility:

[email protected]:/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:

[email protected]:/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:

[email protected]:/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);
postgres=# select pg_relation_filepath('i1');
(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;
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

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);
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;
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 …