SQLAlchemy, PostgreSQL Connection Pooling

Question

Hopefully this should be a quick answer for somebody. I've looked through the docs a bit, but still haven't found a definitive answer. I have a number of 'idle' connections that stick around, even if I perform a session.close() in SQLAlchemy. Are these idle connections the way SQLAlchemy/Postgres handle connection pooling?

This is the query I used to check db connection activity

SELECT * FROM pg_stat_activity ;

Here is sample code:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

application = Flask(__name__)
application.config.from_object('config')
db = SQLAlchemy(application)


class Brand(db.Model):
    id = db.Column(db.Integer, primary_key=True)

@application.route('/')
def documentation():
    all = Brand.query.all()
    db.session.remove() #don't need this since it's called on teardown
    return str(len(all))

if __name__ == '__main__':
    application.run(host='0.0.0.0', debug=True)

univerio · Accepted Answer · 2016-03-03 20:09:56Z

1

Yes. Closing a session does not immediately close the underlying DBAPI connection. The connection gets put back into the pool for subsequent reuse.

From the SQLAlchemy docs:

[...] For each Engine encountered, a Connection is associated with it, which is acquired via the Engine.contextual_connect() method. [...]

Then, Engine.contextual_connect() points you to Engine.connect(), which states the following:

The Connection object is a facade that uses a DBAPI connection internally in order to communicate with the database. This connection is procured from the connection-holding Pool referenced by this Engine. When the close() method of the Connection object is called, the underlying DBAPI connection is then returned to the connection pool, where it may be used again in a subsequent call to connect().

edited Mar 3, 2016 at 20:09

answered Mar 3, 2016 at 19:46

univerio

20.6k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Trent Over a year ago

Do you have a link to documentation that describes this? This leaves some interpretation: docs.sqlalchemy.org/en/rel_1_0/core/pooling.html

univerio Over a year ago

@Trent This describes how a session operates in relation to a connection. It tells you that a session acquires a connection through Engine.contextual_connect and ultimately Engine.connect, which then describes the pooling behavior.

Trent Over a year ago

I'm keenly interested in tying connection pooling back to the db. That is, does the specific 'idle' state on the connection indicate that this could be part of that pooling? My primary aim is to determine when/if we have a connection leak.

Collectives™ on Stack Overflow

SQLAlchemy, PostgreSQL Connection Pooling

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related