HAProxy vs ProxySQL for Read/Write Splitting

Problem statement: You need reads on replicas and writes on the primary, and you are weighing HAProxy against ProxySQL — two tools that reach the same outcome through opposite mechanisms. One splits by port and trusts the application to classify queries; the other splits by parsing the SQL and classifies for you. The right choice depends on where you want the classification responsibility to live.

Symptom Identification

Teams reach this comparison from a handful of concrete situations:

  • Writes are intermittently rejected on a replica with ERROR: cannot execute INSERT in a read-only transaction, and the routing layer has no idea a statement was a write.
  • After a failover, the old primary keeps receiving writes for several seconds because the proxy health check only tested TCP reachability, not database role.
  • A proposed ProxySQL deployment stalls because the workload is PostgreSQL, where ProxySQL is not production-ready, and the team needs a protocol-agnostic alternative.
  • Read latency budgets are tight and a per-query SQL parse in the hot path is under scrutiny.

All of these are questions about where the read/write decision happens and how the proxy learns which node plays which role. HAProxy and ProxySQL answer both differently, and both sit under the connection routing and pooling strategy for the fleet.

Root Cause Analysis

HAProxy is a protocol-blind load balancer. In its default L4 (TCP) mode it forwards bytes between a client socket and a backend socket without understanding what protocol rides on top. It has no idea whether a payload is a SELECT or an UPDATE. Its splitting model is therefore structural: you define two backends — a write backend and a read backend — bound to two different listening ports, and the application decides read versus write by choosing which port to connect to. HAProxy’s contribution is world-class health checking and load distribution across the members of each backend.

Because HAProxy cannot query the database itself, it learns node roles through an external check. An agent-check or httpchk target on each node runs SELECT pg_is_in_recovery() and reports the result. The write backend admits only the node reporting f (primary); the read backend admits only nodes reporting t (standby). When a replica is promoted, its check flips, HAProxy drains it from the read pool and admits it to the write pool — automatic failover routing without HAProxy ever parsing a query.

ProxySQL is an SQL-aware query router. It parses each MySQL statement, computes a digest, matches it against ordered query rules, and forwards it to the mapped hostgroup. Read/write classification is the proxy’s job: a ^SELECT rule routes to the reader hostgroup, everything else defaults to the writer. The application connects to a single port and sends everything; ProxySQL sorts it out. Its monitor module tracks role and lag, so promotion and lag-gating are handled internally.

The essential difference: HAProxy splits by connection destination and delegates classification to the app; ProxySQL splits by statement content and owns classification. This is the same axis that separates the tools in choosing a read replica routing proxy, applied to the two most common splitting engines.

Architecture

The diagram shows the two mechanisms in parallel. HAProxy relies on the application already having chosen a port and on agent checks to fix node roles; ProxySQL takes undifferentiated traffic and classifies it by SQL.

HAProxy port-based split versus ProxySQL SQL-based split On the left, the application sends writes to HAProxy port 5432 and reads to port 5433; agent checks running pg_is_in_recovery determine which backend each node belongs to. On the right, the application sends all statements to ProxySQL on one port, and query rules classify each statement to the writer or reader hostgroup. HAProxy — split by port ProxySQL — split by SQL Application write → :5432 read → :5433 HAProxy (L4) two backends, agent-check Primary recovery = f Replicas recovery = t agent runs pg_is_in_recovery() → sets backend role Application all SQL → one port ProxySQL (L7) parse → query rule Writer hg non-SELECT Reader hg SELECT, lag-gated statement content decides

Latency and Overhead

HAProxy in L4 mode is the lowest-overhead option on this comparison. It never decodes the database protocol; it copies bytes between two sockets, adding on the order of tens of microseconds. There is no per-statement CPU cost because there is no per-statement work — HAProxy operates on the connection, not the query.

ProxySQL pays a parse-and-match cost on every statement: it decodes the MySQL packet, computes the digest, and walks the ordered rule list. In absolute terms this is small — typically low tens of microseconds for a cached digest — but it scales with query rate and rule count, and a pathological rule list can dominate. The compensating value is that this work is exactly what removes read/write classification from your application code.

The practical reading: if your application already emits reads and writes on separate connections and you want the thinnest possible proxy, HAProxy wins on overhead. If you want to stop maintaining read/write classification in application code, ProxySQL’s per-query cost buys you that. The overhead comparison across all six tools is tabulated in the routing proxy selection guide.

Failover Behavior

HAProxy’s failover quality is entirely a function of its check. A TCP-only check is dangerous: it keeps a promoted-but-still-listening old primary in the write backend, so writes land on a node that may now be a replica. A role-aware check via pg_is_in_recovery() fixes this — on promotion the check result flips and HAProxy re-sorts the node within one inter interval. Set inter below your failover RTO so stale role state cannot outlive the promotion. HAProxy does not itself understand replication lag; to drain a lagging replica you must extend the agent script to report a drain weight based on lag.

ProxySQL’s monitor module handles both role changes and lag internally. It polls read_only to detect promotion and Seconds_Behind_Source to shun laggy readers, moving servers between hostgroups automatically. This is a more complete failover story out of the box, but it is MySQL-specific and adds monitor configuration surface. For the telemetry both approaches ultimately depend on, see detecting and handling replication lag in real time.

Configuration Snippet

HAProxy — split by port with a role-aware httpchk against a Patroni-style REST endpoint:

text
listen pg_write
    bind *:5432
    mode tcp
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 2s fall 2 rise 2 on-marked-down shutdown-sessions
    server pg1 db-1.internal:5432 check port 8008
    server pg2 db-2.internal:5432 check port 8008

listen pg_read
    bind *:5433
    mode tcp
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 2s fall 2 rise 2
    server pg1 db-1.internal:5432 check port 8008
    server pg2 db-2.internal:5432 check port 8008

A minimal role agent (if you are not running Patroni) that HAProxy can call over xinetd, returning role via exit status:

bash
#!/bin/bash
# /usr/local/bin/pg_role_check.sh  — 200 if standby, 503 if primary (read backend)
in_recovery=$(psql -tAqc "SELECT pg_is_in_recovery();" 2>/dev/null)
if [ "$in_recovery" = "t" ]; then
  printf 'HTTP/1.1 200 OK\r\nContent-Length: 2\r\n\r\nup'
else
  printf 'HTTP/1.1 503 Service Unavailable\r\nContent-Length: 4\r\n\r\ndown'
fi

ProxySQL — query rules do the same split by parsing SQL, with lag gating on the reader hostgroup:

sql
INSERT INTO mysql_servers (hostgroup_id, hostname, max_replication_lag)
VALUES (10, 'db-1.internal', 0), (20, 'db-2.internal', 3), (20, 'db-3.internal', 3);

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (10, 1, '^SELECT.*FOR (UPDATE|SHARE)', 10, 1),
       (20, 1, '^SELECT',                     20, 1);
-- everything unmatched falls through to the default writer hostgroup 10

LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;    SAVE MYSQL QUERY RULES TO DISK;

Verification and Rollback

Verify HAProxy places nodes in the correct backend from the admin socket:

bash
echo "show stat" | socat stdio /run/haproxy/admin.sock | cut -d, -f1,2,18 | grep pg_
# pg_write should list only the primary as UP; pg_read only standbys

Verify ProxySQL classification:

sql
SELECT hostgroup, digest_text, count_star
FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 10;
-- SELECT rows on hg 20; writes on hg 10

Rollback for HAProxy is a config revert plus systemctl reload haproxy, which hands off listeners without dropping established connections. For ProxySQL, deactivate the offending rule and LOAD MYSQL QUERY RULES TO RUNTIME — no restart required.

Edge Cases and Gotchas

HAProxy TCP checks give false failover confidence

A promoted old primary often keeps listening on 5432, so a plain check (TCP connect) reports it healthy and writes keep flowing to the wrong node. The role must be tested at the database layer with pg_is_in_recovery() (or the read_only variable on MySQL). Never rely on TCP reachability alone for a write backend.

ProxySQL multi-statement and prepared statements can bypass rules

Multi-statement packets and some prepared-statement paths may not match a match_digest rule as expected, letting a read fall through to the writer or a write reach a reader. Test prepared-statement-heavy workloads explicitly and add match_pattern rules where digests are unstable. This is the same class of misrouting covered in read/write splitting at the proxy layer.

Neither approach alone gives read-your-writes

HAProxy’s port split and ProxySQL’s SQL split both route on statement or connection properties, not on session causality. A user who just wrote and immediately reads can still hit a stale replica. Combine either proxy with primary-pinning after writes — see managing sticky sessions in distributed database reads — or push LSN/GTID checks into ORM middleware.

FAQ

How does HAProxy know which node is the primary?

HAProxy cannot read the database protocol, so it calls an external check. A small HTTP or xinetd agent on each node runs SELECT pg_is_in_recovery() and returns a status HAProxy interprets: 200/up for the expected role, down otherwise. The write backend accepts only the node reporting primary, and the read backend accepts only nodes reporting standby, so a promotion drains and re-routes automatically within one check interval.

Is HAProxy or ProxySQL lower latency for splitting?

HAProxy in L4 TCP mode is lower latency because it forwards packets without parsing the SQL protocol, typically adding tens of microseconds. ProxySQL parses and rule-matches every statement, adding low but non-zero per-query CPU. The trade-off is that HAProxy pushes read/write classification onto the application, while ProxySQL does it for you.

Can HAProxy split reads from writes by looking at the SQL?

No. HAProxy is a protocol-blind L4/L7 balancer and does not understand SQL. It splits traffic by backend, meaning the application must connect to different ports for reads and writes. If you need the proxy itself to classify statements, you need an SQL-aware router such as ProxySQL or pgpool-II instead.

← Back to Choosing a Read Replica Routing Proxy