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"
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)