The read sees eventual consistency with a lag of 2-3 seconds. The write that just happened on the primary is not visible on the replica for 2-3 seconds. If the application needs read-after-write consistency, it must either read from the primary after writes or use synchronous replication. For most web applications, 2-3 seconds of lag is acceptable. For financial or inventory systems, it is not.

The lagging replica is a bottleneck — it cannot replay WAL entries as fast as the primary generates them. This is usually caused by slower disk I/O (the replica is applying changes from disk rather than cache), less CPU than the other replicas, or a long-running query on that replica blocking the replication apply thread. Check the replica's CPU, disk throughput, and pg_stat_activity for blocking queries. The fix is either to upgrade the replica's hardware, isolate it from other workloads, or use a load balancer that routes reads away from the lagging replica until it catches up.

Asynchronous replication: the primary commits writes and responds to the client immediately, then replicates to replicas in the background. Replicas may lag by milliseconds to seconds. Read/write splitting with asynchronous replication means some reads return stale data. Synchronous replication: the primary waits for at least one replica to confirm the write before committing. This guarantees the replica has the write immediately, eliminating stale reads on replicas that have confirmed. The tradeoff is write latency — every write now waits for a network round-trip to the replica.

ProxySQL uses the mysql_query_rules table to define routing. Key rules: for transactions marked as read-only (via SET TRANSACTION READ ONLY), route to the replica hostgroup; for writes (INSERT, UPDATE, DELETE, or transactions not marked read-only), route to the primary hostgroup. You also need to configure mysql_servers to define primaries and replicas with their hostgroups, and mysql_users to authenticate. The scheduler in ProxySQL can also run periodic checks to verify replica lag and dynamically adjust weights. The rule order matters — ProxySQL evaluates rules top-down and applies the first matching rule. Use match_digest for regex matching on query patterns and apply=1 to ensure the rule is applied.

GTID (Global Transaction Identifier) assigns a unique UUID + sequence number to each transaction. File-position replication uses binlog filename and byte offset to track replication position. GTID simplifies failover because you specify the GTID position to resume from rather than a file+offset. It also makes it easier to detect skipped transactions and verify replica consistency. Choose GTID when you need automatic position tracking, easier failover, or when using MySQL 8.0+ (GTID is the default). Choose file-position when using older MySQL versions, when you need finer-grained control over replication position, or when your existing tooling depends on binlog coordinates.

Statement-based replication (SBR) logs SQL statements — lower storage but nondeterministic for functions like NOW() or UUID(). Row-based replication (RBR) logs actual row changes — deterministic but larger binlogs for bulk operations. Mixed mode switches between them: uses SBR for deterministic statements, RBR for nondeterministic ones. For read/write splitting, row-based is safer because it correctly replicates all changes regardless of function determinism. MySQL 8.0 defaults to row-based. SBR can cause data drift between primary and replica when nondeterministic functions produce different values.

Auto-commit means every statement is its own transaction. In read/write splitting, you want to route multi-statement transactions to the primary to maintain consistency. With auto-commit, each SELECT is a separate transaction — the router can send each to a different replica, which means two reads in the same logical operation hit different replicas with different lag states. Additionally, if the ORM sends BEGIN implicitly or uses connection pooling with transaction interleaving, a read can be routed to a replica while the previous write is still pending on the primary. Fix by disabling auto-commit at the application level, using explicit transactions for related query groups, or ensuring the routing layer tracks transaction context.

Configure max lag tolerance in your routing layer. ProxySQL has max_replication_lag per server — if lag exceeds this, the server is excluded from the active pool. Application-level: check pg_stat_replication (PostgreSQL) or SHOW REPLICA STATUS (MySQL) and only route reads to replicas whose lag is below your tolerance. Use weighted routing: replicas with lower lag get more weight. For PostgreSQL, synchronous_commit = remote_apply ensures replicas are fully current before the primary commits, eliminating lag at the cost of write latency. Alternatively, use a connection pool that monitors lag and temporarily excludes lagging replicas.

Sticky routing binds a user's session to a specific replica for a period after they write. The idea: the replica that just received the write (or is closest to the primary) stays associated with that user for subsequent reads. This reduces read-after-write inconsistency because the user's reads hit the same replica that has already received their writes. Implementation: track the LSN or GTID position after writes, and route the user's reads to a replica that has replayed past that position. Most load balancers and proxies support session affinity (sticky cookies or source IP). The tradeoff is reduced load distribution — some replicas may be overloaded if their associated users are more active.

pgpool-II is a PostgreSQL-specific pooling and routing solution. It supports read/write splitting via primary_router_mode and query routing rules. ProxySQL is database-agnostic (works with MySQL, PostgreSQL, MariaDB). pgpool-II runs inline — queries pass through pgpool — while ProxySQL runs as a sidecar proxy. pgpool-II can be simpler for pure PostgreSQL setups but adds latency as queries pass through it. ProxySQL offers more granular routing rules, better health checking, and is more flexible for multi-database environments. Both support connection pooling, health checks, and query routing. Choose pgpool-II for a tightly integrated PostgreSQL solution; choose ProxySQL for heterogeneous database environments or when you need more routing control.

Implement a tiered routing strategy. Route inventory checks, payment processing, and any query that must reflect recent writes to the primary. Route product browsing, search results, and cached content to replicas. At the application level, annotate queries: mark some as requires_consistency=true and those go to primary; others go to replicas. Some ORMs support this via hinting or explicit read/write mode. Alternatively, use synchronous replication for the primary + one replica (synchronous target) so that replica is always current — this replica handles the consistency-sensitive reads while other replicas handle purely eventual-consistent reads.

Key metrics: replica lag per replica (alert if > 30 seconds), primary CPU and connection count, replica CPU and connection count, query latency by routing target (primary vs replica), error rate on replicas, and replication slot age (PostgreSQL). Set alerts: replica lag > your RPO threshold (e.g., 30s), primary connections > 80% of max, replica CPU > 80%, query latency p99 exceeding your SLA. Track the ratio of reads going to primary vs replicas — if primary reads are high (above 20% for read-heavy workloads), your routing is misconfigured. Monitor replication I/O and SQL thread status in MySQL to catch replica failures early.

Connection pooling (PgBouncer, ProxySQL pooling) sits in front of your routing layer. If the pool is not shard-aware, connections from the pool may hit different replicas for the same session, defeating sticky routing and increasing read-after-write inconsistency. Pitfalls: if you use transaction-mode pooling and read/write splitting at the proxy level, a single connection may be used for both reads and writes within a transaction, causing routing confusion. The fix: ensure the pool is configured to route connections appropriately based on query type, or implement routing at the application level before the pool. Also ensure the pool does not hold connections to replicas that are lagging behind — pool health checks should include replication lag.

Cascading replication compounds lag. If primary commits at T0, replica1 receives at T0 + 100ms (network), and replica2 receives from replica1 at T0 + 200ms. The lag for replica2 is effectively double the single-hop lag. For read/write splitting, cascading makes sense only if the intermediate replica (replica1) also serves reads — otherwise you are adding latency for no benefit. Use cascading when you need many replicas and the primary cannot handle all replication streams simultaneously. Monitor lag at each tier — a lagging intermediate replica causes all downstream replicas to lag equally. If latency matters for your reads, avoid cascading or use synchronous replication to the first tier.

A query joins across multiple tables that were recently updated. The write committed on the primary. The replica has applied the change to one table but not yet to a joined table due to different replication apply times. The read hits the replica, sees the updated first table but stale joined table, and returns rows that never existed together on the primary — silently incorrect join results with no error. This is a read-after-write inconsistency that manifests as wrong query results rather than missing data. The application sees what appears to be correct data but is actually a cross-section of old and new states. The fix: use primary for queries that join recently-written tables, or use synchronous replication.

Prepared statements cache execution plans. In PostgreSQL, a generic plan is cached for a prepared statement regardless of parameter values. For partitioned tables, this can be problematic — the cached plan may be suboptimal for different parameter values. For read/write splitting specifically, if a prepared statement is first executed with a write (routed to primary) and then reused for reads (routed to replica), the plan may be optimized for the primary's statistics rather than the replica's. Use PREPARE ... FOR EXECUTE with explicit execution rather than generic prepared statement reuse across different query types. For read/write splitting with replicas, prefer client-side parameter binding with custom plans rather than generic cached plans.

Replication slots prevent WAL from being removed on the primary until the replica acknowledges receiving it. This ensures that if a replica falls behind or disconnects, the primary retains the WAL needed to resync. In a read/write splitting setup, if a replica falls behind and the replication slot is not properly configured, the primary's disk can fill with WAL that cannot be recycled — causing the primary to stop accepting writes. Conversely, if a slot is misconfigured with max_slot_wal_keep_size too small, the primary may remove WAL that a lagging replica still needs, and when that replica reconnects it cannot resume and must be re-initialized, causing data loss. Always monitor replication slot age and ensure wal_keep_size or max_slot_wal_keep_size is set appropriately for your replica lag tolerance.

Vitess is a database middleware that sits in front of MySQL and handles sharding and read/write splitting transparently. It routes reads to replicas and writes to primaries based on SQL parsing — it understands SQL and can determine query type without application hints. Unlike application-level routing (where routing logic is in your code) or proxy-level routing (like ProxySQL with rules), Vitess provides a single logical database endpoint and handles routing internally. It also handles failover, resharding, and connection pooling automatically. The tradeoff is operational complexity — Vitess requires its own cluster management. For read/write splitting without full sharding, Vitess is heavyweight; ProxySQL or application-level routing is simpler. Use Vitess when you need both read/write splitting and horizontal sharding together.

Mixed transactions are the hardest problem in read/write splitting. A transaction like BEGIN; SELECT (read); INSERT (write); SELECT (read); COMMIT must route the read to a replica but the write to the primary — but within a single transaction, you cannot have two different connections to two different servers. The router must detect this pattern and route the entire transaction to the primary, defeating read scaling for that transaction. Some proxies (ProxySQL) detect transaction type from the first statement and pin the connection — if the first statement is a write, the whole transaction goes to primary; if read-only, it can go to replica. Application-level routers handle this explicitly by tracking transaction state. The failure mode: an ORM that uses auto-commit or interleaves statements can route reads to replicas within a transaction that has pending writes on the primary, returning stale data for those reads. The safest approach: at the application layer, mark transactions as read-only or read-write explicitly, and route accordingly. If your framework auto-commits every statement, every statement is a new transaction and routing is per-statement (not per-batch), which is easier to handle.

Long-running transactions that started on the primary before failover may fail or produce inconsistent results on the new primary — the transaction's session state is lost, locks may be held differently, and the new primary has a different replication position. If the transaction was read-only, it may simply fail to commit. If it was a write transaction, the writes may be lost entirely. Strategies to minimize failures: keep transaction durations short so they are less likely to span a failover event. Use application-level transaction retry logic with idempotent operations — catch connection errors and re-execute failed transactions. Avoid multi-statement transactions that hold locks across the failover window. For read/write splitting specifically, use sticky sessions to route the same user to the same replica, reducing the chance that a transaction spans a failover. Monitor in-flight transaction count and alert before failover — a spike in long-running transactions indicates higher risk during promotion.