PostgreSQL vs. MySQL is an important decision when it comes to choose an open-source relational database management system. Both PostgreSQL and MySQL are time-proven solutions that can compete with the enterprise solutions such as Oracle, SQL Server, etc.
MySQL has been famous for its ease of use and speed, while PostgreSQL has more advanced features which is often described as an open-source version of Oracle.
The following table compares the features of PostgreSQL vs. MySQL:
| PostgreSQL | MySQL | |
|---|---|---|
| Known as | The world’s most advanced open source database | The world’s most popular open source database |
| Development | PostgreSQL is an open source project | MySQL is an open-source product |
| Pronunciation | my ess queue ell | post gress queue ell |
| Licensing | MIT-style license | GNU General Public License |
| Implementation programming language | C | C/C++ |
| GUI tool | PgAdmin | MySQL Workbench |
| ACID | Yes | Yes |
| Storage engine | Single storage engine | Multiple storage engines e.g., InnoDB, MyISAM, etc. |
| Full-text search | Yes | Yes |
| Drop a temporary table | No TEMP or TEMPORARY keyword in DROP TABLE statement | MySQL supports the TEMP or TEMPORARY keyword in the DROP TABLE statement that allows you to remove the temporary table only. |
DROP TABLE | Support CASCADE option to drop table’s dependent objects e.g., tables, views, etc., | Does not support CASCADE option |
TRUNCATE TABLE | PostgreSQL TRUNCATE TABLE supports more features like CASCADE, RESTART IDENTITY, CONTINUE IDENTITY, transaction-safe, etc. | MySQL TRUNCATE TABLE does not support CASCADE and transaction safe i.e,. once data is deleted, it cannot be rolled back. |
| Auto increment Column | SERIAL | AUTO_INCREMENT |
| Analytic functions | Yes | No |
| Data types | Support many advanced types such as array, hstore, user-defined type, etc. | SQL-standard types |
| Unsigned integer | No | Yes |
| Boolean type | Yes | Use TINYINT(1) internally for Boolean |
| IP address data type | Yes | No |
| Set default value for a column | Support both constant and function call | Must be a constant or CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns |
| CTE | Yes | No |
EXPLAIN output | More detailed | Less detailed |
| Materialized views | Yes | No |
| CHECK constraint | Yes | No (MySQL ignores the CHECK constraint) |
| Table inheritance | Yes | No |
| Programming languages for stored procedures | Ruby, Perl, Python, TCL, PL/pgSQL, SQL, JavaScript, etc. | SQL:2003 syntax for stored procedures |
FULL OUTER JOIN | Yes | No |
INTERSECT | Yes | No |
EXCEPT | Yes | No |
| Partial indexes | Yes | No |
| Bitmap indexes | Yes | No |
| Expression indexes | Yes | No |
| Covering indexes | Yes (since version 9.2) | Yes. MySQL supports covering indexes that allow data to be retrieved by scanning the index alone without touching the table data. This is advantageous with large tables with millions of rows. |
| Common table expression (CTE) | Yes | No |
| Triggers | Support triggers that can fire on most types of command, except for ones affecting the database globally e.g., roles and tablespaces. | Limited to some commands |
| Partitioning | RANGE, LIST | RANGE, LIST, HASH, KEY, and composite partitioning using a combination of RANGE or LIST with HASH or KEY subpartitions |
| Task Schedule | pgAgent | Scheduled event |
| Connection Scalability | Each new connection is an OS process | Each new connection is an OS thread |