I am currently writing a Flask web application that uses Flask-SQLAlchemy to communicate with a Postgres database. I want to have several read-replicas to which all database read queries can be directed, while all read+write queries can go to the master database instance. In other words, a master-slave setup.
A stackoverflow question from 2012 <http://stackoverflow.com/questions/8947918/read-slave-read-write-master-setup> discusses how this can be done in SQLAlchemy. I have implemented a variant of the accepted answer, one that uses the Flask-SQLAlchemy db object (which holds the session). In the global scope, I have: slave_engine = create_engine(app.config['SQLALCHEMY_DATABASE_SLAVE_URI']) Then, as a function decorator for any method that wishes to read from a slave instance: def with_slave(f): @wraps(f) def decorated_function(*args, **kwargs): s = db.session oldbind = s.bind s.bind = slave_engine try: return f(*args, **kwargs) finally: s.bind = oldbind return decorated_function Here's my problem. My AWS RDS dashboard consistently tells me the there are no open connections to the read servers (even during heavy use), while the master server (which is the default) has a number of open connections (probably because of SQLAlchemy's pooling). The question: am I correct to assume that the @with_slave decorated does not account for connection pooling? If so, I'm sure this affects performance, so the follow up would be: what could be changed to enable connection pooling to the read-servers? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
