What if database branching was easy?

> Imagine you need to add an index to a table with a few million rows. On a seeded database with 200 rows, the migration runs in milliseconds. Obviously. But on a branch with realistic data, it takes 40 seconds and needs CREATE INDEX CONCURRENTLY to avoid locking the table. The branch is isolated, so locking there isn't the issue — the point is that the rehearsal shows the production migration would need CONCURRENTLY.

A few million rows should take at most, on the most awful networked storage available, maybe 10 seconds. I just built an index locally on 10,000,000 rows in 4 seconds. Moreover, though, there are vanishingly few cases where you wouldn't want to use CONCURRENTLY in prod - you shouldn't need to run a test to tell you that.

IMO branching can be a cool feature, but the use I keep seeing touted (indexes) doesn't seem like a good one for it. You should have a pretty good idea how an index is going to behave before you build it, just from understanding the RDBMS. There are also tools like hypopg [0], which are also available on cloud providers.

A better example would be showing testing a large schema change, like normalizing a JSON blob into proper columns or something, where you need to validate performance before committing to it.

0: https://github.com/HypoPG/hypopg

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 really understand why that’s needed. Can someone explain to me my misunderstanding here?

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 remember what it's called but I think Postgres has had it now for a few years.

We had a similar journey with Neon's branching. Initially it was a huge win for our CI workflows — spinning up an isolated, production-shaped database per PR made migration testing and integration checks dramatically more realistic than seed fixtures ever were.

That said, we've since pulled back from branching production schemas, and the reason is data masking. In principle you can define masking rules for sensitive columns, but in practice it's very hard to build a process that guarantees every new column, table, or JSON field added by any engineer is covered before it ever touches a branch. The rules drift, reviews miss things, and nothing in the workflow hard-fails when a new sensitive field slips through.

Most of the time that's fine. But "most of the time" isn't the bar for customer data — a single oversight leaking PII into a developer environment is enough to do real damage to trust, and you can't un-leak it. Until masking can be enforced by construction rather than by convention, we'd rather pay the cost of synthetic data than accept that risk.

I thought this was a repost but I was thinking of dolt which has similar capabilities but a different approach. As in https://news.ycombinator.com/item?id=38109880

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.

I actually just did this recently. I looked at a bunch of solutions for my dev environment, but Claude kept pushing me back to a really simple one: use Postgres.

Postgres has template database that effectively give you a really easy means of "cloning" a database. On AFS (and several other file systems), copy-on-write is pretty much native.

This is something that Palantir Foundry supports extremely well. Its data layer is built around the idea that anytime you're making a change, you make a branch, build on branch, only data you modified is copied to the branch, and then you can test it end to end on the branch.

Can't imagine doing it any other way

Wouldn't it be great if Aurora Serverless V2 actually supported this copy-on-write semantic? I would immediately be able to throw out a pile of slow code if they did.

Using neon for this and it's an absolute game changer, would recommend implementing database branching whatever solution you pick

Has anyone used this for debugging? Like an error in datadog triggers an agent sandbox and branched database?

what if writing a blog post without ai was easy?

Xata is open source btw (open core): https://github.com/xataio/xata

i was using neon and they had some similar feature but now using planetscale. would be curious to know how you all are doing it?

This kind of magic is the reason that I'm very itchy to be able to line up real work on Datomic or XTDB someday.

feels like branching doesn't replace seeding, just complements it- seeds for speed, branches for realism.

I mean there's a whole book about this, "Purely Functional Data Structures" (2009) by Chris Okasaki.

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.