MySQL vs PostgreSQL |
|
| [MySQL to PostgreSQL Converter] [About Migration] [Types] [Views] | |
MySQL is a relational database management system in classic meaning of this term. It allows to store and extract data organized as tables with rows and columns. MySQL is a part of the famous Linux-Apache-MySQL-PHP (LAMP) stack used for building websites and web applications due to excellent integration with PHP and web development frameworks.
PostgreSQL is an object-relational DBMS that offers more features and enhanced capabilities than MySQL. In PostgreSQL, data can be stored as objects with associated properties. Object is a flexible data type implementing the concept of inheritance, it is used in various programming languages such as Java and .NET.
Both MySQL and PostgreSQL define the term "database". However, MySQL queries can access tables from different databases while user has access to each database. PostgreSQL does not allow to use multiple databases in the same query when not using Foreign Data Wrappers. On the other hand, PostgreSQL can operate with multiple "schemas" inside the single database that makes it pretty close to MySQL databases.
Here is a list of common MySQL and PostgreSQL commands that allows to query some useful data:
| MySQL | PostgreSQL | Description |
| USE `database_name` | \c "database_name" | connect to database |
| SHOW DATABASES | \l | list all available databases |
| SHOW TABLES | \dt | list all tables in the current database |
| DESC `table_name` | \d "table_name" | describe a table |
| SHOW FULL TABLES IN `database_name` WHERE TABLE_TYPE LIKE 'VIEW' | \dv | list available views in the current database |
| QUIT | \q | quit the command line processor |
When talking about MySQL, "cluster" means multiple database servers connected together and working like a single host for end users. PostgreSQL "cluster" is a single running instance of database server that contains one or more databases.
PostgreSQL supports wide range of advanced types such as arrays, key-value store, binary JSON. Also, it allows to create user-defined data types via CREATE TYPE statement. MySQL supports less data types and it does not allow user-defined data types.
The following table shows the mapping between MySQL data types and PostgreSQL data types: MySQL to PostgreSQL Type Mapping.
PostgreSQL offers sophisticated indexing system that includes B-tree, GiST (Generalized Search Tree), and GIN (Generalized Inverted Index) indexes. Those options are significant for optimization of query performance and data extraction. On the other hand, MySQL supports only B-tree index type that is most suitable for common use.
Both PostgreSQL and MySQL implement Multi-Version Concurrency Control (MVCC) for concurrent access to the data (MySQL supports it for InnoDB table engine only). However, transaction options in MySQL are more limited compared to PostgreSQL. Unlike MySQL, PostgreSQL offers such advanced features of transaction management as transaction isolation levels, atomic transactions and savepoints. This makes PostgreSQL a better choice for projects requiring high concurrency or complex transaction logic.
PostgreSQL and MySQL support views, however syntax and set of built-in functions differ. Basic differences between syntax of views in MySQL and PostgreSQL are listed in this article.