Russ <russandheat...@gmail.com> wrote:
> What is the recommended way to handle connection pooling when there will be a > small fixed set of database users (eg: 2) connecting? > > For example, the two users may be: > > `app`: the main user used by the application with full r/w privs > `readonly`: limited SELECT-only user for running SQL passed through the > server api > > With both users only actually used locally by the server app. typically you use two separate engines for that use case. > With the connection credentials being in the connection string passed to > create_engine(), does this limit the way of managing this to creating two > independent engines? well the engine is essentially a holder for a connection pool. If you use a pool like NullPool, it makes a new connection on every use, but in that case there is still not an official way to send in different connection parameters. There’s no advantage to trying to make Engine work in a different way, using two engines is just shorthand for using two different sets of credentials. > It seems that going with two engines also means two sessionmakers and two > connection pools. Use NullPool, and there’s no pool. Or set the pool size to one. This isn’t an issue unless you have some odd case where the application uses dozens of connections, and the ratio of app/readonly connections is changing dramatically and constantly. Definitely don’t need two sessionmakers, the engine can be passed both to the constructor of Session directly as well as to the sessionmaker function at the moment the new Session is created. But it shouldn’t be any less convenient to have two sessionmakers vs. passing two different engines in, vs. passing connection credentials in somewhere when you need to connect. There’s “two of something” going on no matter how you try to organize that. > The server application would then grab the appropriate session/connection > based on the required operation (or app-level user account). > > I don't really see a problem with this, but am wondering if there is an > alternative/better way (while maintaining pooling). > > The only minor "issues" I can drum up are 1) double the initial engine setup > cost (one time), totally negligible > and 2) two independent pools that aren't sharing a max pool size. I'm pretty > sure that neither will really matter much, though. well this depends on the load and how the app is running. In reality it’s pretty easy to get away with just turning off pooling unless you’re trying to scale up a web app. If this is Postgresql and you really wanted to scale pooling, you might use PGBouncer. > For the independent pool "issue" I looked into pgbouncer for comparison, and > it seems that it manages independent pools per (db, user) as well, ah. Well then that’s that :). > so one would presume that that method isn't horrific. Then again, you still > occasionally see recommendations for pool sizes at (2*cpu_count + > spindle_count) so I don't know. Definitely premature optimization thoughts > that can be ignored, I imagine. I think what’s odd here is that if this is a web app, why is it needing to maintain two sets of credentials internally in a single process for what is apparently the same database. > Russ > > > -- > 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
