Most, if not all, common websites that run under PHP and apache use mysql for the database. Mysql is fast, reliable and free. However, there are other solutions, and, inspired by some colleagues, we have tried postgresql in a complex project. The project basis is many types of profiles connected through various relationships. These types share some but not all properties, and have complex messaging and listing system to access each of the profiles. The recoding started in spring 2005. The results at this date are as follows:
First, postgresql has much more capabilities than mysql (version 4.1, version 5, that was released just recently is much closer). Views and triggers simplified the programming a lot. And putting some of the stuff at the database layer improved the project much compared to the old release. At the moment postgresql lacks free replication solution, but this will come with time. Additionally, postgresql has some conflicts with SQL92 syntax , but older mysql releases does not support everything as well.
On the downside, the postgresql is much harder to administrate. The main issue are vacuuming and indexes. Somehow, some of the indexes get corrupted and that clogs up the vacuuming. It is possible, that some triggers corrupt index but the bug needs to be traced down. On the meantime I made some script to fix the indexes when required.
Additionally I am thinking on a test to estimate the robustness of both database servers for our project. Although the speed of the project increased significantly despite the complexity growth, it is more due the caching than database solution. But I do not want to be categorical before testing it myself. Especially after mysql 5.0 has arrived.
Conclusions:
Stay away from postgresql when there is no good postgresql admin near. You will be better with mysql when the relationships are not so complex. And mysql 5.0 already has views and limited support for the triggers.
