CODEWITHSUNDEEP
pythonsqlalchemy
Matty
Matty

Reputation: 34523

What happens when a connection pool is exhausted?

I'm reading about SQLAlchemy's connection pooling, which has a default of 5 connections and will by default overflow to 10.

If the number of cached connections is exceeded, what happens? Are subsequent requests queued until a free connection becomes available or will a new connection that doesn't enter the pool be created?

Also, what happens to unused connections when the pool has "overflowed" to its default max of 10? Do those connections disconnect after the default time (as with the standard pool), or are they released more aggressively than the standard pool?

Upvotes: 16

Views: 25126

Answers (2)

9000
9000

Reputation: 40894

Per SQLAlchemy docs,

When the number of checked-out connections reaches the size set in pool_size, additional connections will be returned up to this limit. When those additional connections are returned to the pool, they are disconnected and discarded. It follows then that the total number of simultaneous connections the pool will allow is pool_size + max_overflow, and the total number of “sleeping” connections the pool will allow is pool_size.

So yes, overflowed connections get freed more aggressively than normally sleeping connections.

If you actually look at the sources of QueuePool._do_get(), you will see that it raises a TimeoutError when connection count equals pool size + overflow, and no connection is returned back to the pool soon after connect() is called.

Upvotes: 7

Rian Rizvi
Rian Rizvi

Reputation: 10835

You are reading about the QueuePool, which manages database connections for better performance. It does this by holding open idle connections, in case you want to reuse them later. The number of connections it will hold open is pool_size=5 (default). If you open a sixth connection, one of the connections in the queue will be closed, as long as it is idle. If none are idle, the QueuePool will open additional ones, upto max_overflow=10 (default). Any more and you will get an error. However both of these parameters are configurable. Set pool_size=0 to have unlimited open connections. The source is here

Upvotes: 19

Related Questions