Why limit DB Connection Pool Size in SQLAlchemy?

Question

This is a follow-up to a question I posted earlier about DB Connection Pooling errors in SQLAlchemy.

According to the SQLAlchemy docs the sqlalchemy.pool.QueuePool.__init__() method takes the following argument:

pool_size – The size of the pool to be maintained, defaults to 5. This is the largest number of connections that will be kept persistently in the pool. Note that the pool begins with no connections; once this number of connections is requested, that number of connections will remain. pool_size can be set to 0 to indicate no size limit; to disable pooling, use a NullPool instead.

What are the drawbacks to setting pool_size=0? What is the benefit of limiting the connection pool size? Is it just to save memory? The database shouldn't really care if a large number of unused connections are open, right?

Answer 1

The main drawback to not limiting the pool size would be the potential for a runaway program to create too many connections.

There are real limits, both at the database level, and O/S level to how many connections the database will support. Each connection will also use additional memory. Limiting the pool size helps protect your database against both bugs in your program, or a malicious attack on your server. Either of those could bring your database server to a standstill by using too many connections or too much memory.

Under normal circumstances, the additional memory each connection uses shouldn't be too much of an issue, but it's best to limit it to the maximum number you think you'll use concurrently (maybe plus a few for good measure).