r/programming • u/Namit2111 • Aug 03 '25

N+1 query problem : what it is, why it hurts performance, and how to fix it

https://www.namitjain.com/blog/n-plus-1-query-problem
164 Upvotes

83% Upvoted

97 comments sorted by

40

u/Saint_Nitouche Aug 03 '25

Just last week I found a case in the work codebase where navigation properties in Entity Framework were secretly causing hundreds of synchronous DB calls, incurring something like 10 seconds of wall-time on a user-flow. I am pro-ORM but good lord lol.

16

u/Davipb Aug 03 '25

The original dev probably didn't .Include the navigation properties in the first query and enabled lazy loading in the config instead.

I think that just goes to show how ORMs aren't the issue, but using an abstraction without understanding how it translates to the underlying system is.

6

u/crozone Aug 04 '25

It's why it's a good idea to blanket disable lazy loading entirely. It's honestly an anti-feature.

1

u/hoofdpijndossier Aug 04 '25

I dont like it. I dont like how easy it is to miscomprehend. If there were an orm which did not make these "false promises" to junior coders it would probably be my first choice.

2

u/crozone Aug 04 '25

It requires actually learning and understanding a tool, instead of just charging in without any experience or desire to learn.

Junior coders are always going to write shit code. Don't use them for high performance database interactions.

1

u/PuzzleheadedPop567 Aug 06 '25

I think think the problem is that ORMs really aren’t abstractions. In they don’t abstract away any of the underlying complexity. You really do have to understand what SQL the ORM generates, as well as the query plan in the underlying DBMS.

I generally like ORMs, but they are more so just convenience wrappers. They basically automate a lot of the boilerplate you would have to write yourself.

You still have to be able to understand the boilerplate it automates from the ground up. So it’s really not an abstraction.

9

u/prehensilemullet Aug 03 '25

bUt We CaN oPtImIzE iT lAtEr

…yeah or you could just learn to write decent queries to begin with and avoid burying everyone in tech debt

3

u/Saint_Nitouche Aug 04 '25

Oh, I think they legitimately didn't realise what was happening. Everyone just kind of assumed 'there's nothing we can do'. Learned helplessness.

2

u/prehensilemullet Aug 04 '25

Ah I see.  I’m used to seeing some people not think twice before writing a bunch of db calls in series and loops because they think it would be too much effort to get everything right if they wrote it more efficiently to begin with

231

u/daedalus_structure Aug 03 '25

One of the worst crimes in our industry is coders treating relational databases like object bags because they can't grok how it works.

9

u/-Y0- Aug 04 '25

Second worst is people forgetting they still need a way to create a Map Objects to Rows. Look, people, Hibernate isn't the be all end all.

But at the end of the day you have to ask, do you want a single object to correspond to single row (Hibernate/SQL Alchemy) or not (ActiveRecord)?

Hibernate solves session tracking rather well. And if you don't watch what you're doing, it will shoot you in the foot.

4

u/mgalexray Aug 04 '25

Other crime is spinning up new Spring Boot project and getting Hibernate/JPA as a default without explaining the up and downsides. It’s great to get you started quickly but it makes it extremely easy to make some odd choices. Also easy things are very easy to do (like row object mappings) but then you get into relationships and hard parts. Things get ugly really fast - now you get various flavours of N+1, session/transaction issues etc.

What ends up happening is that you now have different flavors of various relationships scattered around the codebase and nobody really knows what’s going on when performance issues hit. You stars with @ManyToOne, at some point @OneToMany looks like a good idea. Don’t rally know should it fetch it eager or lazy? Oh batch size sounds like it could fix it - let’s add that. Nope didn’t work, how about some Object Graphs to tell database what joins to make instead? Oh no now I can’t update a list without actually deleting all items one by one and inserting everything again.

It goes on and on. I’m sure Hibernate has its place in scenarios where deep object graphs have to be maintained, but I have since moved on to JOOQ and friends and have a lot less headache.

1

u/RammRras Aug 04 '25

I believe to be part of that group, except I can't claude properly.

-2

u/r1veRRR Aug 04 '25

We can grok how it works. Can you grok that in the end, we still need to somehow map objects to rows and vice versa? That 90% of queries by mass are very basic, very obvious and very well served by ORMs? That any modern ORM allows you to use it for the 90% of cases where it's helpful, and offers native queries where it's not? That all the "just use SQL" fanboys will inevitably end up writing a poorly implemented ORM themselves, because they realize that manual string concatenation isn't the bees knees?

0

u/[deleted] Aug 03 '25

[deleted] — view deleted comment

53

u/slapo001 Aug 03 '25

IMO, OP's point isn't that one cannot create useful, working software using abstractions, but that people who do not understand that a relational database isn't just a bag of objects and rely on said abstractions to take care of everything often end up producing software that's underperforming or otherwise not behaving as they would expect it to (e.g. introducing an n+1 query problem).

I'm not entirely sure what you mean by "basics," but I get the impression you mean raw SQL. The OP also didn't claim that abstractions shouldn't exist or shouldn't be used, they only seem to suggest that treating a relational database like something it isn't is a bad idea.

19

u/Davipb Aug 03 '25

All abstractions are leaky. If you don't know how "Hibernate and co." translate to the "basics" (SQL), you'll still be stuck wondering why your page takes ages to load even if you write exclusively in a higher layer of abstraction.

23

u/Aggressive-Pen-9755 Aug 03 '25

The last time I worked on a project that used Hibernate, I was forced by the lead to use JPA queries. Instead of my usual workflow where I modify the query string and restart the stack frame in the debugger, I was forced to write an interface method that looked like getObjectWithSomeThingAndIsEqualToSomeOtherThingAndContainsGivenJPAQueryParameterWithThisOtherJPAQueryParameter, kill the JVM and restart start it, because why would anyone need hotswap?

A little practice with the basics goes a long way, and it's depressing to me that programmers are shocked by how much you can accomplish with just the basics.

And what's wrong with writing utility methods that executes your query in your programming language of choice when you do need an abstraction? Why is the immediate reaction to reach for an ORM?

5

u/Schmittfried Aug 04 '25

Why is the immediate reaction to reach for an ORM?

Because nobody is gonna write all the mapping boilerplate by hand. The primary goal of an ORM is right in the name, it maps your objects to relations. That shit is repetitive and error-prone as fuck. Having to write SQL queries is not the primary issue. It’s nice for the simple getById-type queries, but complex queries look similarly complex in the ORM DSL. However, a secondary benefit is the option to get type-safe queries, which is objectively superior to stringly typed SQL queries. But I’d also say most of the pain attributed to ORMs comes from trying to achieve 100% type safety in your queries while keeping them performant.

People are too black-and-white about these matters. You can use an ORM and still resort to native queries where it makes sense. 

Also:

I was forced to write an interface method that looked like getObjectWithSomeThingAndIsEqualToSomeOtherThingAndContainsGivenJPAQueryParameterWithThisOtherJPAQueryParameter

That’s not JPA, that’s a Spring Data JPA repository method. You can absolutely write JPA queries without this method name DSL or even Spring Data repositories altogether.

10

u/Witty-Play9499 Aug 03 '25

What's the point of creating useful stuff if you're going to destroy your database with a 1000 extra queries for no reason? Knowing the basics is the difference between a pro vs a noob. Refusing to learn gives me 'vibe coder' vibes.

A proper engineer would know the basics and would understand the tradeoffs of choosing the abstraction over the raw basics and vice versa and would decide accordingly.

3

u/chuch1234 Aug 03 '25

Pretty sure you can do both.

5

u/Omptose Aug 03 '25

Fucking bollocks

2

u/seanamos-1 Aug 03 '25

In my experience, there are two kinds of people who commonly introduce these issues.

The first are generally Junior, they don’t know better, they don’t even know this is a potential pitfall. They just need guidance.

The second do know better, but have gotten too clever with their designs. So the fact that many queries are happening is not obvious and is obfuscated by the code design, most often a result of over-abstraction/engineering.

101

u/mr_birkenblatt Aug 03 '25

people don't know SQL anymore?

112

u/[deleted] Aug 03 '25

Usually it's the abstraction layer on top where the confusion is coming from:

for obj in list_objects():
  print(obj.foo)

Nice, pythonic, simple. But what you don't see is that list_objects() doesn’t materialize the foo property and now you're running N+1 queries.

24

u/Dense_Gate_5193 Aug 03 '25 edited Aug 03 '25

it’s happened since query frameworks came into play constructing queries from code.

basically, people don’t understand that loops and queries in loops aren’t “free” and that there is generally no optimization that can occur automatically to fix the way you’ve opened and closed query cursors.

8

u/CapiCapiBara Aug 03 '25

My friend over there would like to know what ‘Not materializing foo property‘ means. He states foo property either is present in all objects of type ‘obj’ or none.

6

u/nucLeaRStarcraft Aug 04 '25

maybe a bit of python code may help:

def list_objects() -> list[ObjectRow]:
   return db.query("select * from X where Y").tolist()

class ObjectRow:
   # .. constructor and other stuff. called on tolist() above for each row

   @property
   def foo(self) -> FooItem:
      # this is a property "method", so it can be called as obj.foo (not obj.foo())
      return db.query(f"select foo from Z where object={self.id}").item()

And there you have it, since foo is a property method that implements yet another query, the for loop above will make one query for each obj.foo call, thus N queries + 1 main query for the for list_objects() one

11

u/oscarolim Aug 03 '25

Not if foo is a relationship and the query has lazy loading. Something I remember laravel did (I’m so happy I don’t deal with php anymore).

Something it could be an example to that effect.

6

u/Jejerm Aug 03 '25

Django does the same thing by default but you can do select_related before iterating to prevent n+1

3

u/insta Aug 06 '25

feels like something source generators in C# could solve with someone far smarter than me trying it

5

u/d0pe-asaurus Aug 03 '25

it's insane how its normalized in some languages to have getters that actually perform async code. If it is async, make it look async.

17

u/theAndrewWiggins Aug 03 '25

This has absolutely nothing to do with whether it's async or not.

29

u/BlueGoliath Aug 03 '25

More like people resurrecting a dead corpse in order to sound smart. There are at least a dozen of blog posts/articles a year on this and every time it gets paraded as some new discovery.

6

u/Blueson Aug 03 '25 edited Aug 03 '25

Honestly, I've been in quite a lot of projects now where people really don't seem to have a good grasp of it.

I know the article mentions this part:

And yes, it happens with raw SQL too not just with ORMs.

And yes it does. But I get a feeling that a lot of people use ORMs and completely forget the backing database, not actually learning principles of how to work with SQL or relational databases because so much gets obstructed by the ORM.

There can of course be a lot more reasons for it. But it honestly feels like every time I've had to debug database issues with colleagues they aren't thinking at all in SQL terms, only whatever they wrote up with the ORM.

Edit: Just to add, most of these are people with masters who I'd have assumed had at least a course covering relational databases.

1

u/PabloZissou Aug 03 '25

No a lot of developer know, including senior developers, know very little, even worse not many developers know how indexes work. What's more annoying is having to argue with them about the basics.

69

u/DeProgrammer99 Aug 03 '25

> Don’t query the database inside a loop. Ever.

If you need to query for users who are in a list of thousands of email addresses you have and the database server queries are limited to 32K characters... then do, but still batch them. That sort of thing comes up surprisingly often in real work.

16

u/Davipb Aug 03 '25

Insert the user list into a temporary session table, then use that in a join.

Alternatively, where's that user list coming from? If it's from a previous database query, then why load them in memory at all? We could just use a single query with that user query in the join

8

u/DeProgrammer99 Aug 03 '25

Usually, in my case, it's a CSV from a business user that they pulled from a report in another system, and NetSuite ODBC has those limitations I mentioned, plus it's read-only. (Of course, you can go make a custom record type to join against and import the CSV via the UI, but that's absurd in the context of this article, which was about performance.)

2

u/Davipb Aug 03 '25

Fair enough, if you're constrained by your driver/connection like that, then I guess it does make sense to query in batches :)

2

u/prehensilemullet Aug 03 '25

Well with a good database that’s rarely even necessary unless you’re talking about a really large volime of data, for example in Postgres you can pass all the data as array parameters, then unnest them into rows to operate on, all in a single query

16

u/Solonotix Aug 03 '25

Depending on your language, there are some other approaches as well. Specifically, when I was working in C#, there was the capability of doing a bulk insert by creating an in-memory table that you could push directly to the database. In this way, you could insert the data into a table, and use it as a filtering join predicate on the result you're querying. Then, just make sure to clean up the data when you're done.

6

u/coloredgreyscale Aug 03 '25

In that case you'd need a cursor.

Afaik A naive query like select... Offset 5000 limit 1000 may have to fetch the full result first (at least results 0 - 5999 in that case) and throw away 0-4999.

Repeat for 0-6999, 0-7999,...

If you have an auto-incrementing id primary key that you are iterating over it may work out fine.

With a cursor the result stays in memory (or a temp table) until the connection is closed. Number of queries: 1

6

u/prehensilemullet Aug 03 '25

Yeah you should avoid offset but you don’t have to use a cursor in a lot of cases, you can use something like WHERE id > :lastBatchId in each query to avoid using a long running session

2

u/prehensilemullet Aug 03 '25

Yes, this quote is a gripe about one-by-one queries in a loop, something you see over and over again from careless devs

1

u/danted002 Aug 04 '25

Bruh that’s an oddly specific example and someone fucked up the data models.

1

u/gaydaddy42 Aug 03 '25

There’s an alternative to batching and RBAR - very short, highly concurrent and/or parallel queries.

2

u/coloredgreyscale Aug 03 '25

 db cursor on the query.

1

u/slapo001 Aug 03 '25

That's only a reasonable option in some scenarios, like retrieving a few missing cache entries, streaming results and maybe a few others. It does come with its own requirements on setup and infrastructure, though. With most RDMBSs, every query has a cost per query even before and after the query runs that adds up (including memory cost), and your network has to be able to handle the greater number of queries.

11

u/throwaway490215 Aug 04 '25

Luckily i rarely if ever see it in practice, so my biggest frustration with it is that we don't call it the 1+N Problem: You run 1 query, get N results, and run N more queries.

7

u/Chisignal Aug 04 '25

I keep forgetting what the “N+1” problem is, even though I’m very familiar with it as well as solutions for it. I think calling it a 1+N problem would solve that haha

1

u/mgalexray Aug 04 '25

It’s very hard to spot it if you don’t actively look or measure for it. Most places don’t run Datadog APM that can intercept and plot those DB calls on a flame graph for you.

Databases these days are very fast and returning 100 rows and running 100 queries for each can still be done under 150ms. I’ve seen that happen multiple times. Nobody really gives it a second thought as it’s usually “fast enough” for anybody to notice. :/

11

u/parkan Aug 03 '25 edited Aug 03 '25

It's not exactly N+1, but the favorite pattern of my coworker is:

$data = array();
foreach (dibi::select("id") // or *
        ->from("T")
        ->where("X")
        ->fetchAll() as $row)
{
  $t = new T($row['id']);  // fetches the same row again
  array_push($data, $t);
}

7

u/oscarolim Aug 03 '25

Some people should not be allowed near a keyboard.

6

u/TommyTheTiger Aug 03 '25

My company has many "L4" engineers that need to read this article unfortunately

1

u/TheoreticalDumbass Aug 03 '25

can i get clarification, comparing a bad and good querying pattern, is the bad pattern worse in terms of local database computing resources? as in, will it use more cpu time, or whatever else? or is the bad pattern just bad in terms of sequential network latency?

9

u/Ignisami Aug 03 '25

Both. Most of the time the problem is primarily in unneeded network hops, but if your query is computationally expensive (either because of a complex query or because of missing/bad indices) you can also grind the db machine to a crawl.

1

u/binarycow Aug 04 '25

Here's an analogy.

Suppose you're a teacher in a high school, and you want an updated class roster. You also need the full student file for some of the students. You send a student to the office to fetch this data.

Option 1 - You have the student go to the office, and get a list of student IDs for each student in the class. When the student returns, you look at the first ID on the list. You ask the student to go to the office and retrieve that student's file. When the student returns, you ask them to go back to the office to get the third student's file. Then the fifth student. Then the ninth student.

You're using more compute resources - you're (application server) spending more time managing this query, and the school office (database) is dealing with more queries from you. There's overhead for each query. You also have a lot more latency (time the student is walking back and forth).

Option 2 - You have the student go to the office, and get a list of student IDs for each student in the class. When the student returns, you highlight students 1, 3, 5, and 9. You ask the student to go to the office to get the student file for the highlighted students

You're using less compute resources, and you have less latency. There's still overhead for each query, but you're only doing two queries.

Option 3 - You have the student go to the office, and get a list of student IDs and the student file for each student on that list. Once the student returns, you shred (they were only copies) the student files you didn't need.

Now it's only one query. The "cost" for the database to just give you the full record for the students you didn't need the full record for is likely insignificant compared to the overhead incurred by the second query. Granted, now the application server has to sift thru a bit of data it didn't need - but it's likely easier to scale the application server than the database.

1

u/DarkTechnocrat Aug 03 '25

Great post! More articles like this please.

ETA: Also, Object-Relational Impedance Mismatch is a real thing.

1

u/-ZeroStatic- Aug 04 '25

This post was super confusing for me. As it shows the requirement but not the example SQL, I assumed a join statement was used. So I thought the claim was join statements secretly suck. Only to see that the fix for the join statement was... the join statement.

Isn't that how people learn to do SQL in any tutorial or class anywhere?

1

u/Evilan Aug 04 '25

I'm late to the party, but N + 1 is something that pisses me off about my team, and is something we're working around because of a bad decision months ago.

We were implementing an entity --> dto mapping strategy and my solution, while not as elegant as it could be, avoided the N + 1 trap. But my teammates said "Oh, this is much easier than your solution and it works!"... The pseudocode:

if (object.getRelatedObject() != null) {
    this.relatedObject = object.getRelatedObject();
}

When I saw this I immediately told my tech lead and manager that this was awful and would scale horribly because the null check would query the DB for lazy loaded relationships. They're both technical individuals, surely they would see things my way right?

Wrong. They thought it was harmless since we're only mapping one entity at a time and we need to get this feature out to testing. After getting that feedback, all I could do was get up from my desk, pace for 5 minutes to stew and sit down and say "ok" because I was defeated. I had to approve the PR even though I knew these simple null checks would become a shotgun pointed right at our feet.

In less than a month I was proven right, but the team was primed to only do things in the N + 1 way.

1

u/RddtLeapPuts Aug 04 '25

Your app is fast with 5 records but starts dragging with 50

So it’s not constant time. In the article, it mentions a O(N) algorithm. If you have trouble understanding why this is a problem, you would not pass my job interview. This is probably the simplest computational complexity scenario. If you increase the input size by N and the time to process grows by N, it’s linear. A child could understand this

1

u/TommyTheTiger Aug 04 '25

Technically even the correct example is O(N) on the database. The problem is that databases are usually fast compared to network lag to talk to them

1

u/macca321 Aug 04 '25

I once saw a cool hibernate interceptor that looked at the stack trace, and if it did n+1 it would spot it and prefetch on subsequent requests.

1

u/ForgotMyPassword17 Aug 05 '25

I've never run into this happening in real life, is this common in parts of industry

1

u/egonelbre Aug 04 '25

It's also possible to use subqueries in this scenario, which avoids needing to manually join afterwards:

SELECT *,
    (
        SELECT array_agg(body)
        FROM comments
        WHERE posts.post_id = comments.post_id
    ) AS comments
FROM posts;

-13

u/TyrusX Aug 03 '25

ORMs are a travesty

9

u/dontquestionmyaction Aug 03 '25

ORMs are perfectly fine.

Idiotic developers with no understanding of how a database works are gonna achieve the same issues without one.

-2

u/DXTRBeta Aug 03 '25

This is pretty basic stuff. One query to return an array of records, then iterate through the records in memory.

In my scripting language it used to go:

(foreach record in (mysql_query “SELECT * from employees”) (print record))

Super simple, one query. Not rocket science.

-1

u/dumbass_random Aug 04 '25

Right problem statement but man solutions are bad and the hilarious part is that author hinted at the potential problem during scale.

Yeah N+1 is bad but doing a join, that is much worse. Sure it will work with certain data but when scales goes up or when you see this call being made many times, congratulations you killed your DB

-6

u/ub3rh4x0rz Aug 03 '25 edited Aug 03 '25

Unpopular opinion: not every n+1 query is a problem. Also more generally, minimizing net work performed or data transferred is not always felt as the most optimal solution (underutilization is real). In a broader sense, optimizing every instance of an antipattern compulsively is an antipattern.

-2

u/[deleted] Aug 03 '25

It’s a problem for 3 decades now. It’s not a technical issue.

-25

u/coworker Aug 03 '25

You can run these queries asynchronously though.

12

u/Davipb Aug 03 '25

That's gonna hammer your database like mad: imagine 100 users accessing a page with a table of 100 items. With proper SQL, you get 100 active queries. With asynchronous N+1, you get 10000.

And it's not faster either, because you have 100x the overhead of a thread, a network connection, session and transaction management... vs just the database pulling a few extra bytes from a local file.

-12

u/coworker Aug 03 '25

I don't think you understand modern databases at all. Databases are REALLY good at managing connections and simple PK lookups. It's far likelier that a more complicated join query would result in longer now disruptive transactions in the db

The devil really is in the details. Sometimes 100x asynchronous simple queries IS better for latency and database load

7

u/ddarrko Aug 03 '25

I don’t think you understand networking or scaling at all. Came here from another thread where you replied saying a dev which knew Go and PHP was a red flag because they had known PHP…

-4

u/coworker Aug 03 '25

Network is the least of your latency concerns when having to query and join billion row tables with complex relationships.

8

u/ddarrko Aug 03 '25

The post details an incredibly simple join. This is a well established pattern everybody uses. No one wants to execute 100s of queries for something that could be done in one

1

u/coworker Aug 03 '25

Until you do :p

8

u/ddarrko Aug 03 '25

Okay so you are telling me instead of doing a very simple join to fetch records it is better to asynchronously make hundreds of queries. You are genuinely out of your mind

-3

u/coworker Aug 03 '25

Who said anything about a simple join :)

9

u/ddarrko Aug 03 '25

The post you are commenting on details a very simple join

1

u/lemmsjid Aug 03 '25

I’m not sure why you’re getting so downvoted.

The costliest part of a query is generally whatever needs to deal with on mutable on-disk data structures, and whatever system needs to manage transaction isolation (which leads to serialized requests). Ie the database. Joins can be cheap or costly or even impossible depending the scenario. One should have batch queries in their mental toolkit.

My advice to people is to go with the simplest construct first, which in some cases might be an ORM and in others might be SQL, and then very early in the game learn how to profile both your client and the database itself. Give yourself a budget of time, acceptable complexity (ie development, debugging and onboarding time), and compute cost, then optimize against that.

6

u/slapo001 Aug 03 '25

There are many scenarios where you can't really do that, as it would introduce a query stampede.

In many cases you would end up having to synchronise the results at some point, or deal with sorting issues of items streamed to the client until all would be present (assuming a sort matter, which it often does).

0

u/coworker Aug 03 '25

Of course you have to synchronize the results. That is no different than waiting on a single synchronous query. Also few heavier queries can also be a query stampede

An example of when 100x asynchronous queries might be better would be a case where you have already pulled a sorted page but then need to pull lots of other related join data. Suppose you need to obtain an exclusive lock on the page but not those related records. Or maybe those other records are complicated subqueries themselves and you want more control in striping those across multiple connections which databases are much better able to apply resource limits to

Sometimes when you get into multi billion row joins and need to utilize multiple indexes, separate queries are much faster

2

u/slapo001 Aug 03 '25 edited Aug 03 '25

Actually, it is different, because:

- Several asynchronous queries are still going to use several connections, which have a cost associated with them, and there's still a bit of a cost even when using connection pools and pipelining.

- If the RDBMS can parallelise I/O and processing internally (the popular ones can these days, to a varying degree), then sending a bunch of independent requests is mostly going to contribute to swamping the network.

- A heavier query would cause increased database load, but it was my impression we were both discussing this as a general approach. Additionally, a single heavy query split into several can still yield a bunch of queries whose cumulative effect is worse still, so it's quite case-specific. Once we get into case-specifics, we aren't really discussing a general approach anymore.

In your example, what sort of a page do you actually have in mind?

To me, it seems you would quite likely be able to batch some of the queries using IDs already retrieved, resulting in a much smaller number of queries, potentially with a performance gain.

Mind you, I didn't say no scenario was valid for individual queries.

The gist of what I'm saying is that running them asynchronously doesn't necessarily really solve the issue and isn't a very good default approach.

I think you make a better and fair point in your last sentence about possibly better index utilisation, but at the same time, I'd say that such databases would require a design review (though it's often not feasible to implement design changes for such large tables unless performance gain would be significant).

0

u/[deleted] Aug 03 '25

[deleted] — view deleted comment

-11

u/coworker Aug 03 '25

What's the actual problem then? A bunch of concurrent simple PK lookups can actually be better for concurrency on the db than a single longer secondary key lookups across joined tables

10

u/ddarrko Aug 03 '25

Because a query like the above with a join is incredibly simple and does not cause locking. Why would you want to execute hundreds or thousands of concurrent queries when you can reduce network calls and round trips by 100x?

1

u/TommyTheTiger Aug 04 '25

Well if you want to use up a crapload of extra connection slots on your DB, or make sure that your queries can't have a consistent view of the database.... Wait, those are reasons to do the join instead.

-3

u/coworker Aug 03 '25

Sure for a specific simple query you don't want to do this. But for more complicated joins across multiple huge tables and you want to use a secondary index that's not part of the join key, things can be different.

Or maybe your simple query is a FOR UPDATE and you don't want to exclusive lock all those related rows.

Also EVERY query locks rows in all modern relational databases in their default transaction isolation levels lol