Article

Why Your 200,000-Row Backend Job Takes 30 Seconds — and How to Fix It

Why Your 200,000-Row Backend Job Takes 30 Seconds — and How to Fix It

A developer recently described a familiar kind of pain on the Chinese tech forum LINUX DO: a payroll settlement job covering roughly 200,000 registration records took about 30 seconds to run, and rolling it back took another 20. The stack was NestJS, Prisma, and PostgreSQL, and batch SQL, indexes, and some logic optimization were already in place — yet the job still crawled. Transactional consistency had to hold, and every change needed a full audit history.

First, find out which part is slow

Before touching a single line of code, measure. "The settlement is slow" can mean three very different things: the database is slow to read the data, slow to compute the results, or slow to return them — and each has a different fix.

PostgreSQL's EXPLAIN ANALYZE is the starting point. Run the settlement query with it and look at where the time actually goes. If most of the time sits in a sequential scan over a table that should be indexed, you have an indexing problem. If the time sits in a sort or a hash join over hundreds of thousands of rows, the query shape or the data model may be the issue.

Stop updating one row at a time

The single most common cause of slow bulk jobs in ORM-based code is the row-by-row loop: fetch a record, compute something, save it, repeat 200,000 times. Each iteration is a network round trip, and the connection pool drains fast.

Prisma's updateMany() sets the same values on every row it matches, so it cannot assign a different computed value to each row — though per Prisma's docs, the bulk operations do run as a single transaction. When every row needs a different value, the standard approaches are:

  • Group rows that share a value. If many records end up with the same result, one updateMany per group replaces thousands of single updates.
  • Use a single raw SQL statement. A CASE expression can assign different values per row in one statement, and on PostgreSQL, passing arrays with UNNEST or joining against a VALUES list is faster still. Prisma exposes this through prisma.$executeRaw when the ORM's API cannot express it.
  • Stage the data, then merge. Load computed results into a temporary table — PostgreSQL's COPY command is the fastest way to load large amounts of data, according to the official docs — then run one UPDATE ... FROM against the staging table.

Any of these turns tens of thousands of round trips into a handful of statements while preserving transactional consistency: one statement, one transaction, one rollback if anything fails.

Make the database do less work per row

Even a single well-written bulk statement can be slow if the database is doing unnecessary work on every row it touches. PostgreSQL's documentation on populating a database spells out the main levers:

  • Indexes are a tax on writes. Every index on a table must be updated when a row changes. For a bulk job, temporarily dropping non-essential indexes and recreating them afterwards can be dramatically faster than updating them incrementally — the official docs recommend exactly this for large loads. Recreate them with CREATE INDEX CONCURRENTLY in production so other queries are not blocked.
  • HOT updates avoid index work. PostgreSQL can perform a heap-only tuple (HOT) update when only non-indexed columns change and there is free space on the page. Setting a lower fillfactor (for example, 70) on update-heavy tables leaves room for HOT updates.
  • Foreign keys and triggers cost time too. The PostgreSQL docs note that foreign key checks and trigger queues add overhead during bulk loads. If your settlement writes audit history through triggers, consider writing the history rows in bulk as part of the same transaction instead of one trigger firing per row.
  • Tune memory for the job. Temporarily raising maintenance_work_mem speeds up index creation and foreign key checks, and raising max_wal_size reduces how often checkpoints interrupt a large write. These are session-level settings — set them for the job, then revert.

Split the work into chunks

A single transaction that touches 200,000 rows holds locks for a long time, generates a large amount of write-ahead log data, and risks hitting statement timeouts. Splitting the job into chunks — say, 1,000 to 5,000 rows per batch — bounds all three problems. Walk the table with keyset pagination — WHERE id > $lastSeenId ORDER BY id LIMIT 1000 — which stays fast as the offset grows, unlike offset pagination. Recording progress after each chunk also lets a crash resume from the last completed chunk. And before any bulk operation that rewrites large parts of a table, make sure you have a tested backup — see our guide to cheap server backups that costs almost nothing.

There is a trade-off to be honest about. Chunking with per-chunk commits weakens the all-or-nothing guarantee: if chunk 47 of 200 fails, chunks 1–46 are already committed. That is usually acceptable if each chunk is itself atomic and the job records its progress — you can rerun the failed chunk or roll back the completed ones. But if the business rule genuinely requires all 200,000 rows to commit or none, keep one transaction and fix the speed another way.

Don't make the user wait for it

Here is the uncomfortable truth about the original forum post: even a well-optimized 200,000-row settlement may take several seconds, and several seconds is too long for an HTTP request. The right fix is often not to make the job faster, but to stop running it inside the request at all.

Move the settlement into a background queue. In the NestJS ecosystem, the standard choice is BullMQ, a Redis-backed queue library: the HTTP handler enqueues a job and returns immediately, while a worker runs the settlement in chunks. Running workers alongside your app changes what the server needs — if you are sizing a box for this, our guide to choosing a VPS covers CPU, memory, and disk. BullMQ supports retries with backoff and job progress reporting, so the front end can poll a status endpoint and show real progress instead of a frozen spinner, and the user gets a notification when the job finishes.

Two details matter when you do this. First, queues deliver at-least-once: a crash between finishing the work and marking the job complete means the job will be retried, so the worker must be idempotent — safe to run twice without double-settling anyone. A deterministic job ID derived from the settlement's natural key, plus a status column, handles this. Second, persist the outcome (completed, failed, retried N times) on the settlement itself, so support staff can answer "what happened to my settlement?" without digging through queue internals.

When the table itself is the problem

If the settlement table keeps growing — a new batch of registrations every month, history never deleted — the problem may be structural. PostgreSQL's declarative partitioning splits one logical table into smaller physical pieces by range, list, or hash key. A payroll table partitioned by month lets a settlement query touch only the current month's partition instead of scanning years of history, and old partitions can be dropped with a single DROP TABLE instead of a slow, locking DELETE.

Partitioning is a design decision, not a quick fix. PostgreSQL's documentation is explicit that a regular table cannot be converted into a partitioned table — you create the partitioned table and migrate the data, or use ATTACH PARTITION to bring an existing table in as a partition. It is worth doing when the table is large and growing, and overkill when the real problem is a missing index or a row-by-row loop.

A practical order of operations

StepWhat to doWhen it helps
1. ProfileEXPLAIN ANALYZE the settlement queryAlways — do this first
2. Batch the writesReplace row-by-row updates with updateMany, CASE, or a staging tableLoop-based code, the most common cause
3. Trim write overheadDrop and recreate indexes, enable HOT updates, tune maintenance_work_memBulk writes on heavily indexed tables
4. Chunk the job1,000–5,000 rows per chunk with keyset paginationLong transactions, timeouts, lock contention
5. Queue itBullMQ background job with status polling and notificationAny job too slow for an HTTP request
6. PartitionRange-partition by time or another natural keyTables that grow without bound

The forum thread that inspired this article ended without a single definitive answer, and that is the point: there is no single fix for a slow bulk job. The developer's 30-second settlement was probably slow for two or three reasons at once — a loop here, an index there, a synchronous request holding everything together. Work through the list in order, measure after each step, and stop when the job is fast enough.

Sources and further reading

Keep reading
Nexus: Turn Your Web AI Subscription Into a Local Coding Agent
Read next →