Why Pgpool-II HA Mode Breaks Camunda 7 in Kubernetes: A Systems Post-Mortem

Table of contents

The Incident: When High Availability Breeds Chaos

In enterprise Java setups, workflow engines like Camunda 7 coordinate critical stateful processes. Because these engines represent the source of truth for business flows, you naturally want high availability (HA) at every layer—especially the database.

A common layout deployed via Helm is putting Pgpool-II in HA mode (using its Watchdog feature) in front of a PostgreSQL primary-replica cluster.

But when transactional, lock-heavy workloads like Camunda 7 hit this architecture, things tend to break in spectacular ways: deadlocks, duplicate runs, database split-brains, and recurring prepared statement does not exist SQL errors.

Here is the post-mortem of why this happens and how to prevent it.

1. The Architecture of the Failure

Let us analyze the components of this architecture under typical conditions:

                                  +------------------------------------+
                                  |    K8s Cluster (Helm Deployed)     |
                                  |                                    |
                                  |  [ Camunda 7 Java Microservices ]  |
                                  |    (Spring Boot, MyBatis/Hikari)   |
                                  |                 |                  |
                                  +-----------------+------------------+
                                                    | Writes & Reads
                                                    v
                                  +------------------------------------+
                                  |   [ Pgpool-II Watchdog Service ]   |
                                  |   (HA Load Balancer, Write Split)  |
                                  +--------+------------------+--------+
                                           |                  |
                                 Writes    |                  | Reads (Load Balanced)
                                           v                  v
                                  +--------+--------+  +------+--------+
                                  |  PG Primary     |  |  PG Replica   |
                                  |  (Read/Write)   |  |  (Read-Only)  |
                                  +-----------------+  +---------------+

1. The Application Layer: Java microservices running Camunda 7 embedded within Spring Boot, using HikariCP for connection pooling and MyBatis/Hibernate for ORM access.
2. The Database Proxy Layer: Pgpool-II deployed via a Helm chart (e.g., the Bitnami Pgpool-II chart) with Watchdog HA enabled. Watchdog coordinates multiple Pgpool instances using virtual IPs (VIP) or Kubernetes service bindings.
3. The Storage Layer: A primary-replica PostgreSQL StatefulSet, utilizing streaming replication.

2. Failure Mode 1: The Watchdog vs. Kubernetes Orchestrator Conflict

The primary architectural mistake when deploying Pgpool-II in HA mode inside Kubernetes is the Double Orchestration Conflict.

High availability in modern systems requires a single source of truth for consensus and state management. Pgpool-II’s Watchdog was designed for bare-metal or traditional VM environments. It elects a leader among Pgpool pods using heartbeat packets (UDP/multicast) and manages a shared Virtual IP (VIP) to route traffic to the active leader.

                      [ K8s Control Plane (Service Endpoints, Probes) ]
                                             |
                     Conflicts with          |
                     Leader Election         |
                                             v
[ Pgpool Pod 1 (Watchdog Leader) ] <--- UDP Heartbeat Ping ---> [ Pgpool Pod 2 (Watchdog) ]

When deployed in Kubernetes via Helm, Watchdog conflicts directly with the Kubernetes control plane:

The Network Partition Split-Brain

1. Heartbeat Drop: Inside a busy Kubernetes cluster, short network hiccups or CPU throttling on a node can delay Watchdog’s heartbeat packets over UDP.
2. Dual-Leader Promotion (Split-Brain): If Pgpool Pod 2 misses heartbeats from Pgpool Pod 1 for a few seconds, it assumes Pod 1 is dead. Pod 2 promotes itself to the active leader.
3. VIP / DNS Chaos: If Watchdog attempts to bind a virtual IP within a Kubernetes network namespace, it fails or conflicts with the native Kube-DNS / ClusterIP routing. If the Helm chart maps traffic via standard K8s headless services, both Pgpool pods may start writing to different PostgreSQL nodes or misidentifying which PostgreSQL node is the actual writable primary.
4. Catastrophic Write Routing: Java microservice instances end up routing read-write transactions to the read-only PostgreSQL replica through the split-brain Pgpool instance, resulting in immediate transaction rollbacks and database state corruption.

3. Failure Mode 2: Read-Write Splitting vs. Camunda 7 Database Locks

Camunda 7 relies heavily on pessimistic database locking to coordinate distributed execution of workflow steps (called jobs) across multiple microservice replicas. This is driven by Camunda’s Job Executor.

                  [ Camunda Pod A ]                  [ Camunda Pod B ]
                         |                                  |
            1. Query:    |                     1. Query:    |
            "Select due  |                     "Select due  |
             job X"      |                      job X"      |
                         v                                  v
             +-----------+-----------+          +-----------+-----------+
             |    Pgpool Replica     |          |    Pgpool Primary     |
             |    (Lag: 150ms)       |          |    (Up-to-date)       |
             +-----------+-----------+          +-----------+-----------+
                         |                                  |
                         v                                  v
              +----------+----------+            +----------+----------+
              |  Read-Only Replica  |            |  Writable Primary   |
              |  (State: Job Free)  |            |  (State: Job Free)  |
              +---------------------+            +---------------------+

The Anatomy of a Locking Breakdown

Pgpool-II provides read-write splitting at the SQL parser level. It intercepts incoming SQL queries, routing SELECT statements to read replicas, while routing INSERT, UPDATE, and DELETE queries to the primary database.

This strategy completely breaks Camunda 7’s engine semantics due to replication lag:

1. The Polling Query: Camunda’s Job Executor running on Camunda Pod A polls the database to find available jobs to execute:

   SELECT * FROM ACT_RU_JOB WHERE DUEDATE_ <= ? AND RETRIES_ > 0 AND LOCK_OWNER_ IS NULL;

   Because this is a plain SELECT statement, Pgpool-II’s SQL parser intercepts it and routes it to a read-only replica.
2. Replication Lag Miss: Suppose there is a brief network lag ($150\text{ ms}$) in PostgreSQL’s streaming replication. Camunda Pod B has just acquired and locked Job 101 on the Primary database, but the replica has not yet received this WAL frame.
3. Double Claiming: Camunda Pod A queries the lagging replica, sees Job 101 as still available, and immediately attempts to lock it by sending an UPDATE to the primary:

   UPDATE ACT_RU_JOB SET LOCK_OWNER_ = 'Pod_A', LOCK_EXP_TIME_ = ? WHERE ID_ = '101' AND LOCK_OWNER_ IS NULL;

4. Optimistic Locking Exception (OLE) Storm: At the primary database, the lock check fails because Pod B already owns the row. The primary rejects Pod A’s write. Inside your Java application logs, this triggers a massive cascade of Camunda OptimisticLockingException and Deadlock exceptions:

   org.camunda.bpm.engine.OptimisticLockingException: 
   ENGINE-03005 Execution of 'UPDATE JobEntity[101]' failed. Entity was updated by another transaction concurrently.

5. Worker Starvation: CPU cycles are wasted on both Camunda pods constantly rolling back transactions and thrashing the database, resulting in a complete freeze of your business process execution pipelines.

The SELECT ... FOR UPDATE Parser Trap

To prevent double claiming, Camunda uses pessimistic locking via SELECT ... FOR UPDATE query formats in specific transaction blocks.

While Pgpool-II is designed to route SELECT ... FOR UPDATE to the primary node, it relies on static string parsing. If your Helm chart or MyBatis configuration uses custom SQL formats, wraps select queries inside complex stored procedures, or utilizes transaction isolation blocks that Pgpool-II’s parser cannot cleanly decode, it will inadvertently route the query to a read-replica anyway, rendering the pessimistic lock completely useless.

4. Failure Mode 3: The Prepared Statement Nightmare (42P05)

Spring Boot Java microservices utilize advanced database connection pools like HikariCP coupled with ORMs (Hibernate/MyBatis). To achieve high performance, these frameworks cache Prepared Statements at the JDBC connection level.

When a Java application prepares a query:

1. It sends the SQL template to PostgreSQL: PREPARE S_1 AS SELECT * FROM ACT_RU_USER WHERE ID_ = $1;
2. PostgreSQL compiles the query plan, assigns it an identifier (S_1), and stores it in the local backend process memory.
3. The Java driver executes the statement by calling EXECUTE S_1(42); repeatedly.

[ Hikari Pool Thread ] -----> [ Pgpool Proxy ] ===== (Routes to Backend A) =====> [ PG Worker 1: Caches "S_1" ]
                                     |
    Next Execute Query               |
[ Hikari Pool Thread ] -----> [ Pgpool Proxy ] ===== (Routes to Backend B) =====> [ PG Worker 2: "S_1" does not exist! ]
                                                                                   (CRASH: SQLState: 42P05)

If Pgpool-II is configured in Transaction Pooling or Statement Pooling mode:

• A single physical connection in HikariCP does not map to a single backend process on PostgreSQL. Pgpool-II dynamically multiplexes requests across different backend connections.
• The PREPARE statement is sent to PostgreSQL Worker Process A (where S_1 is cached).
• In the next database call, Pgpool-II routes the EXECUTE command to PostgreSQL Worker Process B.
• Because Worker B has no knowledge of S_1, it throws a fatal SQL error:

  org.postgresql.util.PSQLException: ERROR: prepared statement "S_1" does not exist (SQLState: 42P05)

This error tears down active database transactions, causing random API failures and engine rollbacks within the Camunda process execution loop.

5. The Production-Grade Resolution Strategy

To achieve true, bulletproof HA for Camunda 7 in Kubernetes without compromising transactional consistency, you must bypass Pgpool-II’s HA and parser layers completely.

The Cloud-Native Solution: Kubernetes Operators (e.g., CloudNativePG)

Instead of using Watchdog to manage database clustering, deploy a Kubernetes-native PostgreSQL operator like CloudNativePG (CNPG), Patroni, or the Zalando Postgres Operator.

                                  +------------------------------------+
                                  |    K8s Cluster (CNPG Orchestration)|
                                  |                                    |
                                  |  [ Camunda 7 Java Microservices ]  |
                                  |    (Spring Boot, MyBatis/Hikari)   |
                                  |       /                    \       |
                                  +------+----------------------+------+
                                         |                      |
                    Route RW (Primary)   |                      | Route RO (Replica)
                    via cnp-primary-srv  |                      | via cnp-replica-srv
                                         v                      v
                                  +--------------+      +--------------+
                                  |  PG Primary  |      |  PG Replica  |
                                  | (Read/Write) |      | (Read-Only)  |
                                  +--------------+      +--------------+

Why Kubernetes Operators Excel:

• Single Source of Truth: Primary election is managed natively via etcd consensus or Kubernetes custom resources APIs (leases). Split-brain is prevented at the infrastructure layer.
• Dumb Proxies, Smart Control Planes: The operator provides native, static Kubernetes services (my-db-primary and my-db-replica). Traffic routing is managed by core Kubernetes networking (ClusterIP iptables/IPVS), bypassing the need for complex, buggy Watchdog heartbeats.

Sizing and Isolation in Spring Boot for Camunda

Inside your Java application, isolate Camunda’s transactional database traffic. Do not let read-only analytics load-balance against replica nodes if they are executed inside the workflow execution loop.

1. Disable Pgpool Read Splitting for Camunda

If you must use Pgpool-II, disable read-write splitting entirely for the Camunda datasource. All Camunda tables (ACT_*) must be mapped to a dedicated connection pool that routes all traffic exclusively to the Primary database node.

2. Configure preparedStatement Cache in HikariCP

To resolve the 42P05 prepared statement error, configure the PostgreSQL JDBC driver to clean up statement caches, or disable server-side prepared statements by appending prepareThreshold=0 to your connection string if you are using transactional proxies like PgBouncer or Pgpool-II:

spring.datasource.url=jdbc:postgresql://pg-bouncer-primary:5432/camunda_db?prepareThreshold=0

3. Adjust Camunda Job Executor Pessimistic Locks

Optimize Camunda’s engine settings inside application.yml to prevent lock contention:

camunda.bpm:
  job-execution:
    # Reduce thread pools to prevent concurrent lock-contention storms on the database
    max-pool-size: 10
    core-pool-size: 3
    # Prevent aggressive polling loops
    max-jobs-per-acquisition: 3
    wait-time-in-millis: 5000
  generic-properties:
    properties:
      # Instructs MyBatis to wait longer for database locks before rolling back
      databaseSchemaUpdate: "false"
      jdbcBatchProcessing: "true"

Summary Checklist for Java Architects

When deploying highly transactional engines (Camunda, Temporal, Quartz Scheduler) on PostgreSQL via Helm:

1. Ditch Pgpool-II Watchdog in K8s: Do not run Watchdog HA inside Kubernetes. Use native K8s operators (like CloudNativePG or Patroni) for failover orchestration.
2. Force Camunda Traffic to Primary: Never split reads and writes for Camunda database sessions. Run a dedicated Hikari pool pointing strictly to the Primary service endpoint.
3. Disable Read Splitting for Locks: Even a $50\text{ ms}$ replication lag on read-replicas will trigger catastrophic OptimisticLockingException storms in polling workers.
4. Neutralize Prepared Statement Errors: Use prepareThreshold=0 or set up session pooling instead of transaction pooling if routing through proxies.
5. Tune Thread Counts: Keep your Spring Boot Job Executor threads constrained. Too many threads querying database lock tables will increase latch contention and degrade throughput.