Selecting a large set of information from a database

Question

So this is a topic I have never really tackled, so bare with me as I try to describe my issue and the scenario.

I have an API endpoint in my service that sends emails to a bunch of users. The set of users selected is based on the criteria in the payload in API request. It works well so far. There are new use cases that are now being brought up by the business that may realistically result in 10s of thousands of users being selected. How would you typically go about scenarios that may result in a huge entity selection from a DB? To give more information on this service, it is a NodeJs service with a Mongo database that is running on Kubernetes. Would you introduce new infrastructure to facilitate this sort of thing? I'm just afraid that with my current implementation, I'll run out of memory by simply trying to load the result set into memory.

Sorry for the abstract question. I'm looking for a generally direction on this rather than concrete answer. if I can expand on anything here, let me know.

Answer 1

Chunk it.

Without a use case that requires loading it all in memory at once my answer is don't do that. Rather, work out how many you can load at the same time performantly and stay under that number.

This is similar to pagination where you need the ability to ask for a set number of records starting at an offset or ID.

I see no reason you need all users in memory at the same time. If you have one reconsider this plan. Otherwise, load what you can, send it, then load some more.

Answer 2

If users are independent in terms of processing query results (sending email to one user is independent of sending to another) several strategies are worth exploring

Chunking

Indeed, is most simple one, I think. Just use pagination as @candied_orange suggested. This will save you some application memory, however almost certainly database load will raise. With paging you have to repeat query several times. But each query (especially deep page queries) will do almost the same amount of work as without paging. So overall database will do more work.

It is sometimes called deep paging problem. And it may or may not be a problem in your circumstances.

Streaming

You can read records from database and process them one-by-one. This will give your algorithm constant complexity in terms of memory. But there is a catch. Databases resources will be locked until you finish your processing. And processing us usually a lot longer than querying. This can cause lock timeout's, transaction rollbacks and other kind of concurrency issues depending on a database you using.

Offload streaming

Somewhat more advanced approach (more like variant of a streaming) is offload streaming. You stream data from database to a temporary file in one-by-one faction. Then release database resources and start streaming from tempfile.

Answer 3

Disclaimer: won't go into details about different ways to chunk and move through datasets. I'll rather stick to what could you do with MongoDB. For other DB engines, you have to figure out a way to do the same with a different set of features.

How would you typically go about scenarios that may result in a huge entity selection from a DB?

It depends on the DB engine and the available features. Given you are working with MongoDB, you might find cursors and cursor.batchSize to be more interesting than pagination (limit().skip()) because it implies less overhead (handling the pointer) and can reduce the network round trips significantly.

const query = {...};
const projection = {...};
const cursor = db.users.find(query, projection).batchSize(B);
for (let user = await cursor.next(); user != null; user = await cursor.next()) { 
... //do something
}

I'm just afraid that with my current implementation, I'll run out of memory by simply trying to load the result set into memory.

Then, you have to set limits. How much RAM is acceptable depends on the context, you will have to figure it out.

Once you have a number, tune the service by testing different configurations. For example

1. Set projection to fetch the minimum information required
2. Estimate the size S (in bytes) of 1 record based on the previous projection
3. Set a batchSize (B) for which product with S is lesser than L (RAM max size in bytes). But don't go all in with L, the service does more things and you want it to handle some concurrency, so make B to be proportional to L (1/2, 1/3, 1/4, ...)
4. Perform load tests¹
5. Get metrics
6. Repeat steps [3-5]
7. Compare

This is for a single instance of the service!

Given you are deploying on K8S, you could make B to be close to L (say 90%) and bound Kubernetes horizontal autoscale to memory. The new POD is likely to handle all the incoming requests while the old one is processing notifications at full capacity.

---

^{1: Try setting up a fake SMTP server so you can test the load and the behaviour of the service under different conditions. For example, when the SMTP server is not accessible}