7 ms·

Show HN: DoltgreSQL – Version-Controlled DB, Like Git and PostgreSQL had a baby

162 points by Hydrocharged 3y ago

From the company behind Dolt—the world's first fully versioned database—comes DoltgreSQL, which implements PostgreSQL's variant of SQL. DoltgreSQL is at a very early stage, and we have quite a lot of work left to do, but we'd love to hear all thoughts and opinions! You can read more in the announcement blog post: https://www.dolthub.com/blog/2023-11-01-announcing-doltgresql/ https://www.dolthub.com/blog/2023-11-01-announcing-doltgresq...

leononame 3y ago
I've been waiting eagerly for this. Do you have a clear position on which PostgreSQL features not to support? I suppose there are more than just some things that won't make the cut because of the architectural decisions. While I unnderstand the decision, I'm not sure it's the best way to go about it. If you only emulate a subset of PostgreSQL's syntax and features, few people will be compelled to switch because they might be afraid. For greenfield projects, most people would probably choose the MySQL syntax since it's the default. I don't think this is about the necessity of running the PostgreSQL binary itself (although your approach already removes extensions which for many people is a downer). It's just that you can't trust an emulated system to be 100% equal in behavior (and people rely on implicit behavior of a system all the time, unfortunately) and that might be already enough for a lot of people to not use it. Have you guys already encountered some things in the PostgreSQL engine that just behave a bit differently from Dolt's engine? If so, what was your approach to mitigate it? Edit: just wanted to add: I'm really impressed by your work and I'm looking forward to trying this out. I don't mean to be mean, these are genuine questions I have. Congratulations on the launch.
- Hydrocharged 3y ago
  > Do you have a clear position on which PostgreSQL features not to support? I suppose there are more than just some things that won't make the cut because of the architectural decisions. While I unnderstand the decision, I'm not sure it's the best way to go about it. If you only emulate a subset of PostgreSQL's syntax and features, few people will be compelled to switch because they might be afraid. Eventually, we'd like to support the entirety of PostgreSQL's feature set, even including features like extensions. Dolt (https://github.com/dolthub/dolt https://github.com/dolthub/dolt), our first product, is the same to MySQL and DoltgreSQL is to Postgres, and we're taking a no-compromises approach to what we support. That, of course, means that there are a lot of features that need to be implemented, but Dolt is already almost there. For the majority of customers, Dolt has implemented everything they need from MySQL. I'd definitely recommended checking out how Dolt compares with MySQL to see how we're approaching compatibility. All behavior, implicit and explicit, is something that we aim to model, and any deviations are considered bugs that we need to fix. There are exceptions, but those are only used when we feel it's for good reason (an example being how MySQL handles collation cascading in some circumstances). > Have you guys already encountered some things in the PostgreSQL engine that just behave a bit differently from Dolt's engine? If so, what was your approach to mitigate it? With DoltgreSQL, it's at an extremely early stage. We're still working on getting the basic functionality working before we rigorously start testing to make sure that we match PostgreSQL's behavior. However, we can point to our approach with Dolt and MySQL for how we plan to handle DoltgreSQL and PostgreSQL. For every feature we implement, we compare the functionality with what is written in MySQL's documentation as a baseline. From there, we move on to comparing the output across a range of input statements. Sometimes the documentation differs from MySQL's own results, and we then try to find out why that's the case (Configuration? Out of date documentation? Bug? etc.). We also use external benchmarks to measure our correctness versus MySQL. In one such benchmark, containing around 6 million tests, Dolt recently reached 99.99% compared to MySQL (https://www.dolthub.com/blog/2023-10-11-four-9s-correctness/ https://www.dolthub.com/blog/2023-10-11-four-9s-correctness/). I hope this answered your questions! Let me know if you have any more :)
  - leononame 3y ago
    That does answer my questions. It's an extremely ambitious undertaking and I wish you the best. I'll be following this closely. Some people do performance optimisations based on PostgreSQL's inner working (e.g. trying to force data that isn't read often but not small into toast). How far do your ambitions go? Are you planning on modeling internal behavior like this as well? Do you think putting in an abstraction layer like this between Dolt will hurt performance? Do you have a time frame (probably not) or a roadmap for Doltgres?
    - Hydrocharged 3y ago
      Thank you! Performance optimizations are tackled a bit differently than correctness ones. For the most part, we'll try to use metrics to find weak points in the execution graph and optimize those, but we won't go so far as to try and model the internal performance behavior. In part because our storage format is so different that we'll have different performance characteristics by necessity. We don't have a public roadmap for Doltgres just yet, but we're hoping to put one out quite soon! We have a lot of low-hanging roadblocks that we want to take care of before we can get a better look at the overall time frame. We should definitely have one up by the end of the month, but I don't want to commit to a time before that.
selcuka 3y ago
> In 2019, when we were conceiving of Dolt, MySQL was the most popular SQL-flavor. Over the past 5 years, the tide has shifted more towards Postgres, especially among young companies, Dolt's target market. Really? I was under the impression that PostgreSQL had already won by then.
- Hydrocharged 3y ago
  Back then, when we were surveying the landscape, PostgreSQL was definitely in widespread use, but we were seeing MySQL being used in more professional/non-hobby spaces. Nowadays that's not necessarily the case, and we are seeing shops that were once MySQL-only now adopt PostgreSQL for some of their newer projects. With Dolt supporting MySQL and DoltgreSQL supporting PostgreSQL, we're hoping to appeal to both audiences.
  - whizzter 3y ago
    Why a fork? Couldn't you just add a separate front-end? Will the underlying storage format stay the same so one can flip over or do you want to get closer to PG semantics?
    - Hydrocharged 3y ago
      This isn't a fork of PostgreSQL, it's a completely bespoke database solution. Its only tie to PostgreSQL is that we've chosen to appear as a PostgreSQL server to clients. If a user didn't use any versioning features, then the goal is that they should be unable to tell that they're not on an actual PostgreSQL server. The versioning features are an important distinction though. Dolt (production ready, MySQL protocol) and DoltgreSQL (pre-alpha, PostgreSQL protocol) are built specifically to address the lack of versioning support in databases, and gaining these versioning features is as easy as swapping out the database you are using for Dolt and DoltgreSQL (once it's finished). MySQL and PostgreSQL are written using C/C++, while Dolt and DoltgreSQL are using Go, so there is no shared code. The storage format is implemented using prolly trees (https://docs.dolthub.com/architecture/storage-engine/prolly-tree https://docs.dolthub.com/architecture/storage-engine/prolly-...), which are based on merkle trees (used by Git and Bitcoin), so there is no overlap with any existing database solutions.
esafak 3y ago
Can people who use Dolt explain their use case? Dolt competes with Flyway and Bytebase, but it requires you to run their forked database? Schema migration is unpleasant and not something I can imagine doing willy-nilly like a git commit.
- Hydrocharged 3y ago
  We don't really compete with Flyway and Bytebase. Schema migrations are but one aspect of a versioned database. We version everything, from the schema to the data. You can read more here: https://www.dolthub.com/blog/2022-08-04-database-versioning/ https://www.dolthub.com/blog/2022-08-04-database-versioning/ A lot of products have come out that attempt to tackle schema versioning, but none have tackled data versioning before Dolt (https://github.com/dolthub/dolt https://github.com/dolthub/dolt). In addition, our database isn't forked, it's a full, bespoke solution that can operate as a drop-in replacement for MySQL (Dolt) or PostgreSQL (DoltgreSQL). It's honestly quite exciting technology, so definitely feel free to ask any more questions if you're curious to learn more! Here is a link to a few use cases as well: https://www.dolthub.com/blog/2022-07-11-dolt-case-studies/ https://www.dolthub.com/blog/2022-07-11-dolt-case-studies/
  - tianzhou 3y ago
    One of Bytebase authors here. I learned Dolt a while back (memorable name). I think Dolt is closer to Neon/Xata. But still there are differences. IIUC, Dolt is bringing the database feature to Git, while Neon/Xata is bringing the Git feature to database. Speaking of Bytebase, if Dolt is really good at versioning schema migration, Bytebase value proposition will be a bit less attractive, but not much. It's similar to the Git story, regardless how powerful Git is, people still need GitLab/GitHub for the developer workflow on top of the mere versioning.
    - Hydrocharged 3y ago
      I just learned of Neon a few weeks ago. From the looks of it, Neon supports branching, but it doesn't support merging. Xata supports both branching and merging, however it only applies to the schema. Dolt (and eventually DoltgreSQL) handles everything. Branching, merging, diffing, cherry-picking, commits, and more. Working with both the schema and data. On top of that, we also have DoltHub (https://www.dolthub.com/ https://www.dolthub.com/) that's analogous to GitHub, and DoltLab (https://www.dolthub.com/#doltlab https://www.dolthub.com/#doltlab) that's analogous to GitLab. We are targeting the entire ecosystem from the bottom up.
zx8080 3y ago
No offence, but how to read the name?
- Hydrocharged 3y ago
  In person, we generally say Doltgres, similar to Postgres. For the full name, it's just going to be Doltgres-Q-L.
- aitchnyu 3y ago
  The more I read on, the more was convinced I was reading an April fools article or a Tanenbaum textbook. Turns out Dolthub is hosted by Dolt and Doltlab is self hosted.
gijsnijholt1980 3y ago
Do you know VMDS by GE (part of Smallworld)? It has data versioning too, using a concept called ‘alternatives’. Do you know where DoltgreSQL and VMDS differ/overlap, functionally? See https://en.m.wikipedia.org/wiki/VMDS https://en.m.wikipedia.org/wiki/VMDS
- Hydrocharged 3y ago
  This is interesting, I've never heard of VMDS before. Someone else on the team may have, but I have not personally. From a quick glance at the linked Wikipedia page, it looks like this was created around the same time as SQL, and therefore has a different interaction model. It also predates a lot of modern version-control software like Git, SVN, and Perforce. So it looks like VMDS is a rather unique take on versioning data, where data is viewed as objects. Dolt and DoltgreSQL use the table and row paradigms that the majority of modern relational databases use. Dolt is designed to be a drop-in replacement for MySQL, and DoltgreSQL for PostgreSQL, so that already determines the interface. Regarding VMDS' versioning functionality, I see that is supports merging and conflict resolution at the transaction layer, but I'm not seeing anything similar to the concept of branches, which is something that Dolt and DoltgreSQL supports. Overall, for modern use cases, it doesn't look like they overlap much. VMDS seems focused more on the spatial case while representing data as objects, while Dolt and DoltgreSQL are traditional relational databases that support versioning all aspects of a relational database.
anonzzzies 3y ago
How is the replication / master-master / scale-out for this database? We really would love a solution like this but it would need to scale for our use case.
- Hydrocharged 3y ago
  At the moment, it does not exist for DoltgreSQL as it is in pre-alpha. Dolt (https://github.com/dolthub/dolt https://github.com/dolthub/dolt), however, may be a better fit for your use case. https://docs.dolthub.com/concepts/dolt/rdbms/replication https://docs.dolthub.com/concepts/dolt/rdbms/replication
  - anonzzzies 3y ago
    Ah, I missed that! Thanks for pointing that out.
deleted 3y ago
[deleted]
alecst 3y ago
Nice job guys! -Alec
- Hydrocharged 3y ago
  Thank you! :D
itslennysfault 3y ago
How is this better than existing migration solutions? More specifically, what does this give me that I'm not getting using the migration provided by an ORM? Current migration implementations live on the software side and are essentially a folder full of sequential, date stamped, SQL commands to execute. These files are checked into git so they are versioned. No offense to you or your team or this project, but I obviously trust the Postgres core team far more than you. However, everything is about trade-offs. So, what (briefly) makes this a risk worth taking?
- hahn-kev 3y ago
  From what I understand it's less about Schema history or migrations, then it is about data history. So if your data was stored in a VCS but it was also a Db. So you could query the database as it was last Thursday, just like you might look at an old version of a file in Git
  - itslennysfault 3y ago
    Interesting. I don't think I've ever really needed that capability, but it is a pretty cool concept. I HAVE needed an old version of a database to check/compare something, but nightly backups have always sufficed, and for disaster recovery I typically have point-in-time recovery set up. So if a migration goes boom I can just say, "ummmm lets go back to a couple minutes ago" which is a nice safety net.
    - Hydrocharged 3y ago
      hahn-kev made a really good comparison, but it can go a step further. Not only can you query the data at some previous point in time, but you can even run a join across old and current data. If you look at it from the perspective of commits and working sets, then the working set is just the "current" commit, and commands implicitly target the current commit. We give you explicit control over which commit is being referenced, so there's a lot of power in the model. Recovery is a valid use-case for version control, but fully applying it to data opens up possibilities that haven't really been explored before.
hahn-kev 3y ago
Really exciting, I was interested in Dolt but I prefer postgres. I'm curious if you have any plans for local first development. I love the idea of a version controlled database, but I want to use it for local first and distributed data syncing for users who are frequently without internet. It's not clear how you would do this without running the database locally. Do you have thoughts on this? DoltLite?
- timsehn 3y ago
  So you can do local first with the `dolt_clone()`, `dolt_remote()`,`dolt_fetch()`, `dolt_pull()`, and `dolt_push()` procedures: https://docs.dolthub.com/sql-reference/version-control/dolt-sql-procedures https://docs.dolthub.com/sql-reference/version-control/dolt-... as well as the `dolt_remotes` system table: https://docs.dolthub.com/sql-reference/version-control/dolt-system-tables#dolt_remotes https://docs.dolthub.com/sql-reference/version-control/dolt-... We have a ton of remote options: https://docs.dolthub.com/sql-reference/version-control/remotes https://docs.dolthub.com/sql-reference/version-control/remot... Right now, DoltHub (https://www.dolthub.com/ https://www.dolthub.com/) will still work as a remote but all the SQL there will be the MySQL version. Over time the Doltgres storage format may become more bespoke and we'll have to figure out how DoltHub will work.
- lifty 3y ago
  You can already use Dolt in embedded mode. Check this blog post from them: https://www.dolthub.com/blog/2022-07-25-embedded/ https://www.dolthub.com/blog/2022-07-25-embedded/. I use it like that and it works great.
huhuli 3y ago
Hmmm... I've been trying to extend a website I have with essentially wiki functionality. But Dolt is 1.7x slower than MySQL, as is written in that Readme. I'm looking for ways to have effortless and traceable changes to an entry or entries. I've experimented with ArangoDB but it wasn't for me. Dolt is now the only other VCS for databases I'm aware of. Are there any alternatives? So I could try more and decide which to settle with.
- zachmu 3y ago
  Dolt is the only version controlled SQL database. 1.7x MySQL is still very fast. You're talking about .5ms for a point lookup instead of .3ms, it's not something users will notice at typical scales. And we're still iterating on performance, the gap will continue to close over time.
coddx 3y ago
Great work. I will use this for the pet project I'm working on. Postgres rules. Thanks.
- Hydrocharged 3y ago
  Just want to point out that we're announcing development on the project. It's absolutely not ready for mainstream use yet! We have Dolt (https://github.com/dolthub/dolt https://github.com/dolthub/dolt) which is production-ready and widely in use, but it uses MySQL's syntax and wire protocol. We are building the Dolt equivalent for PostgreSQL, which is DoltgreSQL, but it's only pre-alpha.
maxisaurus 3y ago
Congrats - love this, especially joining across old and current data (or any point in time if i understood well?) I recently stumbled upon the concept of "bitemporal modeling" (ie. rewinding data "as of") - thought it described well this use case.
- Hydrocharged 3y ago
  You understood correctly! That's just one use case, there are many, many more. Some of which we haven't even considered yet, just because the model is that powerful.
bapetel 3y ago
Question: what is the difference with database migration tools like alembic ?
- Hydrocharged 3y ago
  This is a fully-versioned database. By versioning, think of how Git applies to text files, and all of the power that comes from that model. You have branching, diffing, merging, etc. It allows you to collaborate with each other, or you can create a brand new project by "forking" an existing one. Now, imagine that model, but applied to a relational database. There are products that can do something similar for schemas, but DoltgreSQL (and Dolt, the production-ready DB based around MySQL rather than PostgreSQL) applies this to both schemas and data. This is unique to DoltgreSQL and Dolt, and what sets us apart from all other products. One example I like to use that I think really captures this power, is the ability to query data from any commit. We use Git's model of version control, so your database has a commit history and a working set. The working set can be viewed as the "current" commit. When you run queries, you implicitly select the working set, however we expose the ability to explicitly set which commit you're targeting. This means you can do things like run a join query across old and current data! These situations, and more, and not possible via migration tools, backup tools, or any other kind of tools. This is unique to DoltgreSQL and Dolt, by fully embracing data versioning. This is but one example, and there are many, many more.
  - bapetel 3y ago
    OK, i understand

162 points by Hydrocharged 3y ago

leononame 3y ago

I've been waiting eagerly for this. Do you have a clear position on which PostgreSQL features not to support? I suppose there are more than just some things that won't make the cut because of the architectural decisions. While I unnderstand the decision, I'm not sure it's the best way to go about it. If you only emulate a subset of PostgreSQL's syntax and features, few people will be compelled to switch because they might be afraid. For greenfield projects, most people would probably choose the MySQL syntax since it's the default. I don't think this is about the necessity of running the PostgreSQL binary itself (although your approach already removes extensions which for many people is a downer). It's just that you can't trust an emulated system to be 100% equal in behavior (and people rely on implicit behavior of a system all the time, unfortunately) and that might be already enough for a lot of people to not use it. Have you guys already encountered some things in the PostgreSQL engine that just behave a bit differently from Dolt's engine? If so, what was your approach to mitigate it? Edit: just wanted to add: I'm really impressed by your work and I'm looking forward to trying this out. I don't mean to be mean, these are genuine questions I have. Congratulations on the launch.

Hydrocharged 3y ago
> Do you have a clear position on which PostgreSQL features not to support? I suppose there are more than just some things that won't make the cut because of the architectural decisions. While I unnderstand the decision, I'm not sure it's the best way to go about it. If you only emulate a subset of PostgreSQL's syntax and features, few people will be compelled to switch because they might be afraid. Eventually, we'd like to support the entirety of PostgreSQL's feature set, even including features like extensions. Dolt (https://github.com/dolthub/dolt https://github.com/dolthub/dolt), our first product, is the same to MySQL and DoltgreSQL is to Postgres, and we're taking a no-compromises approach to what we support. That, of course, means that there are a lot of features that need to be implemented, but Dolt is already almost there. For the majority of customers, Dolt has implemented everything they need from MySQL. I'd definitely recommended checking out how Dolt compares with MySQL to see how we're approaching compatibility. All behavior, implicit and explicit, is something that we aim to model, and any deviations are considered bugs that we need to fix. There are exceptions, but those are only used when we feel it's for good reason (an example being how MySQL handles collation cascading in some circumstances). > Have you guys already encountered some things in the PostgreSQL engine that just behave a bit differently from Dolt's engine? If so, what was your approach to mitigate it? With DoltgreSQL, it's at an extremely early stage. We're still working on getting the basic functionality working before we rigorously start testing to make sure that we match PostgreSQL's behavior. However, we can point to our approach with Dolt and MySQL for how we plan to handle DoltgreSQL and PostgreSQL. For every feature we implement, we compare the functionality with what is written in MySQL's documentation as a baseline. From there, we move on to comparing the output across a range of input statements. Sometimes the documentation differs from MySQL's own results, and we then try to find out why that's the case (Configuration? Out of date documentation? Bug? etc.). We also use external benchmarks to measure our correctness versus MySQL. In one such benchmark, containing around 6 million tests, Dolt recently reached 99.99% compared to MySQL (https://www.dolthub.com/blog/2023-10-11-four-9s-correctness/ https://www.dolthub.com/blog/2023-10-11-four-9s-correctness/). I hope this answered your questions! Let me know if you have any more :)
- leononame 3y ago
  That does answer my questions. It's an extremely ambitious undertaking and I wish you the best. I'll be following this closely. Some people do performance optimisations based on PostgreSQL's inner working (e.g. trying to force data that isn't read often but not small into toast). How far do your ambitions go? Are you planning on modeling internal behavior like this as well? Do you think putting in an abstraction layer like this between Dolt will hurt performance? Do you have a time frame (probably not) or a roadmap for Doltgres?
  - Hydrocharged 3y ago
    Thank you! Performance optimizations are tackled a bit differently than correctness ones. For the most part, we'll try to use metrics to find weak points in the execution graph and optimize those, but we won't go so far as to try and model the internal performance behavior. In part because our storage format is so different that we'll have different performance characteristics by necessity. We don't have a public roadmap for Doltgres just yet, but we're hoping to put one out quite soon! We have a lot of low-hanging roadblocks that we want to take care of before we can get a better look at the overall time frame. We should definitely have one up by the end of the month, but I don't want to commit to a time before that.

selcuka 3y ago

> In 2019, when we were conceiving of Dolt, MySQL was the most popular SQL-flavor. Over the past 5 years, the tide has shifted more towards Postgres, especially among young companies, Dolt's target market. Really? I was under the impression that PostgreSQL had already won by then.

Hydrocharged 3y ago
Back then, when we were surveying the landscape, PostgreSQL was definitely in widespread use, but we were seeing MySQL being used in more professional/non-hobby spaces. Nowadays that's not necessarily the case, and we are seeing shops that were once MySQL-only now adopt PostgreSQL for some of their newer projects. With Dolt supporting MySQL and DoltgreSQL supporting PostgreSQL, we're hoping to appeal to both audiences.
- whizzter 3y ago
  Why a fork? Couldn't you just add a separate front-end? Will the underlying storage format stay the same so one can flip over or do you want to get closer to PG semantics?
  - Hydrocharged 3y ago
    This isn't a fork of PostgreSQL, it's a completely bespoke database solution. Its only tie to PostgreSQL is that we've chosen to appear as a PostgreSQL server to clients. If a user didn't use any versioning features, then the goal is that they should be unable to tell that they're not on an actual PostgreSQL server. The versioning features are an important distinction though. Dolt (production ready, MySQL protocol) and DoltgreSQL (pre-alpha, PostgreSQL protocol) are built specifically to address the lack of versioning support in databases, and gaining these versioning features is as easy as swapping out the database you are using for Dolt and DoltgreSQL (once it's finished). MySQL and PostgreSQL are written using C/C++, while Dolt and DoltgreSQL are using Go, so there is no shared code. The storage format is implemented using prolly trees (https://docs.dolthub.com/architecture/storage-engine/prolly-tree https://docs.dolthub.com/architecture/storage-engine/prolly-...), which are based on merkle trees (used by Git and Bitcoin), so there is no overlap with any existing database solutions.

esafak 3y ago

Can people who use Dolt explain their use case? Dolt competes with Flyway and Bytebase, but it requires you to run their forked database? Schema migration is unpleasant and not something I can imagine doing willy-nilly like a git commit.

Hydrocharged 3y ago
We don't really compete with Flyway and Bytebase. Schema migrations are but one aspect of a versioned database. We version everything, from the schema to the data. You can read more here: https://www.dolthub.com/blog/2022-08-04-database-versioning/ https://www.dolthub.com/blog/2022-08-04-database-versioning/ A lot of products have come out that attempt to tackle schema versioning, but none have tackled data versioning before Dolt (https://github.com/dolthub/dolt https://github.com/dolthub/dolt). In addition, our database isn't forked, it's a full, bespoke solution that can operate as a drop-in replacement for MySQL (Dolt) or PostgreSQL (DoltgreSQL). It's honestly quite exciting technology, so definitely feel free to ask any more questions if you're curious to learn more! Here is a link to a few use cases as well: https://www.dolthub.com/blog/2022-07-11-dolt-case-studies/ https://www.dolthub.com/blog/2022-07-11-dolt-case-studies/
- tianzhou 3y ago
  One of Bytebase authors here. I learned Dolt a while back (memorable name). I think Dolt is closer to Neon/Xata. But still there are differences. IIUC, Dolt is bringing the database feature to Git, while Neon/Xata is bringing the Git feature to database. Speaking of Bytebase, if Dolt is really good at versioning schema migration, Bytebase value proposition will be a bit less attractive, but not much. It's similar to the Git story, regardless how powerful Git is, people still need GitLab/GitHub for the developer workflow on top of the mere versioning.
  - Hydrocharged 3y ago
    I just learned of Neon a few weeks ago. From the looks of it, Neon supports branching, but it doesn't support merging. Xata supports both branching and merging, however it only applies to the schema. Dolt (and eventually DoltgreSQL) handles everything. Branching, merging, diffing, cherry-picking, commits, and more. Working with both the schema and data. On top of that, we also have DoltHub (https://www.dolthub.com/ https://www.dolthub.com/) that's analogous to GitHub, and DoltLab (https://www.dolthub.com/#doltlab https://www.dolthub.com/#doltlab) that's analogous to GitLab. We are targeting the entire ecosystem from the bottom up.

zx8080 3y ago

No offence, but how to read the name?

Hydrocharged 3y ago
In person, we generally say Doltgres, similar to Postgres. For the full name, it's just going to be Doltgres-Q-L.
aitchnyu 3y ago
The more I read on, the more was convinced I was reading an April fools article or a Tanenbaum textbook. Turns out Dolthub is hosted by Dolt and Doltlab is self hosted.

gijsnijholt1980 3y ago

Do you know VMDS by GE (part of Smallworld)? It has data versioning too, using a concept called ‘alternatives’. Do you know where DoltgreSQL and VMDS differ/overlap, functionally? See https://en.m.wikipedia.org/wiki/VMDS https://en.m.wikipedia.org/wiki/VMDS

Hydrocharged 3y ago
This is interesting, I've never heard of VMDS before. Someone else on the team may have, but I have not personally. From a quick glance at the linked Wikipedia page, it looks like this was created around the same time as SQL, and therefore has a different interaction model. It also predates a lot of modern version-control software like Git, SVN, and Perforce. So it looks like VMDS is a rather unique take on versioning data, where data is viewed as objects. Dolt and DoltgreSQL use the table and row paradigms that the majority of modern relational databases use. Dolt is designed to be a drop-in replacement for MySQL, and DoltgreSQL for PostgreSQL, so that already determines the interface. Regarding VMDS' versioning functionality, I see that is supports merging and conflict resolution at the transaction layer, but I'm not seeing anything similar to the concept of branches, which is something that Dolt and DoltgreSQL supports. Overall, for modern use cases, it doesn't look like they overlap much. VMDS seems focused more on the spatial case while representing data as objects, while Dolt and DoltgreSQL are traditional relational databases that support versioning all aspects of a relational database.

anonzzzies 3y ago

How is the replication / master-master / scale-out for this database? We really would love a solution like this but it would need to scale for our use case.

Hydrocharged 3y ago
At the moment, it does not exist for DoltgreSQL as it is in pre-alpha. Dolt (https://github.com/dolthub/dolt https://github.com/dolthub/dolt), however, may be a better fit for your use case. https://docs.dolthub.com/concepts/dolt/rdbms/replication https://docs.dolthub.com/concepts/dolt/rdbms/replication
- anonzzzies 3y ago
  Ah, I missed that! Thanks for pointing that out.

deleted 3y ago

[deleted]

alecst 3y ago

Nice job guys! -Alec

Hydrocharged 3y ago
Thank you! :D

itslennysfault 3y ago

How is this better than existing migration solutions? More specifically, what does this give me that I'm not getting using the migration provided by an ORM? Current migration implementations live on the software side and are essentially a folder full of sequential, date stamped, SQL commands to execute. These files are checked into git so they are versioned. No offense to you or your team or this project, but I obviously trust the Postgres core team far more than you. However, everything is about trade-offs. So, what (briefly) makes this a risk worth taking?

hahn-kev 3y ago
From what I understand it's less about Schema history or migrations, then it is about data history. So if your data was stored in a VCS but it was also a Db. So you could query the database as it was last Thursday, just like you might look at an old version of a file in Git
- itslennysfault 3y ago
  Interesting. I don't think I've ever really needed that capability, but it is a pretty cool concept. I HAVE needed an old version of a database to check/compare something, but nightly backups have always sufficed, and for disaster recovery I typically have point-in-time recovery set up. So if a migration goes boom I can just say, "ummmm lets go back to a couple minutes ago" which is a nice safety net.
  - Hydrocharged 3y ago
    hahn-kev made a really good comparison, but it can go a step further. Not only can you query the data at some previous point in time, but you can even run a join across old and current data. If you look at it from the perspective of commits and working sets, then the working set is just the "current" commit, and commands implicitly target the current commit. We give you explicit control over which commit is being referenced, so there's a lot of power in the model. Recovery is a valid use-case for version control, but fully applying it to data opens up possibilities that haven't really been explored before.

hahn-kev 3y ago

Really exciting, I was interested in Dolt but I prefer postgres. I'm curious if you have any plans for local first development. I love the idea of a version controlled database, but I want to use it for local first and distributed data syncing for users who are frequently without internet. It's not clear how you would do this without running the database locally. Do you have thoughts on this? DoltLite?

timsehn 3y ago
So you can do local first with the `dolt_clone()`, `dolt_remote()`,`dolt_fetch()`, `dolt_pull()`, and `dolt_push()` procedures: https://docs.dolthub.com/sql-reference/version-control/dolt-sql-procedures https://docs.dolthub.com/sql-reference/version-control/dolt-... as well as the `dolt_remotes` system table: https://docs.dolthub.com/sql-reference/version-control/dolt-system-tables#dolt_remotes https://docs.dolthub.com/sql-reference/version-control/dolt-... We have a ton of remote options: https://docs.dolthub.com/sql-reference/version-control/remotes https://docs.dolthub.com/sql-reference/version-control/remot... Right now, DoltHub (https://www.dolthub.com/ https://www.dolthub.com/) will still work as a remote but all the SQL there will be the MySQL version. Over time the Doltgres storage format may become more bespoke and we'll have to figure out how DoltHub will work.
lifty 3y ago
You can already use Dolt in embedded mode. Check this blog post from them: https://www.dolthub.com/blog/2022-07-25-embedded/ https://www.dolthub.com/blog/2022-07-25-embedded/. I use it like that and it works great.

huhuli 3y ago

Hmmm... I've been trying to extend a website I have with essentially wiki functionality. But Dolt is 1.7x slower than MySQL, as is written in that Readme. I'm looking for ways to have effortless and traceable changes to an entry or entries. I've experimented with ArangoDB but it wasn't for me. Dolt is now the only other VCS for databases I'm aware of. Are there any alternatives? So I could try more and decide which to settle with.

zachmu 3y ago
Dolt is the only version controlled SQL database. 1.7x MySQL is still very fast. You're talking about .5ms for a point lookup instead of .3ms, it's not something users will notice at typical scales. And we're still iterating on performance, the gap will continue to close over time.

coddx 3y ago

Great work. I will use this for the pet project I'm working on. Postgres rules. Thanks.

Hydrocharged 3y ago
Just want to point out that we're announcing development on the project. It's absolutely not ready for mainstream use yet! We have Dolt (https://github.com/dolthub/dolt https://github.com/dolthub/dolt) which is production-ready and widely in use, but it uses MySQL's syntax and wire protocol. We are building the Dolt equivalent for PostgreSQL, which is DoltgreSQL, but it's only pre-alpha.

maxisaurus 3y ago

Congrats - love this, especially joining across old and current data (or any point in time if i understood well?) I recently stumbled upon the concept of "bitemporal modeling" (ie. rewinding data "as of") - thought it described well this use case.

Hydrocharged 3y ago
You understood correctly! That's just one use case, there are many, many more. Some of which we haven't even considered yet, just because the model is that powerful.

bapetel 3y ago

Question: what is the difference with database migration tools like alembic ?

Hydrocharged 3y ago
This is a fully-versioned database. By versioning, think of how Git applies to text files, and all of the power that comes from that model. You have branching, diffing, merging, etc. It allows you to collaborate with each other, or you can create a brand new project by "forking" an existing one. Now, imagine that model, but applied to a relational database. There are products that can do something similar for schemas, but DoltgreSQL (and Dolt, the production-ready DB based around MySQL rather than PostgreSQL) applies this to both schemas and data. This is unique to DoltgreSQL and Dolt, and what sets us apart from all other products. One example I like to use that I think really captures this power, is the ability to query data from any commit. We use Git's model of version control, so your database has a commit history and a working set. The working set can be viewed as the "current" commit. When you run queries, you implicitly select the working set, however we expose the ability to explicitly set which commit you're targeting. This means you can do things like run a join query across old and current data! These situations, and more, and not possible via migration tools, backup tools, or any other kind of tools. This is unique to DoltgreSQL and Dolt, by fully embracing data versioning. This is but one example, and there are many, many more.
- bapetel 3y ago
  OK, i understand