2 ms·

PGBouncer uses "DISCARD ALL" as it's default method to recycle connections, which has all the effects of creating a new connection. Of course it is configurabl

PGBouncer uses "DISCARD ALL" as it's default method to recycle connections, which has all the effects of creating a new connection. Of course it is configurable. I would strongly advise you to do this as well. As for idle connections, Postgresql uses a process-per-connection model, and therefore idle connections use a fair amount of resources, and having too many connections can have other bad consequences for performance. (Too many is generally considered to be more than 100.) I don't see any reason to use an in-application connection pool in addition to pgbouncer. However, if I were configuring SQLAlchemy, I would just use the NullPool. EDIT: HN won't let me reply to your reply to this comment, but here is how I would make DISCARD ALL the default behavior. Add a method reset_connection() to the connection proxy base class, with the default implementation just being self.rollback() Then, on the dialect specific postgresql one, make the implementation do something better, like DISCARD ALL (which isn't supported on postgres older than 8.2 btw).

zzzeek 14y ago
> PGBouncer uses "DISCARD ALL" as it's default method to recycle connections, which has all the effects of creating a new connection. Of course it is configurable. I would strongly advise you to do this as well. yes I'm glad we had this discussion so that I've learned PG has this handy command. We can't do it by default because it's a PG-only behavior and I don't have an analogue for all the other databases, but we can add it to the Postgresql dialect documentation as a recommended addition for apps that are modifying session state. > As for idle connections, Postgresql uses a process-per-connection model, and therefore idle connections use a fair amount of resources, and having too many connections can have other bad consequences for performance. (Too many is generally considered to be more than 100.) If I'm going over 100 connections for a single PG database, which is not something that occurs with the typical medium-size web applications I work with (they are more like in the 20 connection range), then I'd use PGBouncer. But in-app pooling gives me something that works immediately in any situation and on any database equally well. > I don't see any reason to use an in-application connection pool in addition to pgbouncer. However, if I were configuring SQLAlchemy, I would just use the NullPool. Probably not, though I'd want to see what kind of overhead psycopg2 has starting up connections and such, I think it's not much. But this is not the case for other DBAPIs and databases that don't have a product like PGBouncer (like cx_oracle, which is dog slow on connect, ODBC over unix, etc.)

zzzeek 14y ago

> PGBouncer uses "DISCARD ALL" as it's default method to recycle connections, which has all the effects of creating a new connection. Of course it is configurable. I would strongly advise you to do this as well. yes I'm glad we had this discussion so that I've learned PG has this handy command. We can't do it by default because it's a PG-only behavior and I don't have an analogue for all the other databases, but we can add it to the Postgresql dialect documentation as a recommended addition for apps that are modifying session state. > As for idle connections, Postgresql uses a process-per-connection model, and therefore idle connections use a fair amount of resources, and having too many connections can have other bad consequences for performance. (Too many is generally considered to be more than 100.) If I'm going over 100 connections for a single PG database, which is not something that occurs with the typical medium-size web applications I work with (they are more like in the 20 connection range), then I'd use PGBouncer. But in-app pooling gives me something that works immediately in any situation and on any database equally well. > I don't see any reason to use an in-application connection pool in addition to pgbouncer. However, if I were configuring SQLAlchemy, I would just use the NullPool. Probably not, though I'd want to see what kind of overhead psycopg2 has starting up connections and such, I think it's not much. But this is not the case for other DBAPIs and databases that don't have a product like PGBouncer (like cx_oracle, which is dog slow on connect, ODBC over unix, etc.)