4 ms·

Normalization is not only about data storage but most importantly, data integrity.

endisneigh 3y ago
Yes, but I assert that it's possible to use transactions to update everything consistently. Serializable transactions weren't really common when MySQL/Postgres first came out, but now that they're common in new DBs + ACID, I think it's not possible to do with reasonable difficulty. If you agree with this, than its easy to prove that denormalized tables performance increase is well worth the annoyance of updating everything to transactionally update the dependencies. I won't say that it's trivial to update all of your business logic to do this, but I think it's definitely worth it for a new project at least.
- Guvante 3y ago
  You always need to compare write vs read performance. Turning a single table update into a 10 table one could tip your lock contention to the point where you are write bound or worse start hitting retries. Certainly it makes sense to move rarely updated fields to where they are used makes sense. Similarly "build your table against your queries not your ideal data model" is always sage advice.
- Bognar 3y ago
  Denormalized transactions are not trivial unless you are using serializable isolation level which will kill performance. If you don't use serializable isolation level, then you risk either running into deadlocks (which will kill performance) or inconsistency. Decent SQL databases offer materialized views, which probably give you what you want without all the headache of maintaining denormalized tables yourself.
  - endisneigh 3y ago
    all fair points, but to be fair I don't necessary think this makes the most sense for an existing project for the reasons you state. I do think for a new project would best be able to design around the access patterns in a way that eliminate most of the downsides.
- williamdclt 3y ago
  Transactions are not only (actually mainly not) about atomicity. Of course it’s possible to keep data integrity without normalisation, but that means you need to maintain the invariants yourself at application level and a big could result in data inconsistency. Normalisation isn’t there to make integrity possible, it’s there to make (some) non-integrity impossible. Nobody says you have to have only one view of your data though. You can have a normalised view of your data to write, and another denormalised for fast reads (you usually have to, at scale). Something like event sourcing is another way (which is actually pushing invariants to application level, in a structured way)
  - sgarland 3y ago
    > that means you need to maintain the invariants yourself at application level Foreign Keys. Of course, now you have a new set of problems, but referential integrity isn't one of them.

Yes, but I assert that it's possible to use transactions to update everything consistently. Serializable transactions weren't really common when MySQL/Postgres first came out, but now that they're common in new DBs + ACID, I think it's not possible to do with reasonable difficulty. If you agree with this, than its easy to prove that denormalized tables performance increase is well worth the annoyance of updating everything to transactionally update the dependencies. I won't say that it's trivial to update all of your business logic to do this, but I think it's definitely worth it for a new project at least.

Guvante 3y ago
You always need to compare write vs read performance. Turning a single table update into a 10 table one could tip your lock contention to the point where you are write bound or worse start hitting retries. Certainly it makes sense to move rarely updated fields to where they are used makes sense. Similarly "build your table against your queries not your ideal data model" is always sage advice.
Bognar 3y ago
Denormalized transactions are not trivial unless you are using serializable isolation level which will kill performance. If you don't use serializable isolation level, then you risk either running into deadlocks (which will kill performance) or inconsistency. Decent SQL databases offer materialized views, which probably give you what you want without all the headache of maintaining denormalized tables yourself.
- endisneigh 3y ago
  all fair points, but to be fair I don't necessary think this makes the most sense for an existing project for the reasons you state. I do think for a new project would best be able to design around the access patterns in a way that eliminate most of the downsides.
williamdclt 3y ago
Transactions are not only (actually mainly not) about atomicity. Of course it’s possible to keep data integrity without normalisation, but that means you need to maintain the invariants yourself at application level and a big could result in data inconsistency. Normalisation isn’t there to make integrity possible, it’s there to make (some) non-integrity impossible. Nobody says you have to have only one view of your data though. You can have a normalised view of your data to write, and another denormalised for fast reads (you usually have to, at scale). Something like event sourcing is another way (which is actually pushing invariants to application level, in a structured way)
- sgarland 3y ago
  > that means you need to maintain the invariants yourself at application level Foreign Keys. Of course, now you have a new set of problems, but referential integrity isn't one of them.