Instant database clones with PostgreSQL 18
PostgreSQL 18 can create near-instant, space-efficient database clones by combining `FILE_COPY` with filesystem reflinks. A 6GB test database cloned in 212ms rather than 67 seconds, with storage consumed only as cloned pages diverge.
Instant database clones with PostgreSQL 18
Author: Radim Marek | Published: 2025-12-22 | Generated: 2025-12-23 | Domain: boringsql.com
Tags: ‘#postgresql’ ‘#databases’ ‘#filesystems’ ‘#reflinks’ ‘#performance’
TLDR
PostgreSQL 18 adds the
file_copy_methodsetting, enablingCREATE DATABASE ... STRATEGY=FILE_COPYto use filesystem reflinks (clone) instead of physically copying template database files. On reflink-capable filesystems such as XFS, ZFS, and APFS, this yields near-instant copy-on-write database clones without initially consuming extra disk space. Clones diverge as PostgreSQL writes 8KB pages during updates and maintenance, so the technique is ideal for disposable test databases, reproducible snapshots, and dedicated templates rather than frequently modified long-lived copies.
Key Takeaways
- Reflink-backed cloning: Set
file_copy_method = clonein PostgreSQL 18+ on XFS, ZFS, APFS, or similar supported filesystems, then useCREATE DATABASE new_db TEMPLATE source_db STRATEGY=FILE_COPY. - Major benchmark improvement: Cloning a roughly 6GB database took 67,000ms (67 seconds) with the default
WAL_LOGstrategy and 212ms withFILE_COPYplus reflinks. - Logical versus physical size:
pg_database_size()reports both source and clone as roughly 6GB because it measures logical contents, even though both initially point to the same physical filesystem blocks. - Copy-on-write occurs per page: Writes to shared PostgreSQL 8KB pages cause the filesystem to allocate separate blocks. A small update can also affect tuple, index, free-space-map, and visibility-map pages; later
VACUUMwork increases divergence. - Operational constraints: The source database must have no active connections while it is cloned; cloning works only within one filesystem, falls back to physical copies across tablespaces or mount points, and is generally unavailable on managed services such as AWS RDS and Google Cloud SQL.
Images & Media
None present in article.
Referenced Links
- Template Database documentation — PostgreSQL documentation on creating databases from templates.
- How not to change PostgreSQL column type — Related guide on safely handling long-running migrations.
- EXPLAIN buffer statistics — Explanation of buffer activity visible in query plans.
- Introduction to buffers — Background on PostgreSQL’s 8KB page-based storage and buffer system.
- Inside the 8KB page — Detailed look at PostgreSQL data-page internals.
- VACUUM Is a Lie (About Your Indexes) — Related discussion of VACUUM’s page-level effects.