Comparison of PostgreSQL and MySQL


The following are the points that I could gather about PostgreSQL vs MySQL(It has been collected from multiple sources, and based on personal experience, please point out any errors or incompleteness that might have inadvertently crept in):

  • WITH queries, also know as Common table expression(CTE) is not available in MySQL, is available in PostgreSQL.
    (It has been requested from 2006 onwards: http://bugs.mysql.com/bug.php?id=16244)
    More Details: http://stackoverflow.com/a/325243
  • PostgreSQL offers more enterprise DB features, provides PL/SQL support.
  • PostgreSQL offers a more detailed and low-level query EXPLAIN result, along with the execution plan, if required. In comparison MySQL EXPLAIN/DESCRIBE results are more limited.
  • PostgreSQL has only one DB engine, whereas MySQL has many like INNODB, MyISAM, etc..
  • MySQL is natively supported in cPanel(widely used server admin panel), for PostgreSQL it has to be installed manually, and support is partial,
    so its easy to find a hosting provider with MySQL support, when compared to PostgreSQL.
  • PostgreSQL follows proper SQL standards compliance, but MySQL doesn’t, so integration with other RDBMSs might cause problems.
  • PostgreSQL supports multiple schemas per database, in MySQL, a datbase and schema can be considered the same, for all intents and purposes.
  • PostgreSQL supports INTERSECT, EXCEPT ( set operations), MySQL does not.
    https://en.wikipedia.org/wiki/Set_operations
  • PostgrSQL supports merge joins( also calles Sort merge joins), MySQL does not
    http://use-the-index-luke.com/sql/join/sort-merge-join
  • PostgreSQL supports more types of index, like Inverted index, GiST(used in postGIS), bitmap index, reverse index, partial index.

 

For more details, check out:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s