Proxy Routing vs Application-Level Sharding: Decision Guide

This walkthrough helps you decide where shard-routing logic should live — in a dedicated proxy tier, one of the proxy routing architectures covered in this section, or inside your services as application-level sharding logic — as part of the wider cross-partition querying & aggregation strategies guide. Both models answer the same question (“which shard holds tenant 4217?”), but they distribute the costs differently: a proxy (ProxySQL, pgcat, HAProxy) buys application transparency and a single authoritative shard map at the price of an extra network hop and a new failure domain; in-process routing buys minimal latency and no new infrastructure at the price of re-implementing the map in every language and coupling map changes to deploys.

What you need to know first

Gather these facts about your estate — the decision table in Step 5 keys off them:

• Number of programming languages that talk to the sharded database (1, 2, or many)
• Number of services and teams that would each need an embedded router
• Your p99 latency budget per request, and how many queries a typical request issues
• How often the shard map changes (only during resharding events, or continuously as tenants move)
• Whether an infra/DBA team exists that can own a proxy tier as a 24/7 production service
• Current connection counts per database — proxies double as poolers, which matters near max_connections
• Protocol in use: ProxySQL speaks MySQL, pgcat/PgBouncer speak PostgreSQL, HAProxy is protocol-agnostic at L4

Step 1 — Map the latency and failure domains

Start with the physics. A proxy adds one round trip per query: budget 0.1–0.5 ms when the proxy runs as a sidecar or on the same host, 0.5–2 ms cross-zone. A request that issues five sequential queries therefore pays 0.5–10 ms extra — negligible for a 200 ms page render, decisive for a 5 ms trading path. In-process routing adds effectively nothing: the shard lookup is an in-memory hash or dictionary probe measured in microseconds.

Failure domains cut the other way. With application-level sharding, routing fails only if the application fails — there is nothing new to break, but there are N copies of routing code that can each break differently. A proxy tier is one new thing that can break for everyone at once: a bad config reload, a connection-table exhaustion, or an undersized instance turns into a total outage rather than a partial one. Treat the proxy as tier-0 infrastructure from day one:

# Minimum viable proxy tier: never one instance
proxy_tier:
  instances: 3            # spread across availability zones
  fronted_by: keepalived  # or an NLB / DNS with health checks
  health_check: "SELECT 1"
  drain_timeout_s: 30     # finish in-flight txns before restart
  alerting:
    - proxy_up < instances            # instance loss
    - client_conn_used / max > 0.8    # connection saturation
    - backend_error_rate > 0.1/s      # shard connectivity

Operational note: Proxies also remove a failure mode: they pool connections, so 400 application pods no longer hold 400 × pool_size direct connections against each shard’s max_connections. If you are already near that ceiling, the proxy pays for its hop immediately.

SRE tip: Measure the hop yourself before deciding — run your real workload through a single proxy instance in staging and diff the p50/p99 histograms. Teams routinely discover the pooling benefit makes the proxied path faster at p99 because connection storms disappear.

Step 2 — Compare shard-map update mechanics

The shard map changes every time you split a hot shard, add capacity, or migrate a tenant. How that change reaches running traffic is the sharpest operational difference between the models.

With a proxy, the map is runtime configuration, updated centrally without touching applications. ProxySQL routes by query rule and applies changes with a runtime load:

-- ProxySQL admin interface: move tenant range to new hostgroup 30
UPDATE mysql_query_rules
SET destination_hostgroup = 30
WHERE rule_id = 12;   -- rule matching /* shard_key: 4000-4999 */ comments

LOAD MYSQL QUERY RULES TO RUNTIME;   -- atomic, in-flight queries unaffected
SAVE MYSQL QUERY RULES TO DISK;

pgcat takes the same shape declaratively — edit the pool definition and SIGHUP:

# pgcat.toml — shard 2 just moved to a new primary
[pools.orders.shards.2]
servers = [["10.0.3.21", 5432, "primary"]]
database = "orders_s2"

With application-level sharding the map is code or config baked into every service, and an update is a coordinated redeploy. The standard mitigation is versioning the map and gating cutover on a flag rather than the deploy itself:

# shard_map.py — deployed to every service that touches the database
SHARD_MAP_VERSION = 12
RANGES = [
    (0,    3999, "postgres://orders-s0.internal:5432/orders"),
    (4000, 7999, "postgres://orders-s1.internal:5432/orders"),
    (8000, 9999, "postgres://orders-s2.internal:5432/orders"),  # new in v12
]

def dsn_for(tenant_id: int) -> str:
    if not flags.enabled("shard_map_v12"):      # cutover gate, not deploy gate
        return legacy_dsn_for(tenant_id)
    for lo, hi, dsn in RANGES:
        if lo <= tenant_id <= hi:
            return dsn
    raise LookupError(f"tenant {tenant_id} not in shard map v{SHARD_MAP_VERSION}")

The dangerous window is mixed versions: during a rolling deploy, pods on map v11 write tenant 8500 to shard 1 while v12 pods write it to shard 2. A proxy has no such window — the runtime load is atomic at one place.

Operational note: Whichever model you choose, store the authoritative map in one system of record (a shard_map table or config service) and have both proxies and services derive from it. Hand-edited copies drift, and drift means rows on the wrong shard.

DBA tip: During tenant moves, enforce correctness below the router: REVOKE INSERT on the moved tenant’s old shard rows via row-level security or a check constraint, so a stale router errors loudly instead of writing silently to the wrong place.

Step 3 — Weigh language count and team structure

Routing logic is code someone must own. In a single-language shop, an in-process router is one well-tested library, and application-level sharding is cheap. In a polyglot organisation, the same logic exists in Python, Go, Java, and PHP — four implementations of hashing, range lookup, retry, and failover that must agree exactly. A one-line difference in hash normalisation (signed vs unsigned, UTF-8 vs bytes) sends the same key to different shards from different services, and the corruption is silent until a customer reports missing data.

Rules of thumb from production estates:

• 1 language, 1–5 services: embed the router; a proxy is pure overhead.
• 1 language, many services: embed it as a versioned internal library with a conformance test suite (golden file of key → shard pairs every release must reproduce).
• 2+ languages: centralise. Either a proxy tier, or a thin internal “data access” gRPC service that owns routing — which is architecturally a proxy you wrote yourself.
• No team to run a proxy 24/7: do not deploy one. An unowned tier-0 component is worse than duplicated libraries.

SRE tip: The conformance suite matters even with a proxy — batch jobs, migration scripts, and BI extracts often bypass the proxy “just this once”. Publish the golden key → shard fixture and make every bypass consumer test against it in CI.

Step 4 — Plan the migration path between models

You are rarely choosing from scratch; you are usually escaping the model you have. Both directions are well-trodden:

App-level → proxy. Point each service’s existing per-shard DSNs at the proxy’s per-shard ports (or pools) instead of the databases. The application keeps computing the shard for now — the proxy initially does only pooling and failover. Then move routing into the proxy one query class at a time (ProxySQL query rules matching comments like /* shard_key: N */ that services already emit), and finally collapse the app’s map to “send everything to the proxy”. Each step is independently reversible.

Proxy → app-level. Ship the routing library to all services behind a flag, run it in shadow mode — compute the shard in-process, still send via the proxy, and emit a metric when the two disagree. After a week at zero disagreements, flip services to direct connections one at a time, and keep the proxy running as the fallback path until connection counts on the shards stabilise.

# Shadow-mode disagreement rate — must be zero before cutover
sum(rate(shard_router_disagreement_total[5m])) by (service)

Operational note: The hybrid endpoint of the first migration — app computes the shard, proxy owns pooling and server topology — is a legitimate permanent architecture, not just a waypoint. Many large estates stop there deliberately.

Step 5 — Apply the decision table

Scenario recommendations:

• Polyglot SaaS, tenants migrate between shards monthly: proxy tier. Central map updates and query mirroring during moves outweigh the hop.
• Single Go monolith plus workers, p99 budget under 10 ms: application-level, shipped as one library with the conformance fixture.
• 400+ pods brushing against max_connections: at minimum the hybrid — you need the pooler regardless of where routing lives.
• Two teams, no one owns infra: application-level, even if polyglot; duplicated libraries fail more gracefully than an orphaned proxy.
• Heavy scatter-gather analytics on top of OLTP routing: either model for OLTP, plus a federation engine for the analytical fan-out — see the cross-partition querying & aggregation strategies overview for that layer.

Failure mode table

FAQ

Related

• Proxy Routing Architectures — parent guide covering proxy topologies, connection pooling, and shard-map management in depth
• Building a Custom Query Router with HAProxy — hands-on build of the proxy side of this decision, with health checks and routing ACLs
• Application-Level Sharding Logic — the in-process side: router libraries, shard-map versioning, and per-language implementation patterns