I agree postgres 9 is great, but I'm not sure that 'load balanced separate db servers' are 'super-simple' 
In fact, I'm pretty sure the correct description is 'impossible', with standard postgres 9 out of the box.
Postgres 9 does have a great new 'hot standby' feature which means that you can run multiple servers to handle 'read' transactions. However you can still only have a single server that handles 'write' transactions.
As the Moodle API doesn't provide a clear way to distinguish transactions before they start, it is not easily possible to send 'read' transactions to a load-balanced mirror and 'write' transactions to a master server. This is made even more difficult by the limited use of explicit transactions in Moodle. Commonly, without indicating an explicit transaction you might do code like the following:
- Make a database change (write transaction)
- Do some query that depends on the change you just made (read transaction)
This scheme will not work in a system where you try to distinguish between read and write transactions, serving the former through mirrored servers to gain performance. The write transaction will likely not have been passed to the mirror servers yet. It would be necessarily to correctly mark these as dependent as each other by having a single transaction that encompasses the write + any reads which might depend on it.
What's the way forward without rewriting all Moodle? Well, the majority of transactions in Moodle are 'read' transactions although each request typically makes at least one write transaction (log table). If we were to make progress in allowing Moodle to benefit from this type of optimisation, we would probably want to have some way where frequently-used pages are identified using a special new API, along the lines of:
$DB->enter_read_only_mode();
At this point, attempts to write to database will throw an exception, but also add new API:
$DB->execute_okay_if_delayed($sql)
Which can be used for the mdl_log update (and similar) to indicate that you want to execute an update, but it is OK if the result of the update is not available until after this PHP request has finished.
With this API, it would then be possible to configure a moodle_database subclass so that if you call the enter_read_only_mode, it makes all requests from a read-only database, except for execute_okay_if_delayed which would use a second db connection to the master database. Pages which don't call the enter_read_only_mode would use only the master database.
In most moodle instances the vast majority of usage is probably concentrated around a few pages (course view, forum view, forum discussion) which are generally read-only so if you could ship all those queries onto separate servers you have solved the scalability problem.
Removing scalability limitations from Moodle is a fairly big potential improvement and implementing this change would probably be less work than replacing all the existing uses of 'exec' Which won't gain anyone anything.
So anyway, that would be nice and would allow for hugely greater scalability of moodle instances using postgres 9, but it doesn't exist at the moment, so the best you can do with the new 'hot standby' feature is just that; have your failover server ready to kick in the instant there is a problem.
Feel free to correct me if this is wrong...
--sam
PS So far we have been able to handle high load just with a single fast database server. (It's actually a pair in failover configuration - so yes we are wasting good hardware by not being able to use it in the 'hot standby' manner for read-only queries.)
