Mitigating PostgreSQL Write Lag and Execution Hangs During High-Volume n8n Sub-Workflow Fan-Out

n8n Labs
1

### Part 1: The Real-World Pain / Symptom

You push a heavy sub-workflow fan-out—say, 10,000+ items processed concurrently—expecting the execution pipeline to clear in a few minutes. Instead, your n8n UI execution history shows workflows stuck in a perpetual ‘Running’ state for up to 24 hours.

Yet, when you check individual node logs, the processing time per item is actually blazing fast (around 200ms). Your CPU and RAM are under-utilized, but your self-hosted n8n instance feels entirely unresponsive. Jobs pile up, webhooks time out, and your monitoring alerts start screaming.

Part 2: The Architectural Root Cause

The bottleneck isn’t your logic, and it isn’t your CPU. It’s **PostgreSQL write contention and bloated execution state persistence**.

By default, self-hosted n8n writes every single execution state, node input, and node output directly into your PostgreSQL database (`execution_entity` table). When you fan out 10,000 items concurrently, n8n attempts thousands of simultaneous write operations and index updates on the database.

PostgreSQL quickly bogs down in lock contention, connection pool exhaustion, and WAL (Write-Ahead Log) flushing limits. The execution thread is blocked waiting for database write acknowledgments, causing the UI and execution queue to freeze while the database struggles to clear its write queue.

Part 3: The 3-Step Architectural Fix

To handle high-volume fan-out without choking your database, you must decouple data persistence from execution speed and control concurrency.

  1. **Implement Chunking and Batching via the Loop Node**
  • Never dump 10,000 items into a parallel sub-workflow simultaneously.
  • Insert a **Loop Node** or **Split In Batches** node upstream. Process your payload in controlled batches (e.g., chunks of 100 to 250 items).
  • Add a microscopic `Wait` node (e.g., 50ms–100ms) between batches to allow the database I/O queue to drain.
  1. **Tune PostgreSQL for High-Throughput Write Operations**
  • Your default `postgresql.conf` is optimized for general web apps, not high-frequency event streaming. Adjust these parameters in your container/server setup:
  • Increase `shared_buffers` (typically 25% of total RAM).
  • Increase `effective_cache_size` (50% to 75% of total RAM).
  • Tune `wal_buffers` and set `checkpoint_completion_target = 0.9` to smooth out disk write spikes.
  • Ensure your database is running on fast NVMe storage with high IOPS.
  1. **Aggressively Prune Execution History Data**
  • Stop keeping execution logs forever. Set environment variables in your n8n deployment to automatically purge successful executions:
  • `EXECUTIONS_DATA_PRUNE=true`
  • `EXECUTIONS_DATA_MAX_AGE=168` (keep data for only 7 days)
  • `EXECUTIONS_DATA_PRUNE_MAX_COUNT=50000`
  • This keeps the database index small and prevents bloat on the `execution_entity` table.

Part 4: Open Community Close

Are you seeing execution locks or database lag on your self-hosted n8n instance during heavy AI or data ingestion pipelines? Drop your infrastructure specs (RAM, DB type, item volume) and current bottlenecks in the thread below, and let’s troubleshoot your queue configuration together.

1 Like
2

Good breakdown of the write contention problem. A few settings that tend to help in this situation:

  1. Reduce what gets stored: set EXECUTIONS_DATA_SAVE_ON_SUCCESS to none for high-volume workflows and keep failed executions only, so the database writes far less data per item.
  2. Turn on pruning (EXECUTIONS_DATA_PRUNE with a short max age) so the execution table does not keep growing, and check that PostgreSQL autovacuum is keeping up with that table.
  3. Avoid one sub-workflow execution per item. Batch items with Loop Over Items (for example 200 to 500 per batch) and let each sub-workflow process the batch, which cuts the number of execution records dramatically.
  4. For sustained load, use queue mode with Redis and separate workers, so the main instance is not handling every execution itself.
  5. If items carry large payloads or files, store binary data on the filesystem or object storage rather than in the database.

It would also be interesting to see how execution hangs behave once the database writes are reduced, since that confirms whether contention was the main cause.

1 Like
3

Good breakdown. One additional point worth considering is the difference between workflow processing time and persistence time.

In my webhook reliability testing, I focused on concurrent requests, duplicate-event prevention, and database-backed idempotency. It highlighted how important it is to measure not just execution speed, but also how reliably the system handles concurrent writes and retries.

For high-volume fan-out, I’d also investigate database connection limits, transaction duration, and execution-data volume before changing PostgreSQL parameters.

Batching and pruning can help, but the actual bottleneck should be confirmed through database and n8n metrics.

1 Like
4

You hit on an absolute killer that often gets overlooked: binary data persistence. Storing large binaries directly inside PostgreSQL JSON blobs (execution_entity) causes brutal WAL (Write-Ahead Logging) bloat and destroys the PostgreSQL buffer cache hit ratio. Flipping N8N_DEFAULT_BINARY_DATA_MODE=filesystem (or offloading to S3/MinIO) in tandem with EXECUTIONS_DATA_SAVE_ON_SUCCESS=none is usually the single biggest win for keeping PostgreSQL memory-resident.

Also 100% agreed on the batching pattern. Sub-workflow instantiation carries a non-zero overhead in n8n. Spawning 5,000 sub-workflows for 5,000 items is a guaranteed freeze; chunking them into batches of 250 via Loop Over Items reduces that overhead by ~99%.

5

That distinction between processing time vs. persistence time is spot on. In many cases we see in production, the actual DAG (nodes executing logic) completes in 100–200ms, but the execution hangs for 5–10 seconds because the worker is waiting to serialize a massive multi-megabyte execution state to disk. To the user, it looks like an execution freeze, but to the system, it’s purely I/O wait.

Your point about connection pool headroom is also critical. When scaling n8n workers, people often forget that each worker process maintains its own pool. If you have 4 workers with a pool size of 50 each (DB_POSTGRESDB_POOL_SIZE=50), you can quickly saturate PostgreSQL’s default max_connections=100, which leads to silent queue stalls that look like workflow bugs.

Appreciate both of your additions here, this gives people a complete, end-to-end checklist from workflow design down to database I/O!