
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.
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.
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:
updateMany per group replaces thousands of single updates.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.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.
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:
CREATE INDEX CONCURRENTLY in production so other queries are not blocked.fillfactor (for example, 70) on update-heavy tables leaves room for HOT updates.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.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.
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.
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.
| Step | What to do | When it helps |
|---|---|---|
| 1. Profile | EXPLAIN ANALYZE the settlement query | Always — do this first |
| 2. Batch the writes | Replace row-by-row updates with updateMany, CASE, or a staging table | Loop-based code, the most common cause |
| 3. Trim write overhead | Drop and recreate indexes, enable HOT updates, tune maintenance_work_mem | Bulk writes on heavily indexed tables |
| 4. Chunk the job | 1,000–5,000 rows per chunk with keyset pagination | Long transactions, timeouts, lock contention |
| 5. Queue it | BullMQ background job with status polling and notification | Any job too slow for an HTTP request |
| 6. Partition | Range-partition by time or another natural key | Tables 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.
COPY, index handling, maintenance_work_mem, max_wal_size.createMany, updateMany, deleteMany run as transactions.