Instant database clones with PostgreSQL 18
4 months ago
- #Performance Optimization
- #PostgreSQL
- #Database Cloning
- PostgreSQL's templating system allows for database cloning using `CREATE DATABASE ... TEMPLATE`.
- PostgreSQL 15 introduced `CREATE DATABASE ... STRATEGY` to optimize cloning, with `WAL_LOG` as the default for smoother I/O.
- PostgreSQL 18 added `file_copy_method = clone` for instant, zero-copy cloning on modern filesystems like XFS, ZFS, and APFS.
- Cloning a 6GB database with `FILE_COPY` strategy and `clone` method took only 212 ms compared to 67 seconds with `WAL_LOG`.
- Cloned databases share storage until modified, triggering copy-on-write for changed pages.
- Active connections to the source database must be blocked during cloning, a PostgreSQL limitation.
- Cloning is restricted to a single filesystem and not available in managed cloud environments like AWS RDS.