Programming

This is when you paginate. It's pretty cheap for the database to grab a limited amount ordered by certain criteria, you'd just need to make sure your indexes are correct.

"disk reads" are unavoidable. It's finding the data in the first place that's expensive. In an appropriately indexed database, reading a sequential range is extremely efficient. Rather than reading 10,000 times from a hash table, it's like reading a single table into memory, which is possible because you know in advance that the data you're looking for is there.

Bear in mind that indexing a database can include the physical organization of the data on the disk. As a simplified example, if you choose a clustered index based on, timestamp, then selecting data between 2 timestamps is as easy as locating the endpoints and reading the data sequentially off the disk. (The reality is more technically complex, but doesn't involve much more physical work.)

You've got the right idea with your SQL example, that's pretty much exactly what N+1 would look like in your query logs.

This can happen when using an ORM, if you're not careful to avoid it. Many ORMs will query the database on attribute access, in a way that is not particularly obvious:

class User:
  id: int
  username: str

class Post:
  id: int

class Comment:
  id: int
  post_id: int  # FK to Post.id
  author_id: int  # FK to User
 

Given this simple python-ish example, many ORMs will let you do something like this:

post = Post.objects.get(id=11)

for comment in post.comments:  # SELECT * FROM comment WHERE post_id=11
    author = comment.author  # uh oh! # SELECT * FROM user WHERE id=comment.author_id

Although comment.author looks like a simple attribute access, the ORM has to issue a DB query behind the scenes. As a dev, especially one learning a new tool, it's not particularly obvious that this is happening, unless you've got some query logging that you're likely to notice during development.

A couple of fixes are possible here. Some ORMs will provide some method for fetching the comments via JOIN in the initial query. e.g. post = Post.objects.get(id=11).select_related("comments") instead of just post = Post.objects.get(id=11). Alternately, you could fetch the Post, then do another query to grab all the comments. In this toy example, the former would almost certainly be faster, but in a more complex example where you're JOINing across multiple tables, you might try breaking the query up in different ways if you're really trying to squeeze out the last drop of performance.

In general, DB query planners are very good at retrieving data efficiently, given a reasonable query + the presence of appropriate indexes.

@TauZero you would use a join so that one call would fetch all comment rows for that post:

`SELECT p.post_id, p.title, p.description, c.text

FROM posts p JOIN comments c ON p.post_id = c.post_id

WHERE p.post_id = 79`

This would return a list of all comments for post 79, along with the post id, title, and description also in every row. This isn't a perfect example of optimized SQL, and I have no idea if these are the correct table and field names, as I didn't look them up. But it's a simple example of joins.