Back in February, I upgraded the PostgreSQL version of Cushion’s database. I (naively) didn’t expect a simple version upgrade to have a significant effect on anything, but the following week, I started experiencing memory spikes on the PostgreSQL server, which I attempted to troubleshoot. In the process, I was able to fine-tune a number of heavy queries, but never got to the bottom of the memory issue—I simply lowered my worker count to keep connections low.

Recently, however, I dug into the issue again, and discovered pgbouncer, a connection pooler that is coincidentally supported by Heroku as of this year. pgbouncer lets your app reuse already-open database connections to avoid connection limits and out-of-memory errors—exactly the issue I’ve been facing. Enabling pgbouncer on Heroku is as easy as calling:

heroku pg:connection-pooling:attach DATABASE_URL --as DATABASE_CONNECTION_POOL

…then connecting to your database with:

ENV['DATABASE_CONNECTION_POOL_URL'] || ENV['DATABASE_URL']

Using the database URL as the fallback was helpful in safely trying out pgbouncer, as I was able to attach it to the environment variable, and if I experienced any issues, I could simply detach it without needing a new deploy. Luckily, there was no need to detach pgbouncer because it immediately got to work and stabilized my memory spikes.

There’s still so much I can do to improve Cushion’s database queries, which I’m still actively pursuing, but at least I can rest assured knowing that pgbouncer is keeping the memory issues at bay.