Configure PGBouncer with django

Using Django Deployment
1

Hello!

I have a website up and running but keep getting “OperationalError“ in Sentry. I have read the Heroku documentation and it seem that configuring a PGBouncer would help me a lot.

Here are the articles that I have read:

  1. PgBouncer Configuration | Heroku Dev Center
  2. Server-Side Connection Pooling for Heroku Postgres | Heroku Dev Center

Have anyone encountered the issue described below and can guide me on how I should treat this error?

If PGBouncer is not the right way to go, what should I do?

```

connection failed: connection to server at "XX.XXX.XXX.XXX", port 5432 failed: FATAL:  too many connections for role "u9b3d5hgd8isgf"

```

This part of the settings file seem to be relevant:

```
DATABASES = {

“default”: dj_database_url.config(

conn_max_age=300,

conn_health_checks=True,

ssl_require=True,

),

}
```
2

pgbouncer in transaction-pooling mode allows you to have thousands of clients run queries against your database. It comes with a trade-off though: you can’t use postgres features tied to sessions/connections.

One of those features you can’t use, and a common pgbouncer+django gotcha, is server side cursors, which is Django’s default behavior. You’ll need to disable that: Settings | Django documentation | Django

There can still be limits though. Pgbouncer and Postgres both have settings for max allowed connections. You’ll need to make sure both pgbouncer is configured to to use fewer than your Postgres’s max connections, and you need to make sure you’ve configured pgbouncer to allow enough connections for your application.

pgbouncer can handle 5k, 10k, maybe more connections. It is single threaded, so you can check to see its CPU utilization and if you’re still using less than a core you may be able to push up its max connections higher.

3

Proper Setup (Simplified Steps)

1. Add Pg bouncer buildpack before Python buildpack in Heroku.

heroku buildpacks:add GitHub - heroku/heroku-buildpack-pgbouncer: Classic buildpack that installs pgbouncer in the dyno alongside your app.

heroku buildpacks:add heroku/python

2. Set environment variable:

heroku config:set PGBOUNCER_TRANSACTION_POOLING=true

3. Update your settings:

DATABASES = {

"default": dj_database_url.config(

    conn_max_age=0,  # let PgBouncer manage pooling

    ssl_require=True,

),

}

DISABLE_SERVER_SIDE_CURSORS = True

4. Restart Heroku dynos:

heroku restart

My Opinion:

Pg bouncer is absolutely the right step for your situation.

Heroku’s built-in Postgres service + PgBouncer in transaction pooling mode

gives you stability, lower latency, and prevents “too many connections” errors.