Tweaking Postgres for Mastodon

This post is primarily a note to self for future reference.

Sometime over the last weekend I updated my Mastodon instance to the latest Glitch version. Since then I got very frequent errors in my app (both web app and Ivory), and the following error was logged in my web server's logs every few minutes:

ActiveRecord::ConnectionTimeoutError (could not obtain a connection from the pool within 5.000 seconds (waited 5.000 seconds); all pooled connections were in use)

It took me a while to figure out how to resolve this: Since I was already using pgbouncer, my first thought was to tweak pgbouncer's max_client_conn and default_pool_size settings. This, however, had no effect.

Ultimately @mwadmin@mastodon.world suggested increasing the max_connections settings in postgres itself, so this is what I did:

Open up /etc/postgresql/15/main/postgresql.conf and find the max_connections setting. Mine was at 200, so I set it to 300:

max_connections = 300 # was 200

Additionally I inserted the DB_POOL setting into my .env.production file: By default this seems to be the same value as MAX_THREADS, but it looks as though Mastodon/Glitch somehow introduced a change there, that required a larger pool. I currently have these settings in there (keep in mind, this is for a single user instance, so these are very low values):

DB_POOL=10
WEB_CONCURRENCY=1
MAX_THREADS=2

With this in place, my Mastodon instance is now running smoothly again.