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. By leveraging HAProxy’s TCP payload inspection and ACL-driven routing, platform engineers and DBAs can direct traffic to specific shards, read replicas, or aggregator nodes without modifying application connection strings. This guide provides a production-ready implementation playbook, focusing on precise configuration, transaction integrity, 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 or routing target for queries requiring federated joins or global aggregations.

Crucially, map routing rules directly to your Application-Level Sharding Logic. Stateless proxies evaluate packets independently; without explicit transaction boundaries or sticky routing, mid-transaction splits will corrupt data integrity.

HAProxy Configuration Syntax & ACL Setup

HAProxy operates in pure TCP mode for database routing, requiring explicit payload inspection to parse the initial SQL query string. The configuration relies on req.payload extraction, anchored regex ACLs, and strict use_backend priority ordering.

frontend db_router
    bind *:3306
    mode tcp
    option tcplog
    tcp-request inspect-delay 5s
    tcp-request content accept if { req.payload(0,10) -m found }

    # ACLs for query type and partition routing
    acl is_read req.payload(0,10) -m reg -i ^SELECT
    acl is_write req.payload(0,10) -m reg -i ^(INSERT|UPDATE|DELETE)
    acl tenant_a req.payload(0,50) -m reg -i tenant_id=1
    acl tenant_b req.payload(0,50) -m reg -i tenant_id=2

    # Routing rules (evaluated top-to-bottom)
    use_backend shard_a_rw if is_write tenant_a
    use_backend shard_b_rw if is_write tenant_b
    use_backend shard_a_ro if is_read tenant_a
    use_backend shard_b_ro if is_read tenant_b
    default_backend aggregator_node

This configuration demonstrates payload inspection delay, regex ACL matching for SQL verbs and sharding keys, and priority-based backend assignment. When structuring these rules, ensure your evaluation order mirrors your Proxy Routing Architectures to prevent broad is_read or is_write matches from overriding tenant-specific routing. Always configure tcpka and tune timeout client/timeout server parameters to maintain transaction integrity during long-running Federated Query Execution.

Health Checks & Failover Routing

Zero-downtime execution depends on accurate node readiness validation and automatic partition failover. HAProxy supports native database-specific health probes that validate actual query execution capability rather than mere TCP port availability.

backend shard_a_rw
    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_rw
    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

The configuration above implements TCP-level health probes using mysql-check (or pg-check for PostgreSQL), defines primary/backup server roles, and sets strict failure thresholds (fall 3 rise 2) for automatic failover. During scheduled maintenance or unexpected partition outages, traffic seamlessly shifts to the backup node. To prevent cascading failures, integrate Fallback Routing Mechanisms that queue or redirect queries to the aggregator node when all primary and replica shards in a partition report degraded states.

Performance Optimization & Connection Management

High-throughput database workloads require precise tuning of HAProxy’s connection lifecycle. Disable HTTP mode entirely to enforce pure TCP routing, eliminating unnecessary header parsing overhead. Adjust maxconn at both the frontend and backend levels based on your database’s connection limits and query execution profiles. Implement connection rate limiting per backend using stick-table or rate-limit directives to prevent noisy-neighbor scenarios from overwhelming individual shards.

For distributed deployments, coordinate routing weights with Cross-Datacenter Partition Routing strategies. Route local application traffic to geographically proximate database partitions first, using HAProxy’s weight and backup directives to maintain low-latency paths while preserving global failover capability.

Failure Mode Analysis & Operational Mitigation

Stateless SQL routing introduces specific operational risks. Understanding these failure modes is critical for maintaining zero-downtime SLAs.

Failure Mode Root Cause Mitigation Strategy
High CPU Overhead on Payload Inspection Complex or greedy regex patterns on req.payload force HAProxy to buffer and scan entire packets, increasing routing latency. Use anchored, literal prefix matching (-m beg or -m str) instead of full regex. Restrict inspection to the first 50 bytes where SQL verbs and partition keys reside.
Split Routing Mid-Transaction HAProxy routes per-request by default. Without session persistence or transaction-aware routing, subsequent queries in a multi-statement transaction hit different backends. Enforce application-level transaction boundaries. Use stick-table with tcp-request content track-sc0 to bind a client IP or session ID to a specific backend for the duration of the transaction.
Overlapping ACL Priorities HAProxy evaluates ACLs sequentially. Placing broad is_read rules above specific tenant rules routes all reads to the default backend, bypassing partition logic. Order use_backend directives from most specific to least specific. Always place tenant/shard-specific ACLs above generic read/write routing rules.

Frequently Asked Questions

Can HAProxy route based on SQL query content? Yes, using req.payload inspection and ACL regex matching on the initial TCP packet containing the SQL query string.

How does HAProxy handle cross-shard joins? HAProxy cannot execute joins. It routes traffic 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, ProxySQL, or a similar database proxy for true connection pooling, query caching, and advanced transaction management.

Back to Proxy Routing Architectures