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?
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/
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.
