How do I limit connections per user or database?

With a connection limit set on the role, on the database, or on both. They are catalog properties rather than configuration file settings, so they are changed with an alter statement and need no reload and no restart. A value of minus one, the default, means no limit. They are the cheapest way to stop one application consuming every slot the server has.

Which of the two to reach for

The database limit protects a shared cluster from one tenant. The role limit protects against one service, which is usually the better unit, because services and roles map onto each other and databases often do not.

Three details decide how they behave in practice. They are checked at login, so lowering a limit never disconnects anybody and the effect appears gradually as sessions turn over. The documentation describes the role limit as only approximately enforced, since two logins racing for the last slot can both be refused. And it is never enforced for superusers at all, which is deliberate and is what keeps an administrator’s way in when a limit has done its job.

Two kinds of connection are not counted against the role limit either: prepared transactions and background worker connections. That matters on a cluster running logical replication or parallel query, where the worker processes would otherwise consume a limit meant for application sessions.

What a limit buys and what it does not

It converts an unbounded failure into a scoped one. Without it, one service leaking connections refuses logins for everybody, which is the ordinary form of sorry, too many clients already. With it, the leaking service is refused and everything else keeps working, and the error text names the role or the database, so the alert points at the culprit rather than at the database as a whole.

What it does not do is fix the leak. The refused application still needs a pool, and a limit without a pool simply moves the outage from the cluster to one service. Connection pooling and PgBouncer covers the durable version, where the number of server connections stops tracking the number of application threads.

Set the limits a little above the pool size each service is configured for, so they fire on a genuine leak rather than on a busy afternoon. Then alert on the refusal, because a role hitting its ceiling is the earliest visible form of a connection storm and it is far easier to read here than in the aggregate.