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

51–59 of 59 posts

Re: What if database branching was easy?

#51

Post by SkyPuncher »

gonzalohm wrote: post #49
SkyPuncher wrote: post #48

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.

I was also looking into this for dev environments but I haven't been able to solve "rebases". If someone merges to prod then the dev environment is "out of sync" and you have to clone the DB again. It's pretty painful to orchestrate

We're using rails, so it mostly just comes down to pulling main and running migration.

I don't really worry about conflicts on branches since most features aren't long lived enough.

Re: What if database branching was easy?

#54

Post by pd_grahamt »

e7h4nz wrote: post #6

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…

This was a big reason Xata acquired privacy dynamics in Jan - I was the founder. Definitely a tough problem to address because pii can take so many forms.

Re: What if database branching was easy?

#56

Post by bhouston »

zachmu wrote: post #44
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.

Can you be more specific about what complications you ran into? As for performance, Dolt is faster overall than MySQL on sysbench now. https://docs.dolthub.com/sql-reference/benchmarks/latency

It was https://threekit.com. It was a while ago now but we had to use MySQL for our primary copy that users used (e.g. prod), and only when they were working on branches did we use dolt. I think the second complication was that Dolt was not stable enough to use in heavy load scenarios as well.

I can delete this comment if you do not want to discuss this publicly.

Re: What if database branching was easy?

#57

Post by gregw2 »

TheMrZZ wrote: post #8
tremon wrote: post #7

Earlier quoted context omitted.

You don't actually need to physically copy data, just create a view for every table that does a replacing merge between the original read-only data and the developer's own copy. And you can put a trigger on the view to redirect writes to the same private-copy table, making the whole thing transparent to the user. Not disputing that Oracle might have had something like this built-in, but it sounds like something that…

Snowflake uses a similar system with their 0-copy cloning. It starts with the original table's partition, and keeps track of the delta created by subsequent operations. Always found that builtin mechanism pretty neat!

I heard about this feature first from Snowflake but there are similar options around in other ecosystems which may be of interest to someone here and one thing to keep in mind with even Snowflake's implementation...

Snowflake's implementation only works within a single Snowflake account, not cross-account, which implies if you want to clone across dev/qa/prod you must manage those environments within a single Snowflake account.

BigQuery has a very similar "table clone" feature. It works across GCP projects (accounts) but not across organizations.

Redshift and Azure Synapse do not really have this feature at all.

Databricks, Microsoft Fabric and the Iceberg Nessie-only catalog do support something similar, often called shallow cloning.

(Nobody really supports cross-region cloning... which makes sense if you think about it.)

Re: What if database branching was easy?

#58

Post by zachmu »

bhouston wrote: post #56
zachmu wrote: post #44

Earlier quoted context omitted.

Can you be more specific about what complications you ran into? As for performance, Dolt is faster overall than MySQL on sysbench now. https://docs.dolthub.com/sql-reference/benchmarks/latency

It was https://threekit.com . It was a while ago now but we had to use MySQL for our primary copy that users used (e.g. prod), and only when they were working on branches did we use dolt. I think the second complication was that Dolt was not stable enough to use in heavy load scenarios as well. I can delete this comment if you do not want to discuss this publicly.

I remember that engineering decision. You guys were pretty early customers for your throughput and durability requirements (we hadn't even added standby replication yet when you started your integration). We've come a long way in the years since then.

Re: What if database branching was easy?

#59

Post by timsehn »

bhouston wrote: post #56
zachmu wrote: post #44

Earlier quoted context omitted.

Can you be more specific about what complications you ran into? As for performance, Dolt is faster overall than MySQL on sysbench now. https://docs.dolthub.com/sql-reference/benchmarks/latency

It was https://threekit.com . It was a while ago now but we had to use MySQL for our primary copy that users used (e.g. prod), and only when they were working on branches did we use dolt. I think the second complication was that Dolt was not stable enough to use in heavy load scenarios as well. I can delete this comment if you do not want to discuss this publicly.

Thanks for being an early adopter. We learned a lot trying to support your use case and you’re still customers so it can’t have been too bad…
Post reply on HN