What if database branching was easy?
xata.io
xata.io
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.
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?
At the same time Postgres people don't seem comfortable with the idea in practice so I'm not sure if this is actually ok to do.
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 two are fine, but if you want to do a branch per PR, that will likely not work.
If you separate the compute from storage via the network, this problem goes away.
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.
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.
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 limitation, not a filesystem one.
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.
These aren't really "branches" though, they're hard forks. You can't merge them back after making changes. Dolt is still the only SQL database with branch and merge.
Not disputing that Oracle might have had something like this built-in, but it sounds like something that I could have whipped up in a day or so as a custom solution. I actually proposed a similar system to create anonymized datasets for researchers when I worked at a national archive institute.
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.)
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.
seems most versions would be better managed at application level, zfs/btrs snapshots not withstanding.
I can delete this comment if you do not want to discuss this publicly.
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 don't really worry about conflicts on branches since most features aren't long lived enough.
Can't imagine doing it any other way
Yes planetscale can branch too, but it takes longer and you pay individually for each branch
I actually built my own immutable database which does support branching (see profile), so it seems like a huge miss that these ones don't. It's pretty much the main reason I would want an immutable database.
That said, I’m adding xitdb to the list of tech to try out. Thank you for building it!
Oh, and thanks for linking to my article :-)
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.
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.
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.