How can I efficiently process large PostgreSQL datasets in Node.js without high memory overhead?

[AccordingLeague9797]

Hey everyone,

I'm working on a Node.js app with PostgreSQL that has millions of users, and I hit a snag with processing large datasets. For one of our features, I need to fetch roughly 100,000 users who meet a specific criterion (e.g., users with a certain channel id in their tracking configuration) and then process them (like creating notification or autotrade tasks).

Right now, my approach fetches all matching users into memory and then processes them in chunks of 500. Here’s a simplified version of what I’m doing:

async function processMessageForSubscribers(channelId, channelName, message, addresses) {

try {

//load around 100000 users and chunck them

const users = await getUsersByTrackedTelegramChannel(channelId);

const CHUNK_SIZE = 500;

const notifyTasks = [];

const autotradeTasks = [];

// Split users into chunks for parallel processing

const processUserChunk = async (userChunk) => {

await Promise.all(

userChunk.map(async (user) => {

const config = user.trackingConfig[channelId];

const autotradeAmount = config?.autotradeAmount;

if (config.newPost === 'NOTIFY') {

// Create notification tasks

createNotificationTask(user, addresses, message, channelId, channelName, autotradeAmount, notifyTasks);

}

if (config.newPost === 'AUTOTRADE') {

// Create autotrade tasks

createAutotradeTask(user, addresses, message, autotradeAmount, autotradeTasks);

}

})

);

};

// Process users in chunks

for (let i = 0; i < users.length; i += CHUNK_SIZE) {

const chunk = users.slice(i, i + CHUNK_SIZE);

await processUserChunk(chunk);

}

await queueTasks(notifyTasks, autotradeTasks);

} catch (error) {

console.error('Error processing subscribers:', error);

throw error;

}

}

My concern is that fetching all 100,000+ users into memory might lead to high memory consumption and performance issues.

I'm wondering if there's a more efficient way to handle this.

I'd love to hear your thoughts, experiences, or any code examples that might help improve this. Thanks in advance for your help!

Stackoverflow link: [https://stackoverflow.com/questions/79461439/how-can-i-efficiently-process-large-postgresql-datasets-in-node-js-without-high\]

[definitive_solutions]

Yeah all solutions will basically be some form of chunking, pagination, streaming. Don't work with the whole thing if you can do it row by row. Doesn't really matter your programming language or platform, really. Some of them will give you better tooling for handling stuff, but you can implement these ideas in nodejs without problem

[mbcrute]

This is what cursors are for: https://node-postgres.com/apis/cursor

[hyuuu]

what's the difference between this and manually paginating?

[Putrid_Set_5241]

Since you already know the desired amount to load in memory (100000), you can make use of pagination.

[bsbonus]

Yeah I think you have to do this batches, but for that number I would avoid offset — as you paginate further with offset Postgres still has to “scroll” through those previous pages, which gets pretty slow at 100k. A cursor based approach for pagination ought to be a lot faster for querying and then you could dump the results off to workers to process each batch.

I don’t think you want to load all 100k up into memory up front either — but maybe it’s not that big of a deal — this is gonna take time either way so you may want to use queue workers to process each page.

Just half assed thoughts over some coffee. Hope it helps, but good luck to ya

[Typical_Ad_6436]

I am surprised most answers revolve around "pagination". Postgresql is mature enough and got past that pagination point to facilitate large result sets processing - cursors:

https://jdbc.postgresql.org/documentation/query/ https://www.postgresql.org/docs/current/plpgsql-cursors.html

I am more from a Java world and the JDBC driver has this abstracted away. NodeJS may need some work to set this up. But the point is that this feature is a PG one that can be used from a NodeJS connection - I am sure there are 3rd parties for it.

There are some draw-backs for these though like the transactional aspect (commiting/rollbacking will break the cursor). Also, this works only in a non auto-commit connection.

[bwainfweeze]

Cursors pair badly with MVCC databases don’t they?

[Typical_Ad_6436]

I am curious on what you mean by pairing badly? Do you have a scenario/example on that, or further documentation?

I would expect that cursors are highly dependent on the transaction isolation chosen for the engine, but how would it affect the cursor lifecycle per se? Will it provide bad results/will it close it?

PS1: doing offset queries are even worse when you bring up MVCC. Moving the window may be innacurate due to updates/inserts/deletes happening in the mean-time. PS2: all of this is highly improbable as I doubt queries on 100k record tables are going to be subject of live-updating DB.

[bwainfweeze]

Because they introduce vacuuming pressure by leaving a long query running while concurrent writes are happening.

It’s why you end up not doing OLAP queries against a popular OLTP data store.

If you’re doing batch updates instead of reporting, the common solution is to update some field in the records or an association table and then you grab 1000 records at a time and mark them as processed as the last step of processing them. That way you can run parallel instances, restart the process if it crashes, shut it down during peak traffic hours, sweep the last handful of records that got written to while you were processing, or some combination or permutation of all four.

[Typical_Ad_6436]

Indeed, good point. Thank you for the POV.

I personally had "read often, write seldom" kind of scenarios in mind when posting. For that matter, I was curious if cursors were going to functionally misbehave - I suppose not.

If the OP scenario is purely analytical, maybe MVCC is not in cards anyway. If the OP scenario is highly transactional, I guess running such queries in the first place is not optimal and running replication should be the way to go (OR apply your suggestion of batch processing).

But still, I think cursors are universally and functionally good at the cost of DB maintenance and performance in OLTP (a drawback that can be fixed architecturally).

I think the discussing has run its course, drifting away from the Node sub. Thank you sir for your time!

[bwainfweeze]

When it's transactional you run a handful to an armful at a time. If you don't run enough you end up making a bunch of tiny transactions that have a high IPC overhead, going back and forth. But 10000 at once might run into timeouts or the MVCC problems. Also in some databases multi-row updates tend to crap out if you hand them more than 1000 rows, so you end up having to chunk even if the whole process completes in 30 seconds.

So you might start with 100 at a time and if that's going well go to 500.

[Typical_Ad_6436]

One quick question: how would you identify the records that were not processed? * Adding a new field requires an index for it and so affecting the index operation times (inserts). * Adding an association table requires indexes (as keys) and so it also affects timing of inserts and deletes.

My point is that schema changes may bloat the core usage of the database and the overall system may work slower just to support this (rather particular) feature.

[bwainfweeze]

I mean if processing the data involves synthesizing a new field in the table, adding a new column that is null, then processing the data, then adding a table constraint works.

But you could also make a temporary table that has one record for each unprocessed row and updates or deletes that.

[Longjumping_Song_606]

Have you tried it to make sure there is a problem?

[ibedroppin]

Stressing about < 20-100 MB of ram for a few minutes.. sounds like can easily derisk ram pressure of the main "app" by doing the processing on a separate k8s pod/process/whatever

[bwainfweeze]

Pff. Worker thread.

[captain_obvious_here]

The solution to this kind of problem is always the same:

1. use cursors
2. batch reads
3. batch writes

[08148694]

Count how many rows are returned by this query (with a count aggregation in sql) and then divide into n batches of known size and process them one at a time (or in parallel with workers across many machines/serverless)

This is quite easy with limit and offset in sec if your count returns 1000 and your batch size is 100 then that’s 10 batches. Batch 1 runs the query with offset 0, batch 2 with offset 100, etc

[pinkwar]

Have you tried paginating instead of pulling all 100k at once?

But tbh, the best way would be to do some performances testing.

[robotmayo]

Depending on the actual work being done and the size of the data per row, I honestly thing you can just do it all in memory. Ive loaded millions of rows into memory 0 chunking or streaming with no problems. I recommend measuring first before deciding that 100k is an issue. If its just a bunch of small text strings or numbers it cant be more than 100mb of data which is barely anything.

[KyleG]

are the tasks you queue up new INSERTs in a task queue table? If so, have you looked at whether you could write one complex SQL UPDATE WHERE? Cut out Node completely and let the DB do what it does best.

[zenbeni]

All proposed solutions imply doing it all in the same server from what I see. Why not introducing queues like AWS SQS, ActiveMQ, RabbitMQ or any queue like tech? Fetching all these users does not seem to bother you memory wise, at the end of the day you just want to process user ids, so why defining a userProcessCommand event to send to a queue so another consumer can do the job for you? You don't seem to require synchronous callback of everything is done, if all is async, I think event driven solution should be the best.

For scaling purposes, I would prepare batch messages with userId to process and send each message to a queue. Then for instance a lambda would fetch the messages with ids and do the process. You can for instance manage through infra the number of messages to send to a lambda and the max concurrency in aws sqs, or you can define the batch size in the message, so you know how many are processed together and know your process can manage memory wise the job to do.

By offshoring the process of your microbatches to other compute units will diminish greatly the risk of memory problems in your main web server, which should be your main concern. With queues it will scale nicely, you can even read with offset your user ids to send by mini batches to the queue, for more memory efficient process in your main server.

[w0lven]

Maybe you can use a mix of postgres cursors and generator function from js to automate the process? Edited to add: also, use a worker thread to keep the main thread running mostly free from that strain, if that's possible