8 ms·

Your database skills are not 'good to have' (2023)

112 points by saikatsg 3y ago

LargeWu 3y ago
Smart developers both know SQL well, AND use ORM's. Sure ORM's are not going to write the most optimized code by default, and they're not going to build indexes for you, etc. But they are a huge productivity enhancer and work well enough for a lot of the queries you're going to need. Most ORM's these days have advanced options too which enable the more fine-tuned use cases without having to drop into raw SQL.
- onlyrealcuzzo 3y ago
  If performance is mission critical - ORMs seem like extra hoops to jump through. Am I missing something?
  - phailhaus 3y ago
    ORM's allow you to make changes without having to refactor every single SQL statement you've written manually. They can also be typed, unlike raw SQL strings, which helps a lot during development. It's another abstraction, just like the one between your language of choice and assembly code.
    - deleted 3y ago
      [deleted]
  - brandall10 3y ago
    It's a higher level abstraction that that boosts dev speed and readability and is often good enough by default, esp. for CRUD work. More advanced ones have the ability to optimize things fairly well within the constraints of their API. And it's not one or the other. There's always an SQL escape hatch when you need one for complex queries.
  - ryanjshaw 3y ago
    Modern ORMs are pretty efficient [1]. Some developers use the ORM for the "write model" and native SQL for reads/reports, if you are read-heavy. [1] https://michaelscodingspot.com/npgsql-dapper-efcore-performance/ https://michaelscodingspot.com/npgsql-dapper-efcore-performa...
  - BurningFrog 3y ago
    It can be, but in real applications only a small percentage of queries are performance problems, and you're probably better off optimizing those than writing and maintaining hand written queries.
  - ivanhoe 3y ago
    It's Pareto principle at play, in RL you usually need to highly optimize just a very small portion of the queries - let's call it 20% for the sake of tradition, though it's realistically <5% IME - to get almost all of the speed-ups worth the trouble. ORMs then help you to write the other 80% quickly and in a maintainable fashion.
  - kayodelycaon 3y ago
    Manually writing queries has a lot of overhead cost. Your entire application now has the UI directly tied to what the query returns. You can make intermediate classes to separate the two but eventually you'll end up making a worse ORM you need to test and maintain. What an ORM gives you is a tunable level of abstraction. I know Ruby on Rails best so I'll use it as an example. ActiveRecord has a lot of tuning you can do, like partial selects and snippets of sql where you need it. If that isn't enough, there is find_by_sql as an escape hatch. If that is still not fast enough, you can call ActiveRecord::Base.connection and skip the ORM entirely. This will keep complexity limited to places where it needs to be complex. In my experience, every level of optimization you do comes with decreasing maintainability.
    - randomdata 3y ago
      > Your entire application now has the UI directly tied to what the query returns. Not really. Without ORM, your entire application is now tied to sets of tuples (i.e. relations) instead of rich graph structures (i.e. objects), but the relation need not be equal to the relation returned by the query. You could still have a transformation from one relation to another. Not since the PHP days of embedding MySQL calls right in the middle of HTML have I seen anyone carry relations from end-to-end, though. The idea that anyone is not doing ORM these days seems like a straw man.
      - lelanthran 3y ago
        > Not really. Without ORM, your entire application is now tied to relations (i.e. sets of tuples) instead of rich graph structures (i.e. objects), but the relation need not be equal to the relation returned by the query. And, in some edge-cases, the relation is more useful than the object graph.
      - jimbokun 3y ago
        Depends. Clojure uses objects rarely. But still has really powerful abstractions for writing applications quickly.
  - evantbyrne 3y ago
    Yes. Every mature ORM that I'm aware of includes tools to trivialize fetching related records in an efficient manner. All people typically have to do is call the right method. In Django, for example, these methods are select_related and prefetch_related. Also, they don't have to be slow. When benchmarking my hobby golang ORM, I found the performance cost vs hand writing optimal SQL to be in the microseconds per returned row. Small price to pay for everything it gives you: schema migrations, query builder, object mapping, validation, etc. And if you happen to have a particularly hot endpoint that needs to return tens of thousands of rows at once during a HTTP lifetime, you can always dip back down to the driver level. It pains me that every time the topic of ORMs come up on HN that the comments are full of people warning not to use such a powerful category of tools. Maybe the tools they are using aren't very mature, maybe the underlying technologies are bit dated, maybe the documentation doesn't emphasize performance enough. I'm not really sure what's going on. Hopefully we can improve things so fewer people have issues and instead see performance improvements from using ORMs.
    - lrem 3y ago
      I haven’t seriously used an ORM for the simple reason that I find SQL simple enough to not need to learn an extra DSL to hide it. Furthermore, when I’m done figuring out the query I need in a Jupyter notebook (technically Google colab, should be same), I can just paste it to the C++/Go server or the dashboard config. Raw performance didn’t ever matter that much to me. With all this: is there an ORM with documentation that’s good at explaining what would I gain over plain old SQL?
      - evantbyrne 3y ago
        Have you ever used a rapid development framework like Django or Laravel? A good ORM is basically the core of any decent backend framework that interfaces with a RDBMS. You get to define a schema once which can then be used as a foundation for migrations, validation, queries, serialization, etc. Many ORMs may only help solve one or two of those things well, and in personal experience it is rare to see even okayish JavaScript ORMs, which may contribute to why people hate the category of tools.
- toomuchtodo 3y ago
  Do you have any references or resource recommendations for the migration path from ORMs to highly optimized SQL ops?
  - davidw 3y ago
    Most good ORM's let you write your own SQL to fetch things when you realize there's a particular operation that requires it. So write the initial system with an ORM and then optimize as needs be. That might be obvious from the start, or might become evident later.
- matthewmacleod 3y ago
  Absolutely this - the attitude of “never use an ORM” is an immediate red flag for me in a developer, indicating that they don’t understand the capabilities of a wide variety of ORMs and the trade-offs involved.
  - BurningFrog 3y ago
    It sounds like that "nerd macho" thing, where your main goal at work is to show how smart you are, not build working software systems.
  - randomdata 3y ago
    The bigger red flag is confusing ORM with ORM toolkits.
  - deleted 3y ago
    [deleted]
  - epgui 3y ago
    I think “never use X” is probably a bit far. But personally I can’t really think of a situation where I would rather have an ORM. I used to use them all the time. When I started doing pure FP I just realized I just didn’t really need my old ORM. When I came back to OOP languages (out of necessity, not pleasure), I realized I didn’t really need my old ORM there either. It’s not a strong opinion and it’s not a religious thing, it’s just a different perspective whereby you see that something isn’t really necessary. Benefits or not, you do pay quite a heavy price for the ORM, and I think most people don’t see this price because it has been normalized so much.
- xxs 3y ago
  a very good point. The downside is that I find most people just consider ORM a auto-sql, and the (data) modeling remains in a similar fashion. ORM benefits mostly from navigation + caching.
- bovermyer 3y ago
  I will use an ORM if it makes sense for the app in question, and then heavily tune it. Usually, though, I get better performance and less fragility out of leaning on carefully-crafted SQL+indexes to do the heavy lifting.
- randomdata 3y ago
  Your second paragraph reads more like you are talking about query builders rather than anything in the domain of ORM. ORM happens after the query has executed and you have a relation already in hand. Everything that happens before the relation is made available is outside of the ORM purview. Some ORM toolkits bundle query builders as a secondary feature, but they are still logically distinct features.
  - itsdrewmiller 3y ago
    The only reason ORMs are relevant to this conversation at all is because they include query generation. The vast majority for popular ones do that - one that I know of that doesn’t (dapper) labels itself a “micro ORM” because of it.
    - deleted 3y ago
      [deleted]
    - randomdata 3y ago
      ORM never includes query generation. Some libraries often include both ORM toolkits and query builders, but that's like calling sorting 'encryption' because libraries often include functionality for both. That would be quite silly, and confusing for the reader.
      - simonw 3y ago
        Every ORM I've ever used (or contributed to building) has included a query builder as a core component. What ORMs have you seen that don't do that?
        randomdata 3y ago
        ORM is the act of mapping relations (sets of tuples) to objects (graph structures). How could query building be a part of that? There are ORM toolkits that help produce the code to perform that mapping to save you from doing it by hand (although you can do it by hand, if you so wish!), but not even they could shove query building in the middle. Query execution has to happen before ORM can take place. You cannot perform ORM until you have the relation already in hand. (Mutation cases are in reverse, but I know you are capable of understanding that without it spelt out in detail) What would it even mean for ORM to include query building? If I'm reading you right, you seem to be saying that database helper libraries often include both query building and ORM toolkit features, usually along with other features like database migration management. Which is very much true. But why would you call one feature by the name of another? If you call query building ORM, is ORM best called database migration?
- gorjusborg 3y ago
  > Smart developers both know SQL well, AND use ORM's. > But they are a huge productivity enhancer and work well enough I disagree with the implication that using an ORM is the default that 'smart developers' always choose. Good developers choose the right tool for the job, so the tool selected depends on the job. Also, what the term ORM means (and its effects on the design) vary based on language ecosystem and library. For instance, if the application doesn't need to dynamically navigate relations, and instead can get by with just treating the database as a set of structs, an ORM like Hibernate would probably introduce more complexity than it gives benefit. In such cases, a library like Jooq or JDBI could make the overall design simpler, perform better, and make it easier to reason about the behavior of the system. I would argue those are not ORMs, yet they can yield a better product, depending on the situation.
- marcosdumay 3y ago
  So... Your claim is that the ORM optimizer is better than the DBMS one? What DBMS do you use? I've used a few where that is true, and really, instead of using an ORM for that, you should stick to Postgres.
- renegade-otter 3y ago
  While my article reads like an anti-ORM manifesto, my warning is about relying on ORMs to do all the work. How am I supposed to understand advanced fine-tuning options if I do not understand how the low-level query works? Copilot may be your little helper in writing code, but it is not going to consistently write the best code that YOU can write.
eatonphil 3y ago
Good call linking to Use The Index Luke. A great resource. https://use-the-index-luke.com/ https://use-the-index-luke.com/
- saikatsg 3y ago
  Thanks for sharing!
- sherburt3 3y ago
  His book SQL Performance Explained is also very enlightening as well. I think the most useful takeaway I got from that book is that the effectiveness of an index is heavily determined by how the application will be querying that table. If you want to design an effective index for a table you should experiment with multiple options and benchmark the queries that the application is going to run.
ergonaught 3y ago
Every time I read someone's "horror story" of "queries that used a lot of joins, like even 3 or 4 tables", I...well, sigh, at this point, I guess. It's been a very long time since I needed advanced SQL to do things that probably shouldn't be happening in the database anyway, but the fundamentals seem largely foreign concepts today (speaking generally), and they are definitely still relevant and enormously helpful.
- west0n 3y ago
  lesson: DONOT join more than 3 tables in MySQL, lol
  - mewpmewp2 3y ago
    What? It should be fine to join that amount and more, given you have proper indexes.
    - baq 3y ago
      This was valid advice in MySQL 3 days.
      - Twirrim 3y ago
        People love to keep trotting it out, which is annoying. "Can't use MySQL because <thing-that-hasn't-been-true-for-over-a-decade>", which I guess is arguably adjacent to other points in the article about the stuff databases have added over the years: Check in on your databases periodically, and refresh your memory. There's a very real chance they've picked up features that'll make your life a lot easier.
    - marcosdumay 3y ago
      More than this. Joining the tables in the database should perform better than joining them on the application layer. If it doesn't, there's something very odd. This holds for any production ready DBMS. Even the ones that don't care about performance or preserving your data.
ryanjshaw 3y ago
I don't understand why they didn't generate a bunch of static pages from the database and avoid a whole lot of stress?
- mrkeen 3y ago
  Faceting. > A user will land on the search page and have the ability to “drill down” and narrow the results based on those properties. To make things much harder, all of these properties would come with exact counts.
  - ryanjshaw 3y ago
    Have a look at the archived page: https://web.archive.org/web/20070519214259/http://nymag.com/fashion/fashionshows/2007/spring/main/newyork/womenrunway/yigalazrouel/ https://web.archive.org/web/20070519214259/http://nymag.com/... Click on "Types of Clothing" - unfortunately the link isn't archived but you can see the URL: http://nymag.com/search/fashion-search.cgi?nymbreadcrumb_push=Bag&other_params=&results_per_page=20&sort_params=&search_type=fashion&autonomy_fieldname=Items&autonomy_fieldvalue=Bag&filter_prettyname=Bag http://nymag.com/search/fashion-search.cgi?nymbreadcrumb_pus... Judging from the query params, it looks like you can only filter one item at a time - seems like static pages would work.
    - renegade-otter 3y ago
      No, you could have drilled into multiple facets. I don't think the Archive cached the deep-nested results to see that. So, you could go "Dolce & Gabbana -> Bag -> Red". Then you could remove the middle one, for example, and end up with "Dolce & Gabbana -> Red" This gives you unlimited permutations of results, so static pages were a no go.
gigatexal 3y ago
I love everything about this article. This is the kind of database black arts that I really enjoy.
- cogman10 3y ago
  Black arts? Databases, pretty much all of them, are relatively simple creatures. If you can understand how a Tree structure and pointers work, you basically have most of the fundamental knowledge underlying the "black art" concepts of databases. The primary key/clustered index is how the data is physically stored and sorted, in a btree. Additional secondary indexes are generally quiet literally just secondary tables clustered on the secondary index key with primary key pointers. The job of the optimizer is to take when you have "Select foo from bar where baz=2" and ask questions like "Is there an index on baz. Does that index contain foo?" and then executing based on that. If there is an index on baz, it traverses the baz index looking for 2. If that index covers foo, then the query is done and it just pulls foo from the baz index. If it doesn't, then it queries the foo table with each of the primary keys in the baz index. If no index exists, then the db has to do a linear search through the table looking for "baz=2". When databases are slow, it's because you are forcing them to do O(n). When they are fast, it's because you use an index to turn that into a O(log n) search. (or in some cases, O(1).) The black black arts of databases is when you start getting into things like CTEs and windowed functions and their interactions with the indexes/optimizers. That's when it can be almost trivial to introduce O(n^2) performance if you aren't careful. NoSQL databases generally speaking, are implemented similar to how SQL databases are (just trees) but with less features and less guarantees.
  - sgarland 3y ago
    > The primary key/clustered index is how the data is physically stored and sorted, in a btree. This is only true for RDBMS with a clustered index, like MySQL (assuming InnoDB) and MSSQL. Postgres stores tuples in a heap. The indices are generally B+trees, yes, but there is an extra level of indirection via the visibility map. There are a million gotchas, is the problem. You declared a partial index on foo as WHERE foo = true, but you’re querying for WHERE foo IS NOT false? I have bad news about the secret third state, WHERE foo IS NULL (not to mention the subtle differences between equality and IS). You made a UUIDv4 as the PK, thinking that since Postgres doesn’t cluster on it, it won’t suffer the same performance issues? Visibility map just wrecked your IOPS, enjoy the 7x hit to pages. You have an index and it’s not being used? Could be incorrect equality check (see first example), or inadequate auto-analyze on a write-heavy table leading to incorrect statistics, or any other number of things. RDBMS are much easier to understand if you know what a B+tree looks like, yes, and I highly recommend any backend dev take the time to do so. But there is so, so much more that can go wrong.
dieselgate 3y ago
Can’t believe they mentioned “Autonomy” here which definitely dates the piece - I’ve never ever heard it referenced. Was a part of an Autonomy cms upgrade at a previous job and it’s pretty old and bad - but got to learn a lot.. Am interviewing for a job now and they seem to have Coldfusion as part of their stack - prefer that to autonomy..!
west0n 3y ago
TLTR: Understand and optimize relational databases (MySQL, PG) rather than introducing a new type of database (e.g., DynamoDB, Kafka, Redis) whenever issues arise. Common mistakes: - Lack of indexes - Using ORM (generating too many SQL queries or inefficient queries that return all columns) Some suggestions: - Use relational databases where possible, instead of NoSQL - Use read-only replicas instead of Redis when applicable - Keep relational databases clean and data size small The author's message is: Hey, folks, if you don't have the ability to use relational databases properly, then your usage of Kafka and DynamoDB will certainly be a mess as well.
- corytheboyd 3y ago
  You can use an ORM and not explode the number of queries to infinity. I’ve never seen it in the wild, so I get the blanket ban. In theory though you COULD contain ORM usage behind an intentional data access interface. I can only really speak for Rails from my own experience, but it most certainly tries exactly 0% to get you to care about this problem. It encourages deeply nested, at all layers of abstraction, through class inheritance and all that garbage, unrestricted access to the database through a very powerful ORM. You end up tuning performance of queries that end up changing in a month because of this. It’s wild.
  - cameronh90 3y ago
    I'm a big fan of SQLAlchemy. While not without its quirks, it lets you write relational-style queries, but in native Python, and automatically maps the result sets to objects. If you have N+1 issues with SQLAlchemy, you probably would have had them with your own custom-written SQL queries also.
  - west0n 3y ago
    so I leaved Rails, lol
    - corytheboyd 3y ago
      Don’t blame you!
samsk 3y ago
This! You come to a fintech to review 'some' performance issues and find a stupid data model and transaction processing completely ignoring how databases work and what limitations that concrete database has ! But hey, look, we used this shiny tech and that newest stack, where is the problem ???
frfl 3y ago
Can anyone share good resources or codebases that have high quality web backend with optimized queries and data structures (db schema, backend internal data structures, overall backend architecture)? I feel like a lot of code you see in articles or modern companies if often just following the popular architecture/paradigm du jour. As opposed to what this article highlights in terms of making backends return all the data you need in the minimal number of queries. I feel like I understand what's being described here, but I've never actually had the chance to work on anything non-trivial that does it like this, most places I've worked have gone no-SQL or used an orm or were not more than very thin wrappers around some CRUD queries.
- stavros 3y ago
  There's nothing in the ORM that prevents you from doing what the article describes; hell, the article describes doing it with the ORM, and, double hell, the ORM does the right thing by default, at least as far as avoiding N queries goes. The solution is to know how a database works, instead of thinking that Product.objects.filter(color="red") and [product for product in Product.objects.all() if product.color = "red"] are exactly the same thing.
  - frfl 3y ago
    I understand that. I was asking more about if anyone knows any open source codebases that are high quality, non-trivial and well designed, that one should read and study to better see how others have written good code and systems.
    - baq 3y ago
      Yes, the sqlalchemy docs. Dead serious.
      - frfl 3y ago
        That's somewhat surprising, but then again, maybe not. Thanks, I'll take note of that. For anyone else, I also remember PostgresSQL manual/docs being very high quality, so that might be worth reading just to better grok SQL/relational DB topics.
throwaway5959 3y ago
The number one mistake is really that the industry continues to pile more and more work on software engineers, including expecting them to be experts at a topic that used to be a whole job (DBA).
- pradn 3y ago
  In the past twenty years, the job of "software engineer" has taken upon ops/QA/DBA/support.
  - deleted 3y ago
    [deleted]
deleted 3y ago
[deleted]
pants2 3y ago
Has Postgres yet enabled any sort of locking of the query plan? It's been a while since I've used it at scale, but at my previous job half of our incidents were caused by Postgres randomly deciding to change the query plan for a call that was working just fine. Then I'd go in there and re-write the SQL a few times until it figured out what to do, rinse and repeat every few months.
- natoliniak 3y ago
  I'm dealing with a very large partitioned table (several millions of inserts per day) and i solve this by running `vacuum analyze` nightly which somehow solved 99% our read issues Prior to that, we tried various indexing strategies, de-normalizing some of the data, but ultimately i found that weird plan inconsistency to be solved by the vacuum job
- sgarland 3y ago
  Not natively. There is pg_store_plans [0] which I haven’t used, but looks interesting. Most of the time, query flips are due to inaccurate statistics over time. Try changing the default statistics target for the affected column. [0]: https://github.com/ossc-db/pg_store_plans https://github.com/ossc-db/pg_store_plans
- polishdude20 3y ago
  I'd love to have that. At a previous gig a query ballooned up to taking 30 seconds up from 1 second. The reason was the query planner decided to use a different plan. Any statistics update didn't work. Finally I tried just swapping the join order. The plan changed all was well. Until the planner decided to change the plan again...
- timacles 3y ago
  No offense guys, but just get a real DBA. Postgres doesnt just randomly do things and will always produce the same result given the same circumstances. Either you werent running routine maintenance and your statistics are messed up, or the statistics arent properly configured or your data is just poorly stored.
chrsig 3y ago
> Imagine if SELECT field1, field2 FROM my_table was faster than SELECT field2, field1 FROM my_table. Why would it do that? I have no idea to this day, and I don’t even want to know. > I don’t even want to know. I feel this. I'm working on a team where I have significantly more tenure at the company than the other engineers on the team, and they'll often ask me questions starting with "why". Normally, I love to encourage this -- curiosity is an amazing quality to have in coworkers. There are still some times when I just need to look at them like I'm dead inside and warn them that it's not worth their time on this earth to find out.
- gigatexal 3y ago
  See this is what I find interesting. Some do others just say the DB is this idiosyncratic thing that sometimes does Z and sometimes does Y.
deleted 3y ago
[deleted]
lakomen 3y ago
I've been thinking recently, well for a longer time. The next project will be like this: I will name the endpoints PagenameFunctionality. E.g. /IndexGetMyProfile There will be global scope functions or endpoints but many scoped endpoints. And there I will do the proper queries just for this use case, and so on for each new use case. In the context of SPA/PWA/TWA I think this is a good approach. Graphql is too complex on the client and RESTful too vague.
- cess11 3y ago
  Can call it Grammarless RPC.

112 points by saikatsg 3y ago

Smart developers both know SQL well, AND use ORM's. Sure ORM's are not going to write the most optimized code by default, and they're not going to build indexes for you, etc. But they are a huge productivity enhancer and work well enough for a lot of the queries you're going to need. Most ORM's these days have advanced options too which enable the more fine-tuned use cases without having to drop into raw SQL.

onlyrealcuzzo 3y ago
If performance is mission critical - ORMs seem like extra hoops to jump through. Am I missing something?
- phailhaus 3y ago
  ORM's allow you to make changes without having to refactor every single SQL statement you've written manually. They can also be typed, unlike raw SQL strings, which helps a lot during development. It's another abstraction, just like the one between your language of choice and assembly code.
  - deleted 3y ago
    [deleted]
- brandall10 3y ago
  It's a higher level abstraction that that boosts dev speed and readability and is often good enough by default, esp. for CRUD work. More advanced ones have the ability to optimize things fairly well within the constraints of their API. And it's not one or the other. There's always an SQL escape hatch when you need one for complex queries.
- ryanjshaw 3y ago
  Modern ORMs are pretty efficient [1]. Some developers use the ORM for the "write model" and native SQL for reads/reports, if you are read-heavy. [1] https://michaelscodingspot.com/npgsql-dapper-efcore-performance/ https://michaelscodingspot.com/npgsql-dapper-efcore-performa...
- BurningFrog 3y ago
  It can be, but in real applications only a small percentage of queries are performance problems, and you're probably better off optimizing those than writing and maintaining hand written queries.
- ivanhoe 3y ago
  It's Pareto principle at play, in RL you usually need to highly optimize just a very small portion of the queries - let's call it 20% for the sake of tradition, though it's realistically <5% IME - to get almost all of the speed-ups worth the trouble. ORMs then help you to write the other 80% quickly and in a maintainable fashion.
- kayodelycaon 3y ago
  Manually writing queries has a lot of overhead cost. Your entire application now has the UI directly tied to what the query returns. You can make intermediate classes to separate the two but eventually you'll end up making a worse ORM you need to test and maintain. What an ORM gives you is a tunable level of abstraction. I know Ruby on Rails best so I'll use it as an example. ActiveRecord has a lot of tuning you can do, like partial selects and snippets of sql where you need it. If that isn't enough, there is find_by_sql as an escape hatch. If that is still not fast enough, you can call ActiveRecord::Base.connection and skip the ORM entirely. This will keep complexity limited to places where it needs to be complex. In my experience, every level of optimization you do comes with decreasing maintainability.
  - randomdata 3y ago
    > Your entire application now has the UI directly tied to what the query returns. Not really. Without ORM, your entire application is now tied to sets of tuples (i.e. relations) instead of rich graph structures (i.e. objects), but the relation need not be equal to the relation returned by the query. You could still have a transformation from one relation to another. Not since the PHP days of embedding MySQL calls right in the middle of HTML have I seen anyone carry relations from end-to-end, though. The idea that anyone is not doing ORM these days seems like a straw man.
    - lelanthran 3y ago
      > Not really. Without ORM, your entire application is now tied to relations (i.e. sets of tuples) instead of rich graph structures (i.e. objects), but the relation need not be equal to the relation returned by the query. And, in some edge-cases, the relation is more useful than the object graph.
    - jimbokun 3y ago
      Depends. Clojure uses objects rarely. But still has really powerful abstractions for writing applications quickly.
- evantbyrne 3y ago
  Yes. Every mature ORM that I'm aware of includes tools to trivialize fetching related records in an efficient manner. All people typically have to do is call the right method. In Django, for example, these methods are select_related and prefetch_related. Also, they don't have to be slow. When benchmarking my hobby golang ORM, I found the performance cost vs hand writing optimal SQL to be in the microseconds per returned row. Small price to pay for everything it gives you: schema migrations, query builder, object mapping, validation, etc. And if you happen to have a particularly hot endpoint that needs to return tens of thousands of rows at once during a HTTP lifetime, you can always dip back down to the driver level. It pains me that every time the topic of ORMs come up on HN that the comments are full of people warning not to use such a powerful category of tools. Maybe the tools they are using aren't very mature, maybe the underlying technologies are bit dated, maybe the documentation doesn't emphasize performance enough. I'm not really sure what's going on. Hopefully we can improve things so fewer people have issues and instead see performance improvements from using ORMs.
  - lrem 3y ago
    I haven’t seriously used an ORM for the simple reason that I find SQL simple enough to not need to learn an extra DSL to hide it. Furthermore, when I’m done figuring out the query I need in a Jupyter notebook (technically Google colab, should be same), I can just paste it to the C++/Go server or the dashboard config. Raw performance didn’t ever matter that much to me. With all this: is there an ORM with documentation that’s good at explaining what would I gain over plain old SQL?
    - evantbyrne 3y ago
      Have you ever used a rapid development framework like Django or Laravel? A good ORM is basically the core of any decent backend framework that interfaces with a RDBMS. You get to define a schema once which can then be used as a foundation for migrations, validation, queries, serialization, etc. Many ORMs may only help solve one or two of those things well, and in personal experience it is rare to see even okayish JavaScript ORMs, which may contribute to why people hate the category of tools.
toomuchtodo 3y ago
Do you have any references or resource recommendations for the migration path from ORMs to highly optimized SQL ops?
- davidw 3y ago
  Most good ORM's let you write your own SQL to fetch things when you realize there's a particular operation that requires it. So write the initial system with an ORM and then optimize as needs be. That might be obvious from the start, or might become evident later.
matthewmacleod 3y ago
Absolutely this - the attitude of “never use an ORM” is an immediate red flag for me in a developer, indicating that they don’t understand the capabilities of a wide variety of ORMs and the trade-offs involved.
- BurningFrog 3y ago
  It sounds like that "nerd macho" thing, where your main goal at work is to show how smart you are, not build working software systems.
- randomdata 3y ago
  The bigger red flag is confusing ORM with ORM toolkits.
- deleted 3y ago
  [deleted]
- epgui 3y ago
  I think “never use X” is probably a bit far. But personally I can’t really think of a situation where I would rather have an ORM. I used to use them all the time. When I started doing pure FP I just realized I just didn’t really need my old ORM. When I came back to OOP languages (out of necessity, not pleasure), I realized I didn’t really need my old ORM there either. It’s not a strong opinion and it’s not a religious thing, it’s just a different perspective whereby you see that something isn’t really necessary. Benefits or not, you do pay quite a heavy price for the ORM, and I think most people don’t see this price because it has been normalized so much.
xxs 3y ago
a very good point. The downside is that I find most people just consider ORM a auto-sql, and the (data) modeling remains in a similar fashion. ORM benefits mostly from navigation + caching.
bovermyer 3y ago
I will use an ORM if it makes sense for the app in question, and then heavily tune it. Usually, though, I get better performance and less fragility out of leaning on carefully-crafted SQL+indexes to do the heavy lifting.
randomdata 3y ago
Your second paragraph reads more like you are talking about query builders rather than anything in the domain of ORM. ORM happens after the query has executed and you have a relation already in hand. Everything that happens before the relation is made available is outside of the ORM purview. Some ORM toolkits bundle query builders as a secondary feature, but they are still logically distinct features.
- itsdrewmiller 3y ago
  The only reason ORMs are relevant to this conversation at all is because they include query generation. The vast majority for popular ones do that - one that I know of that doesn’t (dapper) labels itself a “micro ORM” because of it.
  - deleted 3y ago
    [deleted]
  - randomdata 3y ago
    ORM never includes query generation. Some libraries often include both ORM toolkits and query builders, but that's like calling sorting 'encryption' because libraries often include functionality for both. That would be quite silly, and confusing for the reader.
    - simonw 3y ago
      Every ORM I've ever used (or contributed to building) has included a query builder as a core component. What ORMs have you seen that don't do that?
      - randomdata 3y ago
        ORM is the act of mapping relations (sets of tuples) to objects (graph structures). How could query building be a part of that? There are ORM toolkits that help produce the code to perform that mapping to save you from doing it by hand (although you can do it by hand, if you so wish!), but not even they could shove query building in the middle. Query execution has to happen before ORM can take place. You cannot perform ORM until you have the relation already in hand. (Mutation cases are in reverse, but I know you are capable of understanding that without it spelt out in detail) What would it even mean for ORM to include query building? If I'm reading you right, you seem to be saying that database helper libraries often include both query building and ORM toolkit features, usually along with other features like database migration management. Which is very much true. But why would you call one feature by the name of another? If you call query building ORM, is ORM best called database migration?
gorjusborg 3y ago
> Smart developers both know SQL well, AND use ORM's. > But they are a huge productivity enhancer and work well enough I disagree with the implication that using an ORM is the default that 'smart developers' always choose. Good developers choose the right tool for the job, so the tool selected depends on the job. Also, what the term ORM means (and its effects on the design) vary based on language ecosystem and library. For instance, if the application doesn't need to dynamically navigate relations, and instead can get by with just treating the database as a set of structs, an ORM like Hibernate would probably introduce more complexity than it gives benefit. In such cases, a library like Jooq or JDBI could make the overall design simpler, perform better, and make it easier to reason about the behavior of the system. I would argue those are not ORMs, yet they can yield a better product, depending on the situation.
marcosdumay 3y ago
So... Your claim is that the ORM optimizer is better than the DBMS one? What DBMS do you use? I've used a few where that is true, and really, instead of using an ORM for that, you should stick to Postgres.
renegade-otter 3y ago
While my article reads like an anti-ORM manifesto, my warning is about relying on ORMs to do all the work. How am I supposed to understand advanced fine-tuning options if I do not understand how the low-level query works? Copilot may be your little helper in writing code, but it is not going to consistently write the best code that YOU can write.

eatonphil 3y ago

Good call linking to Use The Index Luke. A great resource. https://use-the-index-luke.com/ https://use-the-index-luke.com/

saikatsg 3y ago
Thanks for sharing!
sherburt3 3y ago
His book SQL Performance Explained is also very enlightening as well. I think the most useful takeaway I got from that book is that the effectiveness of an index is heavily determined by how the application will be querying that table. If you want to design an effective index for a table you should experiment with multiple options and benchmark the queries that the application is going to run.

ergonaught 3y ago

Every time I read someone's "horror story" of "queries that used a lot of joins, like even 3 or 4 tables", I...well, sigh, at this point, I guess. It's been a very long time since I needed advanced SQL to do things that probably shouldn't be happening in the database anyway, but the fundamentals seem largely foreign concepts today (speaking generally), and they are definitely still relevant and enormously helpful.

west0n 3y ago
lesson: DONOT join more than 3 tables in MySQL, lol
- mewpmewp2 3y ago
  What? It should be fine to join that amount and more, given you have proper indexes.
  - baq 3y ago
    This was valid advice in MySQL 3 days.
    - Twirrim 3y ago
      People love to keep trotting it out, which is annoying. "Can't use MySQL because <thing-that-hasn't-been-true-for-over-a-decade>", which I guess is arguably adjacent to other points in the article about the stuff databases have added over the years: Check in on your databases periodically, and refresh your memory. There's a very real chance they've picked up features that'll make your life a lot easier.
  - marcosdumay 3y ago
    More than this. Joining the tables in the database should perform better than joining them on the application layer. If it doesn't, there's something very odd. This holds for any production ready DBMS. Even the ones that don't care about performance or preserving your data.

ryanjshaw 3y ago

I don't understand why they didn't generate a bunch of static pages from the database and avoid a whole lot of stress?

mrkeen 3y ago
Faceting. > A user will land on the search page and have the ability to “drill down” and narrow the results based on those properties. To make things much harder, all of these properties would come with exact counts.
- ryanjshaw 3y ago
  Have a look at the archived page: https://web.archive.org/web/20070519214259/http://nymag.com/fashion/fashionshows/2007/spring/main/newyork/womenrunway/yigalazrouel/ https://web.archive.org/web/20070519214259/http://nymag.com/... Click on "Types of Clothing" - unfortunately the link isn't archived but you can see the URL: http://nymag.com/search/fashion-search.cgi?nymbreadcrumb_push=Bag&other_params=&results_per_page=20&sort_params=&search_type=fashion&autonomy_fieldname=Items&autonomy_fieldvalue=Bag&filter_prettyname=Bag http://nymag.com/search/fashion-search.cgi?nymbreadcrumb_pus... Judging from the query params, it looks like you can only filter one item at a time - seems like static pages would work.
  - renegade-otter 3y ago
    No, you could have drilled into multiple facets. I don't think the Archive cached the deep-nested results to see that. So, you could go "Dolce & Gabbana -> Bag -> Red". Then you could remove the middle one, for example, and end up with "Dolce & Gabbana -> Red" This gives you unlimited permutations of results, so static pages were a no go.

gigatexal 3y ago

I love everything about this article. This is the kind of database black arts that I really enjoy.

cogman10 3y ago
Black arts? Databases, pretty much all of them, are relatively simple creatures. If you can understand how a Tree structure and pointers work, you basically have most of the fundamental knowledge underlying the "black art" concepts of databases. The primary key/clustered index is how the data is physically stored and sorted, in a btree. Additional secondary indexes are generally quiet literally just secondary tables clustered on the secondary index key with primary key pointers. The job of the optimizer is to take when you have "Select foo from bar where baz=2" and ask questions like "Is there an index on baz. Does that index contain foo?" and then executing based on that. If there is an index on baz, it traverses the baz index looking for 2. If that index covers foo, then the query is done and it just pulls foo from the baz index. If it doesn't, then it queries the foo table with each of the primary keys in the baz index. If no index exists, then the db has to do a linear search through the table looking for "baz=2". When databases are slow, it's because you are forcing them to do O(n). When they are fast, it's because you use an index to turn that into a O(log n) search. (or in some cases, O(1).) The black black arts of databases is when you start getting into things like CTEs and windowed functions and their interactions with the indexes/optimizers. That's when it can be almost trivial to introduce O(n^2) performance if you aren't careful. NoSQL databases generally speaking, are implemented similar to how SQL databases are (just trees) but with less features and less guarantees.
- sgarland 3y ago
  > The primary key/clustered index is how the data is physically stored and sorted, in a btree. This is only true for RDBMS with a clustered index, like MySQL (assuming InnoDB) and MSSQL. Postgres stores tuples in a heap. The indices are generally B+trees, yes, but there is an extra level of indirection via the visibility map. There are a million gotchas, is the problem. You declared a partial index on foo as WHERE foo = true, but you’re querying for WHERE foo IS NOT false? I have bad news about the secret third state, WHERE foo IS NULL (not to mention the subtle differences between equality and IS). You made a UUIDv4 as the PK, thinking that since Postgres doesn’t cluster on it, it won’t suffer the same performance issues? Visibility map just wrecked your IOPS, enjoy the 7x hit to pages. You have an index and it’s not being used? Could be incorrect equality check (see first example), or inadequate auto-analyze on a write-heavy table leading to incorrect statistics, or any other number of things. RDBMS are much easier to understand if you know what a B+tree looks like, yes, and I highly recommend any backend dev take the time to do so. But there is so, so much more that can go wrong.

dieselgate 3y ago

Can’t believe they mentioned “Autonomy” here which definitely dates the piece - I’ve never ever heard it referenced. Was a part of an Autonomy cms upgrade at a previous job and it’s pretty old and bad - but got to learn a lot.. Am interviewing for a job now and they seem to have Coldfusion as part of their stack - prefer that to autonomy..!

west0n 3y ago

TLTR: Understand and optimize relational databases (MySQL, PG) rather than introducing a new type of database (e.g., DynamoDB, Kafka, Redis) whenever issues arise. Common mistakes: - Lack of indexes - Using ORM (generating too many SQL queries or inefficient queries that return all columns) Some suggestions: - Use relational databases where possible, instead of NoSQL - Use read-only replicas instead of Redis when applicable - Keep relational databases clean and data size small The author's message is: Hey, folks, if you don't have the ability to use relational databases properly, then your usage of Kafka and DynamoDB will certainly be a mess as well.

corytheboyd 3y ago
You can use an ORM and not explode the number of queries to infinity. I’ve never seen it in the wild, so I get the blanket ban. In theory though you COULD contain ORM usage behind an intentional data access interface. I can only really speak for Rails from my own experience, but it most certainly tries exactly 0% to get you to care about this problem. It encourages deeply nested, at all layers of abstraction, through class inheritance and all that garbage, unrestricted access to the database through a very powerful ORM. You end up tuning performance of queries that end up changing in a month because of this. It’s wild.
- cameronh90 3y ago
  I'm a big fan of SQLAlchemy. While not without its quirks, it lets you write relational-style queries, but in native Python, and automatically maps the result sets to objects. If you have N+1 issues with SQLAlchemy, you probably would have had them with your own custom-written SQL queries also.
- west0n 3y ago
  so I leaved Rails, lol
  - corytheboyd 3y ago
    Don’t blame you!

samsk 3y ago

This! You come to a fintech to review 'some' performance issues and find a stupid data model and transaction processing completely ignoring how databases work and what limitations that concrete database has ! But hey, look, we used this shiny tech and that newest stack, where is the problem ???

frfl 3y ago

Can anyone share good resources or codebases that have high quality web backend with optimized queries and data structures (db schema, backend internal data structures, overall backend architecture)? I feel like a lot of code you see in articles or modern companies if often just following the popular architecture/paradigm du jour. As opposed to what this article highlights in terms of making backends return all the data you need in the minimal number of queries. I feel like I understand what's being described here, but I've never actually had the chance to work on anything non-trivial that does it like this, most places I've worked have gone no-SQL or used an orm or were not more than very thin wrappers around some CRUD queries.

stavros 3y ago
There's nothing in the ORM that prevents you from doing what the article describes; hell, the article describes doing it with the ORM, and, double hell, the ORM does the right thing by default, at least as far as avoiding N queries goes. The solution is to know how a database works, instead of thinking that Product.objects.filter(color="red") and [product for product in Product.objects.all() if product.color = "red"] are exactly the same thing.
- frfl 3y ago
  I understand that. I was asking more about if anyone knows any open source codebases that are high quality, non-trivial and well designed, that one should read and study to better see how others have written good code and systems.
  - baq 3y ago
    Yes, the sqlalchemy docs. Dead serious.
    - frfl 3y ago
      That's somewhat surprising, but then again, maybe not. Thanks, I'll take note of that. For anyone else, I also remember PostgresSQL manual/docs being very high quality, so that might be worth reading just to better grok SQL/relational DB topics.

throwaway5959 3y ago

The number one mistake is really that the industry continues to pile more and more work on software engineers, including expecting them to be experts at a topic that used to be a whole job (DBA).

pradn 3y ago
In the past twenty years, the job of "software engineer" has taken upon ops/QA/DBA/support.
- deleted 3y ago
  [deleted]

deleted 3y ago

[deleted]

pants2 3y ago

Has Postgres yet enabled any sort of locking of the query plan? It's been a while since I've used it at scale, but at my previous job half of our incidents were caused by Postgres randomly deciding to change the query plan for a call that was working just fine. Then I'd go in there and re-write the SQL a few times until it figured out what to do, rinse and repeat every few months.

natoliniak 3y ago
I'm dealing with a very large partitioned table (several millions of inserts per day) and i solve this by running `vacuum analyze` nightly which somehow solved 99% our read issues Prior to that, we tried various indexing strategies, de-normalizing some of the data, but ultimately i found that weird plan inconsistency to be solved by the vacuum job
sgarland 3y ago
Not natively. There is pg_store_plans [0] which I haven’t used, but looks interesting. Most of the time, query flips are due to inaccurate statistics over time. Try changing the default statistics target for the affected column. [0]: https://github.com/ossc-db/pg_store_plans https://github.com/ossc-db/pg_store_plans
polishdude20 3y ago
I'd love to have that. At a previous gig a query ballooned up to taking 30 seconds up from 1 second. The reason was the query planner decided to use a different plan. Any statistics update didn't work. Finally I tried just swapping the join order. The plan changed all was well. Until the planner decided to change the plan again...
timacles 3y ago
No offense guys, but just get a real DBA. Postgres doesnt just randomly do things and will always produce the same result given the same circumstances. Either you werent running routine maintenance and your statistics are messed up, or the statistics arent properly configured or your data is just poorly stored.

chrsig 3y ago

> Imagine if SELECT field1, field2 FROM my_table was faster than SELECT field2, field1 FROM my_table. Why would it do that? I have no idea to this day, and I don’t even want to know. > I don’t even want to know. I feel this. I'm working on a team where I have significantly more tenure at the company than the other engineers on the team, and they'll often ask me questions starting with "why". Normally, I love to encourage this -- curiosity is an amazing quality to have in coworkers. There are still some times when I just need to look at them like I'm dead inside and warn them that it's not worth their time on this earth to find out.

gigatexal 3y ago
See this is what I find interesting. Some do others just say the DB is this idiosyncratic thing that sometimes does Z and sometimes does Y.

deleted 3y ago

[deleted]

lakomen 3y ago

I've been thinking recently, well for a longer time. The next project will be like this: I will name the endpoints PagenameFunctionality. E.g. /IndexGetMyProfile There will be global scope functions or endpoints but many scoped endpoints. And there I will do the proper queries just for this use case, and so on for each new use case. In the context of SPA/PWA/TWA I think this is a good approach. Graphql is too complex on the client and RESTful too vague.

cess11 3y ago
Can call it Grammarless RPC.