Difference between MySQL and PostgreSQL
With regard to open-source database management systems, developers often have to make the choice between MySQL and PostgreSQL. Although both systems share a number of similar characteristics-particularly with regard to how they compete against proprietary database management software-they do have a number of key differences as well. In any case, the effectiveness and feasibility of these two systems have been proven over time, and the choice of whether to go with one or the other is down to the specific requirements of the task at hand.
Speed And Features
MySQL has traditionally reigned supreme over PostgreSQL in terms of sheer speed and ease of use, although it has lagged behind somewhat in terms of features. PostgreSQL's strengths traditionally didn't have much to do with speed, and the system appeared to be geared more towards offering a broad range of features. This has given rise to favorable comparisons between PostgreSQL and Oracle, with the latter being claimed by some to be the open source version of the former.
That being said, PostgreSQL has implemented a number of enhancements in the new release, some of which may just allow it to compete more favorably with MySQL in terms of speed. Unfortunately, many users still judge the relative merits of the two systems based on the performances of the older versions, although the new versions-MySQL 5.1 and PostgreSQL 8.4-offer significant improvements over previous releases.
Data Integrity
PostgreSQL does have a clear edge over MySQL, at least with regard to its close conformance with the academic aspect of "data integrity". PostgreSQL in fact focuses heavily on this facet, and this is apparently enough to give the system a lead over MySQL, which conversely, places a higher premium on accessibility and flexibility for the "typical" developer. Again this assessment is based on the current versions of both systems, which are MySQL 5.1 and PostgreSQL 8.4 respectively.
Popularity
In terms of sheer popularity, MySQL again has the edge over its closest competitor, perhaps owing to its traditional performance advantage over PostgreSQL. Also adding to its popularity is the fact that the MySQL community is generally perceived to be a lot more accessible and faster paced than PostgreSQL's. These factors have resulted in MySQL enjoying a considerable lead over PostgreSQL in terms of number of users, with as many as 50,000 downloads registered daily. At the time of this writing, it remains to be seen whether or not the new enhancements to PostgreSQL will be enough to pull in the numbers in subsequent release.
Summary
MySQL
- Considerably faster than PostgreSQL
- Allows for simpler database design
- Ideally suited to basic Web-driven sites
- Extensive tests performed on its replication aspects
- No cleanups necessary
PostgreSQL
- More efficient for heavy loads
- Recent releases have a number of improvements
- More suitable for complex database design
- A step up from Oracle, Sybase, or MSSQL systems
- Allows for complex rule sets
- Allows the use of stored procedures and geographical data
