178
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
this post was submitted on 15 Jun 2023
178 points (93.6% liked)
Programming
16687 readers
135 users here now
Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!
Cross posting is strongly encouraged in the instance. If you feel your post or another person's post makes sense in another community cross post into it.
Hope you enjoy the instance!
Rules
Rules
- Follow the programming.dev instance rules
- Keep content related to programming in some way
- If you're posting long videos try to add in some form of tldr for those who don't want to watch videos
Wormhole
Follow the wormhole through a path of communities !webdev@programming.dev
founded 1 year ago
MODERATORS
No it wouldn't, that's called the N+1 query problem and it can be avoided by writing more efficient queries
Could you explain more how this works? I see how you should be reducing the number of SQL queries from N+1:
down to 1 query:
(Or something like this, I don't know SQL sorry). But wouldn't the database still have to lookup each comment line record on the backend? Yes, they are all indexed and hashed, but if you have a thousand comments, or even ten thousand (that reddit handles perfectly fine!) - isn't 10000 fetches from a hashtable still slower than fetching a 10000-long array? And what if you've been running your reddit clone for years and accumulated so many gigs of content that they don't fit in memory and have to be stored on disk. Aren't you looking at 10000 disk reads in worst case scenario with a hashtable?
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.
@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.
"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.)
Right! The trivial organization would have been to store comments by timestamp. So in my above example, how would you appropriately index the database such that comments 5, 13, 42, and 57 are stored consecutively, even though other comments to other posts arrive in between?
If you want to optimize for loading comments in a single thread in a single community in a single server in a federation, then timestamp would be a bad choice.
A simple example of an index for this use case would be something like (ServerId, ThreadId, Timestamp). By the time you want to load comments in a thread, you know the server id and thread id.
Ok, so it is possible to do! I've always been suspicious of databases. Loading all comments in a thread is the only thing a reddit clone has to do right. For a popular thread, it may need to be done hundreds of thousands of times (ignoring caching). Everything else, like user pages, is extra. Yet with a database, if instead of a thread I wanted to display comments made every odd Tuesday that have the structure of a haiku, I could. All that power has to be paid for somewhere!
Maybe I'm just a boomer thinking in terms of spinning rust, when everything is SSDs and 128GB+ of RAM. I wonder - do you think reddit stores its entire 18 years of content in RAM, split or duplicated between shards? But I can't shake off the awe at the sheer throughput of contiguous read from disk. 10000 comments, 200 characters per comment = 2MB = done in 2ms. Don't even need 200-at-a-time pagination!
You could, but if you want to do it very efficiently and at scale, you would probably need to specialize your data access layer:
It's paid for in the logical organization that is enforced at write-time (or during a maintenance task like rebuilding indices or recomputing statistics), where millisecond responsiveness is not as important.
Lots of duplication across different layers to support different access patterns and reuse work between data retrieval tasks. You need to be able to efficiently access frequently requested data, ingest new data, synchronize data between the different layers, and provide a reasonable minimum efficiency for arbitrary requests.
Semi-related, here's a story about how Discord does it.
Great link, thanks!
Looks like Discord was using 177 nodes each with 4TB disk space running Cassandra (Java), and then in 2022 migrated to 72 nodes of 9TB disk space running ScyllaDB (C++). Switching to a C++ database and writing their services in Rust allowed them to finally end latency spikes from Java garbage collection. The messages are stored in buckets assigned by channel and time window. Buckets are replicated across 3 nodes, and are accessed using "quorum consistency". They were still having difficulties with "hot partitions" where many users at once all want to access the same bucket, leading to increased latencies. They solved it by putting a data service in front of the database that would detect multiple identical incoming queries and pool them together into a single database request. The nodes are still spending a lot of time periodically "compacting" their tables for better disk read performance.
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:
Given this simple python-ish example, many ORMs will let you do something like this:
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 justpost = 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.