How to fix the "pg: too many connections" error. Postgres and its three connection limits

These limits check how many connections are open to your database and limit them, so that e.g. one users does not use up all resources of your database. This is a security mechanism, which makes a lot of sense.

Sometimes it creates problems if the limits are set too small for your actually production workload.

Software exists to create business value

I am Simon Frey, the author of this blog. And I have great news: You can work with me

As CTO as a Service, I will help you choose the right technology for your company, build up your team and be a deeply technical sparring partner for your product development strategy.

Checkout my website simon-frey.com to learn more or directly contact me via the button below.

Let’s work together!

The three different connection limits in postgres

Instance wide connection limit

You are hitting this limit when you run into the pq: sorry, too many clients already error.

Get instance wide connection limit

SHOW max_connections;

Update instance wide connection limit

This limit is the hardest to set as you need to set it in the postgres config and restart your database.

Important: As stated in this stackoverflow answer, just changing the max_connections might not be enough and your postgress might become unstable! You should know what you are doing

1. Figure out where your postgres config is stored

SHOW config_file;

2. Change the value in the file

max_connections = 1000

3. Save the file and restart your database

Per database connection limit

You are hitting this limit when you run into the pg: too many connections for database "exampledatabase" error.

Get per database connection limit

SELECT datname,datconnlimit FROM pg_database WHERE datname='exampledatabase';

Update per database connection limit

ALTER DATABASE exampledatabase CONNECTION LIMIT 950;

Per role/user connection limit

You are hitting this limit when you run into the pg: too many connections for role "examplerole"error.

Get per role/user connection limit

SELECT rolname,rolconnlimit FROM pg_roles WHERE rolname='examplerole';

Update instance wide connection limit

ALTER USER examplerole CONNECTION LIMIT 400;

What values to set for the limits

If you decide to set the limits (it is not always required. Going with YOLO and having no limits should work for most production loads as well), then there is only one important rule

instance limit > database limit > user limit

Your instance limit should be by far the biggest, then the database limit should be smaller than the instance limit and then the user limit smaller than the database one.

Postgres will not inform you if you try to set a limit that is higher than the parent limit. Always check all three!

—

Hope this little guide helped you to set your postgres limits correctly. I failed on this in the past, and it was quite a burden to figure out why clients weren’t able to connect.