4 ms·

"Well implemented true RDBMSs..." exist only in the weird fantasy world of relational purists. In that perfectly normalized world you just wave your magic wand

"Well implemented true RDBMSs..." exist only in the weird fantasy world of relational purists. In that perfectly normalized world you just wave your magic wand and all performance problems disappear.

kpmah 10y ago
I think most are pragmatic and admit you may have to do denormalise for performance (e.g. I've read this in C.J. Date's books). I interpret their point as the need for denormalisation is not a flaw with the relational model, but a flaw in its implementation. The relational model does not specify an on-disk format.
- calpaterson 10y ago
  So rare to actually have to denormalise for performance today though. Query planners are miles ahead of where they were in the 80s and 90s. SSDs delivered an order of magnitude boost to performance. Rapidly expanding memories hugely increase the how much working set you can hold in memory. No one seems to talk about this but the "Moore's law has ended" meme does not apply to most database scenarios. Database servers are normally not CPU bound: they are bound by available memory and disk bandwidth and these are both still increasing.
  - anonymous_fun 10y ago
    Antidotally, I started at a co 6 years ago where 100's of database servers were all spinning disks. It was common to have to trace down bad queries causing performance issues. Once the servers were upgraded with more ram and SSD's, the need to micromanage performance issues disappeared.
    - sgeneris 10y ago
      The sheer fact that much of the comments are about physical storage is an excellent validation of the article's claim that data professionals don't know and understand data fundamentals and the RDM.
- Nokinside 10y ago
  I think today, most often than not, the fault is between the keyboard and the chair. There is clear lack of domain knowledge. RDBMS's like PostgreSQL, Oracle, DB2, etc. can do much more than people expect them to do. Relational databases are relatively easy for anyone to start working with, but optimizing and tuning queries requires fair amount of domain knowledge. You'd be surprised to learn how little denormalization big financial company databasees have. They have people who are experts that do only databases. For I/O intensive work you may need transaction rows where data written together is stored together, but it's not denormalization, it's optimizing for I/O bottleneck. Query planners can do most of the work, if the person crafting queries has understanding of how they work. Carefully grafted queries and tuning memory layout for tables correctly is usually the right way to fix performance problems.
  - sgeneris 10y ago
    Yes, I would be surprised. Because, unlike most products which only require training to work with, true RDBMSs require knowledge of data and relational fundamentals and such is scarce today, because education has been replaced by sheer training. How many database "experts" do you know today who have any background in logic and math, particularly those designing DBMSs? I've watched them for almost 50 years and they've become worse and worse, not better.
eecc 10y ago
I can imagine an optimizer that having analyzed and simplified the query plan notices that certain attributes bubble up all the wait to the final projection. Coupled with some statistics, it's entirely possible for a DBMS to maintain a precomputed partial result cache.
jasode 10y ago
Responding to several comments. (The _underscores_ in the following quotes are mine for emphasis.) >kpmah: I think most are pragmatic and admit you _may_ have to do denormalise for performance >calpaterson: So _rare_ to actually have to denormalise for performance today though. The article's author didn't describe it as "may"/"rare" which leaves some wiggle room for the cases of denormalization required in real-world implementations. Instead he used absolute qualifiers such as "never", "no reason", "any": - the "additional development costs" that Bolenok refers to -- but they would _never_ be justified: - "consistency-performance tradeoff [...], there is _no_ reason to expect _any_." The author does write his advice in abstract terms instead of discussing concrete "case studies" so we are left to speculate what mental model of the database world he holds in his mind when he's rigid with strict rules of normalization and relational purity. Based on the topics in his papers[1], I'm guessing his world consists of a single OLTP database. E.g, you develop a non-cloud restaurant reservation & POS system with a single-instance database. Yes, you don't need any denormalization hacks in that scenario. But for other problems such as distributed databases, you can't do joins across 2 geographically separated data centers. (Well, you theoretically could do it but the slow nested-loop performance across the WAN would make it unusable for a real-world application.) Some duplication via data denormalization is required and no "sufficiently smart db engine"[2] can automatically optimize for it. An application architect has to manually make that design decision and live with the deliberate tradeoffs. (E.g., batch jobs now have to be run to periodically keep databases at different datacenters in sync.) There are many real-world scenarios that require denormalization which have nothing to do with a junior programmer's lack of SQL knowledge to join 20 tables to populate an "edit customer" data entry screen. [1]http://www.dbdebunk.com/p/papers_3.html http://www.dbdebunk.com/p/papers_3.html [2]riff on the theoretical "Sufficiently Smart Compiler" to solve all performance optimizations so there's never a need to write performance-specific language syntax or switch to a "faster" language... because as we all know... "languages" are not fast or slow -- it's the "implementations" that are fast or slow. That Ruby is not as fast as C/C++ is an implementation detail (SSC) and not the issue of the language syntax.
- CaptainZapp 10y ago
  But for other problems such as distributed databases, you can't do joins across 2 geographically separated data centers That's where replication comes in as your (arguably) most powerful weapon. I work with databases for, literally, decades and have never seen a successful implementation of two-phase-commits or joins on geographically distinct entities.
sgeneris 10y ago
They are a fantasy only because users do not grasp the advantages of the RDM and confuse them with SQL, thus robbing vendors from any incentive to produce true RDBMSs. If users are so accepting of denormalization as a solution to performance and are unaware of all its costs, why should vendors bother to come up with true RDBMSs that obviate the need to denormalize? As long as users do not understand the RDM and its benefits true RDBMSs will remain a fantasy. Ignorance has never been conducive to progress.

"Well implemented true RDBMSs..." exist only in the weird fantasy world of relational purists. In that perfectly normalized world you just wave your magic wand and all performance problems disappear.

kpmah 10y ago

I think most are pragmatic and admit you may have to do denormalise for performance (e.g. I've read this in C.J. Date's books). I interpret their point as the need for denormalisation is not a flaw with the relational model, but a flaw in its implementation. The relational model does not specify an on-disk format.

calpaterson 10y ago
So rare to actually have to denormalise for performance today though. Query planners are miles ahead of where they were in the 80s and 90s. SSDs delivered an order of magnitude boost to performance. Rapidly expanding memories hugely increase the how much working set you can hold in memory. No one seems to talk about this but the "Moore's law has ended" meme does not apply to most database scenarios. Database servers are normally not CPU bound: they are bound by available memory and disk bandwidth and these are both still increasing.
- anonymous_fun 10y ago
  Antidotally, I started at a co 6 years ago where 100's of database servers were all spinning disks. It was common to have to trace down bad queries causing performance issues. Once the servers were upgraded with more ram and SSD's, the need to micromanage performance issues disappeared.
  - sgeneris 10y ago
    The sheer fact that much of the comments are about physical storage is an excellent validation of the article's claim that data professionals don't know and understand data fundamentals and the RDM.
Nokinside 10y ago
I think today, most often than not, the fault is between the keyboard and the chair. There is clear lack of domain knowledge. RDBMS's like PostgreSQL, Oracle, DB2, etc. can do much more than people expect them to do. Relational databases are relatively easy for anyone to start working with, but optimizing and tuning queries requires fair amount of domain knowledge. You'd be surprised to learn how little denormalization big financial company databasees have. They have people who are experts that do only databases. For I/O intensive work you may need transaction rows where data written together is stored together, but it's not denormalization, it's optimizing for I/O bottleneck. Query planners can do most of the work, if the person crafting queries has understanding of how they work. Carefully grafted queries and tuning memory layout for tables correctly is usually the right way to fix performance problems.
- sgeneris 10y ago
  Yes, I would be surprised. Because, unlike most products which only require training to work with, true RDBMSs require knowledge of data and relational fundamentals and such is scarce today, because education has been replaced by sheer training. How many database "experts" do you know today who have any background in logic and math, particularly those designing DBMSs? I've watched them for almost 50 years and they've become worse and worse, not better.

eecc 10y ago

I can imagine an optimizer that having analyzed and simplified the query plan notices that certain attributes bubble up all the wait to the final projection. Coupled with some statistics, it's entirely possible for a DBMS to maintain a precomputed partial result cache.

jasode 10y ago

Responding to several comments. (The _underscores_ in the following quotes are mine for emphasis.) >kpmah: I think most are pragmatic and admit you _may_ have to do denormalise for performance >calpaterson: So _rare_ to actually have to denormalise for performance today though. The article's author didn't describe it as "may"/"rare" which leaves some wiggle room for the cases of denormalization required in real-world implementations. Instead he used absolute qualifiers such as "never", "no reason", "any": - the "additional development costs" that Bolenok refers to -- but they would _never_ be justified: - "consistency-performance tradeoff [...], there is _no_ reason to expect _any_." The author does write his advice in abstract terms instead of discussing concrete "case studies" so we are left to speculate what mental model of the database world he holds in his mind when he's rigid with strict rules of normalization and relational purity. Based on the topics in his papers[1], I'm guessing his world consists of a single OLTP database. E.g, you develop a non-cloud restaurant reservation & POS system with a single-instance database. Yes, you don't need any denormalization hacks in that scenario. But for other problems such as distributed databases, you can't do joins across 2 geographically separated data centers. (Well, you theoretically could do it but the slow nested-loop performance across the WAN would make it unusable for a real-world application.) Some duplication via data denormalization is required and no "sufficiently smart db engine"[2] can automatically optimize for it. An application architect has to manually make that design decision and live with the deliberate tradeoffs. (E.g., batch jobs now have to be run to periodically keep databases at different datacenters in sync.) There are many real-world scenarios that require denormalization which have nothing to do with a junior programmer's lack of SQL knowledge to join 20 tables to populate an "edit customer" data entry screen. [1]http://www.dbdebunk.com/p/papers_3.html http://www.dbdebunk.com/p/papers_3.html [2]riff on the theoretical "Sufficiently Smart Compiler" to solve all performance optimizations so there's never a need to write performance-specific language syntax or switch to a "faster" language... because as we all know... "languages" are not fast or slow -- it's the "implementations" that are fast or slow. That Ruby is not as fast as C/C++ is an implementation detail (SSC) and not the issue of the language syntax.

CaptainZapp 10y ago
But for other problems such as distributed databases, you can't do joins across 2 geographically separated data centers That's where replication comes in as your (arguably) most powerful weapon. I work with databases for, literally, decades and have never seen a successful implementation of two-phase-commits or joins on geographically distinct entities.

sgeneris 10y ago

They are a fantasy only because users do not grasp the advantages of the RDM and confuse them with SQL, thus robbing vendors from any incentive to produce true RDBMSs. If users are so accepting of denormalization as a solution to performance and are unaware of all its costs, why should vendors bother to come up with true RDBMSs that obviate the need to denormalize? As long as users do not understand the RDM and its benefits true RDBMSs will remain a fantasy. Ignorance has never been conducive to progress.