Building a Custom Query Router with HAProxy

Deploying a stateless query router at the network edge is a proven strategy for achieving zero-downtime horizontal scaling across partitioned database clusters. HAProxy operates at the TCP level and excels at connection routing, health checking, and failover. However, it cannot parse SQL statement content by default — routing decisions based on query type or shard keys require a SQL-aware proxy (ProxySQL, PgBouncer) or a Lua script for payload inspection. This guide provides a production-ready implementation, focusing on precise TCP routing, health checks, and automated failover.

Architecture & Routing Topology Design

Before deploying the proxy layer, establish a deterministic mapping between application query patterns and HAProxy frontend/backend structures. Routing decisions must align with explicit partition boundaries — typically tenant_id, geographic region, or date ranges — to prevent cross-shard latency and data consistency violations.

When designing your topology, align HAProxy ACL priorities with established Cross-Partition Querying & Aggregation Strategies to ensure multi-shard operations are handled predictably. The architecture should isolate dedicated backend pools for:

Write-Primary Nodes: Handles INSERT, UPDATE, and DELETE operations routed to the authoritative shard.
Read-Replica Nodes: Distributes SELECT traffic across scaled-out replicas to offload primary I/O.
Cross-Shard Aggregator Nodes: Acts as a fallback for queries requiring federated joins or global aggregations.

Stateless proxies evaluate connections independently. Without session persistence or transaction-aware routing, subsequent queries in a multi-statement transaction may hit different backends unless sticky routing is configured. Map routing rules directly to your Application-Level Sharding Logic to maintain transaction integrity.

HAProxy Configuration Syntax & ACL Setup

HAProxy in TCP mode routes database connections without parsing SQL. To route by query type, you need tcp-request content inspection with a Lua script, or a pre-negotiated protocol-level marker. The example below shows standard TCP routing with tenant-based ACLs using req.ssl_sni (for TLS) or connection source as routing signals:

global
    maxconn 50000

defaults
    mode tcp
    timeout connect 5s
    timeout client  30s
    timeout server  30s
    option tcplog

frontend db_router
    bind *:5432
    mode tcp
    option tcplog
    # Use source IP ranges to route tenant clusters to specific backends
    acl tenant_a_network src 10.1.0.0/24
    acl tenant_b_network src 10.2.0.0/24

    use_backend shard_a if tenant_a_network
    use_backend shard_b if tenant_b_network
    default_backend aggregator_node

For SQL-content-based routing (e.g., distinguishing SELECT from INSERT), use tcp-request inspect-delay with a Lua script to parse the first bytes of the MySQL/PostgreSQL protocol. This approach has performance implications and requires careful regex anchoring to avoid partial-packet mismatches.

# SQL-content inspection via payload (requires protocol awareness)
frontend db_router_sql
    bind *:3306
    mode tcp
    option tcplog
    tcp-request inspect-delay 5s
    tcp-request content accept if { req.payload(0,10) -m found }

    # Anchored regex on first 50 bytes to identify write vs. read
    acl is_write req.payload(0,50) -m reg -i ^(INSERT|UPDATE|DELETE|BEGIN)
    acl is_read  req.payload(0,50) -m reg -i ^SELECT

    use_backend shard_primary if is_write
    use_backend shard_replica  if is_read
    default_backend shard_primary

Critical limitation: This payload inspection only works on the first query of a new TCP connection. Connection poolers reuse connections across queries, so HAProxy will never see subsequent query bytes. For per-query routing, use ProxySQL (MySQL) or a middleware layer.

Health Checks & Failover Routing

Zero-downtime execution depends on accurate node readiness validation and automatic partition failover. HAProxy supports database-specific health probes:

backend shard_a
    mode tcp
    option mysql-check user haproxy_check
    server db_a1 10.0.1.10:3306 check inter 5s fall 3 rise 2
    server db_a2 10.0.1.11:3306 check backup

backend shard_b
    mode tcp
    option mysql-check user haproxy_check
    server db_b1 10.0.2.10:3306 check inter 5s fall 3 rise 2
    server db_b2 10.0.2.11:3306 check backup

backend aggregator_node
    mode tcp
    server agg1 10.0.3.10:3306 check inter 10s fall 2 rise 3

Use option pgsql-check for PostgreSQL backends. The fall 3 rise 2 thresholds define how many consecutive failed/successful health checks trigger failover or recovery. The backup directive marks a server as standby; HAProxy only routes to it when all non-backup servers in the pool are down.

Performance Optimization & Connection Management

High-throughput workloads require precise tuning of HAProxy’s connection lifecycle. Key settings:

maxconn at both global and backend levels: Set to match your database’s max_connections minus reserved slots for superuser and replication.
timeout client and timeout server: Match your longest expected query duration plus network RTT. Long-running aggregations may need 30–60s.
stick-table: For read-your-writes consistency, bind a client session to a specific backend for its duration:

backend shard_primary
    mode tcp
    stick-table type ip size 100k expire 30s
    stick on src
    server db_primary 10.0.1.10:5432 check inter 5s fall 3 rise 2

For distributed deployments, coordinate routing weights with cross-datacenter topology. Route local application traffic to geographically proximate database partitions first, using HAProxy’s weight directive to prefer low-latency paths while preserving global failover capability.

Failure Mode Analysis & Operational Mitigation

Failure Mode	Root Cause	Mitigation Strategy
High CPU Overhead on Payload Inspection	Complex or greedy regex on `req.payload` forces HAProxy to buffer and scan packets	Use anchored, literal prefix matching (`-m beg` or `-m str`) restricted to the first 50 bytes where SQL verbs reside
Split Routing Mid-Transaction	HAProxy routes per-connection by default; connection poolers reuse connections	Use `stick-table` to bind sessions to a backend, or route at the application layer with explicit transaction affinity
Overlapping ACL Priorities	Broad `is_read` rules above specific tenant rules route all reads to the wrong backend	Order `use_backend` directives from most specific to least specific

FAQ

Can HAProxy route based on SQL query content? Only the first query on a new TCP connection, using tcp-request inspect-delay and req.payload ACLs. Connection-pooled environments will not benefit from per-query routing at the HAProxy level; use ProxySQL or a SQL-aware middleware layer.

How does HAProxy handle cross-shard joins? HAProxy cannot execute joins. It routes connections to a designated aggregator node or requires application-level query decomposition to fetch and merge results client-side.

Does HAProxy support database connection pooling? No, HAProxy routes raw TCP streams. Pair it with PgBouncer (PostgreSQL) or ProxySQL (MySQL) for true connection pooling, query caching, and advanced transaction management.