What is Difference Between Postgresql vs Mysql
MySQL and PostgreSQL both are widely used
open source database management systems and considered as alternative to Oracle or SQL Server. Both of them has their own pros and cons which we have shown in the comparison table below.
| Terms | PostgreSQL | MySQL |
| Development | Open source project | Open source product |
| 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 and MyISAM |
| Full-text search | Yes | Yes (Limited) |
| Drop a temporary table | No TEMP or TEMPORARY keyword in DROP TABLE statement | Support 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 and views. | 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 |
| Identity Column | Yes | No |
| Analytic functions | Yes | No |
| Data types | Support many advanced types such as array, hstore, and user-defined type. | 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 | Yes (Supported CTE since MySQL 8.0) |
| EXPLAIN output | More detailed | Less detailed |
| Materialized views | Yes | No |
| CHECK constraint | Yes | Yes (Supported since MySQL 8.0.16) |
| 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 in case of large tables with millions of rows. |
| 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 |
Related posts
- What is the Difference between Journal, Thesis, Dissertation and Research Paper
- What is the Difference between Conclusion, Summary, Abstract and Introduction
- What is the Difference between Principle and Principal
- What is the Difference between Boston Emory Ncea Edexcel Cambridge and Oxford
- What is the Difference between IELTS and TOEFL Tests
- What is the Difference between Empathy and Sympathy
- What is the Difference between Average Mode Standard Deviation Mean and Median
- What is the Difference between Autobiography Bibliography Biography and Memoirs
- What is the Difference between Whose, That, Which, Who and Whom
- What is the Difference between Table of Contents, Index, Appendix and Annex