Postgresql – Does PostgreSQL always initialize database of same size

database-designpostgresql

Let's say I run CREATE DATABASE abcd, PostgreSQL will create an empty database and if I run SELECT pg_size_pretty(pg_database_size('abcd'));, I get size of the database, let's say 8000kB. What I want to know is will the size of the empty database always be the same no matter what or does it depend? I specifically need to know if empty database size would change over time (create database 1 year from now) or if it would change over quantity (10000th database). I can't test for those cases and I couldn't find any information on that. Thank you.

Best Answer

Absolutely not. The database is formed from the contents of template1 a "master database." Adding things to template1 will directly affect the contents of the new database.

From the docs, Template Databases

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1. Thus that database is the "template" from which new databases are made. If you add objects to template1, these objects will be copied into subsequently created user databases. This behavior allows site-local modifications to the standard set of objects in databases. For example, if you install the procedural language PL/Perl in template1, it will automatically be available in user databases without any extra action being taken when those databases are created.

However, you can create a copy of template0 which we hope is a virgin database, also from the docs links above.

There is a second standard system database named template0. This database contains the same data as the initial contents of template1, that is, only the standard objects predefined by your version of PostgreSQL. template0 should never be changed after the database cluster has been initialized. By instructing CREATE DATABASE to copy template0 instead of template1, you can create a "virgin" user database that contains none of the site-local additions in template1. This is particularly handy when restoring a pg_dump dump: the dump script should be restored in a virgin database to ensure that one recreates the correct contents of the dumped database, without conflicting with objects that might have been added to template1 later on

Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1 might contain encoding-specific or locale-specific data, while template0 is known not to.

To create a database by copying template0, use:

CREATE DATABASE dbname TEMPLATE template0;
Related Question