Have you ever watched long running migration script, wondering if it's about to wreck your data? Or wish you can "just" spin a fresh copy of database for each test run? Or wanted to have reproducible snapshots to reset between runs of your test suite, (and yes, because you are reading boringSQL) needed to reset the learning environment?
When your database is a few megabytes, pg_dump and restore works fine. But what happens when you're dealing with hundreds of megabytes/gigabytes - or more? Suddenly "just make a copy" becomes a burden.
You've probably noticed that PostgreSQL connects to template1 by default. What you might have missed is that there's a whole templating system hiding in plain sight. Every time you run
CREATE DATABASE dbname;
PostgreSQL quietly clones standard system database template1 behind the scenes. Making it same as if you would use
CREATE DATABASE dbname TEMPLATE template1;
The real power comes from the fact that you can replace template1 with any database. You can find more at Template Database documentation.
In this article, we will cover a few tweaks that turn this templating system into an instant, zero-copy database cloning machine.
CREATE DATABASE ... STRATEGY
Before PostgreSQL 15, when you created a new database from a template, it operated strictly on the file level. This was effective, but to make it reliable, Postgres had to flush all pending operations to disk (using CHECKPOINT ) before taking a consistent snapshot. This created a massive I/O spike - a "Checkpoint Storm" - that could stall your production traffic.
... continue reading