One of the things I always wonder with giant relational database is. How much of the "typical relational stuff" are they actually using?
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?
My only experience with databases of that size is for data analysis so yeah, constraints are relaxed. But even at that point ideas like normalization are critical to extracting performance out of large datasets.
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?
Normalization is pretty independent of optimization strategy and generally that's not presented as one of its advantages. 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.
There are performance benefits to normalization at scale. Take a look at the 2005 schema changes at Wikipedia [1] for a real-world example.
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.
Normalization is a tool like anything else. You can’t make absolute statements about it.
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.
Denormalization adds to the volume of data to read and write, and often reduces the global hit ratio of caches.
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...).
Denormalization is only a performance optimization if it matches exactly to your queries. If your data is denormalized in some way, but later on you discover you want to query it differently, it may be way more expensive to query it then. Even if you know beforehand that you want to access the data in multiple different ways and you denormalize it for all those ways, it might be faster to use normalization because of better memory-locality. So, there are a lot of tradeoffs.
Aren't a lot of nosql database, especially document databases like mongo based around the idea that denormalization is a performance optimization (for read-heavy load)?
Well, no. Normalization benefits DynamoDB too, if you understand the nature of the database. It’s all a spectrum.
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.
Particularly in the dynamo case, you're working outside of a common buffer pool. One of the key benefits of normalization in a typical db is that you can fit more stuff into memory if you normalize - dynamo renders that point largely moot.
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".
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.
As a former Adyen employee, I can confirm that they use the relational features a lot. It's a ledger with millions (probably billions by now) of financial records, and it's heavily optimized for flexible querying. You have raw tables of transactions that are linked to many other tables (accounts, exchange rates, risk, transaction accept/capture/settle records, batches etc.), and you can ideally query the state of any transaction by just joining those tables on the db. What they also have is plenty of aggregate tables materialized by consumers and compactors, which are used a lot for reporting and auditing - for instance, daily transacted amount broken down by account and currency, or monthly revenue reports broken down by marketplace and currency. Additionally, being a financial institution they are also compelled to hold these records for several years, so there aren't many options for reducing the size of the db by aggregating/moving old records.
Comments
One of the things I always wonder with giant relational database is. How much of the "typical relational stuff" are they actually using?
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?
My only experience with databases of that size is for data analysis so yeah, constraints are relaxed. But even at that point ideas like normalization are critical to extracting performance out of large datasets.
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?
Normalization is pretty independent of optimization strategy and generally that's not presented as one of its advantages. 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.
There are performance benefits to normalization at scale. Take a look at the 2005 schema changes at Wikipedia [1] for a real-world example.
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.
[1]: https://en.m.wikipedia.org/wiki/MediaWiki
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:
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.
In data(bases) performance and normalization are intimately related.
Denormalization adds to the volume of data to read and write, and often reduces the global hit ratio of caches.
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...).
You have that backwards.
Denormalization is a performance optimization. By duplicating data you reduce the need for costly joins at the expense of data consistency.
Denormalization is only a performance optimization if it matches exactly to your queries. If your data is denormalized in some way, but later on you discover you want to query it differently, it may be way more expensive to query it then. Even if you know beforehand that you want to access the data in multiple different ways and you denormalize it for all those ways, it might be faster to use normalization because of better memory-locality. So, there are a lot of tradeoffs.
both of you can be wrong, but in practice its more like: finding the right level of normalization is a performance optimization.
Aren't a lot of nosql database, especially document databases like mongo based around the idea that denormalization is a performance optimization (for read-heavy load)?
Well, no. Normalization benefits DynamoDB too, if you understand the nature of the database. It’s all a spectrum.
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.
Particularly in the dynamo case, you're working outside of a common buffer pool. One of the key benefits of normalization in a typical db is that you can fit more stuff into memory if you normalize - dynamo renders that point largely moot.
That's only one benefit of normalization.
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".
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.
As a former Adyen employee, I can confirm that they use the relational features a lot. It's a ledger with millions (probably billions by now) of financial records, and it's heavily optimized for flexible querying. You have raw tables of transactions that are linked to many other tables (accounts, exchange rates, risk, transaction accept/capture/settle records, batches etc.), and you can ideally query the state of any transaction by just joining those tables on the db. What they also have is plenty of aggregate tables materialized by consumers and compactors, which are used a lot for reporting and auditing - for instance, daily transacted amount broken down by account and currency, or monthly revenue reports broken down by marketplace and currency. Additionally, being a financial institution they are also compelled to hold these records for several years, so there aren't many options for reducing the size of the db by aggregating/moving old records.
One question to ask is if they truly need all the 50 TB data in one database instance at once.
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.