In my experiences, the size of the database nor the number of records really matter. It's the number of queries that you are handling at one time that can really affect performance.
Sure, if you have a single table with a ton of records this can slow things down - but not nearly as much as dealing with a high number of queries.
Yes, caching can really help. Logically placed indexes can too. Switching to postgres will not help. The bottleneck will still be there. The key is to find that before you start tuning/optimizing: http://www.singlehop.com/blog/analyzing-mysql-performance-an...
Sure, if you have a single table with a ton of records this can slow things down - but not nearly as much as dealing with a high number of queries.
I highly recommend this book - http://www.amazon.com/dp/0596101716/?tag=stackoverfl08-20
Yes, caching can really help. Logically placed indexes can too. Switching to postgres will not help. The bottleneck will still be there. The key is to find that before you start tuning/optimizing: http://www.singlehop.com/blog/analyzing-mysql-performance-an...
Good luck!