PostgreSQL High Availability/Scalability using HAProxy and PGBouncer

Question

I have multiple PostgreSQL servers for a web application. Typically one master and multiple slaves in hot standby mode (asynchronous streaming replication).

I use PGBouncer for connection pooling: one instance installed on each PG server (port 6432) connecting to database on localhost. I use transaction pool mode.

In order to load-balance my read-only connections on slaves, I use HAProxy (v1.5) with a conf more or less like this:

listen pgsql_pool 0.0.0.0:10001
        mode tcp
        option pgsql-check user ha
        balance roundrobin
        server master 10.0.0.1:6432 check backup
        server slave1 10.0.0.2:6432 check
        server slave2 10.0.0.3:6432 check
        server slave3 10.0.0.4:6432 check

So, my web application connects to haproxy (port 10001), that load-balance connections on multiple pgbouncer configured on each PG slave.

Here is a representation graph of my current architecture:

This works quite well like this, but I realize that some implements this quite differently: web application connects to a single PGBouncer instance that connects to HAproxy which load-balance over multiple PG servers:

What's the best approach? The first one (my current one) or the second one? Are there any advantages of one solution over the other?

Thanks

Answer 1

Your existing configuration of HAProxy -> PGBouncer -> PGServer approch is better. And that only works. Here is the reason: HAProxy redirects connection to different servers. this results in MAC address change in the database connection. So if PGBouncer is above HAProxy, each time the connections in the pool gets invalidated because of MAC address change.

Answer 2

pgbouncer maintains connections in a pool with a postgres server. TCP connection establishment times are significant in a high-volume environment.

Clients making a large number of DB requests will have to setup a connection with a remote PGBouncer for each request. This is more expensive, than running PgBouncer locally (so the application connects to pgbouncer locally) and pgBouncer maintains a pool of connections with the remote PG server.

So, IMO, PGBouncer -> HAProxy -> PGServer seems to be better than, HAProxy -> PGBouncer -> PGServer, especially when the PGBouncer is local to the client application.

Answer 3

I have to disagree with the answer provided by donatello.

You see, if your application doesn't manage DB connections using a local pool, it will create a new connection each time it needs to query the DB; that happens exactly the same when using PgBouncer, so you will have a very good improvement by using it.

When PgBouncer is managing PostgreSQL connections by pooling them, the time your app spends opening a connection drops significantly, compared to when the connection is opened directly to DB. That's because PG is quite slow to check and verify credentials and everything every single time a connection is requested.

So, the approach App -> HAProxy -> [PgBouncer -> PostgreSQL] is the better one, because PgBouncer saves the connection time to PG. The pooling mode is important to take account of as well. You have to be aware of the way your app behaves. Is it mostly transactional? Or, is it more of execute a bunch of small SQL sentences with a high concurrency? All those parameters have an effect on performance.