Designing Safe Read/Write Splitting in PostgreSQL with PgBouncer and HAProxy

How to route traffic correctly in PostgreSQL HA setups without breaking consistency, performance, or failover

Designing Safe Read/Write Splitting in PostgreSQL with PgBouncer and HAProxy

Read/write splitting sounds simple in theory.

Writes go to the primary.
Reads go to replicas.
Everyone is happy.

In reality, incorrect routing is one of the most common causes of data inconsistency, failed transactions, and broken failovers in PostgreSQL HA environments. The problem is not PostgreSQL itself - it is how traffic is routed.

This article explains how to design safe, predictable read/write splitting using PgBouncer and HAProxy, and why many seemingly "working" setups fail under pressure.

Why Read/Write Splitting Is Harder Than It Looks

The main issue is state.

Applications assume:

writes are immediately visible
transactions are consistent
connections behave predictably

But replicas:

lag behind the primary
may be promoted during failover
may temporarily accept connections while not ready

If routing is naive, you get:

stale reads after writes
broken transactions
hard-to-debug data anomalies

Correct routing must respect role awareness, connection pooling behavior, and failover timing.

The Core Rule: PgBouncer Should Not Decide Roles

PgBouncer is excellent at connection pooling.
It is not a cluster coordinator.

A safe architecture follows this separation:

PgBouncer: connection pooling only
Patroni: primary/replica role management
HAProxy: routing decisions

When PgBouncer is also used to determine roles, for example via pg_is_in_recovery(), routing becomes fragile during failovers and network partitions.

A Safe Reference Architecture

A production-grade setup typically looks like this:

PostgreSQL nodes managed by Patroni
PgBouncer running on each node or as a separate layer
HAProxy querying Patroni's API

HAProxy exposes:

one endpoint for writes: primary only
one endpoint for reads: replicas only

PgBouncer sits behind these endpoints and handles pooling transparently.

This ensures that:

role detection is cluster-aware
routing changes quickly after failover
applications never talk directly to replicas for writes

PgBouncer and HAProxy Reference Pattern

For most production systems, the safest PostgreSQL read/write split is not a transparent SQL parser. It is an explicit two-endpoint pattern:

postgres-write.example.internal: accepts write traffic and points only to the current primary
postgres-read.example.internal: accepts replica-eligible read traffic and points only to healthy replicas

The application chooses the endpoint based on intent. HAProxy decides whether each backend is safe to receive traffic. PgBouncer keeps connection counts under control.

A practical responsibility split looks like this:

Layer	Responsibility	What it should not do
Application	Choose primary or replica path based on query intent	Guess consistency from a random connection
HAProxy	Route to primary or replica backends using role-aware checks	Parse SQL or infer transaction semantics
PgBouncer	Pool PostgreSQL connections efficiently	Decide cluster roles or promote nodes
Patroni	Manage leader election and expose node role state	Handle application-level read-after-write rules

This is why many "PgBouncer read/write splitting" examples are unsafe when copied directly into production. PgBouncer can pool the connections, but the routing decision still has to come from application intent and cluster-aware health checks.

Example endpoint layout

Application write pool
  -> HAProxy :5432 / primary-only check
  -> PgBouncer on current primary
  -> PostgreSQL primary

Application read pool
  -> HAProxy :5433 / replica-only + lag check
  -> PgBouncer on healthy replicas
  -> PostgreSQL replicas

Treat the read endpoint as an optimization path, not a default path. If a query must see its own write, uses session state, takes advisory locks, runs migrations, or changes data indirectly, it belongs on the write endpoint.

Read Traffic: What Can Safely Go to Replicas

Not all reads are equal.

Safe candidates for replicas:

analytics queries
background jobs
dashboards
reporting endpoints

Dangerous candidates:

reads inside write transactions
read-after-write logic
session-dependent queries

If your application requires read-your-writes consistency, those reads must go to the primary.

Many teams solve this by:

routing all transactional traffic to the primary
routing explicit "read-only" pools to replicas

PgBouncer Pooling Modes Matter

Your routing logic can be perfect - and still break because of pooling mode.

Transaction pooling, recommended

safest for HA
connections reset after each transaction
works best with read/write split

Session pooling

dangerous in HA setups
connections may survive failover
role changes are not respected immediately

For most modern systems, transaction pooling is the only safe default.

Failover: The Moment Everything Breaks

During failover:

the old primary disappears
replicas are promoted
connections are dropped or reset

If routing depends on stale SQL checks, applications may continue sending writes to the wrong node.

When HAProxy relies on Patroni's API:

role changes are controlled by the cluster manager
routing switches to the promoted primary
PgBouncer reconnects cleanly

This dramatically reduces recovery time and prevents split-brain writes.

For a deeper look at the operational side, see PostgreSQL failover: what actually breaks.

Common Anti-Patterns to Avoid

Using PgBouncer as a role oracle
Mixing read/write traffic on the same endpoint
Session pooling in HA setups
Ignoring replica lag
Assuming replicas are "just like primaries"
Testing only direct PostgreSQL connections and skipping PgBouncer during failover drills

Most production outages happen because of one of these mistakes.

Final Thoughts

Read/write splitting is not a performance trick - it is an architecture decision.

When implemented correctly, it:

improves scalability
protects consistency
makes failover predictable

When implemented poorly, it introduces subtle data bugs that surface months later.

The safest approach is clear separation of responsibilities:

Patroni decides roles
HAProxy routes traffic
PgBouncer pools connections

This pattern has proven reliable across high-load PostgreSQL environments.

If you need help designing or reviewing your PostgreSQL HA architecture, this is exactly the kind of problem we solve at SysRoot.io - pragmatically, safely, and without unnecessary complexity.

Direct answer: safe PostgreSQL read/write splitting with PgBouncer and HAProxy

PgBouncer does not safely decide whether a PostgreSQL query is a read or a write by itself. In production, the safer pattern is to make the application choose the read or write path, send writes to the primary path, send eligible reads to a replica path, and let HAProxy health checks remove unsafe targets when a node changes role.

Use PgBouncer for connection pooling and HAProxy for routing to known primary or replica backends. Keep the rules explicit: writes, transactions with side effects, migrations, advisory locks, session state, and read-after-write flows should use the primary path. Replica reads should be limited to workloads that can tolerate lag and stale results.

Production checklist

Keep separate connection strings for write traffic and replica-eligible read traffic.
Route all writes, migrations, locks, and critical read-after-write queries to the primary.
Use HAProxy role checks so a promoted replica can become the write target only after PostgreSQL and the cluster manager agree on its role.
Monitor replication lag and stop sending reads to replicas that fall outside the application tolerance.
Test failover with active PgBouncer pools, not only with direct PostgreSQL connections.
Document which application paths are allowed to use replicas and which must stay primary-only.

Need this reviewed in production?

If you are planning PostgreSQL read/write splitting, PgBouncer pooling, HAProxy routing, or replica-based scaling in a live system, the risky parts are usually not the happy-path configuration. The real questions are failover behavior, read-after-write consistency, monitoring, pool recovery, backup confidence, and who owns the operational runbook.

SysRoot can review the design before it reaches production or help stabilize an existing setup:

FAQ: PostgreSQL read/write splitting with PgBouncer and HAProxy

Can PgBouncer safely split PostgreSQL reads and writes by itself?

No. PgBouncer is a connection pooler, not a SQL router. It does not reliably parse application intent, transaction semantics, side effects, or consistency requirements. Treat PgBouncer as the pooling layer and keep read/write routing decisions explicit in the application or in a deliberately designed routing layer.

Is this a replacement for official PgBouncer or HAProxy documentation?

No. Official documentation explains individual components; this article describes the production architecture pattern around them. Use the PgBouncer and HAProxy docs for exact configuration syntax, then apply the safety rules here: explicit write/read endpoints, role-aware health checks, replica lag limits, failover testing, and primary-only routing for consistency-sensitive paths.

What is the difference between PgBouncer and HAProxy in this design?

PgBouncer manages PostgreSQL connection pooling. HAProxy manages network routing and health checks. In a safe read/write split, HAProxy decides which backend endpoint is eligible for traffic, while PgBouncer reduces connection overhead after the route has already been chosen.

Where should HAProxy sit in a PostgreSQL read/write split?

HAProxy should sit between PgBouncer or the application and the PostgreSQL backends when you need health-checked routing to primary and replica pools. A common design is one write endpoint that only points to the current primary and one read endpoint that points to replicas that pass role and lag checks.

What breaks during failover with PgBouncer and replicas?

Failover can leave pooled connections pointing at the old primary, send writes to a node that is now read-only, or continue replica reads against stale data. The fix is to combine role-aware HAProxy checks, short connection lifetimes where appropriate, clear application retry behavior, and failover tests that include PgBouncer rather than bypassing it.

Should all PostgreSQL reads go to replicas?

No. Replica reads are safe only when the application can tolerate lag and stale data. Dashboards, reports, analytics jobs, and background tasks are usually better candidates than user-facing flows that immediately read data after a write. Anything that depends on read-after-write consistency should stay on the primary path.

Is read/write splitting a replacement for backups or disaster recovery?

No. Replicas improve availability and read scalability, but they do not replace tested backups, restore procedures, or disaster recovery planning. A bad migration, accidental delete, or corrupted data can replicate quickly. For that side of the problem, see Backups are not disaster recovery and VM snapshots are not backups for Proxmox and VMware.

Next steps for PostgreSQL reliability

If this article matched a real production problem, these are the next useful reads and service pages:

Related services

Database Administration for PostgreSQL tuning, HA, backups, failover and operational support.
Disaster Recovery Services for restore testing, RPO/RTO planning and recovery runbooks.
Monitoring and Observability Services for replication lag, pool health, database dashboards and actionable alerts.

Talk to an engineer

If you want a second pair of eyes on a PgBouncer, HAProxy, Patroni, replica or failover design, send a short brief with the current topology, failure mode you are worried about, and whether the setup is already in production.

Back to Blog

Designing Safe Read/Write Splitting in PostgreSQL with PgBouncer and HAProxy