max_connections

For DBAs and operators

Each slot is a PGPROC entry in shared memory, so the value sizes the lock table, the predicate-lock table, the snapshot arrays and more; that is why it needs a restart. Background workers, autovacuum workers and WAL senders have their own slots and do not count against it.

Slot accounting at login (verified on 18.6):

• no free slot at all: FATAL: sorry, too many clients already (also for superusers)
• free slots ≤ superuser_reserved_connections: FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute
• free slots ≤ superuser + reserved_connections, and the role lacks pg_use_reserved_connections: FATAL: remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role

SELECT count(*) AS used, current_setting('max_connections')::int AS max
  FROM pg_stat_activity WHERE backend_type = 'client backend';
SELECT state, count(*) FROM pg_stat_activity GROUP BY 1;

Tuning. Throughput peaks at a small multiple of CPU cores of active sessions. Hundreds or thousands of mostly idle connections still cost memory (each backend's private caches grow with the number of objects it touched), snapshot overhead and ProcArray contention. Use PgBouncer/pgpool in transaction mode rather than very large values. Remember work_mem can be used several times per connection. On a standby, max_connections must be ≥ the primary's value; otherwise queries are not allowed on the standby.

How to change it

Change in postgresql.conf or ALTER SYSTEM, then restart the server.