3 ms·

Please excuse my ignorance - how do I enforce nontrivial system-wide invariants with lots of little databases rather than a single consolidated one? The invari

Please excuse my ignorance - how do I enforce nontrivial system-wide invariants with lots of little databases rather than a single consolidated one? The invariants I care about are: (0) Referential integrity. If the table `Foo` has a `FOREIGN KEY (BarID) REFERENCES Bar (BarID)`, then no row in `Foo` must be seen as having a `BarID` whose value can't be found in the table `Bar`. (1) Linearizability. There must exist a total order on the entire transaction history of a database, such that, starting from the empty database, and executing the transactions nonconcurrently in the given total order, the result is the current state of the database. (NOTE: The transaction history need not be physically stored anywhere. So this invariant can't be “tested” - it has to be proven to hold.) These guarantees are so basic, so fundamental in my everyday use of RDBMSs, that I need to be convinced that they hold.

biokoda 11y ago
By finding the natural sharding factor. If you have one, which very often you do. Three completely different examples: - Are you running a mail server? Every user is an actor (i.e. an sqlite instance). - Are you running a backend for a dropbox/evernote/messaging type app? Every user is an actor. - Do you have a distributed filesystem? Use the KV mode and shard on file hash. - Do you need scalable counters? Use KV mode again, split every counter into 10 (or 100) and increment/decrement on those.
- catnaroek 11y ago
  Again, please excuse my ignorance - what is the definition of “natural sharding factor”, and how would I compute it for, say, an ERP system?
  - biokoda 11y ago
    I have no experience in developing in ERP systems. I do have experience on being at the end of a very poorly working one. These systems seem (from my outside view) to have a tendency to become giant monoliths. So when developing you must fight against increasing monolithic complexity. Using something like ActorDB can be somewhat of a beneficial constraint. It forces you to maintain a clean design. I would force sales, marketing, shipping, product planning (taking from wikipedia here..), to be their own separate actors with their own schemas. Then if possible shard within those types. So if something is customer service, have an actor per customer and have all his data there. If you're developing multiple products, every product has an actor.
    - catnaroek 11y ago
      > These systems seem (from my outside view) to have a tendency to become giant monoliths. So when developing you must fight against increasing monolithic complexity. You aren't wrong, that's my experience as well. It's just as annoying for programmers (or, at least, for me) as it is for users. The following question has popped out countless times in my head: “Why do I have to rely on an implicit convention that this application module never touches this database table?” There was never a good answer. The only reason why I put up with such things is that I have no idea how to prevent more modular designs from turning into a data integrity nightmare. (I'll freely admit my lack of education is to blame here.) For instance, let's say we have three modules: inventory, sales and shipping. Furthermore, let's assume each module is its own actor and uses its own backend database. We must implement the use case “enter a sale in the system”: (0) The sales module queries the inventory module whether there is enough of a product in stock to satisfy a customer order. The expected sequence of actions is: (1) The inventory module “locks” the requested quantity/amount of the product [so that it can't be used, say, for another sale], and gives the sales module a “token” that can be used to confirm or cancel the withdrawal. (2) The sales module queries the shipping module if there are enough available trucks/ships/whatever to ship the product to the customer's location by a given delivery date. (3) The shipping module “locks” however many trucks/ships/whatever it deems necessary to ship the product, and gives the sales modules a “token” that can be used to confirm or cancel the shipping. (4) The sales module queries the user for the customer's credit card number and verification code, interfaces with the bank's system, blablabla... (5) The sales module confirms to the inventory and shipping modules [in this specific order] that the product will be withdrawn and shipped. Now some exception handling: (6) If step 3 fails, the sales module cancels the product withdrawal. (7) If step 4 fails, the sales module cancels the product withdrawal and shipping. (8) If any system [inventory, sales, shipping] goes down, neither the product nor the trucks/ships/whatever can be kept locked forever. So each lock must have a timeout, and, if it's neither explicitly confirmed nor explicitly cancelled by the sales module, it will be implicitly cancelled by the inventory and/or shipping module when the timeout elapses. (9) It may happen [unlikely, but not impossible], that the inventory and shipping module's clocks get unsynchronized in such a way that, when the product withdrawal has been been confirmed, the shipping lock has already elapsed. Oh, the nightmare. Implementing all of this correctly in all cases is actually tricky! And if anything is implemented slightly wrong, the whole system goes kaboom! With a monolithic database, there is no need to “lock” any resources, nor issue “confirmation tokens” - just use the DBMS's built-in transaction system!
diroussel 11y ago
You won't find many RDBMS that can guarantee Linearizability. See: https://martin.kleppmann.com/2015/09/26/transactions-at-strange-loop.html https://martin.kleppmann.com/2015/09/26/transactions-at-stra...
- catnaroek 11y ago
  The ones that I use at home (PostgreSQL) and at work (SQL Server) allow serializable transactions. Of course, in many cases it's overkill, but it's good to know that it's there when needed.

biokoda 11y ago

By finding the natural sharding factor. If you have one, which very often you do. Three completely different examples: - Are you running a mail server? Every user is an actor (i.e. an sqlite instance). - Are you running a backend for a dropbox/evernote/messaging type app? Every user is an actor. - Do you have a distributed filesystem? Use the KV mode and shard on file hash. - Do you need scalable counters? Use KV mode again, split every counter into 10 (or 100) and increment/decrement on those.

catnaroek 11y ago
Again, please excuse my ignorance - what is the definition of “natural sharding factor”, and how would I compute it for, say, an ERP system?
- biokoda 11y ago
  I have no experience in developing in ERP systems. I do have experience on being at the end of a very poorly working one. These systems seem (from my outside view) to have a tendency to become giant monoliths. So when developing you must fight against increasing monolithic complexity. Using something like ActorDB can be somewhat of a beneficial constraint. It forces you to maintain a clean design. I would force sales, marketing, shipping, product planning (taking from wikipedia here..), to be their own separate actors with their own schemas. Then if possible shard within those types. So if something is customer service, have an actor per customer and have all his data there. If you're developing multiple products, every product has an actor.
  - catnaroek 11y ago
    > These systems seem (from my outside view) to have a tendency to become giant monoliths. So when developing you must fight against increasing monolithic complexity. You aren't wrong, that's my experience as well. It's just as annoying for programmers (or, at least, for me) as it is for users. The following question has popped out countless times in my head: “Why do I have to rely on an implicit convention that this application module never touches this database table?” There was never a good answer. The only reason why I put up with such things is that I have no idea how to prevent more modular designs from turning into a data integrity nightmare. (I'll freely admit my lack of education is to blame here.) For instance, let's say we have three modules: inventory, sales and shipping. Furthermore, let's assume each module is its own actor and uses its own backend database. We must implement the use case “enter a sale in the system”: (0) The sales module queries the inventory module whether there is enough of a product in stock to satisfy a customer order. The expected sequence of actions is: (1) The inventory module “locks” the requested quantity/amount of the product [so that it can't be used, say, for another sale], and gives the sales module a “token” that can be used to confirm or cancel the withdrawal. (2) The sales module queries the shipping module if there are enough available trucks/ships/whatever to ship the product to the customer's location by a given delivery date. (3) The shipping module “locks” however many trucks/ships/whatever it deems necessary to ship the product, and gives the sales modules a “token” that can be used to confirm or cancel the shipping. (4) The sales module queries the user for the customer's credit card number and verification code, interfaces with the bank's system, blablabla... (5) The sales module confirms to the inventory and shipping modules [in this specific order] that the product will be withdrawn and shipped. Now some exception handling: (6) If step 3 fails, the sales module cancels the product withdrawal. (7) If step 4 fails, the sales module cancels the product withdrawal and shipping. (8) If any system [inventory, sales, shipping] goes down, neither the product nor the trucks/ships/whatever can be kept locked forever. So each lock must have a timeout, and, if it's neither explicitly confirmed nor explicitly cancelled by the sales module, it will be implicitly cancelled by the inventory and/or shipping module when the timeout elapses. (9) It may happen [unlikely, but not impossible], that the inventory and shipping module's clocks get unsynchronized in such a way that, when the product withdrawal has been been confirmed, the shipping lock has already elapsed. Oh, the nightmare. Implementing all of this correctly in all cases is actually tricky! And if anything is implemented slightly wrong, the whole system goes kaboom! With a monolithic database, there is no need to “lock” any resources, nor issue “confirmation tokens” - just use the DBMS's built-in transaction system!

diroussel 11y ago

You won't find many RDBMS that can guarantee Linearizability. See: https://martin.kleppmann.com/2015/09/26/transactions-at-strange-loop.html https://martin.kleppmann.com/2015/09/26/transactions-at-stra...

catnaroek 11y ago
The ones that I use at home (PostgreSQL) and at work (SQL Server) allow serializable transactions. Of course, in many cases it's overkill, but it's good to know that it's there when needed.