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

Are you running into any of the following postgres connection limit errors

• pq: sorry, too many clients already
• pg: too many connections for database "exampledatabase"
• pg: too many connections for role "examplerole"

Yes? Great news: This article will help you to understand where to find that limit and how to increase it.

What are this “too many” connection limits in postgres?

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.

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.