Hello!
Which database is more suitable for a limited VPS: MySQL or PostgreSQL ?
I am developing a small hobby project which I plan to host in a limited XEN based VPS. At work we use PostgreSQL but we also use dedicated servers for DB and our hardware are very good. I've never had an extensive experience with MySQL but wouldn't it be more suitable in case of limited CPU and memory resources ?
According this but vague but applicable benchmark comparison, PostgreSQL is the clear winner, both feature- and performance-wise.
Here's another benchmarK that shows similar results.
My suggestion would be to go with PostgreSQL. We use it for our production database, it's free, it has pretty darn good documentation, and it's been around for a long time so it's stable. It does not use very many resources either.
I run MySQL on a Xen VPS. The site I run doesn't get much traffic but it runs fine. You can tweak it a bit to use less memory by modifying the settings in the my.cnf file. One setting for example would be the query_cache_size.
I guess it all depends on how much traffic your site gets...
SQLite is also a lot better than most people give it credit for, if you don't want to go as far as installing Postgre/MySQL.
Unless you're getting a huge amount of traffic, it'll probably do the job for your site just fine.
The reason many people think Postgre is better than MySQL is because it really is better out of the box. I'm not saying it's not good (I've never even tried it) but out of the box Postgre beats vs out of the box MySQL. Properly tweaked, however, MySQL has very good and comparable performance. Here is an example of a low-memory configuration for MySQL, suitable for a vps (this is from an actual vps site).
MySQL also has a much larger community backing it, so down the road when you need to tweak your server and scale out it's easier to find help. There are also really good books on how to optimize, tweak, and configure MySQL.
"limited CPU and memory resources ?"
Firebird is good for this too http://www.firebirdsql.org
PostgreSQL, definitely. It's not hard on resources, the bare minimum is already fine. MySQL has just too many strange behaviours, just too time consuming to develop, test and debug.