Load balance/distribution for postgresql

I am coming here after spending considerable time trying to understand how to implement load balancing (distributing database processing load) between postgresql database servers.

开发者_开发问答

I have a postgresql system which attracts about 100s of transactions per second and this is likely to grow. Please do note that my case has so many updates + inserts + selects as well. So any solution for me needs to cater to all insert/update and reads.

• I am planning to use plproxy as suggested through db tools from skype at http://www.slideshare.net/adorepump/database-tools-by-skype.
• Now I am also hearing that "postgresql streaming replication + hot standby" in postgres 9.0 can be considered

Can someone suggest me if there is any simple (or complex) solution to implement for the above scenario?

---

If your database is smaller than 100GB then you should first try to maximize what you can from one computer.

You'd need:

• a good storage controller with large battery backed cache;
• a bunch of fast disks in RAID10;
• another bunch of disks in RAID10 for WAL;
• more RAM than you have data;
• as many fast processor cores as you can.

You'd be able to do several 1000s of tps with this one computer.

If it won't be enough I'd try to add a second hot standby server with streaming replication. You'd use it to run long running read-only report queries, backups etc. so your master server won't have to do these.

Only if it prove not enough then you should try to add more streaming replication hot standby servers to load balance read-only queries. This will be complicated though - because it is asynchronous there's delay between master confirming and stand-by seeing a change. You'd have to deal with it in your client application. Your setup will be a lot more complicated.