What is the difference between MySQL and PosgreSQL?

As a client hosted on our servers, you have the benefit of choosing between two database solutions – MySQL and PostgreSQL (not all hosting plans support PostgreSQL, though). As a starting point for our comparison, we can first consider the similarities. Both databases are open-source, supported by contributors and developers on a voluntary basis and are offered free of any charges. Both are relational database management systems (RDBMS), PostgreSQL being focused on the object-oriented database model. MySQL gets the first open-source database market share spot, while PostgreSQL occupies the third position, lagging behind Firebird, another free SQL developer.

MySQL is the most popular open-source database. The reasons behind this success are rooted in its ease of use and speed of performance. All the scripts offered by our Elefante Installer tool are fully MySQL-compatible. In addition to that, MySQL is cross-platform, which means that you can design your database application on a Windows or Mac computer and then host it on our Linux OS server with the same success. IT companies known to use MySQL include Yahoo!, Cisco, Sabre and Slashdot. The following points illustrate why you would want to choose MySQL as your database solution:

  • Broader support
  • Simpler database design will less coding
  • MySQL’s replication is much better than the one provided by PostgreSQL and there’s only one way to do it, while PostgreSQL has lots of different replication systems, thus unnecessarily complicating the process
  • You can easily create basic web-driven websites
  • MySQL is faster than PostgreSQL
  • Data Integrity – PostgreSQL is at disadvantage because it wants your data to be correct by enforcing constraints. MySQL has limited referential constraints.

PostgreSQL is also a cross-platform database. It is the choice of developers who want more flexibility and features, currently not supported by MySQL, like async notifications, OO (Inheritance of tables), transactional DDL statements, triggers (supported by MySQL 5.x), foreign keys (supported by MySQL 5.x) and full implementation of sequences. Additionally, if you are planning a migration from Oracle, DB2 or MSSQL, PostgreSQL follows many of the SQL ANSI standards, thus allowing the creation of complex SQL commands, available with the proprietary database solutions. To summarize, the need for the following components should draw your attention to PostgreSQL:

  • Complex rule sets (business rules, for example)
  • Use of procedural languages on the server
  • Complex database design
  • Use of geographical data

More specific comparative tables are provided at the following addresses for further reference:

http://www.postgresonline.com/journal/index.php?/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html#extended

and

http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html