PostgreSQL vs MySQL

What are the differences between Postgres and MySQL in terms of features, ease of use, and performance?

Introduction

MySQL and PostgreSQL (also called Postgres) are two of the most popular open-source relational databases. They are both widely used for a variety of applications ranging from small websites to large enterprise systems. While they share many similarities, there are good reasons why you might choose one over the other. It's not always clear which would be better for a specific application. If you're trying to determine what you should learn as a new developer or which you should use for your next project, this article is for you.

How Are MySQL and PostgreSQL Similar?

Both MySQL and PostgreSQL are client-server relational databases that store data in tables with rows and columns. They both support SQL (Structured Query Language) as the standard language interacting with them, although there are minor differences in the syntax and specifics for how certain features are implemented. Additionally, both are available for free and open-source, which means you can use them without having to pay for a license.

Both database systems support ACID compliance, broad cross-platform compatibility, indexing, replication, user roles, and a variety of other important features.

How Are MySQL and PostgreSQL Different?

There's a philosophical difference between how each RDBMS is designed. Basically, MySQL emphasizes simplicity, while Postgres prioritizes flexibility and advanced features. Let's break down some of the key differences.

History and Popularity

Both MySQL and PostgreSQL have been around for a long time. POSTGRES, the predecessor to PostgreSQL, was created first in 1986 at UC Berkeley, led by Michael Stonebraker. Its name was short for "POST-Ingres", as it was a successor to the Ingres database. The original system did not use SQL, but support was added around 1995, and soon after, it was renamed to PostgreSQL. Although the database was powerful and had a long history, it wasn't adopted widely due to a perceived lack of user-friendliness and good documentation in the early days. But today, it has powerful administrative tools, excellent documentation, and a strong community.

MySQL was created in 1995 by Michael Widenius and David Axmark as an alternative to the mSQL database. The system was named after Michael's daughter, My. From the start, the database was designed to be flexible, powerful, and relatively easy to use. This led to its quick adoption by the web hosting community, and it was used as the default database for WordPress. In 2008, MySQL was acquired by Sun Microsystems, which itself was acquired by Oracle in 2010. Due to concerns about Oracle's stewardship of the open-source project, MariaDB (named after Michael's other daughter, Maria) was initially created as a drop-in replacement for MySQL. While MariaDB and MySQL have diverged over time, for purposes of this article, MariaDB and MySQL are essentially interchangeable.

For most of the time both databases were in wide use, MySQL was more popular. But in recent years, PostgreSQL has been gaining popularity, and, according to the 2024 Stack Overflow Developer Survey, PostgreSQL is now the most popular database system, followed by MySQL. On DB-Engines, ranking is affected more by use in enterprise and legacy systems, and PostgreSQL is in the 4th place spot, lagging behind MySQL in the 2nd place. So both are widely used and very popular, although PostgreSQL is growing faster.

License

Although both MySQL and PostgreSQL are open-source, they have different licensing models. MySQL is owned by Oracle and is available under the GPL license. The GPL license is a strong copyleft license that requires that any derivative works be released under the same license. This means that if you directly include MySQL client libraries in software, you must also release your code under the GPL license. That can be very restrictive for some commercial software companies, but for cases like this, commercial licenses are available from Oracle.

PostgreSQL is owned by the PostgreSQL Global Development Group and is available under the PostgreSQL License, which is similar to the BSD and MIT licenses—a permissive license that allows anyone to use PostgreSQL in a project without having to release your code under the same license.

SQL Compliance

SQL is a standard language defined by the ISO, and PostgreSQL and MySQL differ significantly in how closely they adhere to this standard. MySQL prioritizes performance and simplicity over strict adherence to the SQL standard. Some examples of this include:

• MYSQL often attempts to coerce data into the expected type instead of raising errors. For example, comparing '123abc' = 123 returns true (when not in strict mode), even though they are different types.
• MySQL uses the INT UNSIGNED type to store non-negative integers, but unsigned integers are not a concept in the SQL standard.
• MySQL allows more lenient handling of NULL values, which can lead to bugs when a developer expects a value that may actually be NULL.

PostgreSQL, on the other hand, prioritizes strict adherence to the SQL standard. Some examples of this include:

• PostgreSQL enforces strict type checking to ensure that queries with mismatched data types fail instead of producing misleading results.
• Features like GROUP BY, JOIN, and NULL strictly adhere to the SQL standard
• Postgres has implemented advanced SQL features like recursive common table expressions (CTEs) that were not found in MySQL until version 8.0.

There are pros and cons to each approach. MySQL's more permissive approach can make it easier to work with, especially for quick prototyping, lightweight development, and scenarios where flexibility and speed are prioritized over strict correctness. Features like implicit type conversions and lenient NULL handling reduce friction for developers who need to iterate quickly or work with inconsistent data. However, this same permissiveness can introduce subtle bugs, such as unexpected results in type comparisons or unintended behavior with NULL values, especially as applications scale or become more complex. In these cases, the lack of strict enforcement can make debugging and maintaining the codebase more challenging.

Data Types

A notable difference between MySQL and PostgreSQL is the datatypes they support, with PostgreSQL supporting a wider variety of modern and esoteric data types that are not found in MySQL. Both MySQL and PostgreSQL share a foundational set of core data types, including:

• Numeric types like INTEGER, SMALLINT, BIGINT, DECIMAL (or NUMERIC)
• Floating-point types such as FLOAT and DOUBLE PRECISION.
• String types such as CHAR, VARCHAR, and TEXT for storing character data
• Binary types BYTEA in PostgreSQL, and BLOB or VARBINARY in MySQL are available for binary data.
• Time and date types DATE, TIME, TIMESTAMP, and INTERVAL (though PostgreSQL's intervals are more comprehensive).
• Enumerated types defining a fixed set of values, though PostgreSQL's ENUM implementation is more flexible.
• JSON types for storing JSON data.
• Geospatial types for storing geographic data, although Postgres requires the PostGIS extension for comprehensive support of all of these.

MySQL Specific Types

In addition to the core types, MySQL also supports a few things not found in PostgreSQL. This includes:

• UNSIGNED Types for Integers: TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT may be defined as UNSIGNED, doubling the range of positive values by disallowing negative numbers.

• SET Type: The SET type is a string object that allows a column to store zero or more values from a predefined list. For example, a column defined as SET('a', 'b', 'c') can store combinations like "a,b" or "b,c". PostgreSQL has no direct equivalent, although similar functionality could be implemented using arrays.

• Year Type (YEAR): MySQL includes a special YEAR data type for storing 4-digit years (e.g., 2023). This type is particularly useful for fields like birth years or manufacturing years. PostgreSQL requires using a more generic type like INT or SMALLINT to represent years.

• Distinct Blob Variants: While both databases support binary data, MySQL provides distinct blob types (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB), offering finer control over size limits for binary data. PostgreSQL uses a single BYTEA type for binary data that supports up to 1GB of data, handling storage optimization internally via the TOAST mechanism.

These types make MySQL well-suited for scenarios requiring predefined value lists, basic spatial data handling, or additional storage flexibility, but they come at the cost of deviating from the SQL standard and sometimes limiting compatibility.

PostgreSQL Specific Types

In addition to the core types, PostgreSQL supports a number of unique and advanced data types not found in MySQL. These types make PostgreSQL particularly powerful for handling complex, specialized, and modern workloads.

• ARRAY Type: Multi-dimensional arrays of any data type that allow a single column to store multiple values. For example, a column of type INT[] can store arrays like {1, 2, 3}.

• UUID Type: Native universally unique identifiers (UUIDs), often used for distributed systems and unique keys.

• Range Types (int4range, tsrange, etc.): Built-in range types to store ranges of values of other types, such as int4range (integer range) or tsrange (timestamp range). These types are useful for things like scheduling or ranges.

• HSTORE Type: Stores key-value pairs in a single column, useful for semi-structured data when the structure varies per row. (Requires the hstore extension.)

• JSONB Type: PostgreSQL's JSONB (binary JSON) type offers advanced indexing and efficient querying capabilities, making it ideal for applications handling semi-structured data at scale. MySQL's JSON type is capable, but much slower.

• CIDR and INET Types: These specialized types allow manipulation of IP addresses and subnet data. These come with functions for querying and validating network data, making them useful for networking applications.

• MACADDR Type: Stores MAC addresses in a standard, efficient format.

• XML Type: PostgreSQL offers a native XML type for storing and querying XML data, including support for XPATH queries.

• TSVECTOR and TSQUERY Types: These types allow for full-text search functionality. TSVECTOR stores preprocessed search text, and TSQUERY allows complex search queries. MySQL supports full-text search but does not have specialized data types for this purpose, and it is not as flexible or performant.

• Composite Types: PostgreSQL allows users to define custom composite types, which group multiple fields into a single logical unit. This feature is useful for complex data modeling. MySQL does not support composite types.

• Geospatial Types: The PostGIS extension provides an extensive suite of geospatial data types (e.g., GEOGRAPHY, GEOMETRY) and functions for advanced spatial analysis. This makes PostgreSQL a leading choice for GIS applications.

• INTERVAL Type: PostgreSQL provides the INTERVAL type for representing periods of time (e.g., 2 years, 3 months, 1 day, 4 hours). It allows operations like addition, subtraction, and formatting alongside date and time types. You can calculate dates by adding an interval (`CURRENT_DATE + INTERVAL '2 days").

As you can see, while there is considerable overlap between the two databases, PostgreSQL has many advanced data types that are not found in MySQL, which can make it more powerful and efficient for certain types of applications.

Performance

Developers always ask, "Which one is faster?" when comparing different systems that do similar things. In the real world, creating a well-structured database using the optimal data types and indexes is the factor that has the biggest impact on performance. Missing a critical index could easily degrade performance by an order of magnitude, regardless of which database you're using.

That said, there are differences in performance between MySQL and PostgreSQL, but it's not straightforward because many factors can affect performance, including the specific workload and database configuration. Overall, for most tasks, MySQL and Postgres will be within about 30% of each other. In some scenarios, however, one will be faster than the other.

In simple, high-throughput scenarios like write-heavy applications or read-heavy workloads with many concurrent users, MySQL's InnoDB storage engine and thread-per-connection model can give it an edge over PostgreSQL. However, in more complex queries or transaction-intensive workloads that require strict ACID compliance, advanced indexing, and rich query capabilities, Postgres can perform better.

Postgres can also get a boost from its advanced data types and indexing capabilities, which can make certain operations faster. For example, Postgres can use its JSONB type to perform faster JSON operations than MySQL. When doing full-text searches, Postgres can use its tsvector and tsquery types to perform faster and more complex full-text searches than MySQL. When needed to do operations in the field of networking or geography, Postgres's support for these features is superior and can offer significant performance benefits.

There are thus many specialized applications, such as data modeling or scientific applications, where Postgres is faster than MySQL. The caveat is that, in order to make use of these features and squeeze the most performance out of Postgres, you need to have a good understanding of how to properly create your database and use the appropriate data types and indexes. For this reason, MySQL often has an edge for beginners or simple applications, although these scenarios don't typically push a system to its limits anyway.

Replication and High Availability

For large-scale databases, it's important to have multiple copies available if one copy goes down. MySQL offers several options: asynchronous replication (fast but less consistent), semi-synchronous replication (ensures at least one replica gets the data before committing), and group replication with Galera Cluster, which lets all nodes handle writes for high availability. PostgreSQL, on the other hand, uses streaming replication, which is typically asynchronous but can be made synchronous for better consistency. For advanced setups, PostgreSQL uses tools like Patroni for automatic failover in case of server issues and supports logical replication, allowing selective replication of specific tables or cross-database syncing, which MySQL lacks.

In a nutshell, MySQL offers more options for replication and clustering, but PostgreSQL offers more advanced features, like logical replication and the ability to selectively replicate specific tables or databases.

Connection Management

The connection models of databases affect their scalability. MySQL uses a thread-per-connection model, where each client connection spawns a new thread. This approach performs well with limited connections but can struggle under heavy load if resources are constrained. PostgreSQL uses a process-per-connection model, spawning a new process for each connection. This provides better isolation and stability but consumes more resources per connection, which can become a bottleneck under high concurrency.

Locking and Concurrency

In order for a database to be read and written to by multiple users simultaneously, a DBMS must be able to lock data to prevent it from being corrupted from being written to by multiple processes at the same time. PostgreSQL and MySQL differ in their approaches to locking and concurrency. PostgreSQL offers multi-user concurrency through its implementation of Multi-Version Concurrency Control (MVCC). PostgreSQL ensures that readers never block writers and vice versa, reducing contention in high-concurrency environments. It also offers more strict transaction isolation levels, including serializable transactions, ensuring consistency even in complex, multi-step operations. Despite the benefits, this MVCC implementation is not without its detractors, as it increases storage requirements and memory usage, and can lead to table bloat.

MySQL's InnoDB engine also implements MVCC but relies more heavily on locks for certain operations, which can lead to deadlocks in high-contention scenarios. PostgreSQL's approach is more robust for applications with many simultaneous transactions, while MySQL's simpler locking mechanisms can suffice for less complex workloads and offer better performance.

Ease of Use

Basically, MySQL is more forgivable, while Postgres is more strict. This often gives the impression that MySQL is more friendly to beginners, although this may lead to problems as an application grows in complexity. For example, MySQL allows non-aggregated columns in a SELECT query that uses GROUP BY, which can produce unexpected results but is less restrictive for quick prototyping or simpler queries. This is not allowed in Postgres.

MySQL also defaults to case-insensitive string comparisons, which can be easier for those unconcerned with precise case. In Postgres, you have to explicitly set the COLLATE and CTYPE parameters to make strings case-insensitive or use the ILIKE operator.

One area in which MySQL one-ups Postgres is that it allows cross-database queries, which can be convenient for some applications that use multiple databases. However, doing something like this is much less straightforward in Postgres.

Ultimately, MySQL's relaxed approach and defaults make it a good choice for beginners or simple applications where ease of use and speed of development are priorities. PostgreSQL's stricter adherence to standards, while requiring a steeper learning curve, provides a more reliable and predictable foundation for complex and large-scale applications.

Conclusion

Despite the many similarities between MySQL and Postgres, when you dig into the details, you can find many differences. To summarize this article, it can be said that MySQL is more forgiving to beginners, and offers slightly better performance for simple queries and high-throughput workloads thanks to its thread-per-connection model and efficient replication options. On the other hand, if you're a new or intermediate developer and want to learn best practices and eventually build complex applications, Postgres could be a better choice.

Postgres is more strict, requires a steeper learning curve, and offers better support for advanced features and complex queries. Many experienced developers prefer Postgres because it has a superior query optimizer, more advanced data types, is less likely to produce unexpected results and deadlocks, and forces developers to write SQL-compliant queries. Postgres is also growing in popularity and is the clear winner in the 2024 Stack Overflow Developer Survey.

Ultimately, MySQL and PostgreSQL are both excellent databases that are extremely popular, powerful, and capable of high performance. The choice of which to use for a particular application is generally a matter of personal preference. Unless you have a very complex or specialized use case in mind, you can't go wrong learning or using either.