5 ms·

Relational queries and ACID are still absolute requirements for managing your data. Web frontends are just that, frontends. Is there something mathematically ch

• lmm 6y ago
  > Relational queries and ACID are still absolute requirements for managing your data. Web frontends are just that, frontends. Is there something mathematically changed that means that relational logic is somehow now false? The web request/response model makes ACID pointless. The whole point of ACID is that if two users try to update something at the same time, you won't lose either of their updates. No web app implements that: if user A loads the edit page and then user B loads the edit page and makes an edit, their update is going to be lost when A saves the page. (Or maybe the webapp implements its own locking, but it won't - and can't - use database transactions for it, because there's no way to know whether the user is still editing or has closed the page). > MySQL was a hack that people that didn't understand relational databases used as a glorified K/V store. It was "fast" compared to the alternative of postgres, but of the three qualities of good, cheap, fast, MySQL only won on the last of those and for a definition of "fast" that most of those web frontends didn't need. Exactly! MySQL did none of the things that "good" databases do, and yet MySQL-based companies were far more successful. Conclusion: the things that "good" databases do aren't actually that valuable. > "SQL is not machine-friendly"? Why not? It's irregular, hard to generate safely, and hard to parse. It makes no sense as a machine-to-machine interfaces (and it was never designed as one). > That's driven by the performance characteristics, which are driven by the query planner using the statistical analysis of that data. Only if the query is complex enough for those to be a dominant factor. For a simple primary-key lookup, today's databases typically spends 3x as long parsing the SQL as actually executing the query. > "...the transaction system means you're virtually guaranteed to deadlock..." you mean, if you try to break ACID, then the database enforces constraints to stop you and make you reconsider your data access properly? That's like saying Rust's borrow checker doesn't allow you to program race conditions. It's just the opposite: SQL databases rely on magically figuring out the locking for you, and don't give you enough visibility or tooling to let you manage it in a safe way. You can't look at a query and know which locks it will take, in which order, unless you're intimately familiar with that particular database's internals. Application programmers know to make sure every thread obtains locks in the same order, or ideally use higher-level abstractions. in SQL queries you don't have that option. > Storing JSON in a K/V store and doing joins in code is a regression, not an advance. Not expressing your data's constraints as actual constraints means that bugs can happen that shouldn't. I'm all for type safe datastores (and RDBMS type systems are pretty limited: no decent collection types, no aggregates unless you count temporary tables which come with a giant set of pitfalls). I can believe that referential integrity could be worthwhile. But SQL is a very poor interface, and datbase-level ACID is worse than useless in today's (web-based) architectures.
  • rswail 6y ago
    > The web request/response model makes ACID pointless. The whole point of ACID is that if two users try to update something at the same time, you won't lose either of their updates. No web app implements that: if user A loads the edit page and then user B loads the edit page and makes an edit, their update is going to be lost when A saves the page. (Or maybe the webapp implements its own locking, but it won't - and can't - use database transactions for it, because there's no way to know whether the user is still editing or has closed the page). That's a problem that is solved by CRDTs and the like, not any form of data store. But it's just one use of web frontends. Banking doesn't use that, neither does a large chunk of the world's applications. > Exactly! MySQL did none of the things that "good" databases do, and yet MySQL-based companies were far more successful. Conclusion: the things that "good" databases do aren't actually that valuable. Conclusion: Early web startups were successful, irrespective of what data store they had. Irrelevant to today's internet. And judging on the wrong criteria. Companies weren't "MySQL-Based", their business model disintermediated and undercut existing models. Correlation is not causation. Companies that ran on SUN/Solaris were successful as well. SQL is not intended as a machine-to-machine interface, it's a language for expressing relational queries. Prepared statements and caching remove that "3x as long parsing" > Application programmers know to make sure every thread obtains locks in the same order... Evidence suggests the opposite, thus the need for things like Rust's borrow checker. Studies have shown that programmers can't work out what the race conditions and locking requirements are. > But SQL is a very poor interface, and datbase-level ACID is worse than useless in today's (web-based) architectures. SQL is a crap language, agreed. But your definition of "web-based" architectures seems to be picked to be the CRDT related stream of changes. Which, by the way, could be implemented in an RDBMS by writing the CRDTs as transactions and enforcing the ordering with the appropriate rollbacks and commits.
    • lmm 6y ago
      > That's a problem that is solved by CRDTs and the like, not any form of data store. But once you accept that you have to use CRDTs or similar, traditional-RDBMS ACID becomes pointless, and you can and should use a datastore that makes better tradeoffs. > But it's just one use of web frontends. Banking doesn't use that, neither does a large chunk of the world's applications. Banking doesn't do that, but it doesn't do RDBMS-level transactions either. People cargo-cult the idea that you should use an ACID database, but I'm not sure I've ever seen a web application that actually made use of the database's transaction support, and fundamentally I think it's probably impossible - people say things like "if you get a deadlock, just retry", but if it's something you can retry then it didn't need to be a transaction in the first place. > Studies have shown that programmers can't work out what the race conditions and locking requirements are. Which is exactly the problem with using RDBMS transactions! It's hard enough to figure out deadlocks when you can see the lock statements in the code - it's impossible when you don't even have tooling that can tell you which locks will be held by which parts of the code before you run it. > But your definition of "web-based" architectures seems to be picked to be the CRDT related stream of changes. The thing about the web is that it makes everything a distributed system. Even if you only have one server, the fact that your clients are accessing it over a network means you have all the problems of distributed systems and are better off using a system that's designed to deal with them. > Which, by the way, could be implemented in an RDBMS by writing the CRDTs as transactions and enforcing the ordering with the appropriate rollbacks and commits. You wouldn't want to do a DB-level rollback or commit, the whole point of using a CRDT is that even when two writes conflict you can still commit them to durable storage straight away and resolve the conflict asynchronously. So the DB-level transactions don't help you. You can implement a perfectly good event sourcing architecture on top of an RDBMS by effectively using the tables as queues - I worked on a system that did exactly that back in 2010, and it worked very well. But at that point you're not really getting much value from the database - its transaction support only gives you ways to shoot yourself in the foot, its flexible query system likewise, its schema support is better than nothing but not by much. There's a lot of really impressive engineering under the hood, but the parts that would be useful to you aren't actually exposed where you can use them (e.g. the MVCC implementation could be really useful, but your database will go to great lengths to pretend that it isn't there and there's only ever one version of your table at a time. E.g. the indexing implementation is often very good, but you don't have enough control over which indices get built when). There are better options these days.