Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What if database branching was easy?

xata.io

Post reply on HN
Print view View original

31–40 of 59 posts

Re: What if database branching was easy?

#31

Post by zbentley »

tee-es-gee wrote: post #21
sastraxi wrote: post #5

I’ve done experiments using BTRFS and ZFS for local Postgres copy-on-write. You don’t need anything but vanilla pg and a supported file system to do it anymore; just clone the database using a template and a newish version of Postgres. Looking at Xata’s technical deep dive, the site claims that we need an additional Postgres instance per replica and proposes a network file system to work around that. But I don’t real…

For context for the others, I think you are referring to this blog post: https://xata.io/blog/open-source-postgres-branching-copy-on-... (in particular the "The key is in the storage system" section) right? What I'm saying there is that if you do Postgres with on top of a local ZFS volume, the child branches Postgres instances need to be on the same server. So you are limited in how many branches you can do. One or t…

ZFS snapshots can be transmitted over the network, with some diff-only and deduplication gains if the remote destination has an older instance of the same ZFS filesystem. It’s not perfect, and the worst case is still a full copy, but the tooling and efficiency wins for the ordinary case are battle-tested and capable.

Re: What if database branching was easy?

#32

Post by camdez »

xeubie wrote: post #22
camdez wrote: post #19

Earlier quoted context omitted.

Wait, this statement seems way too strong. The linked article points out that Datomic doesn't support branching from the past . It absolutely does support branching, and I've built entire test suites that way. From a cursory glance, I'd say Datomic does exactly what the original parent article is discussing. It works great and it's super convenient.

If each "branch" is read only, it's not a branch at all. The entire idea of branching implies that you can make changes on one branch, then switch to another branch and make changes to it. They start from the same point and grow in different directions, as the metaphor of branches on a tree depicts.

I don't disagree with anything you've said here, I just don't see how it applies to the situation.

With `datomic.api/with`, you can apply new datoms and get back a new DB value. Repeat this process as many times as you want, in as many directions as you want, switching as you choose. You're building a tree of immutable DB values—seems clearly like branching to me.

If by "read only" you mean that they're not persisted to disk, then that's an important point, but it surely doesn't obviate the utility of the functionality. It's useful in a number of cases, and especially testing scenarios like the Xata article describes.

If you built an immutable database that persists the branches, that is very cool and sounds useful—kudos! That said, I also don't want to downplay the utility of what Datomic does; it's a major help to me.

Re: What if database branching was easy?

#33

Post by UltraSane »

My company has a Pure storage array with always on dedupe and it works really well to make multiple copies of databases and only have to store modified data. For enterprise storage in 2026 I consider only storing unique blocks once to be table stakes as it enables so many useful capabilities and saves so much money.

Re: What if database branching was easy?

#34

Post by johnthescott »

bhouston wrote: post #18

I have tried this before: https://www.dolthub.com/ It was a lot of work and had poor performance with a lot of complications. I am not using it in my latest projects as a result.

makes sense. i can see things getting complex very quickly.

seems most versions would be better managed at application level, zfs/btrs snapshots not withstanding.

Re: What if database branching was easy?

#35

Post by sastraxi »

tee-es-gee wrote: post #21
sastraxi wrote: post #5

I’ve done experiments using BTRFS and ZFS for local Postgres copy-on-write. You don’t need anything but vanilla pg and a supported file system to do it anymore; just clone the database using a template and a newish version of Postgres. Looking at Xata’s technical deep dive, the site claims that we need an additional Postgres instance per replica and proposes a network file system to work around that. But I don’t real…

For context for the others, I think you are referring to this blog post: https://xata.io/blog/open-source-postgres-branching-copy-on-... (in particular the "The key is in the storage system" section) right? What I'm saying there is that if you do Postgres with on top of a local ZFS volume, the child branches Postgres instances need to be on the same server. So you are limited in how many branches you can do. One or t…

Yes, that's what I'm referring to.

You're still making the assumption in this comment: why does my 2nd (cloned) database need a separate postgres instance? One postgres server can host multiple databases.

Re: What if database branching was easy?

#36

Post by sastraxi »

tee-es-gee wrote: post #26
sastraxi wrote: post #5

I’ve done experiments using BTRFS and ZFS for local Postgres copy-on-write. You don’t need anything but vanilla pg and a supported file system to do it anymore; just clone the database using a template and a newish version of Postgres. Looking at Xata’s technical deep dive, the site claims that we need an additional Postgres instance per replica and proposes a network file system to work around that. But I don’t real…

> You don’t need anything but vanilla pg and a supported file system to do it anymore; just clone the database using a template and a newish version of Postgres. Are you referring to `file_copy_method = clone` from Postgres 18? For example: https://boringsql.com/posts/instant-database-clones/ I think the key limitation is: > The source database can't have any active connections during cloning. This is a PostgreSQL li…

Yeah, that's the one. My use case is largely for local development, so the active connections thing isn't a limiter for me.

Re: What if database branching was easy?

#37

Post by sastraxi »

wadefletch wrote: post #23
sastraxi wrote: post #5

I’ve done experiments using BTRFS and ZFS for local Postgres copy-on-write. You don’t need anything but vanilla pg and a supported file system to do it anymore; just clone the database using a template and a newish version of Postgres. Looking at Xata’s technical deep dive, the site claims that we need an additional Postgres instance per replica and proposes a network file system to work around that. But I don’t real…

You can't have any other connections while a Copy-on-Write is happening, not even a logical replication slot. So you keep a read replica that then gets all connections briefly cut for the COW to avoid locking the master instance. Then you re-enable the logical replication slots on both the new, copied instance and the "copyable" read replica to get both back up to date w/ master.

Thanks for sharing your workflow. My question is about why two databases on the same server would need two separate postgres instances.

Re: What if database branching was easy?

#38

Post by xeubie »

camdez wrote: post #32
xeubie wrote: post #22

Earlier quoted context omitted.

If each "branch" is read only, it's not a branch at all. The entire idea of branching implies that you can make changes on one branch, then switch to another branch and make changes to it. They start from the same point and grow in different directions, as the metaphor of branches on a tree depicts.

I don't disagree with anything you've said here, I just don't see how it applies to the situation. With `datomic.api/with`, you can apply new datoms and get back a new DB value. Repeat this process as many times as you want, in as many directions as you want, switching as you choose. You're building a tree of immutable DB values—seems clearly like branching to me. If by "read only" you mean that they're not persisted…

Yes the article I linked mentioned d/with (speculative writes), and you are right that it is useful for testing -- but not much else, since it is purely in-memory. If you want to call that in-memory branching that's fine, I'll concede that.

My database supports persisted branching, but not just at the database level. You can "branch" (i.e., make a fast clone) data at any level, such as data for a specific user. Many production uses for this, not just testing, yet almost no database supports this. It uses the same HAMT algorithm that Clojure uses.

Re: What if database branching was easy?

#39

Post by tee-es-gee »

zbentley wrote: post #31
tee-es-gee wrote: post #21

Earlier quoted context omitted.

For context for the others, I think you are referring to this blog post: https://xata.io/blog/open-source-postgres-branching-copy-on-... (in particular the "The key is in the storage system" section) right? What I'm saying there is that if you do Postgres with on top of a local ZFS volume, the child branches Postgres instances need to be on the same server. So you are limited in how many branches you can do. One or t…

ZFS snapshots can be transmitted over the network, with some diff-only and deduplication gains if the remote destination has an older instance of the same ZFS filesystem. It’s not perfect, and the worst case is still a full copy, but the tooling and efficiency wins for the ordinary case are battle-tested and capable.

Yes, for sure, and stuff like this is really useful when rebalancing storage nodes, for example.

My point is that for the use case of offering a Postgres service with CoW branching as a key feature, you can't really escape some form of separation of storage and compute.

Btw, don't really want to talk too much about it yet, but our proprietary storage engine (Xatastor) is basically ZFS exposed over NVMe-OF. We'll announce it in a couple of weeks, and we'll have a detailed technical blog post then on pros/cons.

Re: What if database branching was easy?

#40

Post by bob1029 »

comrade1234 wrote: post #2

I was on a big team working on a giant oracle database over 25-years ago. I dont remember the term but each developer had their own playground of the giant database that wasn't affected by anyone else. The DB admin would set it up for each developer in just a few minutes so it definitely wasn't a copy. Then when a developer needed to reset and go back to the original db again it just took a few minutes. I just don't…

https://docs.oracle.com/database/121/ADMIN/create.htm#ADMIN1...
Post reply on HN