4 ms·

The normalization analogy is genuinely clever as a teaching tool, but it quietly papers over the fact that normalization is a logical design concept while colum

The normalization analogy is genuinely clever as a teaching tool, but it quietly papers over the fact that normalization is a logical design concept while columnar storage is a physical one - treating them as the same thing can mislead more than it clarifies, I think

hilariously 6mo ago
Fair, but one of the big benefits of normalization was the benefit on storage and memory back in the day which was tiny comparatively. There's always a reason for a dev to ship something shitty but when you show you can use 80% less storage for the same operation you can make the accountants your lever.
- jklowden 6mo ago
  Nonsense. See Codd’s first paper. 1NF removes repeating groups, putting for example data for each month in its own row, not an array of 12 months in 1 row. Storage efficiency was never the point. IMS had that locked down. Succinctness of expression and accuracy of results was the point. And is: normalization prevents anomalous results.
  - sgarland 6mo ago
    I think parent was saying it’s a benefit, not the original purpose. If I store a FK to a table containing my company’s corporate address, that is a tremendous savings in storage (and memory pressure), and it also eliminates update anomalies.
    - cmrdporcupine 6mo ago
      And other people are saying it may not always have that effect. Normalization ultimately boils down to breaking your data down into the most elemental and simplest "facts" about them, and its greatest value is how it allows and encourages more flexible and disparate ways of looking at the same information.
  - HelloNurse 6mo ago
    Normalizing repeating groups doesn't offer significant savings when they are completely populated (e.g. each entity has the full 12 monthly values per year), but other types of normalization do. For example dependent data are actually redundant.
  - anonymars 6mo ago
    To expand on this point: unexplained "distinct" is often a code smell
- bazoom42 6mo ago
  The purpose of normalization is not to save storage. In fact it might often require more storage, since it involves introducing a foreign-key column. It really depends on the data in question whether it saves storage or require more.
  - hilariously 6mo ago
    Fair, I said one of the big benefits, not the purpose - in some cases it can require more storage (but that storage is often more amenable to compression) -but generally deduplicating your data doesn't increase your storage needs.
  - zaphirplane 6mo ago
    That forign key column is saving duplicating multiple columns But I don’t think that’s the top 5 reasons of normalization
jerf 6mo ago
I've always preferred to think of normalization as more about "removing redundancy" than in the frame it is normally presented. Or, to put it another way, rather than "normalizing" which has as a benefit "removing redundancy", raise the removing of redundancy up to the primary goal which has as a side benefit "normalization". A nice thing about that point of view is that it fits with your point; redundancy is redundancy whether you look at it with a column-based view or a row-based view.
goerch 6mo ago
Theoretically I would agree, but practically I still wonder why we need different database engines for row and columnar storage if supporting different types of indices is trivial(TM) for Postgres?
- gavinray 6mo ago
  In theory, you don't. In practice, it's because the major SQL DBMS were architected around row-oriented storage and the technical effort to implement hybrid storage is large. There are columnar storage engine extensions for many of the popular databases, though.
  - goerch 6mo ago
    Interesting: I transferred the idea of a matrix being stored row or column wise to the database world and assumed this was a more physical than theoretical feature (not a native speaker here)? Looking forward to check out `pg_duckdb`, yes.
  - gfody 6mo ago
    there are hybrid engines too, so-called HTAP (as opposed to OLAP or OLTP) notable efforts are: SingleStore (commercial) and OceanBase (foss)
- jiggawatts 6mo ago
  Microsoft SQL Server can use both row and column store for tables, in various combinations such as row store for the table with a columnar index, or vice versa.
- atombender 6mo ago
  There are definitely hybrid OLTP-OLAP databases, or HTAP (Hybrid Transaction and Analytical Processing). Microsoft and, I believe, Oracle both have HTAP tech. The most novel design, I think, is CedarDB (developed by Thomas Neumann's database group at TUM), which adaptively stores both row and column versions of the data [1], where some data is permanently compressed to columnar format and hot rows are converted "just in time" to columnar data as needed. [1] https://cedardb.com/blog/colibri/ https://cedardb.com/blog/colibri/
_doctor_love 6mo ago
Oh man, thank you for saying this. The difference between logical and physical goes over so many people's heads. It's a little unnerving at times how much people resist it. Definitely agree with what you said - if we treat them as the same thing that's going to mislead some folks.
- LkpPo 6mo ago
  And it's really not the same thing because ids are not position. With this type of structure a field without value will be a missing row in a table. Moreover, from the moment each column is isolated in its own table with a second key column, it becomes essential not to store NULL values because the amount of data doubles for each value, since a primary key is required for each column. It's a 6NF model. See <https://en.wikipedia.org/wiki/Sixth_normal_form https://en.wikipedia.org/wiki/Sixth_normal_form>
zaptheimpaler 6mo ago
Yeah I feel like papering over the physical aspect actually misses the main motivation for columnar storage in the first place, which is to more efficiently store some types of data and perform OLAP queries on it.

hilariously 6mo ago

Fair, but one of the big benefits of normalization was the benefit on storage and memory back in the day which was tiny comparatively. There's always a reason for a dev to ship something shitty but when you show you can use 80% less storage for the same operation you can make the accountants your lever.

jklowden 6mo ago
Nonsense. See Codd’s first paper. 1NF removes repeating groups, putting for example data for each month in its own row, not an array of 12 months in 1 row. Storage efficiency was never the point. IMS had that locked down. Succinctness of expression and accuracy of results was the point. And is: normalization prevents anomalous results.
- sgarland 6mo ago
  I think parent was saying it’s a benefit, not the original purpose. If I store a FK to a table containing my company’s corporate address, that is a tremendous savings in storage (and memory pressure), and it also eliminates update anomalies.
  - cmrdporcupine 6mo ago
    And other people are saying it may not always have that effect. Normalization ultimately boils down to breaking your data down into the most elemental and simplest "facts" about them, and its greatest value is how it allows and encourages more flexible and disparate ways of looking at the same information.
- HelloNurse 6mo ago
  Normalizing repeating groups doesn't offer significant savings when they are completely populated (e.g. each entity has the full 12 monthly values per year), but other types of normalization do. For example dependent data are actually redundant.
- anonymars 6mo ago
  To expand on this point: unexplained "distinct" is often a code smell
bazoom42 6mo ago
The purpose of normalization is not to save storage. In fact it might often require more storage, since it involves introducing a foreign-key column. It really depends on the data in question whether it saves storage or require more.
- hilariously 6mo ago
  Fair, I said one of the big benefits, not the purpose - in some cases it can require more storage (but that storage is often more amenable to compression) -but generally deduplicating your data doesn't increase your storage needs.
- zaphirplane 6mo ago
  That forign key column is saving duplicating multiple columns But I don’t think that’s the top 5 reasons of normalization

jerf 6mo ago

I've always preferred to think of normalization as more about "removing redundancy" than in the frame it is normally presented. Or, to put it another way, rather than "normalizing" which has as a benefit "removing redundancy", raise the removing of redundancy up to the primary goal which has as a side benefit "normalization". A nice thing about that point of view is that it fits with your point; redundancy is redundancy whether you look at it with a column-based view or a row-based view.

goerch 6mo ago

Theoretically I would agree, but practically I still wonder why we need different database engines for row and columnar storage if supporting different types of indices is trivial(TM) for Postgres?

gavinray 6mo ago
In theory, you don't. In practice, it's because the major SQL DBMS were architected around row-oriented storage and the technical effort to implement hybrid storage is large. There are columnar storage engine extensions for many of the popular databases, though.
- goerch 6mo ago
  Interesting: I transferred the idea of a matrix being stored row or column wise to the database world and assumed this was a more physical than theoretical feature (not a native speaker here)? Looking forward to check out `pg_duckdb`, yes.
- gfody 6mo ago
  there are hybrid engines too, so-called HTAP (as opposed to OLAP or OLTP) notable efforts are: SingleStore (commercial) and OceanBase (foss)
jiggawatts 6mo ago
Microsoft SQL Server can use both row and column store for tables, in various combinations such as row store for the table with a columnar index, or vice versa.
atombender 6mo ago
There are definitely hybrid OLTP-OLAP databases, or HTAP (Hybrid Transaction and Analytical Processing). Microsoft and, I believe, Oracle both have HTAP tech. The most novel design, I think, is CedarDB (developed by Thomas Neumann's database group at TUM), which adaptively stores both row and column versions of the data [1], where some data is permanently compressed to columnar format and hot rows are converted "just in time" to columnar data as needed. [1] https://cedardb.com/blog/colibri/ https://cedardb.com/blog/colibri/

_doctor_love 6mo ago

Oh man, thank you for saying this. The difference between logical and physical goes over so many people's heads. It's a little unnerving at times how much people resist it. Definitely agree with what you said - if we treat them as the same thing that's going to mislead some folks.

LkpPo 6mo ago
And it's really not the same thing because ids are not position. With this type of structure a field without value will be a missing row in a table. Moreover, from the moment each column is isolated in its own table with a second key column, it becomes essential not to store NULL values because the amount of data doubles for each value, since a primary key is required for each column. It's a 6NF model. See <https://en.wikipedia.org/wiki/Sixth_normal_form https://en.wikipedia.org/wiki/Sixth_normal_form>

zaptheimpaler 6mo ago

Yeah I feel like papering over the physical aspect actually misses the main motivation for columnar storage in the first place, which is to more efficiently store some types of data and perform OLAP queries on it.