We're in the early stages of testing a new Postgres (7.3) cluster. For background, our database is about 14gb on disk, and we see about a transaction a second (out of about 120 queries/sec.) Our application is a large dynamic Apache-based web system, written in Perl. Our main database machine is a quad P4 Xeon (1.8ghz) with 4gb of RAM, running Linux 2.4.mumble; poorly formed queries and bad disk layout (we're working on it) mean that during times of peak traffic we'd see load sometimes up over 15.
For fail-over, we've been running the contrib/dbmirror single-master replication for about six months (in production) with no ill effects. We do reporting and db backup off of the slave machine, and it works great. However, we project steady, linear growth in usage, and thus needed to find extra performance -- and it's not easy to get a higher performing shared-memory multiprocessor, to say nothing of cost.
As our system is pure Perl, we decided to replace the standard Perl database access layer with a custom, multiplexing, handle cache. It's been running for about a week now and distributing the load flawlessly. A bonus is that proxying the queries has allowed us to being to collect more interesting timing and usage statistics, and we're finally starting to hunt down and mercilessly improve our nastiest queries.
There are some refinements to the dbmirror that we're currently working on, but for now, everything is working flawlessly.
'jfb
C++: an octopus made by nailing extra legs onto a dog.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
