yttrx mastodon pgbouncer

I don’t understand why pgbouncer isn’t enabled by default

For context, I’m running mastodon within docker, with all services running on a single VM. This means that networking is fairly straight forward, and the usage of the system is small, so the complexity of it is also small. But something that I keep reading, is that sooner or later, you’ll start running out of postgresql connections as you start needing to scale up your sidekiq workers.

So why not nip that in the bud?

“The time to fix the roof is when the sun is shining.”

My server’s utilization is quite low, so why not take the time to configure pgbouncer to sit between sidekiq and all of the workers.

postgresql prep

I started by googling around, and came across this guide from 2018, and started adapting it for my docker setup. The first thing that I did was configure a password. The reason that this is necessary, is under a docker configuration, postgres just accepts connections from localhost, so it’s not necessary to set any auth, because we’re all cool here and we can all be trusted. Right? Well, not really.

I ran:

docker exec -it live_db_1 psql -U postgresql

to get into psql, followed by ALTER USER mastodon WITH PASSWORD 'secret'; to set the password (but I used something other than “secret”).

Installing pgbouncer

I googled around a little more, and came upon this small pgbouncer docker image, and filled in my docker-compose.yml with the following:

  pgbouncer:
    restart: always
    image: edoburu/pgbouncer
    networks:
      - internal_network
    depends_on:
      - db

running docker-compose up -d installed the image, and started the container, which immediately started crashlooping. I re-read the docs and started filling in the following environment variables:

  pgbouncer:
    restart: always
    image: edoburu/pgbouncer
    networks:
      - internal_network
    depends_on:
      - db
    environment:
      - 'DB_HOST=db'
      - 'DB_NAME=mastodon_production'
      - 'DB_USER=mastodon'
      - 'DB_PASSWORD=secret'

Ran docker-compose up -d, and then docker logs --follow live_pgbouncer_1 and saw that it started up cleanly. Hurrah!

I then added dependencies of pgbouncer to the other containers (which depended on db), and updated the .env.production file to point to pgbouncer instead of db. Ran docker-compose up -d once more, and everything was working again, but flowing through pgbouncer now.

Last tweaks

Added some max connections settings, and resulted in the following changes to my docker-compose.yml file (I keep daily backups of my configs, and I compared the changes with diff against yesterday’s version of the file):

diff -Naur /backup/dailies/docker-compose.2022-11-29.yml docker-compose.yml
--- /backup/dailies/docker-compose.2022-11-29.yml       2022-11-29 00:00:35.962626591 +0000
+++ docker-compose.yml  2022-11-30 06:29:13.308741138 +0000
@@ -13,6 +13,21 @@
     environment:
       - 'POSTGRES_HOST_AUTH_METHOD=trust'
 
+  pgbouncer:
+    restart: always
+    image: edoburu/pgbouncer
+    networks:
+      - internal_network
+    depends_on:
+      - db
+    environment:
+      - 'DB_HOST=db'
+      - 'DB_NAME=mastodon_production'
+      - 'DB_USER=mastodon'
+      - 'DB_PASSWORD=secret'
+      - 'MAX_CLIENT_CONN=100'
+      - 'DEFAULT_POOL_SIZE=20'
+
   redis:
     restart: always
     image: redis:7-alpine
@@ -69,7 +84,7 @@
     ports:
       - '127.0.0.1:3000:3000'
     depends_on:
-      - db
+      - pgbouncer
       - redis
       # - es
     environment:
@@ -95,7 +110,7 @@
     ports:
       - '127.0.0.1:4000:4000'
     depends_on:
-      - db
+      - pgbouncer
       - redis
 
   sidekiq:
@@ -105,7 +120,7 @@
     env_file: .env.production
     command: bundle exec sidekiq -c 25
     depends_on:
-      - db
+      - pgbouncer
       - redis
     networks:
       - external_network