Hold on, doesn’t one-database-per-user totally absolve all ACID guarantees? You can’t do cross-database transactions (to my knowledge), which means you can end up with corrupted data during aggregations. What am I missing?
One database per tenant only makes sense in multi-tenant applications that don't have any cross-tenant actions. I imagine there are many B2B applications that fall into this category.
If you have a use case with data that is extremely partitionable (like controlling individual industrial devices and collecting data to improve their performance or monitor the processes), then SQLite and Litestream could be a great option, but if you can't reasonably partition the data then it's probably better to use a centralized database server.
There can also be shared, mostly read-only databases, with no transactions crossing database boundaries.
For example, one database per retail store with sharded customers, orders and inventory (most transactions involve one order of one customer fulfilled from that store) and a shared one with item descriptions and prices (modified, say, daily).
>Hold on, doesn’t one-database-per-user totally absolve all ACID guarantees?
No it doesn't. What gave you that idea? You still have all "ACID guarantees" within each database.
>You can’t do cross-database transactions (to my knowledge),
That's true of most databases. If you have two apps and they use two different databases you won't have transactions across those two apps.
>which means you can end up with corrupted data during aggregations.
No, aggregations within each database work as you would expect.
>What am I missing?
As others have said, you use this pattern only if you don't intend to cross databases. By the way, in NoSQL databases like MongoDB, every document is its own little database so having a per user database is a massive upgrade over NoSQL.
- I was talking about ACID guarantees across databases (ie across users)
- I was talking about aggregations across databases (ie across users)
Of course working inside one database works as you would expect it to. My point was that this pattern of database-per-user seems to be a totally different design than people have used with traditional n-tier designs.
Good point about NoSQL! But, wasn’t part of the reason MongoDB fell out of favor because it was lacking consistency?
Yeah, I think MongoDB went through a set of steps
- atomically consistent at document level (which is fine for many apps as you have most related data in the single document anyways)
- atomically consistent within a collection
- and now, with MongoDB 4.0 and higher (released in 2017? 2018? whatever, a long time ago), MongoDB supports full transactional consistency across documents, collections, and shards.
It took them awhile, but they got there.
