PostgreSQL and MySQL are the two most widely-used open source relational databases competing against each other. Let’s take a look at a quick analysis of the two.

Supported data types

  • MySQL: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DOUBLE PRECISION, REAL, DECIMAL, NUMERIC, DATE, DATETIME, TIMESTAMP, YEAR, CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM, SET.

  • PostgreSQL: bigint, bigserial, bit [(n)], bit varying [(n)], boolean, box, bytea, character varying [(n)], character [(n)], cidr, circle, date, double precision, inet integer, interval [fields] [(p)], line, lseg, macaddr, money, numeric [(p, s)], path, point, polygon, real, smallint, serial, text, time [(p)] [without time zone], time [(p)] with time zone, timestamp [(p)] [without time zone], timestamp [(p)] with time zone, tsquery, tsvector, txid_snapshot, uuid, xml.

Quick feature comparison

  MySQL MyISAM MySQL InnoDB PostgreSQL
Transactions No Yes Yes
Foreign Key Constraints No Yes Yes
Locking Table Row/MVCC Row/MVCC

Pros

MySQL:

  • It’s widely used and easy to set up.
  • It’s reasonably flexible and highly scalable
  • It’s platform independent and cloud-ready
  • It’s fast

PostgreSQL:

  • It’s mostly SQL compliant
  • It’s optimized for complex queries.
  • It has a strong community and third-party support
  • It’s built for truly massive databases
  • It supports exotic and custom data types
  • It’s transactional and ACID compliant


Cons

MySQL:

  • Stagnated development
  • Some issues with stability and clustering

PostgreSQL:

  • Over-kill and less efficient for read-heavy operations
  • Less popular than MySQL, thus harder to get support

When to use

MySQL: A great majority of websites (and web-applications) can simply work on MySQL despite the constraints. This flexible and somewhat scalable tool is easy to use and easy to manage – which proves very helpful in the long run. MySQL is ideal for organizations that need a robust database management tool but are on a budget.

PostgreSQL: Compared to other open-source and free RDBMS implementations, for complex database designs, PostgreSQL offers the most in terms of functionality and possibilities without giving up on other valuable assets. Or if you require your database to perform custom procedures, PostgreSQL, being extensible, is the better choice.

Need a good GUI client for MySQL and PostgreSQL? TablePlus is native, beautiful, and available for free. Check it out: Download TablePlus here.

PostgreSQL GUI client