Instant database clones with PostgreSQL 18 • Hacker News Board

Re: Instant database clones with PostgreSQL 18

#11

Post by radimm » Tue, Dec 23, 2025, 12:42 PM UTC

drakyoko wrote: post #5

mvcosta91 wrote: post #2

It looks very interesting for integration tests

would this work inside test containers?

OP here - still have to try (generally operate on VM/bare metal level); but my understanding is that ioctl call would get passed to the underlying volume; i.e. you would have to mount volume

Re: Instant database clones with PostgreSQL 18

#12

Post by radarroark » Tue, Dec 23, 2025, 12:49 PM UTC

In theory, a database that uses immutable data structures (the hash array mapped trie popularized by Clojure) could allow instant clones on any filesystem, not just ZFS/XFS, and allow instant clones of any subset of the data, not just the entire db. I say "in theory" but I actually built this already so it's not just a theory. I never understood why there aren't more HAMT based databases.

Re: Instant database clones with PostgreSQL 18

#13

Post by majodev » Tue, Dec 23, 2025, 1:00 PM UTC

Uff, I had no idea that Postgres v15 introduced WAL_LOG and changed the defaults from FILE_COPY. For (parallel CI) test envs, it make so much sense to switch back to the FILE_COPY strategy ... and I previously actually relied on that behavior.

Raised an issue in my previous pet project for doing concurrent integration tests with real PostgreSQL DBs (https://github.com/allaboutapps/integresql) as well.

Re: Instant database clones with PostgreSQL 18

#14

Post by BenjaminFaal » Tue, Dec 23, 2025, 1:04 PM UTC

okigan wrote: post #10

BenjaminFaal wrote: post #9

For anyone looking for a simple GUI for local testing/development of Postgres based applications. I built a tool a few years ago that simplifies the process: https://github.com/BenjaminFaal/pgtt

Would love to see a snapshot of the GUI as part of the README.md. Also docker link seems to be broken.

Fixed the package link. Github somehow made it private. I will add a snapshot right now.

Re: Instant database clones with PostgreSQL 18

#15

Post by christophilus » Tue, Dec 23, 2025, 1:07 PM UTC

As an aside, I just jumped around and read a few articles. This entire blog looks excellent. I’m going to have to spend some time reading it. I didn’t know about Postgres’s range types.

Re: Instant database clones with PostgreSQL 18

#16

Post by horse666 » Tue, Dec 23, 2025, 1:20 PM UTC

1f97 wrote: post #4

aws supports this as well: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...

Aurora clones are copy-on-write at the storage layer, which solves part of the problem, but RDS still provisions you a new cluster with its own endpoints, etc, which is slow ~10 mins, so not really practical for the integration testing use case.

Re: Instant database clones with PostgreSQL 18

#17

Post by elitan » Tue, Dec 23, 2025, 1:21 PM UTC

For those who can't wait for PG18 or need full instance isolation: I built Velo, which does instant branching using ZFS snapshots instead of reflinks.

Works with any PG version today. Each branch is a fully isolated PostgreSQL container with its own port. ~2-5 seconds for a 100GB database.

https://github.com/elitan/velo

Main difference from PG18's approach: you get complete server isolation (useful for testing migrations, different PG configs, etc.) rather than databases sharing one instance.

Re: Instant database clones with PostgreSQL 18

#18

Post by horse666 » Tue, Dec 23, 2025, 1:25 PM UTC

This is really cool, looking forward to trying it out.

Obligatory mention of Neon (https://neon.com/) and Xata (https://xata.io/) which both support “instant” Postgres DB branching on Postgres versions prior to 18.

Re: Instant database clones with PostgreSQL 18

#19

Post by tobase » Tue, Dec 23, 2025, 1:28 PM UTC

elitan wrote: post #17

For those who can't wait for PG18 or need full instance isolation: I built Velo, which does instant branching using ZFS snapshots instead of reflinks. Works with any PG version today. Each branch is a fully isolated PostgreSQL container with its own port. ~2-5 seconds for a 100GB database. https://github.com/elitan/velo Main difference from PG18's approach: you get complete server isolation (useful for testing migrat…

[flagged]

Re: Instant database clones with PostgreSQL 18

#20

Post by zX41ZdbW » Tue, Dec 23, 2025, 1:33 PM UTC

radarroark wrote: post #12

In theory, a database that uses immutable data structures (the hash array mapped trie popularized by Clojure) could allow instant clones on any filesystem, not just ZFS/XFS, and allow instant clones of any subset of the data, not just the entire db. I say "in theory" but I actually built this already so it's not just a theory. I never understood why there aren't more HAMT based databases.

This is typical for analytical databases, e.g., ClickHouse (which I'm the author of) uses immutable data parts, allowing table cloning: https://clickhouse.com/docs/sql-reference/statements/create/...