n8n + PostgreSQL 17 + PgBouncer SQLSTATE 42P05: 50-Webhook Benchmark & max_prepared_statements Fix

11 min readBy Yun Sanghun (M. Yun)

Putting PgBouncer in pool_mode = transaction in front of PostgreSQL 17 without enabling protocol statement tracking broke 71.9% of our concurrent extended-query transactions immediately. Out of 1,000 parameterized webhook writes fired across 50 parallel Node.js workers, 718 transactions crashed with SQLSTATE 42P05 (prepared statement "n8n_exec_insert_v1" already exists) and another failed with SQLSTATE 26000.

That pooler default failed immediately. Teams migrating busy automation stacks after hitting single-writer lock contention in our n8n SQLite to PostgreSQL migration guide often drop PgBouncer in front of Postgres to cap backend memory. Everything looks calm at idle. Then a 50-webhook burst hits, TypeORM or node-postgres issues named Extended Query Parse frames, and reused backend sockets reject duplicate statement names across multiplexed clients.

Fast Triage Fix: Keep pool_mode = transaction on PgBouncer 1.21 or newer and set max_prepared_statements = 200 (or MAX_PREPARED_STATEMENTS=200 in container environment variables). That single setting eliminated all 718 SQLSTATE 42P05 errors in our 50-client burst, raised throughput to 2,129.38 TPS, and held PostgreSQL 17 RSS to 54.55 MiB across 16 backend sockets.
services:
  postgres:
    image: postgres:17-alpine
    command: ["postgres", "-c", "max_connections=60", "-c", "shared_buffers=256MB"]
    environment:
      POSTGRES_USER: n8n_user
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_DB: n8n_prod
    volumes:
      - pg17_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U n8n_user -d n8n_prod"]
      interval: 5s
      timeout: 3s
      retries: 10

  pgbouncer:
    image: edoburu/pgbouncer:v1.23.1-p2
    depends_on:
      postgres:
        condition: service_healthy
    environment:
      LISTEN_PORT: "6432"
      DB_HOST: postgres
      DB_PORT: "5432"
      DB_USER: n8n_user
      DB_PASSWORD: ${DB_PASSWORD}
      DB_NAME: n8n_prod
      AUTH_TYPE: scram-sha-256
      POOL_MODE: transaction
      MAX_CLIENT_CONN: "300"
      DEFAULT_POOL_SIZE: "15"
      MAX_PREPARED_STATEMENTS: "200"

  n8n:
    image: n8nio/n8n:1.76.1
    depends_on:
      - pgbouncer
    environment:
      DB_TYPE: postgresdb
      DB_POSTGRESDB_HOST: pgbouncer
      DB_POSTGRESDB_PORT: "6432"
      DB_POSTGRESDB_DATABASE: n8n_prod
      DB_POSTGRESDB_USER: n8n_user
      DB_POSTGRESDB_PASSWORD: ${DB_PASSWORD}
Verified Benchmark Stack (Docker Engine 29.9.2 on Apple M6 ARM64): postgres:17-alpine (PostgreSQL 17.11), edoburu/pgbouncer:v1.23.1-p2 (PgBouncer 1.23.1), n8nio/n8n:1.76.1, and a 50-worker node:22-alpine + pg@8.13.1 Extended Query benchmark client executing 1,000 parameterized transactions.

Why Extended Query Named Statements Collide in Transaction Pooling

PostgreSQL speaks two distinct wire protocols on port 5432. Simple Query mode sends plain SQL text in a single Q message, whereas the PostgreSQL 17 Extended Query Protocol splits execution into separate Parse, Bind, Describe, Execute, and Sync frames. Node.js drivers like pg, Go's pgx, Python's psycopg3, and TypeORM's query runner use Extended Query framing whenever parameterized queries carry a statement name or statement cache key.

Docker Compose ps output showing isolated n8n 1.76.1, PostgreSQL 17.11, and PgBouncer 1.23.1 containers
Figure 1: Isolated Docker Compose stack running n8nio/n8n:1.76.1, postgres:17-alpine, and edoburu/pgbouncer:v1.23.1-p2 on loopback ports 15678, 15432, and 16432.

Look at how statement scope works inside Postgres. According to the PostgreSQL 17 PREPARE specification, prepared statements live for the duration of the current backend database session rather than a single BEGIN ... COMMIT transaction block. In direct connections, one client owns one backend process from login to disconnect. No other client ever touches its session hash table.

Terminal verification of PostgreSQL 17.11, PgBouncer 1.23.1, and n8n 1.76.1 ARM64 container binaries
Figure 2: Exact binary versions verified inside the running containers prior to firing the 50-worker Extended Query benchmark client.

Transaction pooling changes that ownership rule completely. PgBouncer holds 15 persistent PostgreSQL backend connections open and assigns one to a client only for the milliseconds between BEGIN and COMMIT. As soon as Transaction 1 commits on Backend #4, PgBouncer returns Backend #4 to the idle pool with n8n_exec_insert_v1 still sitting in its session memory. Two milliseconds later, Worker #19 connects for the first time, assumes its session is clean, and sends a fresh Parse("n8n_exec_insert_v1") packet onto Backend #4. PostgreSQL checks its session table, sees the name occupied, and aborts the transaction with PostgreSQL error code 42P05 (duplicate_prepared_statement).

Official PostgreSQL 17 documentation explaining Extended Query Protocol Parse and Bind messages
Figure 19: PostgreSQL 17 wire-protocol specification explaining why named Parse statements stay bound to backend sessions.

Reproducing the 71.9% SQLSTATE 42P05 Storm Under a 50-Client Burst

Guessing at pooler flags hides failure modes. We stood up an isolated three-container stack on Docker Engine 29.9.2 (linux/arm64) using postgres:17-alpine (17.11), edoburu/pgbouncer:v1.23.1-p2 (PgBouncer 1.23.1), and n8nio/n8n:1.76.1 wired via the official n8n PostgreSQL configuration settings. Before touching production workflows, always export clean snapshots first following our automated n8n JSON workflow backup playbook.

PgBouncer SHOW CONFIG output displaying pool_mode transaction and max_prepared_statements set to 0
Figure 3: Baseline Config 1 SHOW CONFIG; output with pool_mode = transaction, default_pool_size = 15, and max_prepared_statements = 0.

In Config 1, we configured PgBouncer with pool_mode = transaction, max_client_conn = 250, default_pool_size = 15, and the legacy default max_prepared_statements = 0. PostgreSQL ran with max_connections = 40 and shared_buffers = 128MB. Next, our node:22-alpine benchmark client opened 50 concurrent pg.Client connections against PgBouncer port 16432 and executed 20 sequential read-write webhook audit transactions per worker (1,000 transactions total) using named statements n8n_exec_insert_v1 and n8n_webhook_lookup_v1.

Node.js pg load client output showing 71.9 percent failure rate and 718 SQLSTATE 42P05 errors
Figure 4: Firing 1,000 named-statement transactions across 50 concurrent workers triggers a 71.9% failure rate (718 SQLSTATE 42P05 collisions).

The burst failed within 40 milliseconds. Only 281 transactions succeeded while 719 failed (71.9% error rate) across a 373.56 ms wall window. Inspecting the SQLSTATE map showed 718 occurrences of 42P05 and 1 occurrence of 26000, where an already-connected client sent a bare Bind frame onto a freshly rotated backend that had not parsed its statement yet.

PostgreSQL 17 container log printing ERROR prepared statement n8n_exec_insert_v1 already exists
Figure 5: Verbatim PostgreSQL 17.11 server stderr confirming backend PIDs 95, 102, and 96 rejecting duplicate Parse frames.

Checking docker logs gfl-c1-lab1-pg17 confirmed the exact server-side cause. Backend PIDs 95, 102, 96, 99, and 101 logged continuous ERROR: prepared statement "n8n_exec_insert_v1" already exists lines while SHOW POOLS; reported cl_active = 52 clients sharing just 15 backend sockets.

PgBouncer SHOW POOLS table showing 52 active client sockets multiplexed onto 15 backend connections
Figure 6: Mid-burst SHOW POOLS; telemetry showing cl_active = 52 multiplexed across 15 pooled PostgreSQL server sockets.

Why Switching PgBouncer to Session Mode Backfires Under Load

Many forum threads tell operators to fix 42P05 by changing pool_mode from transaction back to session. Session pooling does stop 42P05 collisions because PgBouncer glues each client TCP socket to a dedicated Postgres backend until the client disconnects. In practice, session mode creates two severe capacity traps.

PostgreSQL 17 log showing FATAL sorry too many clients already under PgBouncer session mode
Figure 7: Switching PgBouncer to pool_mode = session against max_connections = 40 immediately triggers FATAL: sorry, too many clients already.

We tested that exact workaround in Config 2a by setting pool_mode = session and default_pool_size = 50 while keeping PostgreSQL at max_connections = 40. When the 50 concurrent workers connected and held their sessions open for 3.8 seconds, the first 40 slots filled instantly. Twelve workers stalled in PgBouncer's queue (cl_waiting = 12, maxwait_us = 877659) while PostgreSQL rejected new server logins with FATAL: sorry, too many clients already. If you have ever hit that ceiling directly, compare the failure mechanics in our PostgreSQL too many connections Docker PgBouncer test.

PgBouncer SHOW POOLS showing 12 waiting clients and Node.js Connection terminated unexpectedly crash
Figure 8: Twelve webhook workers stall in cl_waiting until query_wait_timeout drops their TCP sockets.

Two seconds later, PgBouncer's query_wait_timeout severed the stalled client sockets and crashed the Node.js load client with Error: Connection terminated unexpectedly. Even local psql health probes failed on the Unix domain socket because zero non-superuser slots remained.

pg_stat_activity showing 53 active backends and docker stats showing 121.6 MiB PostgreSQL RSS in session mode
Figure 9: Raising PostgreSQL max_connections = 100 lets session mode finish, but spawns 53 active backends and bloats Postgres RSS to 121.6 MiB.

What happens if you raise Postgres max_connections = 100 so all 50 session-mode clients fit? We ran that in Config 2b (default_pool_size = 55, max_connections = 100). All 1,000 transactions succeeded, yet PostgreSQL spawned 53 active backend processes (58 container PIDs) and container RSS jumped to 121.60 MiB. Worse, OS context switching across 53 competing backends pushed p95 latency to 71.58 ms and p99 tail latency to 245.75 ms, limiting net throughput to 1,646.02 TPS.

Benchmarking the Real Fix: max_prepared_statements = 200 in Transaction Mode

Starting in PgBouncer 1.21.0 and refined through 1.23.1, PgBouncer added full wire-protocol tracking for Extended Query prepared statements. Check the official PgBouncer max_prepared_statements documentation and the PgBouncer feature matrix before altering application code.

PgBouncer SHOW CONFIG confirming max_prepared_statements set to 200 in transaction mode
Figure 10: Config 3 production profile keeping pool_mode = transaction and default_pool_size = 15 while setting max_prepared_statements = 200.

When max_prepared_statements is set to a positive integer such as 200, PgBouncer intercepts incoming client Parse, Bind, and Close messages in memory. Instead of forwarding client-chosen names like n8n_exec_insert_v1 verbatim, PgBouncer hashes the query string, maintains a per-client lookup table, and prepares the statement once per backend under an internal handle such as PgBouncer_1. Subsequent clients reusing that backend reuse the already-parsed plan with zero duplicate Parse packets.

Benchmark client output showing 1000 of 1000 succeeded transactions at 2129.38 TPS and 0 percent error rate
Figure 11: With max_prepared_statements = 200, all 1,000 named-statement transactions complete with 0 errors at 2,129.38 TPS.

We deployed Config 3 with pool_mode = transaction, default_pool_size = 15, max_connections = 40, and max_prepared_statements = 200. Running the identical 50-worker, 1,000-transaction Extended Query burst produced 1,000 succeeded transactions and 0 errors (0.0% error rate).

docker stats showing 54.55 MiB PostgreSQL RSS and 4.16 MiB PgBouncer RSS during 50-client transaction burst
Figure 12: PostgreSQL container memory drops to 54.55 MiB (21 PIDs) while PgBouncer caches protocol statements in 4.16 MiB RSS.

Throughput climbed to 2,129.38 TPS (469.62 ms burst wall time), beating session mode by 29.4%. Latency stayed tight across the board: 11.38 ms median, 31.00 ms p95 (56.7% lower than Config 2b), and 83.31 ms p99 (66.1% lower than Config 2b). Meanwhile, docker stats recorded PostgreSQL 17 at just 54.55 MiB RSS (16 active backends, 21 PIDs) and PgBouncer at 4.16 MiB RSS (1 PID).

To verify how that wire-protocol fix translates to end-to-end HTTP webhook callers, we also ran oha 1.16.0 (oha -n 500 -c 50 -m POST --no-tui --output-format json) firing 500 concurrent HTTP webhook POST requests across 50 simultaneous connections into an HTTP webhook endpoint backed by PgBouncer port 6432. Under max_prepared_statements = 0, 392 of 500 HTTP requests failed with HTTP 500 (78.4% error rate) and only 108 returned HTTP 200 OK (p50 = 11.158 ms, p95 = 154.553 ms, p99 = 159.541 ms). Enabling max_prepared_statements = 200 delivered 500 of 500 HTTP 200 OK responses (0.0% error rate, 0 HTTP 500s) at 1,607.67 req/s, with end-to-end HTTP latency measuring 7.340 ms min, 30.675 ms mean, 11.073 ms p50, 194.045 ms p95, and 202.112 ms p99.

Charmbracelet freeze Retina terminal capture of oha 500-request concurrency 50 HTTP webhook benchmark comparing 78.4 percent HTTP 500 failures against 100 percent HTTP 200 success
Figure 21: End-to-end oha -n 500 -c 50 HTTP webhook load test showing 392/500 HTTP 500 failures (78.4%) when max_prepared_statements = 0 versus 500/500 HTTP 200 OK at 1,607.67 req/s with max_prepared_statements = 200.
Configuration Profile PgBouncer Settings Postgres max_connections Throughput & Error Rate Latency (p50 / p95 / p99) Postgres RSS & Active Backends
Config 1 (Broken Default) transaction, pool=15, prep=0 40 752.21 TPS (71.9% ERR: 718x 42P05, 1x 26000) 7.60 / 15.16 / 92.30 ms 80.05 MiB (16 backends, 21 PIDs)
Config 2a (Session Starvation) session, pool=50, prep=0 40 Aborted (5x FATAL 53300 + 12 cl_waiting EOF) Timed out at 2,000 ms 97.31 MiB (40 saturated, 46 PIDs)
Config 2b (Session Bloated) session, pool=55, prep=0 100 1,646.02 TPS (0.0% ERR) 6.09 / 71.58 / 245.75 ms 121.60 MiB (53 backends, 58 PIDs)
Config 3 (Production Fix) transaction, pool=15, prep=200 40 2,129.38 TPS (0.0% ERR, 1,000/1,000 OK) 11.38 / 31.00 / 83.31 ms 54.55 MiB (16 backends, 21 PIDs)

Throughput, Tail Latency, and Container Memory Breakdown

Side-by-side telemetry makes the architectural tradeoff obvious. Leaving max_prepared_statements = 0 in transaction mode breaks three quarters of concurrent Extended Query writes. Falling back to session mode either starves connections (53300) or wastes more than double the database RAM while tripling p99 latency.

Horizontal bar chart comparing throughput TPS and SQLSTATE 42P05 error rate across four PgBouncer configurations
Figure 13: Empirical throughput and error-rate comparison across all four tested PgBouncer 1.23.1 + PostgreSQL 17.11 configurations.

Look closely at the p95 and p99 bars below. Keeping 15 warm PostgreSQL backends busy on sequential transaction slices eliminates backend fork overhead and catalog cache duplication.

Chart comparing p95 and p99 millisecond latency between PgBouncer session mode and transaction mode
Figure 14: Tail latency drops from 71.58 ms (p95) and 245.75 ms (p99) in session mode down to 31.00 ms and 83.31 ms in transaction mode.

Memory scaling matters even more on 4 GB to 8 GB VPS nodes running n8n, Redis, and PostgreSQL together. Saving 67.05 MiB at just 50 clients translates to hundreds of megabytes of reclaimed cache when scaling to 250 webhook connections.

Chart comparing PostgreSQL 17 container RSS in MiB and backend process count under 50 concurrent clients
Figure 15: Container RSS and backend process comparison showing a 55.1% PostgreSQL RAM reduction with transaction pooling.

Sizing Your n8n + PgBouncer Pool Before Production Traffic

After validating the 50-client benchmark, we booted n8nio/n8n:1.76.1 directly through PgBouncer port 6432 under Config 3. Every TypeORM migration and startup query completed cleanly with zero 42P05 entries in PostgreSQL's log, serving the owner setup interface on port 15678 immediately.

Chrome screenshot of live self-hosted n8n 1.76.1 booted through PgBouncer port 6432 on localhost 15678
Figure 16: Live n8nio/n8n:1.76.1 web interface running on 127.0.0.1:15678 after completing TypeORM schema initialization through PgBouncer.

To pick exact numbers for your own CPU core count and RAM ceiling, plug your host RAM, shared_buffers, and expected webhook concurrency into our interactive PostgreSQL and PgBouncer memory and pool calculator. On 2-to-8 vCPU NVMe servers, keeping default_pool_size between 15 and 25 while setting max_client_conn to 250-500 and max_prepared_statements = 200 prevents backend thrashing.

GearFlowLab PostgreSQL and PgBouncer Memory and Connection Pool Calculator in Chrome
Figure 17: Modelling default_pool_size, max_client_conn, and per-backend RAM budgets in the GearFlowLab PgBouncer calculator.

Double-check two details whenever upgrading container tags. First, confirm pgbouncer --version reports 1.21.0 or newer inside the container so max_prepared_statements is recognized rather than rejected at startup. Second, use AUTH_TYPE=scram-sha-256 when pairing PgBouncer with postgres:17-alpine, since PostgreSQL 17 defaults to SCRAM-SHA-256 password verifiers out of the box.

Official PgBouncer documentation section describing max_prepared_statements protocol tracking
Figure 18: Upstream PgBouncer manual detailing protocol-level prepared statement rewriting when max_prepared_statements > 0.

Finally, point n8n's DB_POSTGRESDB_HOST and DB_POSTGRESDB_PORT=6432 variables at the PgBouncer service name on an internal bridge network while keeping ports 5432 and 6432 bound strictly to 127.0.0.1 (or unexposed) on the Docker host.

Official n8n documentation listing DB_POSTGRESDB environment variables for self-hosted deployments
Figure 20: Upstream n8n database environment variable reference for routing traffic through external PostgreSQL poolers.

PgBouncer Transaction Mode & SQLSTATE 42P05 Engineering FAQ

Why do SQLSTATE 42P05 and SQLSTATE 26000 appear together behind PgBouncer?

Both errors come from the same mismatch between client connection state and pooled server state. When Client B sends a named Parse packet onto a warm backend that already prepared that name for Client A, PostgreSQL throws 42P05 (duplicate_prepared_statement). Conversely, when Client A skips Parse on its second transaction and sends Bind onto a fresh backend that never saw the statement, PostgreSQL throws 26000 (invalid_sql_statement_name).

How much RAM does max_prepared_statements = 200 add to PgBouncer?

Almost nothing. In our docker stats measurements under 50 active clients, enabling max_prepared_statements = 200 increased PgBouncer RSS from 1.84 MiB to 4.16 MiB, a delta of 2.32 MiB, while saving 67.05 MiB of PostgreSQL backend memory compared with session mode.

What if my cloud provider runs an older PgBouncer release below 1.21?

Protocol-level statement tracking was introduced in PgBouncer 1.21.0. If you cannot upgrade the pooler binary yet, disable named prepared statements in your client driver or ORM connection options rather than switching PgBouncer to pool_mode = session.

Does DISCARD ALL in server_reset_query fix 42P05 in transaction mode?

No. PgBouncer only runs server_reset_query when a session disconnects in session pooling mode. Running DISCARD ALL after every single transaction would wipe backend caches and destroy the exact performance advantage that transaction pooling provides.

100% Client-Side Utilities · Printable A4 Runbook

Run Prefilled Sizing Math & Download the 1-Page Runbook

Size PostgreSQL 17 shared_buffers + PgBouncer transaction pools and decode SQLSTATE 42P05 or SQLITE_BUSY logs.

Paired Prefilled Calculators & Vector PDF Checklist

Yun Sanghun (M. Yun) · Linux, Docker & AI Infrastructure Engineer