This content originally appeared on Jonnie Hallman (@destroytoday) and was authored by Jonnie Hallman (@destroytoday)
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.
This content originally appeared on Jonnie Hallman (@destroytoday) and was authored by Jonnie Hallman (@destroytoday)
Jonnie Hallman (@destroytoday) | Sciencx (2021-06-28T12:00:00+00:00) Stabilizing PostgreSQL memory issues with pgbouncer. Retrieved from https://www.scien.cx/2021/06/28/stabilizing-postgresql-memory-issues-with-pgbouncer/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.