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 …