Join our newsletter

By subscribing to our newsletter you agree with Keboola Czech s.r.o. Privacy Policy.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Run your data operations on a single, unified platform.

  • Easy setup, no data storage required
  • Free forever for core features
  • Simple expansion with additional credits
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

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.

August 4, 2020
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.

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 database architecture, which structures data according to the relational data model. The relational model organizes data as tables of rows and columns. In non-tech terms: 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

We offer a completely free tier of our data-stack-in-one platform. Click here to create a free account.

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. web 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 between the database systems.

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. Both Postgres and MySQL implemented several indexes, such as full-text indexes for full-text search, or automatically indexing primary keys for fast speed row retrieval. But 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. A larger assortment of triggers, helping you to govern the constraints of your CRUD operations.

These (and other features) position Postgres as a better choice than MySQL for analytically heavy operations.

Why does this matter?

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

PostgreSQL performance is utilized to the fullest in systems that 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 large volumes of 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 are popular open-source RDBMS, which is reflected in their vibrant active open-source communities, who offer support for free. Alongside its free version governed by the GNU General Public License, MySQL also offers paid-tier support, which can be attractive to enterprises. We can break down their support multiple tiers of support options into several categories:

Support Type

a) Self-serving: MySQL Documentation, Postgres Documentation

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

c) Bug resolution: MySQL StackOverflow, Postgres StackOverflow

d) Paid support: MySQL Oracle Partner Network, Postgres has no officially affiliated companies

e) Database management tools: both RDBMS have at their disposal multiple cross-platform third-party tools to help you access and manage the database from a GUI instead of the command line. For instance, PostgreSQL is administered via pgAdmin, DBeaver, Navicat, DataGrip, …, while MySQL users can pick from a variety of choices such as MySQL Workbench, phpMyAdmin, HeidiSQL, …

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’s commercial license offered by the Oracle Corporation 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 advanced features for analytics. 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. MySQL supports only standard SQL syntaxes, while PostgreSQL supports very advanced custom-made procedures. 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. This makes PostgreSQL an excellent choice for your data science and big data needs.

Bonus Material: PostgreSQL vs MySQL complete comparison table

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 modeling 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 support, but Postgres implements XML data structures as a native data type, widening the possibilities of XML manipulations and transformations
  • The 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 popular choice.

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 corruption. 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 standards-compliant, while MySQL is only compliant when running on the InnoDB storage engine. ACID compliance is part of the wider SQL standard, which guarantees databases behave as expected. If reliability is high on your “must-have” list, Postgres is the best choice.

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

Final verdict: Which is the best database?

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 scoring high on “ease of 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, there is no ideal solution. The choice of which is the better database depends on your specific use cases: 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. Get started with building data pipelines or data science models at no cost.

We offer a completely free tier of our data-stack-in-one platform. Click here to create a free account.

Oops! Something went wrong while submitting the form.

Subscribe to our newsletter
Have our newsletter delivered to your inbox.
By subscribing to our newsletter you agree with Keboola Czech s.r.o. Privacy Policy.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Recommended Articles

Close Cookie Preference Manager
Cookie Settings
By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage and assist in our marketing efforts. More info
Strictly Necessary (Always Active)
Cookies required to enable basic website functionality.
Made by Flinch 77
Oops! Something went wrong while submitting the form.