Load Balance Reads with HAProxy

Your PostgreSQL primary is doing double duty: it writes every transaction and serves every read. As your application grows, that single node becomes a bottleneck — queries queue up, page loads crawl, and the cost of scaling vertically hits the ceiling. The fix isn't to buy a bigger server; it's to load balance reads with HAProxy, directing read traffic across multiple replicas while keeping writes safely on the primary. In this lesson, you'll learn why read scaling matters, how HAProxy's round-robin algorithm distributes connections, and how to wire it up with a real PostgreSQL cluster — hands-on, step by step.

The problem this lesson solves

Before replicas, every SELECT shared the same database connection pool as every INSERT, UPDATE, and DELETE. Reads usually outnumber writes by 10:1 or more in production, so the primary's CPU and I/O become saturated with repetitive queries — many of them identical. The consequence is latency creep: response times rise, timeouts appear, and your database becomes the team's biggest pain point.

A single PostgreSQL instance also has a hard ceiling on memory and disk throughput. You can throw more hardware at it, but that's expensive and still leaves you with one point of failure. Read replicas solve this by providing additional, read-only copies of your data. But replicas are useless if your application doesn't use them. That's where HAProxy comes in — a battle-tested TCP/HTTP proxy that can route incoming database connections to multiple backend servers, balancing the load automatically.

The pain in one sentence: Your primary is drowning in read traffic, and without a load balancer, replicas sit idle while your app stays slow.

Core concept / mental model

Think of HAProxy as a smart traffic cop at a busy intersection. Cars (your application's queries) arrive and need to go to one of several roads (database servers). The cop doesn't just send everyone down the same road — they distribute cars evenly so no single road jams. In database terms, HAProxy accepts a PostgreSQL connection on a single IP/port and forwards it to a selected backend server based on a balancing algorithm (like round-robin).

Key definitions: - Frontend — the entry point where your app connects (e.g., HAProxy:5432). - Backend — the pool of PostgreSQL servers HAProxy can forward to (e.g., pg-primary and pg-replica). - Round-robin — a balancing method where each new connection goes to the next server in the list, cycling evenly. - Health check — a periodic probe HAProxy sends to each server to see if it's up; down servers are removed from rotation.

Why reads specifically? Writes must go to the primary to maintain data consistency — replicas are read-only. Reads, however, can safely go to any replica. HAProxy lets you separate these flows: the primary gets all write traffic, while reads are spread across replicas.

A mental diagram:

App --> HAProxy (listen 5432) --> backend servers
                          |--> pg-primary (writes)
                          |--> pg-replica1 (reads)
                          |--> pg-replica2 (reads)

HAProxy operates at layer 4 (TCP) for PostgreSQL, meaning it doesn't inspect query content — it just proxies the connection. The session sticks to one server for its lifetime (unless configured otherwise), which is perfectly fine for most ORM connection pools.

How it works step by step

1. Set up PostgreSQL replicas — using streaming replication, with each replica in hot standby mode (hot_standby = on), so they can accept read-only connections.
2. Install HAProxy on a dedicated server or the same host as your app — it must be reachable by both app and databases.
3. Configure HAProxy with a frontend (listen block) that uses mode tcp and a backend with balance roundrobin listing primary and replicas.
4. Add health checks — HAProxy sends a simple query like SELECT 1 to each server to verify it's alive.
5. Route writes — Point your app's write connections to the primary directly, or use a separate HAProxy frontend that targets only the primary.
6. Point your app at the HAProxy frontend for reads, and watch traffic spread evenly across replicas.

Cause and effect: Because HAProxy uses round-robin, subsequent connections alternate between replica1 and replica2 (and optionally primary), preventing overload on any single node. If a replica goes down, HAProxy's health check marks it down and stops sending new connections — failover without app changes.

Hands-on walkthrough

Let's put it into practice. For this exercise, you'll need three PostgreSQL instances: one primary and two replicas, all with streaming replication configured. We'll assume they're on 10.0.0.1 (primary), 10.0.0.2 (replica1), and 10.0.0.3 (replica2). You also have HAProxy installed on a separate machine.

Step 1: Verify replicas are ready

On each replica, ensure hot_standby = on in postgresql.conf and that they're accepting connections. Run this on each:

SELECT pg_is_in_recovery();

Expected output on replicas: true (they are in recovery, meaning read-only). On the primary: false.

Step 2: Create HAProxy configuration

Create /etc/haproxy/haproxy.cfg with the following content:

global
    log /dev/log local0
    maxconn 4096
    user haproxy
    group haproxy

defaults
    log global
    mode tcp
    option tcplog
    retries 3
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms

# Read-only frontend
frontend pg_reads
    bind *:5432
    default_backend pg_read_replicas

# Backend for reads — use round robin across replicas
backend pg_read_replicas
    balance roundrobin
    server pg-replica1 10.0.0.2:5432 check fall 3 rise 2
    server pg-replica2 10.0.0.3:5432 check fall 3 rise 2

# Separate frontend for writes (optional) — goes straight to primary
frontend pg_writes
    bind *:5433
    default_backend pg_primary

backend pg_primary
    server pg-primary 10.0.0.1:5432 check

Step 3: Start HAProxy

sudo systemctl start haproxy
sudo systemctl enable haproxy

Step 4: Test the read load balancing

From the app server, run:

psql "host=haproxy-ip port=5432 user=app_user dbname=appdb" -c "SELECT inet_server_addr();"
psql "host=haproxy-ip port=5432 user=app_user dbname=appdb" -c "SELECT inet_server_addr();"
psql "host=haproxy-ip port=5432 user=app_user dbname=appdb" -c "SELECT inet_server_addr();"

Expected output — the server addresses should rotate between 10.0.0.2 and 10.0.0.3 (or whichever IP your replicas have). Each new connection is routed round-robin.

Step 5: Verify health checks

Stop one replica temporarily (sudo systemctl stop postgresql on replica1) and run again:

psql "host=haproxy-ip port=5432 user=app_user dbname=appdb" -c "SELECT inet_server_addr();"

The connection should go to the remaining replica. HAProxy won't route to the down server.

Pro tip: Use option pgsql-check in your backend to run a real PostgreSQL health check (SELECT 1) instead of just a TCP check. Add option pgsql-check user haproxy_check to the backend block for better accuracy.

Compare options / when to choose what

HAProxy is powerful, but it's not the only read-balancing approach. Here's how it stacks up against alternatives:

When to choose HAProxy: - You want a transparent layer that doesn't require app changes — your app just connects to one host. - You need robust health checks and automatic failover of down replicas. - You already run HAProxy for HTTP load balancing, so you can reuse the same infra.

When to avoid HAProxy: - You need read/write splitting at the query level (HAProxy can't inspect SQL). - Your reads must go to the primary to avoid replication lag (e.g., real-time dashboards). - You want to keep your stack lean and prefer to handle routing in code.

Variations and alternatives:

• Use balance leastconn instead of roundrobin if your workloads have uneven connection durations — it sends new connections to the server with the fewest active connections.
• Run multiple HAProxy instances in front of your databases for HA, and use keepalived or DNS to fail over between them.
• Try Pgpool-II if you want a PostgreSQL-native proxy with built-in query caching and automatic read/write splitting — but be prepared for a steeper learning curve.

Troubleshooting & edge cases

Symptom: Connections go to primary instead of replicas. - Cause: Your backend list includes the primary, and round-robin cycles through it. Fix: Remove primary from the read backend, or use a separate frontend for writes.

Symptom: HAProxy health checks fail, marking all servers down. - Cause: Authentication failure in the health check user. Ensure the user haproxy_check exists and can connect from the HAProxy host. - Fix: Add the user in PostgreSQL: CREATE USER haproxy_check WITH PASSWORD 'secret'; and grant CONNECT on your database.

Symptom: "No server is available to handle this request" - Cause: All backends are down or HAProxy hasn't finished starting. Check sudo systemctl status haproxy and sudo tail -f /var/log/haproxy.log.

Symptom: Replication lag causes stale reads. - Edge case: With high write volume, replicas may lag behind the primary. Monitor pg_stat_replication and consider a hybrid approach where critical reads go to primary.

Symptom: Connection timeouts when HAProxy is busy. - Cause: timeout connect too short.Cause: HaProxy is maxing out connections. Increase timeout connect or raise maxconn in the global block.

Common mistake: Forgetting to enable hot_standby on replicas — reads will fail with "recovery is in progress". Always verify with SELECT pg_is_in_recovery(). HAProxy health checks on TCP will pass even if PostgreSQL rejects reads, so use option pgsql-check to catch this.

What you learned & what's next

You've learned how to load balance reads with HAProxy — you built a mental model of read replicas and a load balancer, walked through a step-by-step configuration, ran a hands-on test that showed round-robin distribution, and compared HAProxy against Pgpool-II and app-level routing. You now know how to set up a read-only frontend, add health checks, and troubleshoot common issues like down servers and replication lag.

Key takeaways from this lesson: - Reads are the bottleneck; replicas + load balancing spread the load. - HAProxy works at TCP layer, so it's app-agnostic and doesn't inspect SQL. - Round-robin balances connections evenly; health checks handle failover. - Writes must stay on the primary — use a separate frontend or direct connection. - Always use PostgreSQL-aware health checks (option pgsql-check) for accuracy.

Next step: In the next lesson, you'll learn how to handle replication lag gracefully — implementing read-your-own-writes consistency in your application without sacrificing the read scaling you just achieved. You'll build on your HAProxy setup to route time-sensitive reads to the primary while keeping the replicas busy with the rest.

Before moving on, try this: modify your HAProxy config to use balance leastconn and re-run the inet_server_addr() test. How does the distribution change when you open multiple long-lived connections?