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.

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.

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.

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)

> 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.