Dolt is Git for Data
github.com
github.com
One of the biggest problems with databases (particularly SQL ones) is they're a giant pile of mutable state. The whole idea of "migrations" exists because it is impossible to "just" revert any arbitrary change to a database, diff changes automatically, merge changes automatically. You need some kind of intelligent tool or framework to generate DDL, DML, DCL, they have to be applied in turn, something has to check if they've already been applied, etc. And of course you can't roll back a change once it's been applied, unless you create even more program logic to figure out how to do that. It's all a big hack.
By treating a database as version-controlled, you can treat any operation as immutable. Make any change you want and don't worry about conflicts. You can always just go back to the last working version, revert a specific change, merge in one or more changes from different working databases. Make a thousand changes a day, and when one breaks, revert it. No snapshotting and slowly restoring the whole database due to a non-reversible change. Somebody dropped the main table in prod? Just revert the drop. Need to make a change to the prod database but the staging database is different? Branch the prod database, make a change, test it, merge back into prod.
The effect is going to be as radical as the popularization of containers. Whether you like them or not, they are revolutionizing an industry and are a productivity force multiplier.
What are the tradeoffs here? When wouldn't I want to use this?
It's slower. This is `sysbench` Dolt vs MySQL.
https://docs.dolthub.com/sql-reference/benchmarks/latency
We've dedicated this year to performance with a storage engine rewrite. We'll have some performance wins coming in the back half of the year. We think we can get under 2X MySQL.
It also requires more disk. Each change is at least on average 4K on disk. So, you might need more/bigger hard drives.
In your benchmark it's only 2.1–7.4 times slower than MySQL, average 4.4. And any database someone could fit on a disk 20 years ago (I forget, maybe 8 GB?) fits in RAM now, which makes it about 256 times faster, which is a lot bigger than 4.4. You can get a 20 TB disk now, which is enough space So anything that could be done with MySQL 20 years ago can be done faster and cheaper with Dolt now, which covers, I think the technical term is, a fucking shitload of applications. It probably includes literally every 20th-century application of relational databases.
Well, except for things that have over 5 billion transactions (20 TB ÷ 4 kB/txn) over their lifetime, I guess, so it might be important to find a way to compact that 4K. 5 billion transactions is 19 months at 100 TPS. If you could get that down to 256 bytes it would be almost 25 years of 100 TPS.
Also, as I understand it, and correct me if I'm wrong here, that 4.4× slowdown buys you a bulletproof and highly performant and scalable strategy for backups (with PITR), staging servers, data warehousing, readslaves, disk error detection and recovery, cryptographically secure audit logs, bug reproduction, and backtesting. Along with the legal security the Apache 2 license gives you, which you don't have with Datomic.
Sounds fantastic! It sounds like you're selling its performance a bit short. If someone is really concerned about such a small performance loss they probably aren't really in the market for a new RDBMS.
It's a pipedream, not the future.
Your database is either too big / has too much throughput or migrations just don't matter. And it's not like you wouldn't need migrations with a versioned schema, as otherwise a rollback would mean data loss.
It's all just software. There is essentially no limit to what we can make software do as long as the hardware supports it. And there's no hardware limit I know of that says version-controlled databases can't work. We just need to figure out how they will work, and then make 'em (or try to make 'em and in the process figure it out).
> And it's not like you wouldn't need migrations with a versioned schema, as otherwise a rollback would mean data loss.
When you roll back a code change, you don't lose code, as it's still in history. If you need to revert but keep some code, you branch the code, copy the needed code into some other part of the app, revert the old change, merge everything. If on merge there is a conflict (let's presume losing data is a conflict), it can prompt you to issue a set of commands to resolve the conflict before merge. You could do all of that in a branch, test it, merge into prod, and on merge it could perform the same operations. The database does all the heavy lifting and the user just uses a console the way they use Git today.
It's probably going to be required to lock the version of software and the version of the database together, such that both are changed/reverted at the same time. But because this is version control, we could actually serve multiple versions of the same database at the same time. You could have the database present two different versions of itself with the same data COW-overlayed for each version, and two different versions of an application. You could then blue/green deploy both the application and database, each modifying only its version. If you need to revert, you can diff and merge changes from one version to another.
You might be making the argument that the usability of immutable data is not there yet, but there are well-established theoretical costs of maintaining full history and I don't think they're within bounds of many real-world use-cases.
I can't help thinking how much time the git team or Jetbrains or Chrome or the Confluence team have spent on backing store implementation/migration and file formats instead of using sqlite (like why aren't V8 heap and perf snapshots just a sqlite database?) but then many of these things operate in that gap. So do we keep improving change over time functionality in databases, or make VCS backends with more formal database-like behavior?
IIRC, Trac stores its wiki history in a subversion repository. Since it already had to understand commit histories and show diffs, that was a sensible choice. Of course it is easier to live with such a decision if the API is good, but I haven't heard anyone say that about any version control system yet.
Git is a distributed file manager that operates on files where every change is a commit, and a commit is a set of operations on files, and/or a change to a block of text strings terminated by newlines. Versions are merkle trees of commits.
RPM/Deb/etc is a semi-centralized file manager that operates on files assuming each change is a collection of files with executable stages before and after copying/linking/unlinking. Versions are arbitrary key=value pairs which optionally depend on other versions, with extra logic to resolve relative versions.
Docker/OCI is a distributed file manager that operates on layers assuming every layer is a collection of files overlaid on other layers, with extra logic to do extra things with the layers at runtime. Versions are (I think?) merkle trees of layers.
The database is going to need a helluva lot of custom heuristics and operations to do version-control, because how you use it is so much different than the above. Databases are much more complex beasts, require higher performance, higher reliability, tons more functionality.
I'm not convinced they are. Invention is often a case of adopting a solution well known in another discipline. Discovering that these two things share a category. We keep discovering things that are isomorphic to each other.
In the end git is keeping a database of code changes as write once data entries, and presenting the snapshots for human consumption. It does a very bad job of pretending that's not the case.
Consider a CMS, one of the most common forms of database backed applications. What if you could give your customer a "dev" branch of all their data to make their changes on and test out new content, that you could then merge with back to prod after somebody reviews it in a standard PR workflow?
This is the workflow one of our earliest customers built. They run network configuration software, and they use Dolt to implement a PR workflow for all changes their customers make.
More details here:
I can't recall which it was but one of the timeseries databases was bragging on the fact that there are certain situations where scanning a block of data is as cheap as trying to add finer grained indexes to it, especially with ad hoc queries. They did a bunch of benchmarks that said block scanning with compression and parallelism was workable.
And while compression typically leads to write amplification (or very poor compression ratios), in a timeseries database, or a regular database architected in a timeseries-like fashion, modifying the old data is deeply frowned upon (and in fact I've heard people argue for quasi-timeseries behavior because modifying old records is so punishing, especially as the application scales), so as long as you can decide not to compress some pages - new pages - this is not a problem.
Dolt's unique functionality isn't time travel, although it has that. It's version control, i.e. branch and merge, push and pull, fork and clone. A bunch of database products give you some of this for schema migrations, but Dolt is the only one that does it for table data as well.
More discussion of the immutable DB product space:
Sometimes when you're first entering data you just get it wrong. You said the wrench was in storeroom A1 or the customer lives on 3rd St or the tree was five feet from the sidewalk. If the state of the asset changes due to human action, that's a new thing. But if you opened the wrong list taking inventory, or you missed a keystroke for 33rd St or you just eyeballed the coordinates, then that row was simply wrong, and the correction should be back-dated for most things.
But if I emptied out A1 because we aren't renting that space anymore, the customer moves or the tree got replanted, then it was here and now it's over there. Which might be important for figuring out things like overstock, taxes or success rates.
Similarly if the migration introduces derived data, then the migrated data is assumed/inferred, whereas if we took that data from a user, that information is confirmed, which might introduce subtle differences in how best to relate to the user. Things a mediocre business could easily ignore but a quality establishment might be ill-pleased with such a request.
CALL DOLT_REVERT('bad-commit-hash')
Works just like git revert: creates an inverse of the changes in the commit given and applies it as a patch.
I revert commit1 what would be the state?
First, keeping every version of every piece of data forever is an excellent way to consume non-intuitively vast amounts of storage even if your data model is tiny. Every time this feature has been offered by databases, it immediately causes a rash of "out of storage" errors that force the user to manually and permanently delete large numbers of old versions. This is extremely user-unfriendly, so the feature is almost immediately removed in subsequent versions because the pain it causes far outweighs the benefits even when used carefully. In typical MVCC systems, old versions are aggressively garbage collected automatically to limit out-of-storage errors.
Second, finding or reconstructing an arbitrary number of old versions of data is unavoidably expensive. Much of the architectural difference between various MVCC implementations are trying to manage the rather severe performance tradeoffs of maintaining multiple versions of data and navigating to the version you need, with the understanding that all of these versions live on storage and rarely in a single place. There is no optimal way, and keeping version chains short is critical for good performance.
There is very deep literature around MVCC-style databases. The challenges of generalizing and maximally exploiting MVCC as a user feature while having performance that is not poor to the point of unusability are thoroughly documented.
Dolt's unique functionality isn't time travel, although it has that. It's version control, i.e. branch and merge, push and pull, fork and clone. A bunch of database products give you some of this for schema migrations, but Dolt is the only one that does it for table data as well.
There are fundamental computer science and technical issues that make scaling these systems for arbitrary data models extremely difficult. The platforms always had the ambition to be general but the design tradeoffs required to make them scale requires narrowly overfitting for a particular type of data model such that they can only be used for the original use case. And even then, the performance ends up being not good.
I've never designed one from scratch but I've worked on a few at large companies. All of them started with the vision you are proposing, all of them failed at achieving that vision because of the technical tradeoffs required to enable something resembling scalability. Unless you are proposing some novel computer science that renders these issues moot, you aren't presenting a credible defense that this hasn't been done before.
Table data is stored in a cross between a Merkle DAG and a B Tree (a prolly tree), which is what makes diff / merge performant and scalable. We didn't invent these data structures but we believe we are the first to build a SQL database on them.
https://docs.dolthub.com/architecture/storage-engine/prolly-...
This is false, you are conflating the abstract algorithm with a narrow implementation. That's like saying the only possible sorting algorithm is quicksort.
With all due respect, you seem to be only loosely familiar with database architecture, both theory and historical practice. Nothing you've described is actually novel. That you are unfamiliar with why no one builds things this way, despite many attempts, does not lend confidence.
I am actually a big fan of people trying unorthodox approaches to databases that have never been tried before, this just isn't such an example. Which doesn't make your approach wrong per se, but it leaves you exposed to learning why other people tried and abandoned it.
Tangentially, the "prolly tree" is intrinsically not a scalable data structure. That may satisfy your design requirements but I can't tell.
They're literally telling you Git uses a Merkle DAG and they wanted to recreate Git so they used a Merkle DAG. That's not conflating, it's copying.
> you seem to be only loosely familiar with database architecture
Based on what? The only comments GP has made about DBA in this entire HN thread is "this is not MVCC", which is correct.
> I am actually a big fan of people trying unorthodox approaches to databases that have never been tried before
So stop discouraging the neophyte? Sometimes innovation requires the ignorant to figure things out without knowing better, because that way they won't quit before they've begun. Let them figure it out. And if it's not novel, who cares? It doesn't have to be some perfect architectural masterpiece to solve people's problems. If it works well enough just for WordPress blogs, that's pretty great already.
They literally told them: "Git-like version control requires a Merkle DAG."
Databases aren't magic. They are software. And as you say, there is a deep well of literature, and almost all interesting modern databases are open source. So it's possible for anyone with the interest and motivation to learn how they work, and yes, improve them.
> With all due respect, you seem to be only loosely familiar with database architecture, both theory and historical practice. Nothing you've described is actually novel. That you are unfamiliar with why no one builds things this way, despite many attempts, does not lend confidence.
You claim that there is all this information out there saying why this won't work, and that it has been tried, but don't point to any of it.
> Tangentially, the "prolly tree" is intrinsically not a scalable data structure.
First: Dolt publishes industry standard performance benchmarks and they are an average of 4.4x slower than MySQL:
https://docs.dolthub.com/sql-reference/benchmarks/latency
This is using the original storage format from noms which wasn't written for oltp workloads. A new storage format is coming which is and will dramatically improve this:
https://www.dolthub.com/blog/2022-05-20-new-format-alpha/
In any case 5x slower already shows, experimentally, that this approach works for database-style problems.
===
Second: In a purely algorithmic sense, prolly trees are the definition of scalable. They are log(n) (with a large base) for inserts, deletes, and seeks and they have efficient ordered scans. They have basically the same algorithmic complexity as B Trees, B+ Trees, LSM trees, etc -- very similar properties to the data structures used by other databases. The problem with prolly trees is actually the reverse: they are scalable, but have large constant factors due to the overhead of hashing.
But a single-digit constant factor slower performance than MySQL but with versioning seems like a great product for many applications.
If anyone reading this is interested, the Dolt team did a great job writing up the how prolly trees work and how they compare to classic databases here:
https://www.dolthub.com/blog/2020-04-01-how-dolt-stores-tabl...
Let me quote a bit. I admit, I don't understand it but then how could I?
Algorithm design using topology manipulation can be enormously challenging to reason about. You are often taking a conceptually simple algorithm, like a nested loop or hash join, and replacing it with a much more efficient algorithm involving the non-trivial manipulation of complex high-dimensionality constraint spaces that effect the same result. Routinely reasoning about complex object relationships in greater than three dimensions, and constructing correct parallel algorithms that exploit them, becomes easier but never easy.
Incredible! I wish I could grok this stuff.
You should save your breath and just get out of the way of the real experts.
Are they likely to find a way to jump forward? no. Are they likely to fall into the same traps and dead ends that are well documented in this space? yep. But in general I believe we should temper the negative feedback with constructive details:
Hey have you seen this previous and very similar work in the same space, here is some info where and how they failed to scale.
Taking a stance (even an implied one) that this is not novel and therefore you should stop could have been made against many of the teams working in vast areas of algorithms and architectures that have lead to jumps forward. CS is and should be science and to that end, our current knowledge and understanding is simply what is known today and must remain mutable to move forward. While their current approach is not scalable and a dead end, who knows if this experience will lead them to identify previously unrealized solutions (even if partial).
In modern world of SAASes and connectivity, you use multiple "databases" and such (queues, identity providers) anyways, and the world got connected enough to not need to have a local db most of the time.
I wish we could use this at work. We're trying to predict time-series stuff. However, there's a lot of infrastructure complexity which is there to ensure that when we're training on data from years ago, that we're not using data that would be in the future from this point (future data leaking into the past).
Using Dolt, as far as I understand it, we could simply set the DB to a point in the past where the 'future' data wasn't available. Very cool
For a lot of types of data, auditing requires history.
Have a look at MSSQLs history table, for example: https://docs.microsoft.com/en-us/sql/relational-databases/ta...
Edit: I can always revert the contents of the .dvc folder to a previous commit, but I wonder if there's a more natural way of doing it.
However DVC is fundamentally limited because you can only have dependencies and outputs that are files on the filesystem. Theoretically they could start supporting pluggable non-file-but-file-like artifacts, but for now it's just a feature request and I don't know if it's on their roadmap at all.
This is fine, of course, but it kind of sucks for when your data is "big"-ish and you can't or don't want to keep it on your local machine, e.g. generating intermediate datasets that live in some kind of "scratch" workspace within your data lake/warehouse. You can use DBT for that in some cases, but that's not really what DBT is for and then you have two incompatibile workflow graphs within your project and a whole other set of CLI touch points and program semantics to learn.
The universal solution is something like Airflow, but it's way too verbose for use during a research project, and running it is way too complicated. It's an industrial-strength data engineering tool, not a research workflow-and-artifact-tracking tool.
I think my ideal tool would be "DVC, but pluggable/extensible with an Airflow-like API."
Of course all secondary user-defined, typed indexes are also versioned.
Basically the technical idea is to map a huge tree of index tries (with revisions as indexed leave pages at the top-level and a document index as well as secondary indexes on the second level) to an append-only file. To reduce write amplification and to reduce the size of each snapshot data pages are first compressed and second versioned through a sliding snapshot algorithm. Thus, Sirix does not simply do a copy on write per page. Instead it writes nodes, which have been changed in the current revision plus nodes which fall out of the sliding window (therefore it needs a fast random-read drive).
The reason I used a separate table is so that you don't have to compromise or complicate the primary table's constraints, indices and foreign keys; the history table doesn't really need those because it's not responsible for data integrity.
Anyway, once you have that, you can run queries with a `where $timestamp is between start_date and end_date` condition, which will also allow you to join many tables at a certain point in time. To also be able to get the latest version, you can use a union query (iirc).
I'm sure there's a lot of caveats there though. What I should do is take some time in the weekend and work on that POC, publish it for posterity / blog post fuel.
- Dolt is Git for Data: a SQL database that you can fork, clone, branch, merge (2021) - 177 comments https://news.ycombinator.com/item?id=26370572
- Dolt is Git for data (2020) - 191 comments https://news.ycombinator.com/item?id=22731928
If I have
name job age
remram dev 32
UPDATE table SET age = age + 2 WHERE job = 'dev';
UPDATE table SET job = 'mgr' WHERE age = 32;
Merge conflicts are stored in a special system table so you can decide what to do with them, kind of analogous to conflict markers in a source file. More details here:
https://docs.dolthub.com/sql-reference/version-control/merge...
The situation you're talking about with two transactions isn't a merge operation, it's just normal database transaction isolation level stuff. Dolt supports REPEATABLE_READ right now, with others coming in future releases. So in the example above, whichever transaction committed last would fail and get rolled back (assuming they touched the same rows).
I mean, arguably. It's not like there is a standard definition for "merge operation" on data. Even Git tries to do more than line-level changes, taking context into account, and turning one line change into two line changes if merging across a file copy for example.
Dolt markets itself as "a version controlled SQL database", so I think it is perfectly reasonable to consider the standard that already exists for concurrent changes to a SQL database, and that's transaction isolation.
I guess anything more complex than this would be pretty unwieldy though, with probably little benefits. I am struggling to come up with a good example for the kind of anomaly I imagine.
What you want is to have branches on your data that have many transactions applied to them, for days or weeks. Then you merge the branch back to main when it's ready to ship.
An early customer built pull requests for their configuration application using Dolt, read more here:
The use case I had in mind is closer to a data.world or dbhub, collaborative data cleaning (which often include bulk operations) rather than merging OLTP databases after days or weeks of changes.
What I would use now is a script or Jupyter Notebook checked in Git, where cells are strongly ordered and if someone sends me a pull request (changing the code) I have to re-run the notebook to obtain the "merged dataset". I can't say that I have a use for Dolt but it is definitely cool tech.
https://www.dolthub.com/blog/2022-06-14-announcing-museums-b...
We are a fork of Noms. Lots of incremental changes and we're in the process of a major storage engine overhaul (what we use Noms for) for performance as we speak.
You can define a "virtual" table (schema, how to retrieve rows/columns) and then a MySQL client can connect and execute arbitrary queries on your table (which could just be an API or other source)
Good, good. It was too dangerous to not keep a backup.
dolt noun \ ˈdōlt \ Definition of dolt : a stupid person
: a foolish or worthless person
https://docs.dolthub.com/other/faq#why-is-it-called-dolt-are...
Specifically this: https://en.wikipedia.org/wiki/Git_Along,_Little_Dogies
Every time I type it in I am visualizing my little code changes as sickly orphaned calves being herded into the main pen with all the mature cows currently working away at their various functions.
Of course given the actual author the British/European meaning makes a lot more sense.
The talk expands on what "bitemporal" means in this context, namely, separate "valid" and "audit" time dimensions. The discussion upthread is a good example: if you accidentally mark down inventory in the wrong location and you find out later, you should backdate the fix to when you did the count -- this is the 'valid' dimension, but the fact that you changed it today should also be recorded so it can be reviewed later -- this is the 'audit' dimension.
https://www.dolthub.com/bounties
I participated in one a few months back. I had to stop midway inbetween because work was calling but it was a lot of fun. I know I earned some dollars, lol, but probably too few to count for the competition.
I am actually trying to convince my manager to put up a few bounties later this year.
Their Discord support is also super impressive.
I gave their bounty program a shot but I quit when I was prompted to download/clone a 8gb database. So the best thing would be to have a dolthub account and develop the data from there.
I think if you do a little bit of git hacking you can create a branch with just an empty or a dummy database based on the schema, clone only that branch and make a PR to append to the original database. But I couldn't figure that out.
I think there could be a better design to clone/download the database on local machine. I understand people don't actually download databases to their local machine but I expected a better solution.
[1] - https://docs.dolthub.com/sql-reference/benchmarks/latency
[1]: https://www.dolthub.com/blog/2021-03-09-dolt-use-cases-in-th...
docs $ dolt conflicts cat docs
+-----+--------+----+----+
| | | pk | c1 |
+-----+--------+----+----+
| | base | 1 | 1 |
| * | ours | 1 | 10 |
| * | theirs | 1 | 0 |
+-----+--------+----+----+
(ours is the working copy, theirs is the thing you're merging in)
Today, this means creating a snapshot of the DB, creating a new DB instance, copying the data over, doing a deploy, testing changes, teardown and cleanup. Branching with copy-on-write (very similar to what neon.tech offers) on top of on-prem instances would be lifechanging for teams operating in legacy dbs.
[0] https://www.splitgraph.com/docs/sgr-advanced/getting-started...
Docs site is still up: https://docs.dat.foundation/
[1] https://en.wikipedia.org/wiki/Smalltalk
[2] https://twitter.com/worrydream/status/1424952628207702022 <-- Brett Victor talking about doit
DoIt
Ah... whoops, my bad
Only need Oracle clients but we will eventually have a Postgres foreign data wrapper for the storage engine. I explain how we ended up MySQL in this blog post if you're curious.
https://www.dolthub.com/blog/2022-03-28-have-postgres-want-d...
Nessie is git for data.
I don't want to deal with databases and everything that comes with it. Just give me something that works with the filesystem directly.
https://docs.dolthub.com/sql-reference/benchmarks/latency
We're even slower on heavy transactional use cases.
We're dedicating the year to it and we think we can get under 2X MySQL. We have customers who switched from other No-SQL DBs and actually got faster so getting close to MySQL is a great accomplishment.
You only connect with a MySQL-client. There is no MySQL code in it. Here's the story if you're curious.
https://www.dolthub.com/blog/2022-03-28-have-postgres-want-d...
We will eventually build a Postgres Foreign Data Wrapper.