PostgreSQL
MySQL

PostgreSQL vs MySQL: The Definitive Comparison

Author: Antonello Zanini
Length: 8 MINS
Type: Guide
Published: 2023-09-08
Intro
Dig into the ultimate PostgreSQL vs. MySQL guide and take a look at all the elements that make the two databases unique and different.
Tools used in the tutorial
Tool
Description
DbVisualizer
Top rated database management tool and sql client
MySQL 8.0+
The MySQL database version 8 or later
PostgreSQL
The PostgreSQL database

MySQL and PostgreSQL are two of the most popular DBMSs on the planet, with more than 60% market share in the realm of relational databases. Even though they offer similar functionality and can be used interchangeably in most small or simple projects, there is a difference between PostgreSQL and MySQL.

In this PostgreSQL vs MySQL comparison article, you will learn more about the two databases, digging into their history and understating their similar and different aspects.

What Are PostgreSQL and MySQL?

PostgreSQL, often referred to as “Postgres,” is an open-source object-relational database management system (ORDBMS) known for its robustness, scalability, and wide range of features. It adheres closely to the ANSI SQL standard.

MySQL is an open-source relational database system (RDBMS) popular for its ease of use, speed, and flexibility. It has historically focused on simplicity and performance over strict adherence to standards but has recently made efforts to improve standards compliance.

History of PostgreSQL and MySQL Databases

The history of relational databases began in 1973 with Ingres, one of the first RDBMSs. It was released by UC Berkeley as open-source software under the BSD license and served as the basis for many commercial database products.

In 1986, one of the original Ingres developers returned to Berkeley and started working on a successor to that database technology, called Postgres. The name was eventually changed to PostgreSQL to make it easier to associate with SQL. PostgreSQL 6.0, the first production release, came out in 1997. As stated on the official site, the project is currently developed by an “unincorporated association of volunteers and companies who share code under the PostgreSQL License.”

PostgreSQL was just one of many databases developed in the 1990s. In 1994, in Sweden, Michael “Monty” Widenius and his collaborators began work on a new relational database management system. They decided to call it MySQL.

Unlike PostgreSQL, MySQL has always been under corporate control. MySQL AB, the original company behind the project, was acquired by Sun Microsystems in 2008, and shortly thereafter Sun was acquired by Oracle in 2010. In response to the Oracle acquisition, Widenius forked MySQL and founded MariaDB Corp. But that is a story for another article. MySQL is currently released under both GPLv2 and proprietary licenses.

PostgreSQL vs MySQL: Top Similarities

Here are some of some important similarities between PostgreSQL and MySQL to take into account:

  • Open source: Both databases are open-source projects, which means that anyone can see, modify, and fork their source code.
  • Relational database model: PostgreSQL and MySQL follow the relational database model. In both technologies, data is organized in tables having rows and columns with support for relationships through primary and foreign key constraints.
  • SQL support: They both use the SQL language as the interface to read, interact with, and edit data.
  • Data integrity: Both solutions offer constraints, various data types, triggers, and other mechanisms to enforce data consistency. Learn more in our guide on PostgreSQL data integrity.
  • Replication, backups, and high availability: Both come with built-in data backup, replication, and access control features to ensure high availability and fault tolerance.
  • User management: Both support multiple accounts, privileges, and role-based access control (RBAC) to manage user permissions and restrict unauthorized access.
  • Indexing: They provide indexing features to improve query performance. Read our guide on how to achieve 10x query performance with a database index.

PostgreSQL vs MySQL: Key Differences

Although the two databases have many similarities, they also have some features that set them apart. Understanding their distinctive aspects is critical to choosing the right database for your specific requirements.

Specifically, the difference between PostgreSQL and MySQL boils down to ten main elements. Let's see them all!

1. Number of data types available

MySQL is a purely relational database, while PostgreSQL is an object-relational database. The consequence is that the latter allows data to be stored as objects with properties, supporting concepts such as parent-child relationships and inheritance. As a result, PostgreSQL offers many more data types, such as array, hstore, XML, and JSONB.

2. Auto-incremental column management

MySQL automatically generates unique integer values for a column when it is marked as AUTO_INCREMENT. This keyword is generally specified on primary keys. PostgreSQL does not support AUTO_INCREMENT. Instead, it offers similar functionality through the sequence data type.

3. MVCC implementation

PostgreSQL always supports MVCC (Multiversion Concurrency Control), a mechanism that allows simultaneous read and write operations without compromising data integrity. This allows multiple users to read and modify the same data securely and simultaneously. In contrast, MySQL implements MVCC only with InnoDB and with a different approach than Postgres.

4. Type of indexes supported

MySQL supports B-tree, R-tree, expression, and hash indexes. PostgreSQL offers a wider range of index types, including GiST, SP-GiST, GIN, BRIN, and partial indexes. This results in greater flexibility to meet scalability and performance needs.

5. Full-text search capabilities

Out of the box, PostgreSQL provides robust and advanced full-text search capabilities. It equips developers with advanced features, such as ranking, stemming, phrase matching, and search result highlighting. MySQL also has full-text search functionality, but it is not as feature-rich as the PostgreSQL implementation.

6. Transactional DDL

PostgreSQL supports transactional DDL (Data Definition Language) operations, such as CREATE, ALTER, and DROP statements. This means that schema-altering operations can be included within a transaction block and rolled back if required. In MySQL, DDL statements are typically not transactional. Thus, when an error occurs during a schema-altering operation, it cannot be rolled back.

7. ACID compliance

ACID (Atomicity, Consistency, Isolation, Durability) ensures database integrity even in the presence of unexpected errors. MySQL guarantees full ACID compliance only with the InnoDB or NDB Cluster storage engines, while PostgreSQL is fully ACID compliant in all configurations. Learn more in our in-depth guide on ACID in database systems.

8. Support for materialized views

SQL views are special tables populated on the fly by a query that selects data from one or more tables. Both MySQL and PostgreSQL support them. However, only PostgreSQL offers materialized views. These persistent views store values from the generation query on the file system, enabling better performance in complex operations.

9. Languages available for writing stored procedures

In SQL, stored procedures are custom functions that can be reused for efficient data manipulation. Both DBMSs support them, but PostgreSQL surpasses MySQL by allowing stored procedures to be written in PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python in addition to SQL.

10. Type of triggers available

SQL triggers are operations that automatically execute when specific events occur in the database. MySQL only supports AFTER and BEFORE triggers for INSERT, UPDATE, and DELETE statements. Instead, PostgreSQL also provides the INSTEAD OF trigger to perform actions on views.

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.

Which to Choose Between PostgreSQL and MySQL

When choosing between PostgreSQL and MySQL, you should take into account the following factors.

Application use case

PostgreSQL is ideal for enterprise applications that need to perform advanced queries and frequent writes while maintaining data integrity. It excels in handling complex data and offers strong concurrency control. MySQL is instead well-suited for simpler use cases and prioritizes efficient reads over complex data manipulation.

Development expertise

PostgreSQL has a steeper learning curve because of the many advanced features it offers. Getting the most out of them takes time and effort, making it a more complex technology to learn. On the contrary, MySQL is considered more beginner-friendly and easier to learn. It prioritizes simplicity and ease of use, making it more accessible for beginners.

Performance requirements

PostgreSQL tends to perform faster in concurrent write operations as it does not use read/write locks. On the other hand, MySQL relies on write locks, which reduces the number of concurrent activities per process. Yet, it tends to show better results in read-intensive processes. Since most applications primarily read data from the database, this is huge.

At the same time, PostgreSQL vs MySQL performance can be influenced by various factors. These include hardware configuration, database schema design, query optimization, specific use case, data volume, and workload patterns.

In short, PostgreSQL tends to win in complex queries and write-intensive scenarios, while MySQL is usually the better option for simple queries and read-intensive scenarios.

PostgreSQL vs MySQL: Summary Table

PostgreSQL MySQL
Also known as Postgres
Pronunciation post·gress·queue·ell (IPA: /pˈo͡ʊstɡɚɹˌɛskjˌuːˈɛl/) my·ess·queue·ell (IPA: /ma͡ɪˌɛskjˌuːˈɛl/)
Type of DBMS ORDBMS RDBMS
License Open-source through PostgreSQL License Open source with GPLv2 license or proprietary
Implementation programming language C C/C++
ANSI SQL compliance Yes Partially
Built-in GUI client PgAdmin MySQL Workbench
Main focus Rich features Simplicity of use
ACID Yes Yes on InnoDB, not fully otherwise
Storage engines Single storage engine Multiple storage engines (InnoDB, MyISAM, MEMORY, CSV, and more)
Data types Support many advanced types such as array, hstore, boolean, and user-defined type. Only SQL-standard types
Auto increment Column Through the SERIAL data type Through the AUTO_INCREMENT column attribute
Full-text search Yes Yes, but with limited functionality
Transactional DDL Yes No
Materialized views Yes No
Table inheritance Yes No
Programming languages for stored procedures PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python, SQL, and more SQL
GiST, SP-GiST, GIN, BRIN, and partial indexes Yes No
Trigger actions AFTER, BEFORE, INSTEAD OF AFTER and BEFORE
Performance focus Write operations and complex queries Read operations and simple queries

Conclusion

In this comparison guide, we jumped into PostgreSQL and MySQL. Although the two most popular relational databases on the market have comparable capabilities, they have some key differences. Here you had the opportunity to explore them and figure out which one is best for your needs.

Regardless of what solution you end up adopting, you need a tool that can connect to these and many other databases. You cannot fill your computer with every single built-in database client available, assuming there is any. This is where DbVisualizer comes in!

The connection to the MySQL and PostgreSQL servers.

↑  DbVisualizer in action. Note the connection to the MySQL and PostgreSQL servers.

As a full-featured database client with top user reviews, DbVisualizer allows you to explore data from dozens of DBMSs within the same tool. It offers advanced query optimization features, table exploration in a graph, and full support for most database-specific features, including PostgreSQL’s and MySQL’s. Download DbVisualizer for free now!

FAQ

Do PostgreSQL and MySQL handle JSON and other semi-structured data formats?

Yes, both have built-in support for handling JSON and other semi-structured data formats. They provide data types specifically designed for storing and manipulating JSON data, but PostgreSQL offers more extensive functionality for working with semi-structured data.

Which between PostgreSQL and MySQL is the most popular?

At the time of writing, MySQL is considered the most widely used open-source relational database management system, with a larger market share and greater popularity among users than PostgreSQL.

MySQL is more popular than PostgreSQL.

↑  According to Google Trends, MySQL is more popular than PostgreSQL.

Which would win a PostgreSQL vs MySQL performance comparison?

PostgreSQL excels in write-intensive scenarios, while MySQL tends to perform better in read-intensive processes. The winner in the performance comparison would vary based on the specific requirements and characteristics of the application.

Which companies use PostgreSQL instead of MySQL?

Although large companies tend to use different databases for specific products, it is public knowledge that Apple, Twitch, Sony, Cisco, Reddit, Instagram, Spotify, Skype, and the New York Times use PostgreSQL. In contrast, Facebook, Twitter, Airbnb, Uber, Netflix, and YouTube prefer MySQL.

Which one between MySQL and PostgreSQL gets more releases?

On average, MySQL receives a minor release every 3 months and a major release every 6/7 years. PostgreSQL gets a minor release per month and a major release per year.

DbVisualizer SQL Client.
About the author
Antonello Zanini.
Antonello Zanini
Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.
SIGN UP TO RECEIVE THE TABLE'S ROUNDUP
More from the table
TITLE
AUTHOR
Gayatri Sachdeva
TAGS
DBA'S
DronaHQ
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
TITLE
AUTHOR
Bonnie
TAGS
Generation
TITLE
AUTHOR
Bonnie
TAGS
Joins
TITLE
AUTHOR
Igor Bobriakov
TAGS
MySQL
Security
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Operators
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
NULL
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Reserved words
TITLE
AUTHOR
Igor Bobriakov
TAGS
Oracle
TITLE
AUTHOR
Antonello Zanini
TAGS
DELETE
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
MySQL
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
JSON
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
Null
TITLE
AUTHOR
Antonello Zanini
TAGS
Driver
JDBC
ODBC
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
Connection
TITLE
AUTHOR
Lukas Vileikis
TAGS
Deduplication
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
SQL
Functions
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Math
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
Docker
MySQL
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
Views
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Index
TITLE
AUTHOR
Bonnie
TAGS
BigQuery
TITLE
AUTHOR
Leslie S. Gyamfi
TAGS
Join
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
TITLE
AUTHOR
Leslie S. Gyamfi
TAGS
PostgreSQL
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
PrestoDb
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
Ansible
Automation
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
TITLE
AUTHOR
Leslie S. Gyamfi
TAGS
PostgreSQL
NoSQL
JSON
TITLE
AUTHOR
Igor Bobriakov
TAGS
Oracle
Data types
TITLE
AUTHOR
TheTable
TAGS
ElasticSearch
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
Security
TITLE
AUTHOR
Lukas Vileikis
TAGS
Language
Design
TITLE
AUTHOR
Lukas Vileikis
TAGS
CRUD
DELETE
TITLE
AUTHOR
Lukas Vileikis
TAGS
CRUD
UPDATE
TITLE
AUTHOR
Lukas Vileikis
TAGS
CRUD
SELECT
TITLE
AUTHOR
Lukas Vileikis
TAGS
CRUD
INSERT
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
TITLE
AUTHOR
Leslie S. Gyamfi
TAGS
PostgreSQL
TITLE
AUTHOR
TheTable
TAGS
Bug
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Daemon
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Partitions
TITLE
AUTHOR
Leslie S. Gyamfi
TAGS
Migration
MySQL
PostgreSQL
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
hstore
TITLE
AUTHOR
TheTable
TAGS
SQL
TITLE
AUTHOR
Igor Bobriakov
TAGS
SQL server
Security
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
Visualize
TITLE
AUTHOR
TheTable
TAGS
MySQL
TITLE
AUTHOR
Lukas Vileikis
TAGS
SQL
Security
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
TITLE
AUTHOR
TheTable
TAGS
PostgreSQL
Docker
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Connection
TITLE
AUTHOR
Lukas Vileikis
TAGS
Performance
TITLE
AUTHOR
Lukas Vileikis
TAGS
Security
TITLE
AUTHOR
Antonello Zanini
TAGS
Columns
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
Performance
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
SQL
TITLE
AUTHOR
Lukas Vileikis
TAGS
Performance
Indexes
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Subquery
TITLE
AUTHOR
Lukas Vileikis
TAGS
Performance
TITLE
AUTHOR
Lukas Vileikis
TAGS
ACID
TITLE
AUTHOR
Lukas Vileikis
TAGS
ALTER TABLE
TITLE
AUTHOR
TheTable
TAGS
MySQL
Ports
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Security
TITLE
AUTHOR
Lukas Vileikis
TAGS
ACID
MySQL
Security
TITLE
AUTHOR
Antonello Zanini
TAGS
BLOB
TITLE
AUTHOR
TheTable
TAGS
Foreign Key
PostgreSQL
TITLE
AUTHOR
Leslie S. Gyamfi
TAGS
PostgreSQL
Concurrency
TITLE
AUTHOR
Lukas Vileikis
TAGS
Security
Encryption
TITLE
AUTHOR
Lukas Vileikis
TAGS
Security
TITLE
AUTHOR
Bonnie
TAGS
Security
PostgreSQL
TITLE
AUTHOR
Antonello Zanini
TAGS
Subquery
TITLE
AUTHOR
Antonello Zanini
TAGS
Transactions
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
Data structures
TITLE
AUTHOR
Antonello Zanini
TAGS
MySQL
TITLE
AUTHOR
Lukas Vileikis
TAGS
SSH
TITLE
AUTHOR
Antonello Zanini
TAGS
Stored procedure
MySQL
TITLE
AUTHOR
Antonello Zanini
TAGS
Triggers
TITLE
AUTHOR
Igor Bobriakov
TAGS
Microsoft SQL Server
Optimization
TITLE
AUTHOR
Bonnie
TAGS
PostreSQL
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
Reusable queries
TITLE
AUTHOR
Antonello Zanini
TAGS
BIG Data
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Security
TITLE
AUTHOR
TheTable
TAGS
Beginner
SQL
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
CRUD
SQL Transactions
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Security
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
JSON
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
InnoDB
ibdata1
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
TITLE
AUTHOR
Scott A. Adams
TAGS
Filter
TITLE
AUTHOR
Scott A. Adams
TAGS
SQLite
TITLE
AUTHOR
Scott A. Adams
TAGS
Excel
Export
TITLE
AUTHOR
Scott A. Adams
TAGS
ERD
Join

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.