First of all, the GUC¹ setting max_connections
in postgresql.conf
limits connections. It can only be set at server start.
Heroku obviously limits this to 20 for the "Starter Tier" and 500 for the "Production Tier".
The maximum number is not limited by Postgres itself, but by available system resources. Typically, performance degrades with too many concurrent connections, so even if you can set max_connections = 1000
, it's probably unwise. Idle sessions don't matter much. But concurrently active sessions compete for resources, the bottleneck typically being I/O.
Here is a blog with instructions by someone who did.
¹ Grand Unified Configuration, the PostgreSQL subsystem that handles server configuration