PostgreSQL: remaining connection slots are reserved for non-replication superuser connections, too many clients already

We have web application using Golang, PostgreSQL, and sqlx (adapter, connection pooler), that each request requires 1 to 8 queries, sometimes 1 transaction with 5-8 select and 5-8 insert queries.

We already set our PostgreSQL's max_connections from 1024, then 4096, but it start swapping, so we reduce them back to 64 (this is the limit before swapping).

Our RAM is 2GB, we configure the PostgreSQL's working_mem to 16MB and temp_buffers to 8MB, shared_buffers to 800MB, effective_cache_size to 1536MB (this configuration doesn't swap at all).

There are about 18 request per second everyday at 00:00 to 02:35, and there's about 8 errors per second, at that point only 10 transaction per hours that succeed, others are failed, with error message either:

• remaining connection slots are reserved for non-replication superuser connections

• too many clients already

How to overcome this problem other than caching the read-only page (since the page can be updated about 10 times per minutes) or upgrading the machine?

max_connections = 1024 ?! You need a connection pool.

Use PgBouncer in transaction-pooling mode if your app doesn't support built-in pooling.

You're running PostgreSQL on a tiny toy server. Keep the active connection count low and queue work up in series.

High max_connections introduces significant inefficiency, and having lots of actively working connections adds more.

StackExchange Database Administrators Q#120694, answer score: 5