You probably know that a fresh initialized PostgreSQL cluster comes with three databases by default: template0, template1, and postgres. If you want to know why they are there, and what their purpose is, check this post. In this post we’ll look at something you may never have noticed: Why is the postgres database, even after a fresh initdb, slightly larger than template1 and template0?
Let’s start from scratch and initialize a new cluster:
postgres@debian10pg:/home/postgres/ [pg14] initdb -D /var/tmp/dummy The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. creating directory /var/tmp/dummy ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Europe/Zurich creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: pg_ctl -D /var/tmp/dummy -l logfile start
Once we start start that up and ask for the size of the databases, you’ll notice the difference:
postgres@debian10pg:/home/postgres/ [pg14] export PGPORT=8888 postgres@debian10pg:/home/postgres/ [pg14] pg_ctl -D /var/tmp/dummy start waiting for server to start....2021-05-20 11:04:49.527 CEST [2708] LOG: starting PostgreSQL 14devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit 2021-05-20 11:04:49.528 CEST [2708] LOG: listening on IPv6 address "::1", port 8888 2021-05-20 11:04:49.528 CEST [2708] LOG: listening on IPv4 address "127.0.0.1", port 8888 2021-05-20 11:04:49.541 CEST [2708] LOG: listening on Unix socket "/tmp/.s.PGSQL.8888" 2021-05-20 11:04:49.557 CEST [2709] LOG: database system was shut down at 2021-05-20 11:03:23 CEST 2021-05-20 11:04:49.566 CEST [2708] LOG: database system is ready to accept connections done server started postgres@debian10pg:/home/postgres/ [pg14] psql -c "l+" List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8265 kB | pg_default | default administrative connection database template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8113 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8113 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | | | (3 rows)
The results may differ in your environment, depending on what version of PostgreSQL you are using. This is 14devel, but it doesn’t really matter: You will see that the “postgres” database is a bit larger than the other two. But how can this be? The first database which is created by initdb, is template1. The other two are just copies of it. But then they should all have the same size, shouldn’t they?
To understand what’s going on here, we first need to know how the database size is calculated, when you use the “l+” shortcut. This is easy to check:
postgres=# set ECHO_HIDDEN on postgres=# l+ ********* QUERY ********** SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'n') AS "Access privileges", CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE 'No Access' END as "Size", t.spcname as "Tablespace", pg_catalog.shobj_description(d.oid, 'pg_database') as "Description" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid ORDER BY 1; ************************** List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8265 kB | pg_default | default administrative connection database template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8113 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8113 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | | | (3 rows)
The statement that is used in the background uses the pg_database_size administrative function to calculate the size for each database. But what is pg_database_size actually doing? Time to check the source. pg_database_size is defined in “src/backend/utils/adt/dbsize.c”:
Datum pg_database_size_oid(PG_FUNCTION_ARGS) { Oid dbOid = PG_GETARG_OID(0); int64 size; size = calculate_database_size(dbOid); if (size == 0) PG_RETURN_NULL(); PG_RETURN_INT64(size); }
This in fact calls “calculate_database_size”, which is defined in the same file. If you check this one you’ll see this:
... /* Shared storage in pg_global is not counted */ /* Include pg_default storage */ snprintf(pathname, sizeof(pathname), "base/%u", dbOid); totalsize = db_dir_size(pathname); /* Scan the non-default tablespaces */ snprintf(dirpath, MAXPGPATH, "pg_tblspc"); dirdesc = AllocateDir(dirpath); ...
All it does is to calculate the size of the directory in db_dir_size (and any tablespaces if there are any):
/* Return physical size of directory contents, or 0 if dir doesn't exist */ static int64 db_dir_size(const char *path) ... while ((direntry = ReadDir(dirdesc, path)) != NULL) {
It is looping over all the files and then sums up the size of the files it finds there. Nothing is excluded as you can easily verify:
postgres@debian10pg:/home/postgres/ [pg14] cd /var/tmp/dummy/base/ postgres@debian10pg:/var/tmp/dummy/base/ [pg14] oid2name All databases: Oid Database Name Tablespace ---------------------------------- 13974 postgres pg_default 13973 template0 pg_default 1 template1 pg_default postgres@debian10pg:/var/tmp/dummy/base/ [pg14] cd 13974 postgres@debian10pg:/var/tmp/dummy/base/13974/ [pg14] dd if=/dev/zero of=dummy bs=1M count=100 100+0 records in 100+0 records out 104857600 bytes (105 MB, 100 MiB) copied, 0.0924525 s, 1.1 GB/s postgres@debian10pg:/var/tmp/dummy/base/13974/ [pg14] psql -c "l+" List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 108 MB | pg_default | default administrative connection database template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8113 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8113 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | | | (3 rows) postgres@debian10pg:/var/tmp/dummy/base/13974/ [pg14] rm dummy
All I did here, is to create a file a bit larger than 100MB in the directory of the “postgres” database, and asked for the database size once more. As you can see above, the size of this file is counted.
What information can we get out of this? The difference between the “postgres” and the “template0” and “template1” databases must come from the files on disk. Let’s look at the directories:
postgres@debian10pg:/home/postgres/ [pg14] du -s /var/tmp/dummy/base/13974/ 8280 /var/tmp/dummy/base/13974/ ## postgres postgres@debian10pg:/home/postgres/ [pg14] du -s /var/tmp/dummy/base/1/ 8124 /var/tmp/dummy/base/1/ ## template1
Same picture here: “postgres” is larger than “template1” but what is the difference. Actually the difference can easily be spotted:
postgres@debian10pg:/home/postgres/ [pg14] ls -ltr /var/tmp/dummy/base/13974/ | tail -3 # postgres -rw------- 1 postgres postgres 8192 May 20 11:03 1247_vm -rw------- 1 postgres postgres 114688 May 20 11:03 1247 -rw------- 1 postgres postgres 156486 May 20 11:06 pg_internal.init postgres@debian10pg:/home/postgres/ [pg14] ls -ltr /var/tmp/dummy/base/1/ | tail -3 # template1 -rw------- 1 postgres postgres 24576 May 20 11:03 13792_fsm -rw------- 1 postgres postgres 65536 May 20 11:03 13792 -rw------- 1 postgres postgres 106496 May 20 11:03 1259
To verify that this file really is the reason: Stop the instance, delete the file and compare again:
postgres@debian10pg:/home/postgres/ [pg14] pg_ctl -D /var/tmp/dummy/ stop waiting for server to shut down.... done server stopped postgres@debian10pg:/home/postgres/ [pg14] rm /var/tmp/dummy/base/13974/pg_internal.init postgres@debian10pg:/home/postgres/ [pg14] du -s /var/tmp/dummy/base/13974/ 8124 /var/tmp/dummy/base/13974/ postgres@debian10pg:/home/postgres/ [pg14] du -s /var/tmp/dummy/base/1/ 8124 /var/tmp/dummy/base/1/
Exactly the same now. Once you start the instance again, the file will be re-created:
postgres@debian10pg:/home/postgres/ [pg14] pg_ctl -D /var/tmp/dummy/ start postgres@debian10pg:/home/postgres/ [pg14] ls -ltr /var/tmp/dummy/base/13974/ | tail -3 -rw------- 1 postgres postgres 770048 May 20 11:03 1255 -rw------- 1 postgres postgres 8192 May 20 11:03 1247_vm -rw------- 1 postgres postgres 114688 May 20 11:03 1247 postgres@debian10pg:/home/postgres/ [pg14] psql -c "l+" List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8265 kB | pg_default | default administrative connection database template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8113 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8113 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | | | (3 rows) postgres@debian10pg:/home/postgres/ [pg14] ls -ltr /var/tmp/dummy/base/13974/ | tail -3 -rw------- 1 postgres postgres 8192 May 20 11:03 1247_vm -rw------- 1 postgres postgres 114688 May 20 11:03 1247 -rw------- 1 postgres postgres 156486 May 20 11:45 pg_internal.init
Last question for today: What is that file for? The answer is, once more, in the source code (src/include/utils/relcache.h):
/* * Name of relcache init file(s), used to speed up backend startup */ #define RELCACHE_INIT_FILENAME "pg_internal.init"