Nerdearla 2026 Slides: SQL Traffic Routing Without Breaking Consistency

The slides from my Nerdearla 2026 talk, SQL Traffic Routing Without Breaking Consistency, are now available.

Read or download the presentation (PDF, 50 slides).

A ticket-booking example runs through the presentation. It exposes the engineering problem at the center of the talk: how to make better use of database capacity while preserving the behavior the application depends on.

Start with the read’s requirements

A customer books a ticket. The application commits the write, then reads the booking to display a confirmation. If that read goes to a replica that has not applied the transaction, the booking appears to be missing.

The replica might be reachable, healthy, and only slightly behind. None of those facts proves it can serve this particular read correctly.

The application usually has several kinds of reads. A catalog page may tolerate some staleness. A confirmation page must see the preceding write. A reservation transaction may require locking and a consistent transaction context. Those requirements should drive routing policy.

That is why a blanket rule sending every SELECT to a reader is an incomplete design. Statement text is only part of the decision; session state, transactions, backend eligibility, and freshness requirements also matter.

Pool connections without losing session semantics

Application scaling often multiplies connection pools. If each application instance opens 20 database connections, 30 instances can mean 600 connections, even when relatively few are executing SQL at once.

ProxySQL can keep client connections open while reusing a smaller pool of backend connections. The useful distinction is between connected clients and concurrent database work. Pooling can reduce connection overhead, but it does not create more execution capacity in the database.

Reuse also has conditions. An active transaction needs its backend context. Session settings must be tracked and synchronized where supported; other state can require connection affinity. Prepared statements introduce mappings between the handles clients know and the statements prepared on individual backends.

The slides walk through these cases for MySQL and PostgreSQL. Multiplexing works by respecting their session semantics, rather than assuming every connection is interchangeable.

Read-after-write needs evidence

Several strategies can support a confirmation read. Keeping it on the writer is straightforward, provided the read uses an appropriate fresh snapshot. A temporary writer stickiness window can reduce the chance of a stale read, but a timer is not proof that replication has caught up.

For MySQL, the presentation explains GTID-based causal routing. ProxySQL tracks a dependency from the successful write response and considers which backends have executed that transaction. A reader is eligible when its executed transaction set includes the required dependency.

The Binlog Reader components in the diagram observe each backend’s own binary-log progress. They do not replicate the data. Their purpose here is to provide evidence for the routing decision.

The example connects a writer hostgroup to subsequent reads through gtid_from_hostgroup. It illustrates the mechanism; it is not a complete production configuration. A real policy also needs a deadline and a defined fallback when no reader can satisfy the dependency.

The PostgreSQL slides explore the corresponding idea using a WAL fence and evidence of standby replay progress, with compatible replication history and an appropriate snapshot. That section is explicitly a planned direction, with no release date. It does not describe a released PostgreSQL causal-routing feature.

Measure before moving traffic

Query digests help identify where routing changes might be useful. For MySQL, the presentation uses:

SELECT hostgroup, digest, count_star,
       ROUND(sum_time / 1000000.0, 2) AS total_seconds,
       digest_text
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 5;

These are accumulated statistics. To compare traffic over time, use deltas across matching observation intervals and account for resets. Total query elapsed time is not database CPU time.

With that evidence, choose a specific query family, establish its consistency requirements, and apply a targeted routing policy. The catalog query in the talk is an example of a read that can move independently of the booking confirmation.

The same discipline applies to caching and query rewrites. A cache TTL must fit the application’s tolerance for stale results; it does not automatically invalidate a result when underlying data changes. A rewrite needs testing against the workload it is intended to improve.

Failover has more than one responsibility

ProxySQL can stop selecting an unhealthy backend and direct new work to eligible servers. Writer promotion is a separate responsibility: a topology manager such as Orchestrator determines the new writer, with fencing needed to prevent the old writer from continuing to accept writes.

A stable application endpoint is useful, but it does not make an in-flight transaction survive every failure. Applications still need a recovery strategy for interrupted work.

The thread connecting the presentation is simple: the traffic layer provides mechanisms, and the application defines correctness. Start with what each operation must observe, then choose the pooling, routing, and recovery policies that satisfy it.

Explore the full presentation for the session-state diagrams, GTID example, and step-by-step booking scenario.