Do they have constraints on rows? Are they using views or do they just denormalize and duplicate? Do they use joins at all? Are they even doing more than 1 thing in a transaction?
Normalization is a performance optimization. Denormalization is a development shortcut. Neither is right or wrong but I would be surprised if a 50TB OLTP database wasn’t already highly normalized.
If it isn’t then my next guess is that it could be made smaller or more performant if it was.
We used to be proud of server uptime, back when we gave them names. Today if you have a server up for 900 days you’re going to be accused of malpractice.
Similar for data. We used to be proud of being able to keep “big” data online, but I’m no longer impressed. You’re just spending money. Did you actually solve the business case as efficiently as possible given available resources? Do you need 50TB in the first place?
> Normalizing data can improve write performance but will make reads slower, so if you have a write intensive database then you will see some performance gains.
In OLTP this might be true, based on access pattern. Normalization may improve both read and write performance. It could also make it worse if you take it too far. Domain Key normal form could kill you in unique indices alone.
In OLAP different levels of normalization actually helps both read and write performance. Take a look at the star schema to see how that works. In general dimensions as they relate to fact tables are normalized but dimensions themselves are denormalized.
Normalization is a tool like anything else. You can’t make absolute statements about it. The appropriate application will depend on your use case and may change over time.
That's exactly my point. I specifically said that normalization is independent of optimizations, it might help, it might not. It was specifically in response to your absolute statement about normalization being critical to performance, and I quote:
>Normalization is a performance optimization.
Emphasis taken directly from your quote.
Normalization has very little to do with optimization and it's one of the least cited reasons (if cited at all), for doing it.
On most servers the most plentiful and cheap resource is the CPU (not the storage!), and an adequate level of normalization loads CPU while relieving storage.
Normalization often adds storage 'seeks', and therefore doesn't costs much on some storage (SSD...).
that is a stupid statement. because it's way too generic and it depends on the use case. read heavy data that needs a lot of joins are most often denormalized IF updating a lot is not a problem. sometimes you need to create views that pull in different stuff with different queries which would make them not really performant especially not on postgres which is just super slow when it comes to listing/filtering data.
In the last year my team did a lot of iterating on our DynamoDB schema and eventually just re-discovered normalization.
From what I can tell the benefit of databases like DynamoDB is that you can shard your workload over many hosts mostly transparently. So you get the benefit of more resources than fit in one box. But it’s not magic and you pay the price in other areas, such as hot partitions, and implementing join logic in your application.
Also Postgres is the subject at hand and is relational so normalization is an unavoidable design decision.
Said another way normalization is a fundamental concept to modeling data in general. It might even be the fundamental question. You have to balance your requirements to arrive at an answer.
In our case many of our objects had redundant data (aka denormalized) so updates required multiple calls to the DynamoDB service. By normalizing we saw throughput gains in our application and reduced service calls by taking fewer trips. Additionally we had conflated a couple of our domain-specific concepts in the data model and by splitting what was actually two independent entities that had been modeled as one we reduced the absolute record count.
I describe these optimizations as "making the data smaller" and "normalization".
Denormalization is a performance optimization. By duplicating data you reduce the need for costly joins at the expense of data consistency.
Some old inactive data can be moved into partitions, then detached and moved to a different DB instance, as an archiving step.
It reads like their processed payments (5000 tx/second) are very suitable for daily, monthly, yearly archiving.
For analytics, a summary stab can be kept in place of the detailed (archived) transactions.
For statutory reporting, the slow archives can be accessed on demand.