4 ms·

When you want to support random writes and random reads you don't need any "query" language. You need a dumb and performant key value store which is optimized f

When you want to support random writes and random reads you don't need any "query" language. You need a dumb and performant key value store which is optimized for these and is easy to distribute to multiple machines. Querying can be ad hoc - ie you don't know what your query will be and in that case you want to offload your data to a olap system which will perform about 1 or 2 orders of magnitude better than your typical row oriented mysql database. Moving the data out of your primary database will prevent you from deadlocks and shooting yourself in a foot. If you know the query beforehand you simply compute and update a materialized index with results of your query. Buulding system is such a decoupled way is conceptually and impelementationally much simpler than your typical overcomplicated sql database with unpredictable execution plans. These databases are good only if 1. you don't know what you are doing 2. you need some quick and dirty system to work without thinking which and you are fine that it will never scale up. Many small CRUD ecommerce apps are like that and these databases get used because that is what your typical medium.com article recommends as the best practice.

skyde 6y ago
While i agree with all you said materialized index are not enough you also need transaction that span more than one entity. You can’t simply put all entity you might need to update atomically in the same key/document.
- lmm 6y ago
  As long as you have a clear dataflow you don't need transactions, you just need to make sure there are no loops in your dependencies. And you have to do that with transactions as well, otherwise they'll deadlock - the difference is that if you make a mistake you get to find out in production rather than when designing.
  - skyde 6y ago
    I would be very curious to know how someone could design an airline Computer reservation system this way. I agree that when using a SQL database that use locks instead of snapshot isolation you need to be careful about the order you scan row and join table to reduce frequency of dead-lock. My experience using PostgreSQL is that deadlock are extremely rare and when they happen they are automatically detected by the database and client simply abort and retry the transaction when this happen.
    - lmm 6y ago
      > I would be very curious to know how someone could design an airline Computer reservation system this way. You make each operation an event, you make all your logic into stream transformations, and you make sure the data only ever flows through in one direction (and you have to either avoid "diamonds" in your dataflow, or make sure that the consequences of a single event always commute with each other). So at its simplest, someone trying to make a reservation would commit a "reservation request" event and then listen for a "reservation confirmed" event (technically async, but it's fast enough that you can handle e.g. web requests this way - indeed even with a traditional database people recommend using async drivers), and on the other end there's a stream transformation that turns reservation requests (and plane scheduling events and so on) into reservation confirmations/denials. Since events in (the same partition of) a single stream are guaranteed to have a well-defined order, if there are two reservation requests for the last seat on a given plane then one of them will be confirmed and the other will be rejected because the plane is full. > My experience using PostgreSQL is that deadlock are extremely rare and when they happen they are automatically detected by the database and client simply abort and retry the transaction when this happen. That's no good. If you do that then you haven't solved the problem and you're guaranteed to get a full lockup as your traffic scales up. You have to actually understand which operations have caused the deadlock and rearrange your dataflow to avoid them. (Not to mention that if it's possible for the client to "simply abort and retry" then you've already proven that you didn't really need traditional database-style synchronous transactions).

skyde 6y ago

While i agree with all you said materialized index are not enough you also need transaction that span more than one entity. You can’t simply put all entity you might need to update atomically in the same key/document.

lmm 6y ago
As long as you have a clear dataflow you don't need transactions, you just need to make sure there are no loops in your dependencies. And you have to do that with transactions as well, otherwise they'll deadlock - the difference is that if you make a mistake you get to find out in production rather than when designing.
- skyde 6y ago
  I would be very curious to know how someone could design an airline Computer reservation system this way. I agree that when using a SQL database that use locks instead of snapshot isolation you need to be careful about the order you scan row and join table to reduce frequency of dead-lock. My experience using PostgreSQL is that deadlock are extremely rare and when they happen they are automatically detected by the database and client simply abort and retry the transaction when this happen.
  - lmm 6y ago
    > I would be very curious to know how someone could design an airline Computer reservation system this way. You make each operation an event, you make all your logic into stream transformations, and you make sure the data only ever flows through in one direction (and you have to either avoid "diamonds" in your dataflow, or make sure that the consequences of a single event always commute with each other). So at its simplest, someone trying to make a reservation would commit a "reservation request" event and then listen for a "reservation confirmed" event (technically async, but it's fast enough that you can handle e.g. web requests this way - indeed even with a traditional database people recommend using async drivers), and on the other end there's a stream transformation that turns reservation requests (and plane scheduling events and so on) into reservation confirmations/denials. Since events in (the same partition of) a single stream are guaranteed to have a well-defined order, if there are two reservation requests for the last seat on a given plane then one of them will be confirmed and the other will be rejected because the plane is full. > My experience using PostgreSQL is that deadlock are extremely rare and when they happen they are automatically detected by the database and client simply abort and retry the transaction when this happen. That's no good. If you do that then you haven't solved the problem and you're guaranteed to get a full lockup as your traffic scales up. You have to actually understand which operations have caused the deadlock and rearrange your dataflow to avoid them. (Not to mention that if it's possible for the client to "simply abort and retry" then you've already proven that you didn't really need traditional database-style synchronous transactions).