How do you distribute requests across multiple replicas?

Distributing requests across several replicas is a key part of a replicated architecture, letting you offload the primary server (master) and improve the system's scalability.

Here are the main approaches:

#1. A load balancer

A software or hardware load balancer routes read requests (SELECT) to different replicas. Popular solutions:

• PgBouncer, HAProxy, Pgpool-II (for PostgreSQL),
• ProxySQL (for MySQL). The load balancer tracks replica health and spreads traffic evenly. Good for automation and high fault tolerance.

#2. Application-level logic

The application decides on its own where to send a request. For example:

• every write (INSERT, UPDATE, DELETE) goes to the master;
• every SELECT query goes to one of the replicas, randomly or round-robin. Pro: flexibility. Con: it complicates the application's code and requires tracking replica health.

#3. DNS-based balancing

Every replica is given the same domain (e.g. read-db.example.com), and the DNS server returns a different IP on each lookup. Pro: simple to set up. Con: no control over load distribution or response time.

#4. Smart proxies and middleware

Modern proxies can inspect the type of SQL statement and automatically route:

• write operations to the master,
• read operations to replicas. Example: Pgpool-II, ProxySQL.

Summary: In practice, PgBouncer / HAProxy / Pgpool-II are the tools most commonly used, taking over routing and balancing reads across replicas while providing fault tolerance and staying transparent to the application.