Load balance Postgres connections in Rails with PgBouncer

This post dveles into why we chose load balance our Postgres connections at SupportBee and lists instructions for configuring PgBouncer, a popular Postgres connection pooler. Few days ago, Honeybadger, our exception tracker, alerted us that our Rails app had started to exceed the number of connections we had configured our Postgres instance to accept We had configured our Postgres instance to accept a maximum of 512 connections with 3 of those connections to be reserved for Postgres superusers

max_connections = 512
superuser_reserved_connections = 3

This gave our Rails app a maximum of 512 – 3 = 509 Postgres connections. Across our servers, we had 146 Unicorn instances and 294 Sidekiq workers. Additionally, during deployment, Unicorn starts another 146 instances during deploys to achieve zero downtime deployments before it shut down older Unicorn instances. We calculated that our Rails required atleast (146 * 2) + 294 = 586 Postgres connections. As is the typical practice at SupportBee, we started contemplating for atleast 2 possible solutions to resolve this problem and wrote those solutions along with their pros and cons. We had two possible ways ahead. Increase the number of Postgres connections or install a Postgres connection balancer PgBouncer or like Pgpool II. The former approach had considerable drawbacks

1. It’d require us to restart Postgres (which in meant scheduling a downtime and informing our customers in advance)
2. A significant number of connections will only be utilized during a deploy and will lie idle the remaining time
3. If we had increased the number of Postgres connections from 512 to say, 1024, it’d have allowed us to only add (1024 – 512) / 2 = 256 additional unicorns before we hit the connection limit again
4. It’d reduce the the amount of memory available for each connection which may likely reduce performance

Given these drawbacks, we decided to give ourselves half a day and try a Postgres connection balancer software. We had two alternatives to choose from, PgBouncer & PgPool II. We decided to try PgBouncer as it

1. Supported transaction pooling
2. Is far quicker to configure
3. Is recommended by Honeybadger (whose tech stack is similar to ours) on their blog as they found it more stable

Install PgBouncer

To install PgBouncer, install the pgbouncer apt package

apt-get update
apt-get install pgbouncer

PgBouncer’s configuration file is an .ini file. It consists of multiple sections. Edit the file

vim /etc/pgbouncer/pgbouncer.ini

In the databases section, configure a pool. We chose to start a pool of 500 connections

[databases]
mydatabasename = host=1.2.3.4 port=5432 dbname=mydatabasename user=deploy pool_size=500

Configure the ip address and port PgBouncer should listen on

[pgbouncer]

# Other settings...
listen_addr = 5.6.7.8
port = 6432

Configure authentication settings. Since the server we ran PgBouncer on is only reachable from our app servers, we chose to allow our app servers to connect to it without a password

[pgbouncer]

# Other settings...

auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt

echo '"pgbouncer" "thispasswordwontactuallybeused"' > /etc/pgbouncer/userlist.txt

Configure pool mode. We chose to use the transaction pool mode

[pgbouncer]

# Other settings...

pool_mode = transaction

Limit the maximum number of clients allowed to connect to PgBouncer. We chose 10000

[pgbouncer]

# Other settings...

max_client_conn = 10000

Restart PgBouncer

/etc/init.d/pgbouncer restart

Configure your Rails app to connect to PgBouncer

vim config/database.yml

production:
  adapter: postgresql
  database: mydatabasename
  host: 5.6.7.8
  port: 6432
  username: pgbouncer
  prepared_statements: false

Please note that if you choose to use the transaction pool mode, you must configure your Rails to not use prepared statements. Open a Rails console and ensure your Rails app can connect to PgBouncer

RAILS_ENV=production bundle exec rails console

User.count
User.last.touch

That’s it! If you’d like to learn more about PgBouncer, PgBouncer docs are an excellent resource.