3 ms·

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 criti

by mulmen 5y ago

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?

deleted 5y ago
[deleted]
Kranar 5y ago
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.
- mulmen 5y ago
  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. [1]: https://en.m.wikipedia.org/wiki/MediaWiki https://en.m.wikipedia.org/wiki/MediaWiki
  - Kranar 5y ago
    >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.
    - mulmen 5y ago
      In data(bases) performance and normalization are intimately related.
  - natmaka 5y ago
    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...).
merb 5y ago
> Normalization is a performance optimization 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.
kabes 5y ago
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)?
- mulmen 5y ago
  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.
  - AlisdairO 5y ago
    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.
    - mulmen 5y ago
      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".
ipaddr 5y ago
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.
- dachryn 5y ago
  both of you can be wrong, but in practice its more like: finding the right level of normalization is a performance optimization.
- funcDropShadow 5y ago
  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.

by mulmen 5y ago

deleted 5y ago

[deleted]

Kranar 5y ago

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.

mulmen 5y ago
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. [1]: https://en.m.wikipedia.org/wiki/MediaWiki https://en.m.wikipedia.org/wiki/MediaWiki
- Kranar 5y ago
  >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.
  - mulmen 5y ago
    In data(bases) performance and normalization are intimately related.
- natmaka 5y ago
  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...).

merb 5y ago

> Normalization is a performance optimization 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.

kabes 5y ago

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)?

mulmen 5y ago
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.
- AlisdairO 5y ago
  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.
  - mulmen 5y ago
    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".

ipaddr 5y ago

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.

dachryn 5y ago
both of you can be wrong, but in practice its more like: finding the right level of normalization is a performance optimization.
funcDropShadow 5y ago
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.