Isolation model

[hlavaatch]

You should consider what isolation model will be.
This is the most important thing that will influence performance of everything and is usually an afterthought.

It will influence how much concurrent work will users be able to do (will they block each other? break each others consistency? will users have to be aware of each other somehow?), and even what happens when you change the schema (If you add a field to a very large table, will you be able to do it while others are using the database, or will the database be offline for a week and take twice as much space for that time? Will existing transactions see any of the changes and when? Will other users be able to work during applying the change that can take a long time?)

This is a deep rabbit hole, but extremely interesting :)

[alex-dukhno]

Many thanks for bringing this discussion!

This is a deep rabbit hole, but extremely interesting :)

I have no doubt about this 😄

When I started to work on database, I thought that I make it first distributed and then add transactions. Then I had a thought that I make it transactional and then distributed.

Right now, because I want to start prototyping Machine Learning module as early as possible I think I will piggyback on sled transactions which should have serialisable isolation. I want to know what metrics or config parameters should be exposed to make database self-driven.

will the database be offline for a week and take twice as much space for that time?

I should pinned this question 😄 This is a great example for ML to figure out when to do "vacuum" or whatever will be implemented to reconcile changed table.

[hlavaatch]

Then I had a thought that I make it transactional and then distributed.

Well, you technically need distributed transactions for a distributed database ;)

Serializable isolation is the best model for the users, as it makes it look like the users have the database for themselves, translating conflicts into simple delays/waits for commit of the conflicting transaction).
There can still be deadlocks so you need the proper lock upgradability support (like the SELECT ... FOR UPDATE). It would also be best if users would see the changes they made so far (snapshot isolation does not really work for update transactions).

There are different ways of accomplishing serializable isolation, some of them only usable in certain usage scenarios (like having only one active update transaction at a time). Then there is the advanced stuff with reorderable updates... Devil is in the details :)

If you want good performance you definitely need to support read only transactions (that view snapshot of the data as it was on the transaction start and cannot make any updates, and appear from outside to complete in an instant). That helps with the read heavy use cases like web backends as they need no locking and the snapshot is shared between transactions viewing the same version of the data...

As for "vacuum", that is the worst part of postgres... there is no good time to run it in multiuser environment where you don't know (and dont want to) what the other users are doing...

Hmmm... I was dreaming about and designing an ideal relational database backend for almost two decades now, since I did a big survey of available database systems and their isolation models back in about 2001 and got frustrated with all the silliness like "optimistic" isolation models :) I have put a significant amout of time into research on concurrency and islation, and have some designs that should fit what I want it to do. Maybe it is time I finally put it together so you could use it as a backend? Rust & async may be the last bit I was missing :)

[alex-dukhno]

Then I had a thought that I make it transactional and then distributed.

Well, you technically need distributed transactions for a distributed database ;)

Haha, yeah, but I was worried that making database distributed will have huge impact on transaction design so I wanted to have experience of what is this "distributed database" at least for a single user.

I did a big survey of available database systems and their isolation models back in about 2001 and got frustrated with all the silliness like "optimistic" isolation models :)

Is this still alive? I'd love to have a look on data and results. I don't want to repeat mistakes of the past :D

Maybe it is time I finally put it together so you could use it as a backend?

Haha, sure :) Reach me out for whatever you need from me: code, design, discuss ideas, prototyping. I would especially love to do a review and ask silly questions :)

[alex-dukhno]

I've just seen it on my GH dashboard https://github.com/rystsov/awesome-distributed-transactions. Maybe it could help