Connection Multiplexing and Proxying

you will evaluate the use of database proxies to distribute read and write traffic.

In this lesson, you will learn how database proxies act as intelligent intermediaries to intelligently route read and write traffic across your PostgreSQL cluster.

The Problem: Scaling Reads and Writes

In a typical high-availability setup—such as one managed by Patroni—you have a single primary node for writes and multiple replicas for reads. If your application sends all traffic to the primary, you quickly hit performance bottlenecks. Conversely, if your application manually manages which connection string to use for reads versus writes, you introduce fragility: what happens if a replica fails or a failover promotes a new primary?

How Proxies Distribute Traffic

A database proxy (like HAProxy or PgPool-II) sits between your application and your database cluster. Instead of connecting directly to an IP address, the application connects to the proxy. The proxy inspects the SQL statements and makes routing decisions based on the operation type:

Write Traffic: The proxy directs all INSERT, UPDATE, and DELETE queries to the current primary node.
Read Traffic: The proxy balances SELECT queries across the available read replicas.

This is often implemented using simple protocol parsing:

-- Application connects to Proxy Port 5432
-- If it's a DML statement, the proxy routes it here:
-- 10.0.0.1:5432 (Primary Node)

-- If it's a SELECT statement, the proxy distributes it:
-- 10.0.0.2:5432 (Replica 1)
-- 10.0.0.3:5432 (Replica 2)

Why Use a Proxy?

Beyond simple load balancing, proxies provide an abstraction layer. If you need to perform maintenance on a specific replica, you can configure the proxy to stop sending traffic to that node without the application ever knowing the difference.

However, you must be careful with read-after-write consistency. Since proxies are often unaware of replication lag, they might route a SELECT query to a replica that hasn't yet received the data you just inserted on the primary. While proxies solve the connection distribution problem, they do not automatically solve the inherent latency issues associated with physical replication.

Key Takeaway: A database proxy decouples your application from the physical topology of your cluster, allowing you to scale read traffic horizontally while providing a single, consistent endpoint for all database operations.