Community
PostgreSQL vs MySQL: Which one should you choose?
There are 7 main points of difference between PostgreSQL and MySQL - discover them and download our complete comparison table.

Bonus Material: PostgreSQL vs MySQL complete comparison table

PostgreSQL (or Postgres) and MySQL are both relational database management systems (RDBMS for short). They are complex technological inventions designed to simplify your data operations across a wide variety of business use cases. The “relational” part of the name refers to the way in which they structure data as relations between rows and columns. They’re like Excel but with added features… so more of an Excel on steroids.

From storing a digital representation of all your purchasing orders to analyzing performance metrics, RDBMS were developed to facilitate working with data at scale.

But not all RDBMS were created the same. Choosing the right database from the very beginning can offset and mitigate problems that arise later on, such as limited analytics and the lack of support. 

To make your decision easier, we’ve provided a side-by-side comparison of two of the most popular databases - Postgres and MySQL - across a range of criteria:

  1. Performance
  2. Popularity
  3. Support 
  4. Analytics
  5. NoSQL features
  6. Working with time and dates
  7. ACID compliance

Comparison #1: Performance

Both Postgres and MySQL are relational database management systems (RDBMS), which means that they store data in the relational (tabular) model. An RDBMS is expected to perform a variety of functions:

  • CRUD (create, read, update, delete) operations on data
  • Optimize CRUD operations in the background (abstract from users)
  • Allow other programs to utilize CRUD operations (e.g. Python Application)
  • Multi-user access control
  • Stored procedures for common tasks
  • Backup and recovery management
  • Data integrity management (e.g. ACID compliance)
  • … and a myriad of other things!

People often try to compare MySQL and Postgres on their level of performance - in other words, how fast and how well they realize their functions. 

For decades, it was common knowledge that MySQL was better at read-heavy operations (e.g. a simple BI system for your e-commerce store), while Postgres shined in complex OLTP/OLAP systems but used more memory (each new Postgres process is allocated 10MB).

However, with each new update, their performance at the read-write level is becoming increasingly comparable.

One point of difference which remains noteworthy is the performance features that are unique to Postgres:

  1. Materialized views - allow you to save the results of an analytic SQL query as its physical table on disk, which can be more efficiently addressed by other SQL queries. This speeds up complex or nested SQL queries by several orders of magnitude.
  2. Indexes - indexes allow the RDBMS to execute CRUD operations faster. Unlike MySQL, Postgres boasts a wider range of indexes, such as partial indexes (used for filtering data), bitmap indexes (efficient when working with categorical data), and expression indexes (indexes as a function of other columns).
  3. Larger assortment of triggers, helping you to govern the constraints of your CRUD operations.

These (and other features) position Postgres ahead of MySQL for analytically heavy operations.

Why does this matter?

For most use cases, both MySQL and Postgres are going to be similar in performance. 

Postgres will make your data work better if you expect heavy analytic workloads.

And, unless you have extreme data needs (on the level of Netflix, Facebook, and other tech giants), you will barely notice performance issues. If those arise, you’re better off with a completely different solution to meet your needs, such as developing your own query language to handle petabytes of Facebook data.

Comparison #2: Popularity

MySQL is more popular than PostgreSQL on a variety of metrics:

Nevertheless, in a shocking plot twist, developers love Postgres a lot more than they do MySQL. 

How do we reconcile these differences? 

The truth is, both databases are superior in the wider world of relational databases. Postgres is relied upon by Netflix, Instagram, Spotify, Reddit, Twitch, and many more. MySQL is trusted by Uber, Verizon, NASA, Tesla, and other giants. No matter your database choice, you will join countless other market leaders in making that decision.


Why does this matter?

RDBMS do not hold prom king and queen elections (alas). So, why does popularity matter in the first place? It’s a great signal of market availability. The higher the popularity of an RDBMS, the higher your chances of finding (and hiring) developers and database administrators. This is especially important if you plan to extend your operations in the future.

Comparison #3: Support

Both MySQL and Postgres offer multiple tiers of support options:


Support Type

a) Self-serving: MySQL Documentation, Postgres Documentation

b) Community: MySQL Forum, Postgres Mailing list, IRC, Slack

c) Bug Resolution: MySQL StackOverflow, Postgres StackOverflow

d) Paid Support: MySQL Oracle Partner Newtork, Postgres has no officialy affiliated companies

Even though both Postgres and MySQL come with a vibrant community of volunteers and supporters, MySQL has a better paid-tier support structure, which is directly connected to Oracle. Additionally, MySQL has more StackOverflow questions and answers, as well as a wider range of materials (books, documentation) to help you resolve your issues. 

Why does this matter?

Sooner or later, you’re going to encounter problems with database administration or analytical queries. It’s best to prepare yourself beforehand by exploring your options.

If you’re not very comfortable with RDBMS or lack the necessary experience, MySQL is the safer way to go.

Comparison #4: Analytics

Both MySQL and Postgres rely on SQL. In that respect, both languages are capable of analyzing a wide constellation of data and answering business questions across the board.

However, Postgres has developed more analytic functions. These speed up and optimize analytical queries:

  • Window functions, which allow you to do aggregate computations across rows, such as moving averages and cumulative sums.
  • Date and time functions, which shorten date and time computations from multiple code lines to single function calls.
  • Generate series, allowing you to create sets of objects, such as a date range or all integers. Extremely useful when analyzing data sources with missing data. Simply join the generated series with the original data and observe any missing spots. 
  • Statistical functions, such as standard deviations, least square regression, and others. These allow you to perform advanced analytic analyses within the database without the need to export your data to statistical software.


Why does this matter?

If your use case involves analytics, or is likely to involve more complex analytical queries in the foreseeable future, choose Postgres. Additionally, if you expect to be dealing with a lot of data cleaning in your pipelines, Postgres offers more out-of-the-box solutions for wrangling messy data. With its variety of analytic functions, it will speed up and optimize your analytical operations.

Comparison #5: NoSQL features

NoSQL refers to data modeling, which does not follow the tabular (Excel-like) form of relational databases. This includes anything from XML, JSON and graphs, to other wildly imaginative representations of data.

The challenge of modelling and storing NoSQL data is relatively new. Despite this, the demand for NoSQL databases is increasing due to the proliferation of non-structured data (social networks, which are graphs, Internet of Things logs, XML as a representation of web pages, geospatial data (coordinates) for geolocating specific events, etc.).

Both Postgres and MySQL support the storage of JSON objects (one of the most popular NoSQL data representations), but Postgres trumps MySQL with its additional NoSQL features:

  • JSON native data types not only allow you to store JSON objects as a column, but also to index that column for massively improved performance
  • MySQL has some XML functions, but Postgres implements XML as a native data type, widening the possibilities of XML manipulations and transformations
  • Native UUID data type for smoother handling of ID fields
  • Text vector types, which allow you to work with rich text corpora
  • The geometric types and functions of Postgres are considered to be among the best-in-class for topographic work. Even these can be easily extended with PostGIS to add extra (geometry, geography, raster, and other) types to the native Postgres ones.

Why does this matter?

If your use case depends heavily on NoSQL data, Postgres is the one for you. 


Comparison #6: Working with time and dates

When analyzing time series or working with event logs data, we often use timestamps in the form YYYY-MM-DD HH:MM:SS to determine exactly when an event was observed.

Postgres and MySQL treat timestamps very differently. MySQL will always convert the timestamp to the local time on the server in UTC before storing the value. On the other hand, Postgres offers the same functionality, but also has the option to save timestamps with timezone as a native data type.


Why does this matter?

For the majority of use cases, users do not need to concern themselves with knowing when an event occurred and in which timezone. But for time-sensitive operations (such as financial trading, digital advertising attributions, certain IoT applications, etc.) the ability to determine the time AND the timezone is crucial for core business operations. If you do work with time series, MySQL is simply not the right choice. Opt for Postgres instead.

Comparison #7: ACID compliance

ACID is a standard set of properties for low-level operations, which guarantee that database transactions perform without errors or corruptions. Whether your transaction involves writing something new into the database, updating existing records, deleting old records, or reading/retrieving existing information, you expect the database to do as instructed. That is, create, read, update, or delete (CRUD) records. What could go wrong?

The non-ACID nightmare: Imagine your customer buys from your e-shop while at the same time, your data engineer runs an update on the database and migrates tables to a different location. Because the database did not comply with ACID standards, (only) part of the purchase details were written into the orders table before it was migrated. You’re now left with a customer who has paid for an order, but you’re missing their address and product list. How will you resolve that? And how will you even know which products are missing?

ACID ensures that data does not go missing or get corrupted.

Postgres is fully ACID compliant, while MySQL is only compliant when running on the InnoDB storage engine.


Why does this matter?

In some use cases, you need to have exhaustive data quality. Purchase transactions are one such example, since you’re required to keep a transaction record by law. For these cases, make sure to pick a database (storage engine) that is ACID compliant. If you don’t care about missing or corrupted data, and you just want to keep a partial record (like many projects in IoT), feel free to ignore Postgres’s superiority in ACID compliance.


Final verdict

Both MySQL and Postgres are fantastic examples of relational database management systems, but with different comparative strengths.

MySQL provides better support and is accompanied by a greater number of developers who know its workings inside out. Being slightly faster and easier to use, it offers great value for low technical investment.

In comparison, Postgres is more ACID compliant and comes with out-of-the-box solutions for NoSQL, analytic, and date and time use cases. But this comes at a slightly lower performance level. The performance itself can be enhanced with additional performance boosters, such as multiple indexes, but this requires a higher level of expertise in databases.

In conclusion, MySQL is straightforward and fast, while Postgres is more powerful but also more complex.


Our take on the Postgres vs MySQL comparison

At Keboola, we are an equal opportunity kind of platform. Whether you swing towards Postgres, have a crush on MySQL, or have a soft spot for a dozen other database solutions, we accommodate your taste. 

Why? Because we strongly believe that there are different ways to solve the same problem, and sometimes you should pick multiple tools to reap the best of them all.

But choosing the right database is just one of the puzzles in the bigger data picture. We've prepared a detailed comparison table which you can download with the form below.



Free download just few clicks ahead
Oops! Something went wrong while submitting the form.