To scale read traffic on PostgreSQL streaming replication slaves, I want to be able to load-balance the requests. Postgres documentation suggests using tools like pgPool and pgBouncer, but I am wondering if there is something wrong (in principle) with using a TCP load balancer (like HAProxy or AWS Elastic Load Balancer) fronting the postgres read slaves.
The load balancer serves as a single Read End Point for read requests that need to be made by clients. A significant advantage would be that read requests are not impacted when read slave servers go down, as other servers in the load balancer can pick up the load.
Best Answer
There's nothing wrong with it in principle, though I haven't used it myself yet.
Most client applications maintain long sessions - so the load balancing is effectively always "sticky". This can easily lead to one node having a lot more load than other nodes. A workaround can be to intentionally limit connection duration, forcing apps to reconnect occasionally and potentially be assigned a different node. That'll also let you shrink the system when load is light by collapsing work onto fewer nodes, rather than being stuck with lots of nodes, each mostly idle with only one or two active connections.
Your applications must be written carefully to check all return codes / handle exceptions. They must retry transactions that fail due to transient errors like connection loss, which means they have to remember everything they did until they commit each transaction. This is good application design anyway, it's just easier to get away with being lazy about it if disconnects and transaction aborts aren't a normal part of day to day operations.
In theory PgPool can allow "transparent" load balancing to read replicas while still permitting write queries to the master. In practice I find it's usually fiddly and hard to get right, so I usually want to have the app aware of separate "read-only" and "write/master" connections anyway.
The main advantage PgBouncer offers over TCP load balancing is that (in transaction pooling mode) it lets applications maintain idle connections without imposing overhead on the backing PostgreSQL server(s). So you can do your expensive handshake, SSL negotiation, authentication, etc once against PgBouncer, and let it bind the connection to an actual PostgreSQL backend from the pool only when it's doing something. This allows optimal utilization of Pg backends and reduces the number of idle backends sitting around doing nothing except using up resources and waiting for work. This is only valuable if your application can cope with transaction pooling (or statement pooling) mode, though; there's no benefit if you can only use session pooling mode due to use of session variables, listen/notify, advisory locks, or whatever.
If you TCP load balance you should be able to do SSL offloading just like with PgPool or PgBouncer, at least with a smart enough load balancer. You won't be able to do things like client certificate authentication that depends on pg_hba.conf, though.
I tend to scale up rather than out most of the time, so as yet this isn't something I've needed to explore. I've got some work in the pipeline that'll make it very interesting to scale out with Pg, though, so I'll have to play with this.