Re: Scalability in postgres

2009/5/29 Greg Smith <gsmith@gregsmith.com>

On Fri, 29 May 2009, Grzegorz Ja?kiewicz wrote:

if it is implemented somewhere else better, shouldn't that make it
obvious that postgresql should solve it internally ?

Opening a database connection has some overhead to it that can't go away without losing *something* in the process that you want the database to handle.  That something usually impacts either security or crash-safety. This is why every serious database product in the world suggests using connection pooling; examples:

http://blogs.oracle.com/opal/2006/10/oracle_announces_new_connectio.html
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/conn/c0006170.htm
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
http://dev.mysql.com/tech-resources/articles/connection_pooling_with_connectorj.html

  Exactly, here's the thing, if you have an open transaction somewhere to the system, there may be a REALLY good reason for it.  If you're app or dev team is keeping those open, it's very possible that 'reaping' them is going to cause some kind of data integrity issue in your database.  I would investigate the application and make sure that everything is actually rolling back or commiting.  If you're using an ORM, make sure that it's using autocommit, this usually makes the issue go away.

 As to the context switching point -- A connection pooler is what you need.  Why make your database server dedicate cycles to having to figure out who gets on the CPU next?  Why not lower the number of connections, and let a connection pool decide what to use.  That usually helps with your open transactions too (if they indeed are just abandoned by the application).  

 

The only difference here is that some of the commercial products bundle the connection pooler into the main program.  In most cases, you're still stuck with configuring a second piece of software, the only difference is that said software might already be installed for you by the big DB installer. Since this project isn't in the business of bundling every piece of additional software that might be useful with the database, it's never going to make it into the core code when it works quite happily outside of it.  The best you could hope for is that people who bundle large chunks of other stuff along with their PostgreSQL installer, like Enterprise DB does, might include one of the popular poolers one day.

 This sounds like a dirty plug (sorry sorry sorry, it's for informative purposes only)... 

Open Source:

  

      One-Click installers :    No connection pool bundled  (will be included in 8.4 one-click installers)

      PostgresPlus Standard Edition :  pgBouncer is bundled

Proprietary:

      PostgresPlus Advanced Server: pgBouncer is bundled

  That being said, the well known connection pools for postgres are pretty small and easy to download / build / configure and get up and running.

https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer

http://pgfoundry.org/projects/pgpool/ 

--Scott