Choosing between MySQL and PostgreSQL
MySQL and PostgreSQL are the top two open source relational databases. They have a lot in common: Both offer predefined data types; secondary indexes; data durability; transactions; full text search; XML support; common table expression (CTE); access with JDBC, ODBC, and ADO.NET; and support for commonly used operating systems.
So how can developers choose between them? The application or purpose the database is to be used for should guide the features that need to be prioritized.
Here’s how the two databases compare with regard to the features that most relational databases support.
SQL Standards Compliance
PostgreSQL is one of the most ANSI SQL-compliant databases, and its latest version, 12, supports at least 160 of the 179 mandatory features of the SQL: 2016 standard.
MySQL compromises with SQL standards to some extent, only recently adding support for the CHECK constraint in the 8.0.16 version.
Data Types
PostgreSQL supports some data types that MySQL doesn’t, including array, and hstore for a key/value pair. PostgreSQL also supports an extensible type system to add new user-defined data types, functions, and index types, as well as the Boolean data type.
MySQL’s equivalent to Boolean is TINYINT(1), and it supports the unsigned integer type, which PostgreSQL doesn’t.
DDL Clauses
Some DDL (data definition language) clauses are supported in one database and not the other. PostgreSQL does not support the TEMPORARY clause in DROP TABLE, while MySQL doesn’t support the CASCADE clause in DROP TABLE and TRUNCATETABLE or transaction-safe in TRUNCATE TABLE. Another feature that PostgreSQL supports but MySQL doesn’t is table inheritance.
Speed
MySQL puts emphasis on speed, even if it involves not implementing all the SQL standards features. The new MySQL 8.0 is twice as fast as MySQL 5.7.
Materialized Views
A materialized view is a database object that contains the result of a query. PostgreSQL supports materialized views while MySQL doesn’t.
NoSQL Support
Both MySQL and PostgreSQL support the document store as their secondary data model, the primary being relational. Both also support native JSON as a data type, and PostgreSQL also supports the binary JSON (JSONB) data type.
MySQL 8.0.11 added support for an X Dev API for which CRUD operations may be performed on JSON documents stored as Collections, introduced as new schema objects.
Replication
When replicating, or copying, data from one database server to another, the sending server is called the Master server and the receiving server is called the Slave (or Standby) server. MySQL supports Master-Slave and Master-Master replication, while PostgreSQL only supports Master-Slave replication.
Joins
A joined table is derived from two other tables and can involve inner, left outer, right outer, full outer, and cross joins. PostgreSQL supports the full outer join, while MySQL doesn’t.
In-Memory Tables
PostgreSQL does not support in-memory tables, while MySQL does with the MEMORY storage engine, the default being the InnoDB storage engine.
Indexes
PostgreSQL supports B-tree, Hash, GiST, SP-GiST, GIN, and BRIN index methods, while MySQL only supports Hash and B-tree. PostgreSQL supports partial indexes and expression indexes, which MySQL doesn’t, though both support covering indexes.
