FAQ: Why is Database creation from templates slower for large databases in Postgresql 15?

Aiven Community
1

Suppose you just upgraded to PostgreSQL 15 from an old version, and you’ve noticed that creating databases from templates is noticeably slower, especially with larger databases. I noticed the same thing. It’s test time!

I was using these two services.
PG13: jmartin-pg-13-245434c3
PG15: jmartin-pg-15-10d64f56

I created and populated some databases on these services and then made databases from templates to illustrate the difference in timing.

PG13: jmartin-pg-13-245434c3

  1. Create and populate database
  2. Create new database from template with timing on.
defaultdb=> create database bigtest;
CREATE DATABASE
defaultdb=> \c bigtest;
psql (15.3, server 13.11)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "bigtest" as user "avnadmin".
bigtest=> CREATE TABLE logs (
    log_id SERIAL PRIMARY KEY,
    log_timestamp TIMESTAMP NOT NULL,
    severity VARCHAR(50) NOT NULL,
    log_message TEXT
);
CREATE TABLE

bigtest=> \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | logs | table | avnadmin
(1 row)

bigtest=> INSERT INTO logs(log_timestamp, severity, log_message)
SELECT 
    timestamp '2023-01-01' + interval '1 second' * s.id,
    CASE 
        WHEN s.id % 3 = 0 THEN 'Low'
        WHEN s.id % 3 = 1 THEN 'Medium'
        ELSE 'High'
    END,
    'Test log message ' || s.id
FROM generate_series(1,50000000) AS s(id); -- This will generate 50M records
INSERT 0 50000000
bigtest=> \timing on
Timing is on.
bigtest=> SELECT pg_size_pretty( pg_database_size('bigtest') );
 pg_size_pretty 
----------------
 4844 MB
(1 row)

Time: 62.745 ms
bigtest=> create database bigtest2 template bigtest;
CREATE DATABASE
Time: 40710.862 ms (00:40.711)

PG15: jmartin-pg-15-10d64f56
Same steps here.

  1. Create and populate database
  2. Create new database from template with timing on.
defaultdb=> create database bigtest;
CREATE DATABASE
defaultdb=> \c bigtest;
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "bigtest" as user "avnadmin".
bigtest=> CREATE TABLE logs (
    log_id SERIAL PRIMARY KEY,
    log_timestamp TIMESTAMP NOT NULL,
    severity VARCHAR(50) NOT NULL,
    log_message TEXT
);
CREATE TABLE
bigtest=> \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | logs | table | avnadmin
(1 row)

bigtest=> INSERT INTO logs(log_timestamp, severity, log_message)
SELECT 
    timestamp '2023-01-01' + interval '1 second' * s.id,
    CASE 
        WHEN s.id % 3 = 0 THEN 'Low'
        WHEN s.id % 3 = 1 THEN 'Medium'
        ELSE 'High'
    END,
    'Test log message ' || s.id
FROM generate_series(1,50000000) AS s(id); -- This will generate 50M records
INSERT 0 50000000
bigtest=> \timing on
Timing is on.
bigtest=> SELECT pg_size_pretty( pg_database_size('bigtest') );
 pg_size_pretty 
----------------
 4844 MB
(1 row)

Time: 56.720 ms
bigtest=> create database bigtest2 template bigtest;
CREATE DATABASE
Time: 97675.027 ms (01:37.675)

Look at those times!

PG 13
bigtest=> create database bigtest2 template bigtest;
CREATE DATABASE
Time: 40710.862 ms (00:40.711)

PG 15
bigtest=> create database bigtest2 template bigtest;
CREATE DATABASE
Time: 97675.027 ms (01:37.675)

So, why is this happening? What is reality? For the answer, we need to check out the release notes for PostgreSQL 15.

Add new WAL-logged method for database creation (Dilip Kumar)

This is the new default method for copying the template database, as it avoids the need for checkpoints during database creation. However, it might be slow if the template database is large, so the old method is still available.

It seems our friends at PostgreSQL have introduced a new strategy for creating databases from templates. We can see here there are two options, which are paraphrased below.

WAL_LOG(New default): the database will be copied block by block and each block will be separately written to the write-ahead log. This is the most efficient strategy in cases where the template database is small, and therefore it is the default.

FILE_COPY(old method): This strategy writes a small record to the write-ahead log for each tablespace used by the target database. Each such record represents copying an entire directory to a new location at the filesystem level.

So, let’s run one more database create specifying the FILE_COPY strategy and see what we get.

bigtest=> create database bigtest2 template bigtest;
CREATE DATABASE
Time: 97675.027 ms (01:37.675)

bigtest=> create database bigtest3 template bigtest strategy FILE_COPY;
CREATE DATABASE
Time: 33518.380 ms (00:33.518)

Look at those times! Very much improved.

In summary.

Q: Why is creating a database from template slower in PG 15, especially with larger databases?

A: PG 15 introduced the WAL_LOG strategy for database creation as a default. If this is causing performance issues with larger database templates, make sure to specify FILE_COPY as the strategy for creating databases from said templates.

Hope this helps. See you all next time. :slight_smile:

4 Likes