6 ms·

This is the future of databases, but nobody seems to realize it yet. One of the biggest problems with databases (particularly SQL ones) is they're a giant pile

This is the future of databases, but nobody seems to realize it yet. 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.

jahnu 4y ago
Doesn’t Datomic do all this for some years now?
- zachmu 4y ago
  Lots of databases offer time travel / historical querying, including datomic, MySQL, Postgres, etc (plugins required in some cases). 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.
  - rapnie 4y ago
    I think TerminusDB does that as well.
    - zachmu 4y ago
      Yup, TerminusDB has a very similar set of capabilities, just for a graph DB instead of SQL / relational. Very cool product if you're in the market for a graph DB.
blowski 4y ago
It looks incredible, but somehow seems too good to be true. What are the tradeoffs here? When wouldn't I want to use this?
- timsehn 4y ago
  Creator here. It's slower. This is `sysbench` Dolt vs MySQL. https://docs.dolthub.com/sql-reference/benchmarks/latency 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.
  - EarthLaunch 4y ago
    Another commenter noted a need for migrations in order to handle rollbacks without data loss.
  - kragen 4y ago
    (Disclaimer: I haven't tried Dolt.) 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.
411111111111111 4y ago
> This is the future of databases, but nobody seems to realize it yet 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.
- packetlost 4y ago
  I think the problem is relational datasets like that don't fit well into that model. In reality, it's very possible. Look at Datomic. While I agree, for high-throughput systems, storage is a concern, but the general trends seem to be towards streaming data and retention policies anyways.
- throwaway892238 4y ago
  You're thinking in terms of the present, but I'm saying it's the future. At present it doesn't make sense, because nobody has yet made a database which does version control on very big datasets with a lot of throughput. But when somebody does make it, it will be completely obvious that this was something we always needed. 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.
simlevesque 4y ago
QLDB seems like something that goes in this direction. What's your opinion of it ?
- zachmu 4y ago
  QLDB isn't version controlled, it's an immutable ledger database. If that's what you're looking for, definitely choose it, it's a great product. More discussion of the immutable DB product space: https://www.dolthub.com/blog/2022-03-21-immutable-database/ https://www.dolthub.com/blog/2022-03-21-immutable-database/
LukeEF 4y ago
This has to be imagined in the context of a post-SQL future. Unless of course you are a 'SQL is the end of history' person!
hinkley 4y ago
In a similar vein, I am trying to work on a tool for a volunteer group, and one of the problems I'm trying to figure out how to model is not just tracking when the data changed, but why it changed. 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.
qaq 4y ago
You do realize that how MVCC works right? Turn off GC process that collects old versions on production DB and see what happens. Reverting changes out of order is not possible in many cases.
- zachmu 4y ago
  Right, but it is with Dolt. That's the point. 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.
  - qaq 4y ago
    so I have commit1 drop table blah commit2 create table blah with diff schema I revert commit1 what would be the state?
    - aboodman 4y ago
      It will be a conflict. But you can commit the revert to a different branch and run both schemas at the same time. This provides a lot of flexibility! Check out https://www.dolthub.com/blog/2022-04-29-dolt-revert/ https://www.dolthub.com/blog/2022-04-29-dolt-revert/ for some discussion of this.
jandrewrogers 4y ago
This is how relational databases have commonly worked since at least the 1990s and is called multi-version concurrency control (MVCC). Welcome to the future, it is called PostgreSQL. There are at least two reasons no sensible database designer would allow users to operate a database in this way even though they are technically capable of it. 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.
- zachmu 4y ago
  MVCC is not version control, and time travel / historical querying is not version control. 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.
  - jandrewrogers 4y ago
    The conceit here is the assumption that this has not been built many times by very clever software engineers. It is not a new idea. True git-like version control systems for managing large volumes of data have been built on MVCC kernels for a decades -- branch and merge, push and pull, fork and clone. 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.
dizhn 4y ago
How would you revert, merge, rollback or otherwise make sense of real world data that necessarily reflects "state"? Or is this only for development?
k_bx 4y ago
Hot take: migrations are useless 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.
AaronLasseigne 4y ago
One issue with immutable data stores for businesses is compliance with things like GDPR. You need some mechanism for scrubbing data and its version history effectively making it non-immutable.

jahnu 4y ago

Doesn’t Datomic do all this for some years now?

zachmu 4y ago
Lots of databases offer time travel / historical querying, including datomic, MySQL, Postgres, etc (plugins required in some cases). 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.
- rapnie 4y ago
  I think TerminusDB does that as well.
  - zachmu 4y ago
    Yup, TerminusDB has a very similar set of capabilities, just for a graph DB instead of SQL / relational. Very cool product if you're in the market for a graph DB.

blowski 4y ago

It looks incredible, but somehow seems too good to be true. What are the tradeoffs here? When wouldn't I want to use this?

timsehn 4y ago
Creator here. It's slower. This is `sysbench` Dolt vs MySQL. https://docs.dolthub.com/sql-reference/benchmarks/latency 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.
- EarthLaunch 4y ago
  Another commenter noted a need for migrations in order to handle rollbacks without data loss.
- kragen 4y ago
  (Disclaimer: I haven't tried Dolt.) 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.

411111111111111 4y ago

> This is the future of databases, but nobody seems to realize it yet 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.

packetlost 4y ago
I think the problem is relational datasets like that don't fit well into that model. In reality, it's very possible. Look at Datomic. While I agree, for high-throughput systems, storage is a concern, but the general trends seem to be towards streaming data and retention policies anyways.
throwaway892238 4y ago
You're thinking in terms of the present, but I'm saying it's the future. At present it doesn't make sense, because nobody has yet made a database which does version control on very big datasets with a lot of throughput. But when somebody does make it, it will be completely obvious that this was something we always needed. 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.

simlevesque 4y ago

QLDB seems like something that goes in this direction. What's your opinion of it ?

zachmu 4y ago
QLDB isn't version controlled, it's an immutable ledger database. If that's what you're looking for, definitely choose it, it's a great product. More discussion of the immutable DB product space: https://www.dolthub.com/blog/2022-03-21-immutable-database/ https://www.dolthub.com/blog/2022-03-21-immutable-database/

LukeEF 4y ago

This has to be imagined in the context of a post-SQL future. Unless of course you are a 'SQL is the end of history' person!

hinkley 4y ago

In a similar vein, I am trying to work on a tool for a volunteer group, and one of the problems I'm trying to figure out how to model is not just tracking when the data changed, but why it changed. 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.

qaq 4y ago

You do realize that how MVCC works right? Turn off GC process that collects old versions on production DB and see what happens. Reverting changes out of order is not possible in many cases.

zachmu 4y ago
Right, but it is with Dolt. That's the point. 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.
- qaq 4y ago
  so I have commit1 drop table blah commit2 create table blah with diff schema I revert commit1 what would be the state?
  - aboodman 4y ago
    It will be a conflict. But you can commit the revert to a different branch and run both schemas at the same time. This provides a lot of flexibility! Check out https://www.dolthub.com/blog/2022-04-29-dolt-revert/ https://www.dolthub.com/blog/2022-04-29-dolt-revert/ for some discussion of this.

jandrewrogers 4y ago

This is how relational databases have commonly worked since at least the 1990s and is called multi-version concurrency control (MVCC). Welcome to the future, it is called PostgreSQL. There are at least two reasons no sensible database designer would allow users to operate a database in this way even though they are technically capable of it. 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.

zachmu 4y ago
MVCC is not version control, and time travel / historical querying is not version control. 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.
- jandrewrogers 4y ago
  The conceit here is the assumption that this has not been built many times by very clever software engineers. It is not a new idea. True git-like version control systems for managing large volumes of data have been built on MVCC kernels for a decades -- branch and merge, push and pull, fork and clone. 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.

dizhn 4y ago

How would you revert, merge, rollback or otherwise make sense of real world data that necessarily reflects "state"? Or is this only for development?

k_bx 4y ago

Hot take: migrations are useless 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.

AaronLasseigne 4y ago

One issue with immutable data stores for businesses is compliance with things like GDPR. You need some mechanism for scrubbing data and its version history effectively making it non-immutable.