21 ms·

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - Design your application's hot path to never use joins. Sto

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes. On this note, I have mixed feelings about Amazon's DynamoDB, but one things about it is to use it properly you need to plan your use first, and schema second. I think there's something you can take from this even with a RDBMS. In fact, I'd go as far to say as joins are unnecessary for nonanalytical purposes these days. Storage is so mind booglingly cheap and the major DBs have ACID properties. Just denormalize, forreal. - Use something more akin to UUIDs to prevent hot partitions. They're not a silver bullet and have their own downsides, but you'll already be used to the consistently "OK" performance that can be horizontally scaled rather than the great performance of say integers that will fall apart eventually. /hottakes my sun level take would be also to just index all columns. but that'll have to wait for another day.

Scarbutt 3y ago
Normalization is not only about data storage but most importantly, data integrity.
- endisneigh 3y ago
  Yes, but I assert that it's possible to use transactions to update everything consistently. Serializable transactions weren't really common when MySQL/Postgres first came out, but now that they're common in new DBs + ACID, I think it's not possible to do with reasonable difficulty. If you agree with this, than its easy to prove that denormalized tables performance increase is well worth the annoyance of updating everything to transactionally update the dependencies. I won't say that it's trivial to update all of your business logic to do this, but I think it's definitely worth it for a new project at least.
  - Guvante 3y ago
    You always need to compare write vs read performance. Turning a single table update into a 10 table one could tip your lock contention to the point where you are write bound or worse start hitting retries. Certainly it makes sense to move rarely updated fields to where they are used makes sense. Similarly "build your table against your queries not your ideal data model" is always sage advice.
  - Bognar 3y ago
    Denormalized transactions are not trivial unless you are using serializable isolation level which will kill performance. If you don't use serializable isolation level, then you risk either running into deadlocks (which will kill performance) or inconsistency. Decent SQL databases offer materialized views, which probably give you what you want without all the headache of maintaining denormalized tables yourself.
    - endisneigh 3y ago
      all fair points, but to be fair I don't necessary think this makes the most sense for an existing project for the reasons you state. I do think for a new project would best be able to design around the access patterns in a way that eliminate most of the downsides.
  - williamdclt 3y ago
    Transactions are not only (actually mainly not) about atomicity. Of course it’s possible to keep data integrity without normalisation, but that means you need to maintain the invariants yourself at application level and a big could result in data inconsistency. Normalisation isn’t there to make integrity possible, it’s there to make (some) non-integrity impossible. Nobody says you have to have only one view of your data though. You can have a normalised view of your data to write, and another denormalised for fast reads (you usually have to, at scale). Something like event sourcing is another way (which is actually pushing invariants to application level, in a structured way)
    - sgarland 3y ago
iamwil 3y ago
If you don't use joins, how do you associate records from two different tables when displaying the UI? Do you just join in the application? Or something else?
- endisneigh 3y ago
  this has opinionated answers. if you ask Amazon, they might suggest that you design around a single table (https://aws.amazon.com/blogs/compute/creating-a-single-table-design-with-amazon-dynamodb/ https://aws.amazon.com/blogs/compute/creating-a-single-table...). in my opinion it's easier to use join tables. which are what are sometimes temporarily created when you do a join anyways. in this case, you permanently create table1, table2, and table1_join_table2, and keep all three in sync transactionally. when you need a join you just select on table1_join_table2. you might think this is a waste of space, but I'd argue storage is too cheap for you to be thinking about that. that being said, you really have to design around your access patterns, don't design your application around your schema. most people do the latter because it seems more natural. what this might mean in practice is that you do mockups of all of the expected pages and what data is necessary on each one. then you design a schema that results in you never having to do joins on the majority, if not all, of them.
  - sainez 3y ago
    > what this might mean in practice is that you do mockups of all of the expected pages and what data is necessary on each one. then you design a schema that results in you never having to do joins on the majority, if not all, of them. Great suggestion! I had a role where I helped a small team develop a full stack, data-heavy application. I felt pretty good about the individual layers but I felt we could have done a better job at achieving cohesion in the big picture. Do you have any resources where people think about these sorts of things deeply?
    - walterbell 3y ago
      2001, "Denormalization effects on performance of RDBMS", by G. L. Sanders and Seungkyoon Shin, https://www.semanticscholar.org/paper/Denormalization-effects-on-performance-of-RDBMS-Sanders-Shin/7643e5a175693ca07eff70e933214a67f216186f https://www.semanticscholar.org/paper/Denormalization-effect... > We have suggested using denormalization as an intermediate step between logical and physical modeling, to be used as an analytic procedure for the design of the applications requirements criteria ... The guidelines and methodology presented are sufficiently general, and they can be applicable to most databases ... denormalization can enhance query performance when it is deployed with a complete understanding of application requirements. PDF: https://web.archive.org/web/20171201030308/https://pdfs.semanticscholar.org/2c79/069c01ba8d598f32e61fe367ef6d261a0cb4.pdf https://web.archive.org/web/20171201030308/https://pdfs.sema...
skybrian 3y ago
I’m wondering if indexes and materialized views can be used to do basically the same thing? That is, assuming they contain all the columns you want.
- latchkey 3y ago
  The issue is writes, not reads.
- giantrobot 3y ago
  There's always money in the banana sta...materialized views. Materialized views will get you quite a ways on read heavy workloads.
  - Izkata 3y ago
    As long as you're okay with the reads being a little out of date after writes occur.
veave 3y ago
>Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. Anybody has documentation about this with examples?
- joshstrange 3y ago
  See "Single Table Design" which I talked about in this comment above: https://news.ycombinator.com/item?id=37093357 https://news.ycombinator.com/item?id=37093357
  - deely3 3y ago
    And if you don't want to spend money, you can get basic idea from this article: https://www.alexdebrie.com/posts/dynamodb-single-table/ https://www.alexdebrie.com/posts/dynamodb-single-table/
- newlisp 3y ago
  Duplicate data to avoid joins, use serializable transactions to update all the duplicated data.
latchkey 3y ago
> Design your application's hot path to never use joins. Grab (uber of asia) did this religiously and it created a ton of friction within the company due to the way the teams were laid out. It always required one team to add some sort of API that another team could take advantage of. Since the first team was so busy always implementing their own features, it created roadblocks with other teams and everyone started pointing fingers at each other to the point that nothing ever got done on time. Law of unintended consequences
- endisneigh 3y ago
  yes, this is a fair point. there's no free lunch after all. without knowing more about what happened with Grab I'd say you could mitigate some of that with good management and access patterns, though.
  - latchkey 3y ago
    All in all though, I don't think that 'never use joins' is a good solution either since it does create more developer work almost every way you slice it. I think the op's solution of looking more closely at the hot paths and solving for those is a far better solution than re-architecting the application in ways that could, or can, create unintended consequences. People don't consider that enough, at all. Don't forget that hot path resolution is the antithesis of 'premature optimization'. > you could mitigate some of that with good management and access patterns the CTO fired me for making those sorts of suggestions about better management, and then got fired himself a couple months later... ¯\_(ツ)_/¯... even with the macro events, their stock is down 72% since it opened, which doesn't surprise me in the least bit having been on the inside...
    - jodrellblank 3y ago
      Grab was the company Steve Yegge left Google to go to. He quit during COVID when he could no longer travel to Asia, and his retrospective is glowing, including of the CTO Mark Porter(I think?): "frankly, the “Grab Way” of collaboration teaches you life skills, such as psychological safety and inclusiveness, which work everywhere else as well. [...] We US Grabbers made many mistakes on the journey towards becoming better Grabbers. Mark Porter led the charge on figuring it out, and we’ve learned a ton from him as he has evolved as a person before our eyes [...] I want to thank Mark especially for helping me grow as a leader and as a person. It takes humility to become a better person, and Grab is a humbling place. The core company value of humility comes straight from the CEO and cofounder Anthony Tan, who is one of the most humble, down-to-earth people you’ll ever meet.". Is that, uhh, rose-tinted glasses? 2020: https://steve-yegge.medium.com/saying-goodbye-to-the-best-gig-i-ever-had-a33736833c1e https://steve-yegge.medium.com/saying-goodbye-to-the-best-gi... Previously, 2018: https://steve-yegge.medium.com/why-i-left-google-to-join-grab-86dfffc0be84 https://steve-yegge.medium.com/why-i-left-google-to-join-gra... [I don't have any knowledge of Grab or the people involved other than reading these two blog posts, or of the parent commenter].
tibbetts 3y ago
Premature denomalization is expensive complexity. Denormalization is a great tool, maybe an under-used tool. But you should wait until there are hot paths before using it.
- endisneigh 3y ago
  I agree. To be clear I'm not suggesting anyone start denormalizing everything. I'm saying if you're fortunate enough to be on a green project, you should design the schema around the access patterns which will surely be "denomarlized." as opposed to designing a normalized schema and designing your access patterns around those.
taylodl 3y ago
My hot take: always use a materialized view or a stored procedure. Hide the actual, physical tables from the Application's account! The application doesn't need to know how the data is physically stored in the database. They specify the logical view they need of the data. The DBAs create the materialized view/stored procedure that's needed to implement that logical view. Since the application is never directly accessing the underlying physical data, it can be changed to make the retrieval more efficient without affecting any of the database's users. You're also getting the experts to create the required data access for you in the fastest, most efficient way possible. We've been doing this for years now and it works great. It's alleviated so many headaches we used to have.
- alfor 3y ago
  But for the saves the structure is visible?
  - taylodl 3y ago
    You can update underlying data via a materialized view.
    - NortySpock 3y ago
      Heck, at least with SQL Server 2017, I've been able to write updates to a thin view, and so long as the columns referenced were not ambiguous, the database would handle the underlying tables and locks required.
- walterbell 3y ago
  Interface contracts and indirection FTW. 2011, "Materialized Views" by Rada Chirkova and Jun Yang, https://dsf.berkeley.edu/cs286/papers/mv-fntdb2012.pdf https://dsf.berkeley.edu/cs286/papers/mv-fntdb2012.pdf > We cover three fundamental problems: (1) maintaining materialized views efficiently when the base tables change, (2) using materialized views effectively to improve performance and availability, and (3) selecting which views to materialize. We also point out their connections to a few other areas in database research, illustrate the benefit of cross-pollination of ideas with these areas, and identify several directions for research on materialized views.
wizofaus 3y ago
Can't say I've ever come across a scenario where a join itself was the performance bottleneck. If there's any single principle I have observed is "don't let a table get too big". More often than not it's historical-record type tables that are the issue - but the amount of data you need for day-to-day operations is usually a tiny fraction of what's actually in the table, and you're bound to start finding operations on massive tables get slow no matter what indexes you have (and even the act of adding more indexes becomes problematic. And just indexing all columns isn't enough for traditional RMDBSes at least - you have to index the right combinations of columns for them to be used. Might be different for DynamoDb).
- 8note 3y ago
  Dynamo is quick for that, so long as you are picking good partition keys. Instead, it'll throw you hot key throttling if you start querying one partition too much
- pixl97 3y ago
  In enterprise clients you commonly run into issues where the company thinks you have to save all data forever. Very often this runs in a pattern where the application is lightly used a first then uptake increases over time. Then you run into the slowness issue. They typically expand DB sizing, bit eventually run into the problem where archiving is needed. This can be a huge problem when it's an after thought instead of a primary design. All kinds of fun when you have key relationships between different tablea.
  - wizofaus 3y ago
    It's more that once it gets to a certain size (say, 100s of 1000s of rows), doing anything with the table is painfully slow, often requiring you to take your application offline for considerable periods. Even deleting rows can take 10s of minutes at a time, and it can certainly take a very long time to work out what indexes need to be added and whether they're actually helping. Yes, sometimes the pressure comes from management etc., but more often than not it would be premature optimisation to add the archiving, so it's a matter of finding a balance and "predicting" at what point the archiving needs to happen. Table partitioning can help too but only so much.
i_like_apis 3y ago
Yes I like the zero joins on hot paths approach. It can be hard to sell people on it. It’s a great decision for scaling though.
wtetzner 3y ago
I'd say that probably depends on what your hot path is. If it's write-heavy, then you'll probably end up with performance issues when you need to write the same data to multiple tables in a single transaction. And if all of those columns are indexed, it'll be even worse.
macNchz 3y ago
Over the years I think I've encountered more pain from applications where the devs leaned on denormalization than from those that developed issues with join performance on large tables. You can mash those big joins into a materialized view or ETL them into a column store or whatever you need to fix performance later on, but once someone has copied the `subtotal_cents` column onto the Order, Invoice, Payment, NotificationEmail, and UserProfileRecentOrders models, and they're referenced and/or updated in 296 different places...it's a long road back to sanity.
deleted 3y ago
[deleted]
feoren 3y ago
There are "tall" applications and "wide" applications. Almost all advice you ever read about database design and optimization is for "tall" applications. Basically, it means that your application is only doing one single thing, and everything else is in service of that. Most of the big tech companies you can think of are tall. They have only a handful of really critical, driving concepts in their data model. Facebook really only has people, posts, and ads. Netflix really only has accounts and shows. Amazon (the product) really only has sellers, buyers, and products, with maybe a couple more behind the scene for logistics. The reason for this is because tall applications are easy. Much, much easier than wide applications, which are often called "enterprise". Enterprise software is bad because it's hard. This is where the most unexplored territory is. This is where untold riches lie. The existing players in this space are abysmally bad at it (Oracle, etc.). You will be too, if you enter it with a tall mindset. Advice like "never user joins" and "design around a single table" makes a lot of sense for tall applications. It's awful, terrible, very bad, no-good advice for wide applications. You see this occasionally when these very tall companies attempt to do literally anything other than their core competency: they fail miserably, because they're staffed with people who hold sacrosanct this kind of advice that does not translate to the vast space of "wide" applications. Just realize that: your advice is for companies doing easy things who are already successful and have run out of low-hanging fruit. Even tall applications that aren't yet victims of their own success do not need to think about butchering their data model in service of performance. Only those who are already vastly successful and are trying to squeeze out the last juices of performance. But those are the people who least need advice. This kind of tall-centered advice, justified with "FAANG is doing it so you should too" and "but what about when you have a billion users?" is poisoning the minds of people who set off to do something more interesting than serve ads to billions of people.
- xyzzy123 3y ago
  Thanks I think this is a really interesting way to look at things. What is the market for "wide" applications though? It seems like any particular business can only really support one or two of them, for some that will be SAP and for others it might be Salesforce (if they don't need much ERP), or (as you mentioned) some giant semi homebrewed Oracle thing. Usually there is a legacy system which is failing but still runs the business, and a "next gen" system which is not ready yet (and might never be, because it only supports a small number of use cases from the old software and even with an army of BAs it's difficult to spec out all the things the old software is actually doing with any accuracy). Or am I not quite getting the idea?
crazygringo 3y ago
That is a hot take... ;) But joins should never impact performance in a large way if they're on the same server and properly indexed. "It's truly amazing how much faster everything is when you eliminate joins" is just not true if you're using joins correctly. Sadly, many developers simply never bother to learn. On the other hand, having to write a piece of data to 20 different spots instead of 1 is going to be dramatically slower performance-wise, not to mention make your queries tremendously more complex and bug-prone, when you remember to update a value in 18 spots but forget about 2 of them. You mention cheap storage as an advantage for denormalizing, but storage is the least problem here. It's a vastly larger surface area for bugs, and terrible write performance (that can easily chew up read performance).
- jiggawatts 3y ago
  Storage might be cheap, but memory and bandwidth aren’t. Memory is the new disk, and disk is the new tape. You want everything to remain resident in memory, and spool backups and transaction logs to disk. If you’re joining from disk, you’ve probably done something wrong. E.g.: compression is often a net win because while it uses more CPU, it allows more data to fit into memory. And if it doesn’t fit, it reduces the disk I/O required. This is why I look upon JSON-based document databases in horror. They’re bloating the data out many times over by denormalizing and then expand that into a verbose and repetitive text format. This is why we have insanity like placeholders for text on web apps now — they’re struggling to retrieve a mere kilobyte of data!
- endisneigh 3y ago
  I agree but I’m talking in the context where you can’t vertically scale anymore. I also don’t think it’s worth the trouble “never using joins” for an existing project. Denormalize as necessary. But for a green one I honestly think since our access patterns can be understood as you continue you can completely get rid of joins. Again, assuming your new project can’t fit on a single machine. If it can you’re best just following the “traditional” advice, or better yet keep everything in memory.
  - fastball 3y ago
paulddraper 3y ago
> Design your application's hot path to never use joins I had a Chief Architect who decreed this. So engineers wound up doing joins in application code, with far worse performance, filtering, memory caching, etc.
zeckalpha 3y ago
Materialized views provide a way to have joins that are accessed like tables!
theptip 3y ago
Honestly I couldn’t disagree more. I built a startup and paid little attention to perf for years 1-5, and finally in year 6 we started to get bitten by some perf issues in specific tables, and spent a few engineer-months optimizing. In terms of tech debt it would have been way more expensive to make everything perform well from the start, we would have moved much slower and probably failed during a few crunch points. Instead we paid probably a few $k/mo more than we really needed to on machines, and in return saved man-months of effort at a time when we couldn’t hire enough engineers and the opportunity cost for feature work was huge. (Keep in mind that making everything perform well would have required us to do 10-20x as much work, because we could not know ahead of time where the hot spots would be. Some were surprising.) Joins may be evil at scale, but most startups don’t have scale problems, at least not at first. Denormalizing can be a good optimization but you pay a velocity cost in keeping all the copies in sync across changes. Someone will write the bug that misses a denormalized non-canonical field and serves up stale data to a user. It’s usually cheaper (in total cost, ie CapEx+OpEx) to write the join and optimize later with a read-aside cache or whatever, rather than contorting your schema.
- re-thc 3y ago
  > I built a startup and paid little attention to perf for years 1-5, and finally in year 6 we started to get bitten by some perf issues in specific tables, and spent a few engineer-months optimizing. This screams of if I don’t see it it the problem doesn’t exist view of the world. How do you know it’s not a problem? Perhaps customers would have signed up if it as faster? The problem is also treating it in terms of business value and/or cost. A lot of things are “free” and yet it’s ignored. For most people, in simple cases like turning on http3, brotli, switching to newer instances and many others are all quick wins that I see ignored 90% of the time. A good design, implementing some good practices etc are performance specific and don’t always cost more.
  - kreetx 3y ago
    A denormalized database model is considered bad desig to begin with, and has performance costs on its own. This is why the OP says this is a "hot take". :) Maybe there are situations where this actually helps, although the resulting datastructure to me looks more like a multi-key cache.
chaxor 3y ago
Thanks, I hate it. This seems close to the territory of "why do I need a database? I just keep a bunch of text files with really long names that described exactly what I did to compute the file. They're all in various directories, so if you need to find one just do some greps and finds on the whole system" I recognize there's a big gap, but boy howdy does what you're suggesting sound messy.
backendanon 3y ago
Performance isn't king. The business is king.
hliyan 3y ago
Ten years ago when DB engines were not as good and servers were not as large, I did something similar -- set up a trigger on insert/updates to certain relations that auto updates the main record with a cache column. Back then it was comma separated, but today I would obviously use JSONB. Back then it reduced latency significantly. Today, I would probably not attempt it.
killthebuddha 3y ago
IME if a join is the problem, a join is not the problem.
anonzzzies 3y ago
As indeed a traditional HN remark, you are giving advice for applications that almost no one will ever need to build because you will never, ever see the type of traffic/users for it. Also, doing these things ; dejoining, UUIDs and indexing all columns (really unsure about this one; why?), might be better later on, but at the start it will be a lot heavier. Modern hardware and databases can take an incredible amount of traffic if you use them in the right and natural way without artificial tricks.
Buttons840 3y ago
> On this note, I have mixed feelings about Amazon's DynamoDB, but one things about it is to use it properly you need to plan your use first, and schema second. I think there's something you can take from this even with a RDBMS. This captures the experience I've had with DynamoDB and document databases in general. They appear more flexible at first, but in truth they are much less flexible. You must get them right up front or your going to be paying thousands of dollars every month in AWS bills just for DynamoDB. The need to get things right up front is the opposite of flexibility.
bbojan 3y ago
> - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. Hard disagree. You just re-implemented a database engine in your application code. Poorly.
dajonker 3y ago
Very hot take indeed. As with all things, it depends and use the query planner to measure what actually makes a difference. In our application we have one important join that actually makes things a lot faster than the denormalized alternatieve. The main table has about 8 references to an organization table. To figure out what rows should be selected for a particular organization, you could either query on those 8 columns, making a very big where/or clause. As it turns out, PostgreSQL will usually end up doing a full table scan despite any index you would create. Instead, there is an auxiliary table with two columns, one for organization and one reference to the main table. Joining on this table simplifies the query and also turns out to be much faster.
GrumpyNl 3y ago
When your queries become slow because of joins, you havent designed your table structures properly.
rocqua 3y ago
I wish for a DB that lets me write a completely normalized scheme, and then lets me specify how it should denormalize the scheme for actual storage. There is no reason manual updates to denormalized DBs need to be hand-rolled every time. They are easy to automatically deduce.
- kaba0 3y ago
  These are called material views.
  - tremon 3y ago
    *updatable views. Not every material view is updatable, and a view doesn't need to be materialized to be updatable.
camgunz 3y ago
I would rather just use a cache.
_kidlike 3y ago
> index all columns check out MariaDB "ColumnStore". it recently got merged into the upstream binary, and i started reading about it. ngl i was salivating a bit.

Scarbutt 3y ago

Normalization is not only about data storage but most importantly, data integrity.

endisneigh 3y ago
Yes, but I assert that it's possible to use transactions to update everything consistently. Serializable transactions weren't really common when MySQL/Postgres first came out, but now that they're common in new DBs + ACID, I think it's not possible to do with reasonable difficulty. If you agree with this, than its easy to prove that denormalized tables performance increase is well worth the annoyance of updating everything to transactionally update the dependencies. I won't say that it's trivial to update all of your business logic to do this, but I think it's definitely worth it for a new project at least.
- Guvante 3y ago
  You always need to compare write vs read performance. Turning a single table update into a 10 table one could tip your lock contention to the point where you are write bound or worse start hitting retries. Certainly it makes sense to move rarely updated fields to where they are used makes sense. Similarly "build your table against your queries not your ideal data model" is always sage advice.
- Bognar 3y ago
  Denormalized transactions are not trivial unless you are using serializable isolation level which will kill performance. If you don't use serializable isolation level, then you risk either running into deadlocks (which will kill performance) or inconsistency. Decent SQL databases offer materialized views, which probably give you what you want without all the headache of maintaining denormalized tables yourself.
  - endisneigh 3y ago
    all fair points, but to be fair I don't necessary think this makes the most sense for an existing project for the reasons you state. I do think for a new project would best be able to design around the access patterns in a way that eliminate most of the downsides.
- williamdclt 3y ago
  Transactions are not only (actually mainly not) about atomicity. Of course it’s possible to keep data integrity without normalisation, but that means you need to maintain the invariants yourself at application level and a big could result in data inconsistency. Normalisation isn’t there to make integrity possible, it’s there to make (some) non-integrity impossible. Nobody says you have to have only one view of your data though. You can have a normalised view of your data to write, and another denormalised for fast reads (you usually have to, at scale). Something like event sourcing is another way (which is actually pushing invariants to application level, in a structured way)
  - sgarland 3y ago

iamwil 3y ago

If you don't use joins, how do you associate records from two different tables when displaying the UI? Do you just join in the application? Or something else?

endisneigh 3y ago
this has opinionated answers. if you ask Amazon, they might suggest that you design around a single table (https://aws.amazon.com/blogs/compute/creating-a-single-table-design-with-amazon-dynamodb/ https://aws.amazon.com/blogs/compute/creating-a-single-table...). in my opinion it's easier to use join tables. which are what are sometimes temporarily created when you do a join anyways. in this case, you permanently create table1, table2, and table1_join_table2, and keep all three in sync transactionally. when you need a join you just select on table1_join_table2. you might think this is a waste of space, but I'd argue storage is too cheap for you to be thinking about that. that being said, you really have to design around your access patterns, don't design your application around your schema. most people do the latter because it seems more natural. what this might mean in practice is that you do mockups of all of the expected pages and what data is necessary on each one. then you design a schema that results in you never having to do joins on the majority, if not all, of them.
- sainez 3y ago
  > what this might mean in practice is that you do mockups of all of the expected pages and what data is necessary on each one. then you design a schema that results in you never having to do joins on the majority, if not all, of them. Great suggestion! I had a role where I helped a small team develop a full stack, data-heavy application. I felt pretty good about the individual layers but I felt we could have done a better job at achieving cohesion in the big picture. Do you have any resources where people think about these sorts of things deeply?
  - walterbell 3y ago
    2001, "Denormalization effects on performance of RDBMS", by G. L. Sanders and Seungkyoon Shin, https://www.semanticscholar.org/paper/Denormalization-effects-on-performance-of-RDBMS-Sanders-Shin/7643e5a175693ca07eff70e933214a67f216186f https://www.semanticscholar.org/paper/Denormalization-effect... > We have suggested using denormalization as an intermediate step between logical and physical modeling, to be used as an analytic procedure for the design of the applications requirements criteria ... The guidelines and methodology presented are sufficiently general, and they can be applicable to most databases ... denormalization can enhance query performance when it is deployed with a complete understanding of application requirements. PDF: https://web.archive.org/web/20171201030308/https://pdfs.semanticscholar.org/2c79/069c01ba8d598f32e61fe367ef6d261a0cb4.pdf https://web.archive.org/web/20171201030308/https://pdfs.sema...

skybrian 3y ago

I’m wondering if indexes and materialized views can be used to do basically the same thing? That is, assuming they contain all the columns you want.

latchkey 3y ago
The issue is writes, not reads.
giantrobot 3y ago
There's always money in the banana sta...materialized views. Materialized views will get you quite a ways on read heavy workloads.
- Izkata 3y ago
  As long as you're okay with the reads being a little out of date after writes occur.

veave 3y ago

>Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. Anybody has documentation about this with examples?

joshstrange 3y ago
See "Single Table Design" which I talked about in this comment above: https://news.ycombinator.com/item?id=37093357 https://news.ycombinator.com/item?id=37093357
- deely3 3y ago
  And if you don't want to spend money, you can get basic idea from this article: https://www.alexdebrie.com/posts/dynamodb-single-table/ https://www.alexdebrie.com/posts/dynamodb-single-table/
newlisp 3y ago
Duplicate data to avoid joins, use serializable transactions to update all the duplicated data.

latchkey 3y ago

> Design your application's hot path to never use joins. Grab (uber of asia) did this religiously and it created a ton of friction within the company due to the way the teams were laid out. It always required one team to add some sort of API that another team could take advantage of. Since the first team was so busy always implementing their own features, it created roadblocks with other teams and everyone started pointing fingers at each other to the point that nothing ever got done on time. Law of unintended consequences

endisneigh 3y ago
yes, this is a fair point. there's no free lunch after all. without knowing more about what happened with Grab I'd say you could mitigate some of that with good management and access patterns, though.
- latchkey 3y ago
  All in all though, I don't think that 'never use joins' is a good solution either since it does create more developer work almost every way you slice it. I think the op's solution of looking more closely at the hot paths and solving for those is a far better solution than re-architecting the application in ways that could, or can, create unintended consequences. People don't consider that enough, at all. Don't forget that hot path resolution is the antithesis of 'premature optimization'. > you could mitigate some of that with good management and access patterns the CTO fired me for making those sorts of suggestions about better management, and then got fired himself a couple months later... ¯\_(ツ)_/¯... even with the macro events, their stock is down 72% since it opened, which doesn't surprise me in the least bit having been on the inside...
  - jodrellblank 3y ago
    Grab was the company Steve Yegge left Google to go to. He quit during COVID when he could no longer travel to Asia, and his retrospective is glowing, including of the CTO Mark Porter(I think?): "frankly, the “Grab Way” of collaboration teaches you life skills, such as psychological safety and inclusiveness, which work everywhere else as well. [...] We US Grabbers made many mistakes on the journey towards becoming better Grabbers. Mark Porter led the charge on figuring it out, and we’ve learned a ton from him as he has evolved as a person before our eyes [...] I want to thank Mark especially for helping me grow as a leader and as a person. It takes humility to become a better person, and Grab is a humbling place. The core company value of humility comes straight from the CEO and cofounder Anthony Tan, who is one of the most humble, down-to-earth people you’ll ever meet.". Is that, uhh, rose-tinted glasses? 2020: https://steve-yegge.medium.com/saying-goodbye-to-the-best-gig-i-ever-had-a33736833c1e https://steve-yegge.medium.com/saying-goodbye-to-the-best-gi... Previously, 2018: https://steve-yegge.medium.com/why-i-left-google-to-join-grab-86dfffc0be84 https://steve-yegge.medium.com/why-i-left-google-to-join-gra... [I don't have any knowledge of Grab or the people involved other than reading these two blog posts, or of the parent commenter].

tibbetts 3y ago

Premature denomalization is expensive complexity. Denormalization is a great tool, maybe an under-used tool. But you should wait until there are hot paths before using it.

endisneigh 3y ago
I agree. To be clear I'm not suggesting anyone start denormalizing everything. I'm saying if you're fortunate enough to be on a green project, you should design the schema around the access patterns which will surely be "denomarlized." as opposed to designing a normalized schema and designing your access patterns around those.

taylodl 3y ago

My hot take: always use a materialized view or a stored procedure. Hide the actual, physical tables from the Application's account! The application doesn't need to know how the data is physically stored in the database. They specify the logical view they need of the data. The DBAs create the materialized view/stored procedure that's needed to implement that logical view. Since the application is never directly accessing the underlying physical data, it can be changed to make the retrieval more efficient without affecting any of the database's users. You're also getting the experts to create the required data access for you in the fastest, most efficient way possible. We've been doing this for years now and it works great. It's alleviated so many headaches we used to have.

alfor 3y ago
But for the saves the structure is visible?
- taylodl 3y ago
  You can update underlying data via a materialized view.
  - NortySpock 3y ago
    Heck, at least with SQL Server 2017, I've been able to write updates to a thin view, and so long as the columns referenced were not ambiguous, the database would handle the underlying tables and locks required.
walterbell 3y ago
Interface contracts and indirection FTW. 2011, "Materialized Views" by Rada Chirkova and Jun Yang, https://dsf.berkeley.edu/cs286/papers/mv-fntdb2012.pdf https://dsf.berkeley.edu/cs286/papers/mv-fntdb2012.pdf > We cover three fundamental problems: (1) maintaining materialized views efficiently when the base tables change, (2) using materialized views effectively to improve performance and availability, and (3) selecting which views to materialize. We also point out their connections to a few other areas in database research, illustrate the benefit of cross-pollination of ideas with these areas, and identify several directions for research on materialized views.

wizofaus 3y ago

Can't say I've ever come across a scenario where a join itself was the performance bottleneck. If there's any single principle I have observed is "don't let a table get too big". More often than not it's historical-record type tables that are the issue - but the amount of data you need for day-to-day operations is usually a tiny fraction of what's actually in the table, and you're bound to start finding operations on massive tables get slow no matter what indexes you have (and even the act of adding more indexes becomes problematic. And just indexing all columns isn't enough for traditional RMDBSes at least - you have to index the right combinations of columns for them to be used. Might be different for DynamoDb).

8note 3y ago
Dynamo is quick for that, so long as you are picking good partition keys. Instead, it'll throw you hot key throttling if you start querying one partition too much
pixl97 3y ago
In enterprise clients you commonly run into issues where the company thinks you have to save all data forever. Very often this runs in a pattern where the application is lightly used a first then uptake increases over time. Then you run into the slowness issue. They typically expand DB sizing, bit eventually run into the problem where archiving is needed. This can be a huge problem when it's an after thought instead of a primary design. All kinds of fun when you have key relationships between different tablea.
- wizofaus 3y ago
  It's more that once it gets to a certain size (say, 100s of 1000s of rows), doing anything with the table is painfully slow, often requiring you to take your application offline for considerable periods. Even deleting rows can take 10s of minutes at a time, and it can certainly take a very long time to work out what indexes need to be added and whether they're actually helping. Yes, sometimes the pressure comes from management etc., but more often than not it would be premature optimisation to add the archiving, so it's a matter of finding a balance and "predicting" at what point the archiving needs to happen. Table partitioning can help too but only so much.

i_like_apis 3y ago

Yes I like the zero joins on hot paths approach. It can be hard to sell people on it. It’s a great decision for scaling though.

wtetzner 3y ago

I'd say that probably depends on what your hot path is. If it's write-heavy, then you'll probably end up with performance issues when you need to write the same data to multiple tables in a single transaction. And if all of those columns are indexed, it'll be even worse.

macNchz 3y ago

Over the years I think I've encountered more pain from applications where the devs leaned on denormalization than from those that developed issues with join performance on large tables. You can mash those big joins into a materialized view or ETL them into a column store or whatever you need to fix performance later on, but once someone has copied the `subtotal_cents` column onto the Order, Invoice, Payment, NotificationEmail, and UserProfileRecentOrders models, and they're referenced and/or updated in 296 different places...it's a long road back to sanity.

deleted 3y ago

[deleted]

feoren 3y ago

There are "tall" applications and "wide" applications. Almost all advice you ever read about database design and optimization is for "tall" applications. Basically, it means that your application is only doing one single thing, and everything else is in service of that. Most of the big tech companies you can think of are tall. They have only a handful of really critical, driving concepts in their data model. Facebook really only has people, posts, and ads. Netflix really only has accounts and shows. Amazon (the product) really only has sellers, buyers, and products, with maybe a couple more behind the scene for logistics. The reason for this is because tall applications are easy. Much, much easier than wide applications, which are often called "enterprise". Enterprise software is bad because it's hard. This is where the most unexplored territory is. This is where untold riches lie. The existing players in this space are abysmally bad at it (Oracle, etc.). You will be too, if you enter it with a tall mindset. Advice like "never user joins" and "design around a single table" makes a lot of sense for tall applications. It's awful, terrible, very bad, no-good advice for wide applications. You see this occasionally when these very tall companies attempt to do literally anything other than their core competency: they fail miserably, because they're staffed with people who hold sacrosanct this kind of advice that does not translate to the vast space of "wide" applications. Just realize that: your advice is for companies doing easy things who are already successful and have run out of low-hanging fruit. Even tall applications that aren't yet victims of their own success do not need to think about butchering their data model in service of performance. Only those who are already vastly successful and are trying to squeeze out the last juices of performance. But those are the people who least need advice. This kind of tall-centered advice, justified with "FAANG is doing it so you should too" and "but what about when you have a billion users?" is poisoning the minds of people who set off to do something more interesting than serve ads to billions of people.

xyzzy123 3y ago
Thanks I think this is a really interesting way to look at things. What is the market for "wide" applications though? It seems like any particular business can only really support one or two of them, for some that will be SAP and for others it might be Salesforce (if they don't need much ERP), or (as you mentioned) some giant semi homebrewed Oracle thing. Usually there is a legacy system which is failing but still runs the business, and a "next gen" system which is not ready yet (and might never be, because it only supports a small number of use cases from the old software and even with an army of BAs it's difficult to spec out all the things the old software is actually doing with any accuracy). Or am I not quite getting the idea?

crazygringo 3y ago

That is a hot take... ;) But joins should never impact performance in a large way if they're on the same server and properly indexed. "It's truly amazing how much faster everything is when you eliminate joins" is just not true if you're using joins correctly. Sadly, many developers simply never bother to learn. On the other hand, having to write a piece of data to 20 different spots instead of 1 is going to be dramatically slower performance-wise, not to mention make your queries tremendously more complex and bug-prone, when you remember to update a value in 18 spots but forget about 2 of them. You mention cheap storage as an advantage for denormalizing, but storage is the least problem here. It's a vastly larger surface area for bugs, and terrible write performance (that can easily chew up read performance).

jiggawatts 3y ago
Storage might be cheap, but memory and bandwidth aren’t. Memory is the new disk, and disk is the new tape. You want everything to remain resident in memory, and spool backups and transaction logs to disk. If you’re joining from disk, you’ve probably done something wrong. E.g.: compression is often a net win because while it uses more CPU, it allows more data to fit into memory. And if it doesn’t fit, it reduces the disk I/O required. This is why I look upon JSON-based document databases in horror. They’re bloating the data out many times over by denormalizing and then expand that into a verbose and repetitive text format. This is why we have insanity like placeholders for text on web apps now — they’re struggling to retrieve a mere kilobyte of data!
endisneigh 3y ago
I agree but I’m talking in the context where you can’t vertically scale anymore. I also don’t think it’s worth the trouble “never using joins” for an existing project. Denormalize as necessary. But for a green one I honestly think since our access patterns can be understood as you continue you can completely get rid of joins. Again, assuming your new project can’t fit on a single machine. If it can you’re best just following the “traditional” advice, or better yet keep everything in memory.
- fastball 3y ago

paulddraper 3y ago

> Design your application's hot path to never use joins I had a Chief Architect who decreed this. So engineers wound up doing joins in application code, with far worse performance, filtering, memory caching, etc.

zeckalpha 3y ago

Materialized views provide a way to have joins that are accessed like tables!

theptip 3y ago

Honestly I couldn’t disagree more. I built a startup and paid little attention to perf for years 1-5, and finally in year 6 we started to get bitten by some perf issues in specific tables, and spent a few engineer-months optimizing. In terms of tech debt it would have been way more expensive to make everything perform well from the start, we would have moved much slower and probably failed during a few crunch points. Instead we paid probably a few $k/mo more than we really needed to on machines, and in return saved man-months of effort at a time when we couldn’t hire enough engineers and the opportunity cost for feature work was huge. (Keep in mind that making everything perform well would have required us to do 10-20x as much work, because we could not know ahead of time where the hot spots would be. Some were surprising.) Joins may be evil at scale, but most startups don’t have scale problems, at least not at first. Denormalizing can be a good optimization but you pay a velocity cost in keeping all the copies in sync across changes. Someone will write the bug that misses a denormalized non-canonical field and serves up stale data to a user. It’s usually cheaper (in total cost, ie CapEx+OpEx) to write the join and optimize later with a read-aside cache or whatever, rather than contorting your schema.

re-thc 3y ago
> I built a startup and paid little attention to perf for years 1-5, and finally in year 6 we started to get bitten by some perf issues in specific tables, and spent a few engineer-months optimizing. This screams of if I don’t see it it the problem doesn’t exist view of the world. How do you know it’s not a problem? Perhaps customers would have signed up if it as faster? The problem is also treating it in terms of business value and/or cost. A lot of things are “free” and yet it’s ignored. For most people, in simple cases like turning on http3, brotli, switching to newer instances and many others are all quick wins that I see ignored 90% of the time. A good design, implementing some good practices etc are performance specific and don’t always cost more.
- kreetx 3y ago
  A denormalized database model is considered bad desig to begin with, and has performance costs on its own. This is why the OP says this is a "hot take". :) Maybe there are situations where this actually helps, although the resulting datastructure to me looks more like a multi-key cache.

chaxor 3y ago

Thanks, I hate it. This seems close to the territory of "why do I need a database? I just keep a bunch of text files with really long names that described exactly what I did to compute the file. They're all in various directories, so if you need to find one just do some greps and finds on the whole system" I recognize there's a big gap, but boy howdy does what you're suggesting sound messy.

backendanon 3y ago

Performance isn't king. The business is king.

hliyan 3y ago

Ten years ago when DB engines were not as good and servers were not as large, I did something similar -- set up a trigger on insert/updates to certain relations that auto updates the main record with a cache column. Back then it was comma separated, but today I would obviously use JSONB. Back then it reduced latency significantly. Today, I would probably not attempt it.

killthebuddha 3y ago

IME if a join is the problem, a join is not the problem.

anonzzzies 3y ago

As indeed a traditional HN remark, you are giving advice for applications that almost no one will ever need to build because you will never, ever see the type of traffic/users for it. Also, doing these things ; dejoining, UUIDs and indexing all columns (really unsure about this one; why?), might be better later on, but at the start it will be a lot heavier. Modern hardware and databases can take an incredible amount of traffic if you use them in the right and natural way without artificial tricks.

Buttons840 3y ago

> On this note, I have mixed feelings about Amazon's DynamoDB, but one things about it is to use it properly you need to plan your use first, and schema second. I think there's something you can take from this even with a RDBMS. This captures the experience I've had with DynamoDB and document databases in general. They appear more flexible at first, but in truth they are much less flexible. You must get them right up front or your going to be paying thousands of dollars every month in AWS bills just for DynamoDB. The need to get things right up front is the opposite of flexibility.

bbojan 3y ago

> - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. Hard disagree. You just re-implemented a database engine in your application code. Poorly.

dajonker 3y ago

Very hot take indeed. As with all things, it depends and use the query planner to measure what actually makes a difference. In our application we have one important join that actually makes things a lot faster than the denormalized alternatieve. The main table has about 8 references to an organization table. To figure out what rows should be selected for a particular organization, you could either query on those 8 columns, making a very big where/or clause. As it turns out, PostgreSQL will usually end up doing a full table scan despite any index you would create. Instead, there is an auxiliary table with two columns, one for organization and one reference to the main table. Joining on this table simplifies the query and also turns out to be much faster.

GrumpyNl 3y ago

When your queries become slow because of joins, you havent designed your table structures properly.

rocqua 3y ago

I wish for a DB that lets me write a completely normalized scheme, and then lets me specify how it should denormalize the scheme for actual storage. There is no reason manual updates to denormalized DBs need to be hand-rolled every time. They are easy to automatically deduce.

kaba0 3y ago
These are called material views.
- tremon 3y ago
  *updatable views. Not every material view is updatable, and a view doesn't need to be materialized to be updatable.

camgunz 3y ago

I would rather just use a cache.

_kidlike 3y ago

> index all columns check out MariaDB "ColumnStore". it recently got merged into the upstream binary, and i started reading about it. ngl i was salivating a bit.