5 ms·

Every app I’ve worked on has used transactions to maintain data integrity. And before you say I could have stored everything in a document, I had to move away f

Every app I’ve worked on has used transactions to maintain data integrity. And before you say I could have stored everything in a document, I had to move away from MongoDB because it couldn’t handle the ways I needed to query the data in a performant way.

lmm 5y ago
"Maintain data integrity" sounds good, but what does it actually mean? In practice with an RDBMS it means you drop a write on the floor (or worse, deadlock) if it violates a constraint. And in practice that's almost never good enough, so you end up having to build the same kind of application-level validation logic that you would have written in a non-RDBMS system anyway.
- justinclift 5y ago
  Why would you be input data in a way that violates referential integrity? That would be pretty bizarre. That being said, if your code doesn't catch and handle errors, then there's a couple of deeper problems already.
  - lmm 5y ago
    > Why would you be input data in a way that violates referential integrity? That would be pretty bizarre. Well if you can't get input that violates that integrity then what are you gaining by enforcing that integrity? > That being said, if your code doesn't catch and handle errors, then there's a couple of deeper problems already. Sure, but how can you do error handling without data storage, given that your application itself is stateless? Maybe you retry the failure a few times, but ultimately your only option is to drop the data on the floor, which is almost always not what you want.
    - kayodelycaon 5y ago
      If you allow invalid records and multiple versions of a schema in a collection, you will forever being paying for that mistake every time you read from the database.
      - lmm 5y ago
        If you're fine with just dropping that invalid data, you can always just delete it at any point in the future. (Yes, that should horrify you - but so should the idea of just erroring and dropping it when someone first tried to write it, which is what an RDBMS would do). The overall effect is the same either way. Fixing up data that was written wrongly takes work, there's no getting away from that. But if you have a record of the original write attempt then at least you can do that work and recover the data (if you decide the cost/benefit is worth it). Whereas if you have the datastore reject it at write time then you're SOL before you've even started.
        CRConrad 5y ago
        > Yes, that should horrify you - but so should the idea of just erroring and dropping it when someone first tried to write it, which is what an RDBMS would do Have you ever actually encountered any RDBMS that just silently "drops" your write without informing you, or is this just how you think they work? Otherwise, isn't this just like any other data write erroring out -- MongoDB driver saying "Service down!", simple flat-file write saying "Disk full!", etc -- in that if your app doesn't react to this, it's not the data store but your code that sucks?
    - justinclift 5y ago
      > Well if you can't get input that violates that integrity then what are you gaining by enforcing that integrity? Perhaps I'm wording this badly? Your application should _not_ be generating data which can't go into the database correctly. If it is, that's a different set of problems. ;)
      - lmm 5y ago
        What are you claiming the purpose of integrity constraints in the database is? If your application doesn't generate data which can't go in the database correctly, what's the observable difference between a database that enforces constraints and a database that doesn't?
        CRConrad 5y ago
        The difference is that one of them informs you when you've failed at generating valid data to save, and the other doesn't.
- L0stLink 5y ago
  RDBMS and Non-RDBMS both have there place, I have used both in the same system several times, all for things that they were good at. Transactions allow you to be confident while making complex changes that in case a failure occurs all partial changes will be rolled back. making use of database level validations and enforcing referential integrity is essential for keeping data consistent over the long term and making data migrations easier. Sure in trivial applications you can just dump data to a document store and have a validation soup handle the rest, that too can be implemented cleanly but Knowing when to leverage RDBMS over other DBMS is an essential skill for an engineer. Because that is how you build scalable systems. Not by throwing JSON stores at everything and calling it a day.
  - lmm 5y ago
    If scalability is your concern then you can't use any of the supposedly core features of an RDBMS, since fundamentally there is no way to have a transaction across multiple nodes without solving a much bigger problem. Validation is vital but the datastore is not the place to do it, because handling invalid data by dropping it on the floor is almost never the right behaviour. There is no substitute for actually understanding your data model, but once you do 99% of the time you'll find using an RDBMS comes with minimal benefits and significant costs.
    - tarsinge 5y ago
      The point is that you can go pretty far on a single cluster (GitLab example). That 99% figure is trivially wrong in that case.
    - simonw 5y ago
      My experience is the exact opposite: once you understand your data model, 99% of the time you will find that NOT using a RDBMS comes with minimal benefits and significant costs.
    - L0stLink 5y ago
      It is extremely difficult to design a good RDBMS schema without understanding the data model, and once you do, it is there documented in its entirety with best in class tooling for anyone else to come along, pickup and be up to speed with it, additionally you don't have to forgo document storage, most if not all modern RDBMS suppord json(b) types.

lmm 5y ago

"Maintain data integrity" sounds good, but what does it actually mean? In practice with an RDBMS it means you drop a write on the floor (or worse, deadlock) if it violates a constraint. And in practice that's almost never good enough, so you end up having to build the same kind of application-level validation logic that you would have written in a non-RDBMS system anyway.

justinclift 5y ago
Why would you be input data in a way that violates referential integrity? That would be pretty bizarre. That being said, if your code doesn't catch and handle errors, then there's a couple of deeper problems already.
- lmm 5y ago
  > Why would you be input data in a way that violates referential integrity? That would be pretty bizarre. Well if you can't get input that violates that integrity then what are you gaining by enforcing that integrity? > That being said, if your code doesn't catch and handle errors, then there's a couple of deeper problems already. Sure, but how can you do error handling without data storage, given that your application itself is stateless? Maybe you retry the failure a few times, but ultimately your only option is to drop the data on the floor, which is almost always not what you want.
  - kayodelycaon 5y ago
    If you allow invalid records and multiple versions of a schema in a collection, you will forever being paying for that mistake every time you read from the database.
    - lmm 5y ago
      If you're fine with just dropping that invalid data, you can always just delete it at any point in the future. (Yes, that should horrify you - but so should the idea of just erroring and dropping it when someone first tried to write it, which is what an RDBMS would do). The overall effect is the same either way. Fixing up data that was written wrongly takes work, there's no getting away from that. But if you have a record of the original write attempt then at least you can do that work and recover the data (if you decide the cost/benefit is worth it). Whereas if you have the datastore reject it at write time then you're SOL before you've even started.
      - CRConrad 5y ago
        > Yes, that should horrify you - but so should the idea of just erroring and dropping it when someone first tried to write it, which is what an RDBMS would do Have you ever actually encountered any RDBMS that just silently "drops" your write without informing you, or is this just how you think they work? Otherwise, isn't this just like any other data write erroring out -- MongoDB driver saying "Service down!", simple flat-file write saying "Disk full!", etc -- in that if your app doesn't react to this, it's not the data store but your code that sucks?
  - justinclift 5y ago
    > Well if you can't get input that violates that integrity then what are you gaining by enforcing that integrity? Perhaps I'm wording this badly? Your application should _not_ be generating data which can't go into the database correctly. If it is, that's a different set of problems. ;)
    - lmm 5y ago
      What are you claiming the purpose of integrity constraints in the database is? If your application doesn't generate data which can't go in the database correctly, what's the observable difference between a database that enforces constraints and a database that doesn't?
      - CRConrad 5y ago
        The difference is that one of them informs you when you've failed at generating valid data to save, and the other doesn't.
L0stLink 5y ago
RDBMS and Non-RDBMS both have there place, I have used both in the same system several times, all for things that they were good at. Transactions allow you to be confident while making complex changes that in case a failure occurs all partial changes will be rolled back. making use of database level validations and enforcing referential integrity is essential for keeping data consistent over the long term and making data migrations easier. Sure in trivial applications you can just dump data to a document store and have a validation soup handle the rest, that too can be implemented cleanly but Knowing when to leverage RDBMS over other DBMS is an essential skill for an engineer. Because that is how you build scalable systems. Not by throwing JSON stores at everything and calling it a day.
- lmm 5y ago
  If scalability is your concern then you can't use any of the supposedly core features of an RDBMS, since fundamentally there is no way to have a transaction across multiple nodes without solving a much bigger problem. Validation is vital but the datastore is not the place to do it, because handling invalid data by dropping it on the floor is almost never the right behaviour. There is no substitute for actually understanding your data model, but once you do 99% of the time you'll find using an RDBMS comes with minimal benefits and significant costs.
  - tarsinge 5y ago
    The point is that you can go pretty far on a single cluster (GitLab example). That 99% figure is trivially wrong in that case.
  - simonw 5y ago
    My experience is the exact opposite: once you understand your data model, 99% of the time you will find that NOT using a RDBMS comes with minimal benefits and significant costs.
  - L0stLink 5y ago
    It is extremely difficult to design a good RDBMS schema without understanding the data model, and once you do, it is there documented in its entirety with best in class tooling for anyone else to come along, pickup and be up to speed with it, additionally you don't have to forgo document storage, most if not all modern RDBMS suppord json(b) types.