Says Paul Libbrecht:
My system's very small - approx 20 users and very little content at present. It's for a small collaborative research group which may grew to ~100 members. I expect quite a few largish attachments and have already configured filesystem attachments.
Sounds quite reasonable. Limited memory?
Well I'm running everything off a single VM configured with 8GB RAM and 2 VCPUS. If necessary I can use more VMs but I wanted to keep things simple until I knew what my actual performance needs were. I also have access to plenty of persistent storage. I have Tomcat configured with CATALINA_OPTS="-server -Xms800m -Xmx800m -XX:MaxPermSize=196m"
I'm using PostgreSQL for various reasons, so can't add all the suggested indexes (names the string prefix ones that are specific to MySQL), but I'd be surprised to be hitting limits so soon.
The database difference might be something to explore. I've seen rather often that things are more battle-tested for MySQL. But I fully agree you have reasons to prefer PostgreSQL.
Yes, and I'd be interested to look into optimising common searches once things have grown and I understand the query workload. Alternatives to MySQL string prefix indexes would be GIN or GIST - the pg_trgm package looks promising.