flask, gunicorn (gevent), sqlalchemy (postgresql): too many connections

Solution 1:

You mention:

• worker connections = 1024
• threads = 1

but with gevent, the number of worker connections is the total number of (asynchronous) threads running at once.

As a result, you're trying to establish workers * worker_connections number of connections, which is definitely higher than you can sustain, based on pool_size and postgres limits defined.

Start at Postgres and work backwards:

• Postgres = 20 connections max
• instances = 1 (it seems like)?
• gunicorn processes = 2
• worker_connections = 10 = (20 / 2)
• pool_size + max_overflow = 10 [same as worker_connections!]
• pool_size = 10, max_overflow=0 or any combo you want
  • overflow connections get cleaned up asap; pool_size connections can stick around

worker_connections and pool_size+max_overflow are shared across threads, but not processes.

Note, as soon as you add another instance or anything that talks to the DB, it will use 1 (or more) of those precious 20 Postgres connections, so you'll need to adjust accordingly.