1. It is pushing the entire id table back and forth through the network connection, bit by bit. Replacing with a join completely eliminates this.

2. A query with an IN clause is (probably) doing a hash join under the hood to calculate the result of the IN clause. So the junior's code is effectively submitting a join query over and over, each time with a slightly different tiny chunk of data, rather than asking for the joined data once and processing the result in batches.

It is also worth considering if the entire data processing pipeline can be in SQL, but I can't tell if that's the case from the blog post.

This does break down eventually but I feel this is another one of the several places where developers still sometimes subconsciously have an early-2000s view of the world, as if all relational databases start panting and sweating if you ask them to return more than a couple hundred rows of any kind. No, set them up with the right indexes and foreign keys and they'll happily stream gigabytes at you, without the CPU even hardly doing anything. It's just as likely to be the consuming code that is the bottleneck!

You get up to "big data" and this approach stops working but what constitutes "big data" has also gotten a lot bigger since the early 2000s. Even in the engineering-centric company I work for, a lot of engineers & management assume that things are "big data" way before they should.

For example with PostgreSQL you need to create a cursor, then FETCH NEXT 1000 over and over again in a loop. This is a bit of a pain, but is the difference between processing as data arrives, with only small buffers everywhere, versus waiting for all data to arrive before doing anything.

What exactly you need to do and how to make it work is very much database specific.

I'm not trying to promise that every database will stream a petabyte without a problem; I'm more trying to help people get out of an early 2000s mindset and if nothing else, check what their DB will do. A lot of old programmer's tales about how to baby old databases along are actively pessimal and unnecessary in 2022/almost 2023. Don't spend days writing code to correctly slice and dice a query into tiny pieces when you could just send it in one shot and get better performance in every way.

If the set of things in A that have ids in B is very small, then very little data is returned from the JOIN query, while a lot may be returned from the B query by itself.

(if that set of things is large, then you'll still want to batch the joined query as well, i.e. using find_each in rails. They're orthogonal requirements)

In the example the author gives the query cost is very likely dominated by finding matching rows in A. Where there is no index, then we can expect a full scan of A (or the index of A.id) for every batch of B.

This is the case no matter how many rows of B you are searching with; by running the query 50x you make this cost 50x greater. Using a join you pay it once.

In addition, and probably more to the point, the round trip database costs (serialisation, parsing, planning, scheduling, network comms) are going to dominate the actual query costs for something like this (unless A is exceptionally large).

Furthermore, the memory cost to the DB of the serialisation and parsing is likely to be much larger than just storing all those ids in their native format - and there would be no client memory footprint in a join. For the final result set the client can reduce their memory footprint by using a streaming result which every BigData DB supports, and most others too. If you are particularly concerned about client side memory it is best to either: do everything on the database, or manifest a temporary result table and batch out of that.

There are circumstances where the JOIN will be too expensive to do all at once. I've worked with what is claimed to be "BigData" for about 4 years and have had only a few situations like that; but none of them would be ameanable to a batching like this, and instead need much more complex architectural steps to make cheaper.

Why would you expect that there's no index? I have never seen a single database system where the most basic primary key A.id wasn't indexed. Instead, I would expect that you're correct below that cost of the query is dominated by fetching the rows from disk and serializing them—this is a linear cost that increases with the number of rows returned, so fetching 50,000 rows should be about 50x as slow as fetching 1,000 rows (especially as long as you're fetching them in some sort of block-cache-amenable order, such as in increasing ID order, so that you're seeking to sequential places on the disk most of the time instead of fetching just random blocks)

> In addition, and probably more to the point, the round trip database costs (serialisation, parsing, planning, scheduling, network comms) are going to dominate the actual query costs for something like this (unless A is exceptionally large).

Aside from a small overhead, serialization, parsing and network comms will all increasing linearly with the amount of data returned. 50,000 rows of data will be about 50x the serialization and network cost of 1,000 rows.

> Furthermore, the memory cost to the DB of the serialisation and parsing is likely to be much larger than just storing all those ids in their native format - and there would be no client memory footprint in a join. For the final result set the client can reduce their memory footprint by using a streaming result which every BigData DB supports, and most others too

Sure, I can absolutely agree that using a streaming result set would be the best of all possible worlds here. However, it does require you to keep a client connection open for 50x longer than batching would, which on many databases (e.g. Postgres), would lead to more memory usage and CPU contention then batching the result in a background job queueing system. This comes down to what % of your total pipeline is spent in the database in question compared to data processing or other databases—if only 20% of your job's runtime is fetching the rows from this database, then it's a bad idea to monopolize that DB memory for the much larger amount of time it takes you to process the entire result set, when instead you could be yielding that memory back to the system for other transactions to use. But if 80%+ of your time is spent in the database, then the small amount of time that other transactions would be able to reclaim wouldn't be worth the amount of fixed overhead from re-planning, re-executing, re-fetching the index from cache, etc. And obviously these—as you may have been able to guess, my experience here is rooted in OLTP workloads using Postgres, and I'm sure there are plenty of differences with BigQuery's architecture.

> Why would you expect that there's no index?

I don't. Whilst I didn't write it particularly eloquently, I included that it would be an index-scan if there was one. And like the full table scan, this is a 50 vs 1 cost (unless the querying ids are well sorted, at which point you'd maybe get a 5vs1 cost at best).

> I have never seen a single database system where the most basic primary key A.id wasn't indexed.

Nobody has said it was a primary key. In fact it very likely isn't. All we really know is that there were approx 50k rows selected from B; we do not know how many are matched in A.

As they are using BigQuery and the queries are taking such a long time, it would be reasonable to assume A is some large dataset clustered around some other value (e.g. timestamp). But that itself would be an assumption.

> [streaming] does require you to keep a client connection open for 50x longer than batching would,

It does not. It will be less time.

---

Looking at pg.

I'm trying really hard to see a your point. As far as I can tell, you're bothered by the working set memory of the query caused by the join exceeding a limit and causing contention - this is the only time the streamed join is worse than the batching. On an index join this would have to be a very large table.

As for CPU contention - its a non-issue.

There may be a point related to time-to-execute with respect to lock contention.

Regardless, if either lock or index memory contention are problems for you then you will still want to `JOIN` - just against a subquery/cte with limit and offset.

Roundtripping is not the answer!

But they didn't. Instead they made the database parse every single record, compile it, and then try to optimize it. Which will come up with a plan where you had to do index lookup after index lookup. That parsing and optimization overhead is probably most of your time. But even ignoring that, a single scan for `n` things in an index with `m` things winds up taking an average time `O(n log(m/n))`. Which is generally faster than the `O(n log(m))` of separate lookups.

This change saves a tremendous amount of work on the database, and therefore reduces contention for resources. That's database 101, and any competent DBA should be able to give you the lecture. As a programmer you might not understand how much of a difference it makes. But trust me, it does.

Now about data quantity. You're giving cargo cult advice on queries that is only sometimes going to be right. What is the actual tradeoff for find_each?

The one win is that you return limited data on each trip. 50,000 records really isn't that much these days, so I discount the win. But it can matter, particularly for memory constrained containers.

But what is happening inside the database if you fetch 50,000 records from a join, in batches of a thousand? As I understand, it uses limit and offset statements to figure out the result. But how doe that work?

First, it calculates the join to find 1000 records and returns them.

Second, it calculates the join to find 2000 records, throws 1000 away, and returns the rest.

Third, it calculates the join to find 3000 records, throws 2000 away, and returns the rest.

And so on until it has found a full 1,275,000 records, of which it has thrown away 1,225,000 and returned 50,000. Guess what this means for total database work required? And the behavior is fundamentally quadratic. If you have 10x the data to process, your database has to do 100x the work.

There are definitely a lot of use cases where you need to batch records. But your batch size should be as large as you can comfortably use. And you need to realize that you're trading off trading up front memory for time and more work inside of the database.

The next time that you find yourself having to go down the "optimization tree", I strongly recommend considering whether you're in fact trying to put a patch on a self-inflicted wound. Try proper joins, indexes, and a larger batch size first. See how much of a difference that makes.

Alternately take advantage of the fact that you know your tables in a way that Rails doesn't. Order the results by primary key. Every time you fetch a batch, record the largest primary key you returned. Then instead of offset/limit on the next query, use a limit and a condition on the key. This will eliminate almost all of the duplicate work in most situations, at the cost of having somewhat more fragile logic.

See https://news.ycombinator.com/item?id=34095480 for a more detailed discussion of where time is actually spent here, I think this short explanation glosses over a lot of important issues.

> As I understand, it uses limit and offset statements to figure out the result

You are incorrect. Your entire comment is based on a faulty premise. find_each uses an ordered primary ID column which can be queried efficiently using indexes.

But that said, your "detailed discussion" is going to be wrong for most databases that I've worked with. MySQL makes queries cheap. But PostgreSQL, Oracle, and so on make parsing expensive. Having to parse and try to optimize a good chunk of a MB of SQL is almost certainly more expensive than 50,0000 individual index lookups. (The tradeoff is that the other databases are likely to produce better execution plans if you run the same query over and over again.)

With a join, the database is able to do the join on Table A and Table B in place, using whatever indexes it already has built up. The only thing sent over and processed by the Python is the result set. Even if Table B becomes very large, only the result set is sent over the wire and processed in Python.

Without a join in the query, you're essentially having to replicate the kind of logic that already exists in the database engine, in Python. That is, the database engine is already doing chunking and parallelization for you.