Sometime back, I was going through PostgreSQL internals and found

Sometime back, I was going through PostgreSQL internals and found something interesting about the create database command …

When we create a new database in PostgreSQL with our standard create database command, internally it copies an existing database named template1 and creates a new one. These are called Template Databases.

Given that the new database is a copy of a template database, if we add some tables, rows, or stored procedures to this template1 database, they will also be copied when we create a new database. This is a good way to bootstrap subsequent databases with any tables, data, stored procedures, or functions we need.

The good thing is that we can also create as many additional template databases as we like and specify them as a template when we create a new one, like this

create database new_db template mono_tmpl_1;

The core advantages of having a custom template database are

1. bootstraps a new database with tables, functions, and settings
2. ensures consistency across all the databases in a cluster
3. minimizes the risk of variations and errors in setting up new databases

It is always fun going through database internals, you always learn something new and amusing :)