Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working on a project that is expected to serve millions of users shortly after launch. The database is postgres, and for now I am assuming at least two servers will be needed. A sys admin (who is clearly well versed in scalable systems) has suggested putting a load balancer in between the web servers and the database servers.

My question is with regard to the difference of load balancing versus connection pooling. To maintain performance, should I be looking at one or the other, or both?

share|improve this question
    
They're pretty much the same thing. pgbouncer is the recommended pooler, pgpool II has more features but is more complex to setup. –  Scott Marlowe Sep 30 '11 at 15:34

2 Answers 2

up vote 5 down vote accepted

With PostgreSQL you have two different areas that can do pooling, either at the app layer (i.e. jdbc built in pooling etc) or in an intermediate layer that sits between the app and the db(s), such as pgbouncer or pgpool.

If you do pooling in an intermediate layer, like pgbouncer or pgpool, then that layer can ALSO do load balancing of select queries. Further when load balancing, you can do writes in one of two ways: you can either have a single write master that replicates via other means to your read slaves, using a tool like slony or the built in streaming replication that showed up in pg 9.0 and above, or you can have the load balancer do all the writing, so that incoming reads hit only one db, but writes hit each database to keep them all updated.

Or if you're adventerous, you can move the load balancing layer down another layer into postgresql itself using plproxy. This is a pl language for pgsql designed to allow you to put one pg database on the front end that has no actual data in it, and that database then can run on top of multiple possibly redundant dbs for incredible throughput. plpoxy is pretty complex to get set up and running but it is also quite scalable as well. Note that your application has to be re-written to support it so it can't be tossed under any old app and just work.

http://slony.info/ http://wiki.postgresql.org/wiki/PL/Proxy http://pgpool.projects.postgresql.org/

share|improve this answer

Load balancing and connection pooling are two very different things.

Connection pooling (I'm coming from a Microsoft side of the world, but things are similar I assume) allows the application to keep the connection to the database open so that it can reuse it for the next query instead of having to disconnect and reconnect for each query that needs to be run.

Load balancing allows you to have multiple database servers behind the load balancer so that you can spread the load across multiple servers instead of having a single server handling all of the work.

You can use connection pooling with load balancing however do keep in mind that as the connections won't ever be dropped you may end up with an unballanced load between the two database servers.

If a single database server can't handle the load of the application, then get 3 servers back there. That way you can reboot one as needed without the app crashing.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.