Large Batch Updates Efficiently in PostgreSQL with n8n?

Questions
1

HELLO
I’m working on a workflow that processes thousands of records at a time, and I’m trying to find the most efficient way to update PostgreSQL without hurting performance.
UPDATE workflow_jobs
SET status = ‘completed’
WHERE job_id = 1001;
I’m wondering if batching updates is a better approach when dealing with large datasets.
Do you process records in batches or all at once?
What’s a good batch size in production?
Have you noticed any performance or locking issues with large batch updates?
Any PostgreSQL or n8n best practices for handling high-volume updates efficiently

Describe the problem/error/question

What is the error message (if any)?

Please share your workflow

(Select the nodes on your canvas and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow.)

Share the output returned by the last node

Information on your n8n setup

  • n8n version:
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app):
  • Operating system:
2

Hey @Gabby_Timothy, while you wait for a response, here are some things that might help:

Suggested resources

Automatically matched to your question.

Docs:

Forum:

@Niffzy - you’ve helped with similar issues before, can you take a look?

Automatically suggested by n8n’s community bot. It’s a pilot - please share feedback here.

3

Hey there @Gabby_Timothy

From what I’ve seen, processing records in smaller batches usually performs much better than trying to update everything in one large transaction.

UPDATE workflow_jobs
SET status = ‘completed’
WHERE job_id IN (1001, 1002, 1003);

Keeping each transaction short reduces lock contention and makes it less likely to impact other workflows running at the same time.

I would also recommend indexing the columns you’re filtering on and monitoring query performance as your data grows. The ideal batch size really depends on your workload, so it’s worth testing a few different sizes to see what works best.

smaller, well-managed batches tend to be more reliable and easier to scale than one large update.

4

Thanks but how do you usually decide on the right batch size in production? Do you start with a fixed number of rows and adjust based on performance, or do you use metrics like query time, CPU usage, or lock waits to determine the optimal size?

5

@Gabby_Timothy

I usually start with a reasonable batch size based on the workload, then adjust based on performance metrics. There isn’t a universal number because it depends on factors like table size, query complexity, indexes, and available resources.

I normally monitor: Query execution time
Lock wait time
Database CPU and memory usage
Transaction duration

If batches are too large, they can hold locks longer and affect other workflows. If they’re too small, you may create unnecessary overhead from too many transactions.

The goal is to find a balance where each batch completes quickly while keeping the database responsive. In production, gradual tuning based on real workload data usually works better than choosing a fixed batch size upfront.

6

Thanks for the explanation @Emmas