Here are my answers based on what is now several years of hosting for my customers (and being the one asked to track down any issues with the database):
I have not tried it with 4 CPUs in production only 2, and probably having a lot of CPU is not the answer - instead look at just adding a lot of RAM such as 8GB or 16GB to your system.
After Postgres starts up and has been running for a while, if properly configured there will not be a lot of disk access unless you are adding data into the database (e.g. uploading, blogs, etc.) because the most commonly requested tuples will already be in RAM.
What is the reason to go to clustering? Is it for scalability, or for avoiding hardware caused downtime, or something else?
I think you need to explain a little more what you hope a clustered database will do for you.
The only part of MySQL I can see being supported in the future is the MAXdb (I think it is called), which is the open-source version of the SAPDB database, and which does have the needed things like subselects and stored procedures that OpenACS uses. Of course a database driver would need to be written for it.
