Most developers will tell you that MySQL is better for websites and online transactions, and PostgreSQL is better for large and complicated analytical processes. They’ll also note that PostgreSQL comes with “a slew of great features” – like extensibility and native NoSQL capabilities to help you deal with challenging database circumstances. Finally, they’ll remind you that MySQL is light on features so it can focus “speed and reliability”.
For the most part, these perspectives are right. Even though every new release is bringing MySQL and PostgreSQL closer together with respect to their offerings (see table), the distinct characteristics of these database systems mean that – for certain use cases – one could be better than the other.
In this guide, we’ll help you understand the different characteristics of MySQL and PostgreSQL, so you can determine which one is better for your use case. Feel free to skip to the sections that interest you most:
General Overview of MySQL and PostgreSQL
MySQL: General Overview
As the world’s most popular DBMS – with 39% of developers using it in 2019 – MySQL is a fast, reliable, general-purpose, relational database management system. Although it lacks the extensive features of PostgreSQL, it’s an excellent match for a wide range of applications – especially web applications.
In fact, MySQL is the go-to choice for scalable web applications – partly because it comes standard in the LAMP stack (an open-source suite of web applications that consists of Linux, Apache HTTP Server, MySQL, and PHP). In addition, popular content management systems like Drupal, Joomla, and WordPress rely on MySQL, so you find MySQL virtually everywhere on the web.
Here are some additional characteristics of MySQL:
- Open source: MySQL is a free and open source relational database management system (RDBMS).
- Long history: MySQL first became available since 1995.
- Maintained by Oracle: Oracle owns and maintains MySQL, and it offers premium (paid) versions of MySQL with additional services, proprietary plugins, extensions, and user support.
- Supportive community: A devoted community of volunteers is available to help with troubleshooting when needed.
- Stable and reliable: Users agree that MySQL is a very stable RDBMS as long as you keep your databases “tidy” and perform regular maintenance.
- MVCC features: MySQL now offers multi-version concurrency control (MVCC) features. A feature that PostgreSQL is more widely known for (we’ll talk more about this below).
- Frequent updates: MySQL benefits from frequent updates with new features and security improvements. The most recent update was Version 8.0.16 on April 25, 2019.
- 4.3-Star Rating: MySQL has a 4.3-star rating (out of 5 stars) from 1,261 reviews on G2Crowd.
Here’s a great summation of MySQL from a G2Crowd reviewer:
MySQL is open source, free, stable database management system which can be used in production applications. It is a lightweight database which can be installed and used on production application servers with big multi-tier applications as well as on a desktop by developers. It can be installed on all platforms like Windows, Linux, and Mac. It is secure and is not vulnerable to any security vulnerabilities.
MySQL boasts some notable users:
- US Navy
Integrate Your Data Today!
Try Xplenty free for 7 days. No credit card required.
PostgreSQL: General Characteristics
PostgreSQL is perceived as a go-to solution for performing complicated, high-volume data operations. That’s because PostgreSQL is better at handling extraordinary database situations (we’ll talk more about this later).
PostgreSQL “has more features” than other database management systems (users attest to this). Also, PostgreSQL is extensible “because its operation is catalog-driven”. In other words, it doesn’t just store information about tables and columns; it lets you define data types, index types, and functional languages.
Additional points that set PostgreSQL apart are the fact it is object-relational, ACID-compliant, highly concurrent, and offers NoSQL support (to be fair, MySQL also offers NoSQL support since Version 8.0).
Finally, although PostgreSQL isn’t the most “popular” database system in the world, it did win the Database of the Year Award the last two years for being the fastest-growing DBMS.
Here are some additional characteristics of PostgreSQL:
- Open source: PostgreSQL is a free and open source object-relational database management system (ORDBMS). As an ORDBMS rather than an RDBMS, PostgreSQL allows for both object-oriented and relational database functionality.
- Customizable: You can customize PostgreSQL by developing plugins to make the DBMS fits your requirements. PostgreSQL also lets you incorporate custom functions made with other programming languages like C/C++, Java, and more.
- Long history: PostgreSQL has been available since 1988.
- Frequent updates: The most recent PostgreSQL update was Version 11.3 on May 9, 2019.
- A liberal open-source license: PostgreSQL features a generous open-source license that lets you use, modify, and distribute the DBMS however you want.
- MVCC Features: PostgreSQL was the first DBMS to implement multi-version concurrency control (MVCC) features.
- A supportive community: A devoted community of developers and volunteers is available to help when needed. Private, third-party support services are also available. The same community supports PostgreSQL and updates the platform via the PostgreSQL Global Development Group.
- 4.4-Star Rating: Has a 4.4-star review (out of 5 stars) from 415 reviews on G2Crowd.
Here's a great summation of PostgreSQL from a G2Crowd user:
PostgreSQL is one of the most interesting options in open-source relational databases. It is free… besides that, today it offers a lot of advanced options. In fact, it is considered the most advanced database engine today. In Postgres, it is not necessary to use reading locks when making a transaction, which gives us greater scalability. Also, this tool is not managed by a person or company but a community of developers that helps us make our work easier and faster.
PostgreSQL users include:
- Red Hat
- Sun Microsystem
Why Do Developers Choose One Over the Other?
As the “feature-rich” choice, PostgreSQL gets a lot of fan-fair from developers. As you’ll see in the descriptions below, it does come with a lot of bells and whistles. Nevertheless, when it comes to database design, MySQL’s simplicity, ease, and other characteristics are more valuable for certain use cases. In this respect, DBMS’s excel in different areas.
Let’s take a look at the key features of MySQL and PostgreSQL from the perspective of why DBMS developers choose one over the other.
Why Do Developers Choose MySQL?
Here are some of the most important advantages of MySQL:
Highly flexible and scalable: MySQL lets you select from a wide range of storage engines. This gives you the flexibility to integrate data from a variety of table types. MySQL 8.0 supports the following storage engines:
A Focus on speed and reliability: By not including certain SQL features, MySQL stays light to prioritize speed and reliability. MySQL’s speed is especially apparent when it comes to highly concurrent, read-only functions. This can makes it an excellent choice for certain business intelligence purposes. That being said, if you need to run a lot of complicated queries under heavy loads, PostgreSQL may be a better choice.
Options for server optimization: MySQL offers a lot of options for tweaking and optimizing your MySQL server by adjusting variables like sort_buffer_size, read_buffer_size, max_allowed_packet, etc.
Easy to use and popular: The popularity of MySQL means it’s easy to find database admins with extensive MySQL experience. Users also report that it’s easier to set up and doesn’t require as much fine tuning as other DBMS solutions. This tutorial shows you just how easy it is for beginners to set up their first MySQL database. Plus, a number of front-ends (like Adminer, MySQL Workbench, HeidiSQL, and dbForge Studio) add a graphical interface to MySQL, which offers a more user-friendly experience.
A cloud-ready DBMS: MySQL is cloud-ready, and a lot of cloud platforms offer MySQL features where they’ll install and maintain your MySQL database for a fee.
Multi-version concurrency control (MVCC) and ACID compliance available with the InnoDB engine: The default engine for current versions of MySQL is InnoDB. This adds MVCC and ACID compliance. However, problems with corrupted tables may still arise with InnoDB on MySQL because of its MyISAM table format. According to MySQL, “Even though the MyISAM table format is very reliable (all changes to a table made by an SQL statement are written before the statement returns), you can still get corrupted tables.” Moreover, selecting another engine will probably result in losing MVCC and ACID compliance.
Why Do Developers Choose PostgreSQL?
Here are some of the most important advantages of PostgreSQL. Already, you might notice why this DBMS requires a little more technical knowledge:
ORDBMS not just RDBMS: PostgreSQL is an object-relational programming language (ORDBMS), so it serves as a bridge between object-oriented programming and relational/procedural programming (like C++ does). This allows you to define objects and table inheritance, which translates to more complicated data structures. An ORDBMS is brilliant when you’re dealing with data that doesn’t mesh with a strictly relational model.
Excellent for complex queries: When you need to perform complicated read-write operations, while using data that requires validation, PostgreSQL is an excellent choice. However, the ORDBMS could experience slowdowns while dealing with read-only operations (that’s when MySQL excels).
Supports NoSQL and a large variety of data types: PostgreSQL is a popular choice for NoSQL features. It natively supports a rich variety of data types, including JSON, hstore, and XML. You can define original data types and set up custom functions as well.
Designed for extra-large database management: PostgreSQL doesn’t restrict the size of your databases. According to a database administrator for Adjust.com, his firm uses PostgreSQL to manage “around 4PB [petabytes] of data”. That’s 4,000 terabytes. He further claims, their “environment processes (and then logs) 100k to 250k requests from outside a second.” Now, that’s a heavy load!
Multi-version concurrency control (MVCC): MVCC is one of the most important reasons why businesses choose PostgreSQL. MVCC permits different readers and writers to interact with and manage the PostgreSQL database simultaneously. This eliminates the need for a read-write lock each time someone needs to interact with the data – thus improving efficiency. MVCC achieves this through “snapshot isolation” (as Oracle calls it). Snapshots represent the state of the data at a certain moment in time.
ACID compliance: PostgreSQL prevents data corruption and preserves the integrity of data at the transactional level. Read more about the value of PostgreSQL’s ACID compliance here. (As mentioned above, and to be fair, MySQL offers ACID compliance capability too, but complications could arise).
User Support for MySQL vs. PostgreSQL
Both RDBMS solutions have extremely helpful communities to provide support to users, in addition to paid support options from the DBMS owner or third-party providers. Here's how they compare:
MySQL User Support
As an open source project, MySQL has a large volunteer community that’s ready to help you with free support and recommendations. The best way to seek this kind of support is on the MySQL and Percona websites.
Here’s what one IT specialist says about MySQL customer support on G2Crowd:
What I liked best is that MySQL an open source project and that means that there are tons of thousands of supports and help is available on the internet for free. I have found this to be particularly very helpful in sorting out otherwise difficult situations. One of the best things about MySQL is that is so popular that you will find a huge community of people ready to help you with any problem you have.
In addition to free community support, Oracle (the owner of MySQL) offers 24/7 paid support with the commercial versions of its products, which cost between $2,000 and $10,000 dollars depending on the level of support package you want to purchase. Alternatively, you can do your own troubleshooting by diving deep into the free MySQL books, manuals and guides found here.
PostgreSQL User Support
Like MySQL, PostgreSQL has a large community of volunteers who give free advice to users on IRC and via the following mailing lists. In addition, you can purchase paid support through third-party providers. Or, you can do your own troubleshooting by pouring over the numerous PostgreSQL manuals and books found here.
Here's what one database admin says about PostgreSQL support on G2Crowd:
...in terms of support, the best ally is the community of people who help others through forums.
Another reviewer on G2Crowd said the following:
One drawback that I have personally experienced is that it is a bit harder to get community support or to Google for results. However, with more use of PostgreSQL the community support is getting better.
Ultimately, support for PostgreSQL could be a little more challenging because (1) the DBMS requires more technical expertise to set up and use; and (2) PostgreSQL experts are fewer in number that MySQL experts.
Is MySQL or PostgreSQL Faster?
Both MySQL and PostgreSQL have strong reputations for being some of the fastest DBMS solutions available. As for which one is the fastest, however, the answer isn’t entirely clear. According to Scott Noyes on TechTarget:
It's easy to find benchmarks that favor one database over another, depending on the hardware, the configuration, and the test. One database might do better on a single-core machine with little memory, while another can take better advantage of scaling to multiple core processors. One might lead on reads while another leads on writes.
TechTarget says that speed tests offer contradictory results. For example, Windows Skills says that MySQL is faster, and Benchw says that PostgreSQL is faster. Ultimately, speed will depend on the way you’re using the database. PostgreSQL is known to be faster while handling massive data sets, complicated queries, and read-write operations. Meanwhile, MySQL is known to be faster with read-only commands.
Which Programming Languages Do They Support?
MySQL and Postgres support many of the same languages with slight variances.
MySQL Supported Languages
MySQL offers support for the following languages:
PostgreSQL Supported Languages
PostgreSQL offers support for a slightly wider variety of languages:
- Other programming languages
What Operating Systems Do They Work With?
Here’s what you need to know about the operating system requirements for MySQL versus PostgreSQL:
MySQL OS Compatibility
MySQL offers cloud-based support, on-premises installations and it’s compatible with the following operating systems and formats:
- Linux (Ubuntu, Debian, Generic, SUSE Linux Enterprise Server, Red Hat Enterprises, Oracle)
- Oracle Solaris
- Open Source Build
PostgreSQL OS Compatibility
PostgreSQL offers cloud-based support and on-premises installations, and users typically install PostgreSQL on Linux servers. In addition, the ORDBMS offers PostgREST REST API. According to the PostgreSQL website:
PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations.
PostgreSQL is available for the following operating systems:
- BSD (FreeBSD, OpenBSD)
- Linux (Red Hat family Linux including CentOS/Fedora/Scientific/Oracle variants, Debian GNU/Linux and derivatives, Ubuntu Linux and derivatives, SuSE and OpenSuSE, other Linux operating systems)
How Do They Index?
Indexes improve database performance by speeding up SQL queries when dealing with large tables of data. Without indexing a database, queries would be slow and overly taxing for the DBMS. Both MySQL and PostgreSQL offer different indexing options.
MySQL Indexing Types
MySQL index types include:
- Indexes stored on B-trees, such as INDEX, FULLTEXT, PRIMARY KEY and UNIQUE.
- Indexes stored on R-trees, such as indexes found on spatial data types.
- Hash indexes and inverted lists when using FULLTEXT indexes.
PostgreSQL Index Types
PostgreSQL index types include:
- Hash indexes and B-tree indexes.
- Partial indexes that only organize information from part of the table.
- Expression indexes that create an index resulting from expression functions as opposed to column values.
How Is Coding Different?
Here are three areas of difference between coding with MySQL vs. PostgreSQL that you should be aware of:
- Case sensitivity
- Default character sets and strings
- IF and IFNULL vs. CASE statements
MySQL is not case sensitive. When writing queries, you don't need to capitalize strings as they appear in the database. PostgreSQL is case sensitive. You need to capitalize strings exactly as they appear in the database or the query will fail.
Default Character Sets and Strings
With certain versions of MySQL, it is necessary to convert character sets and strings to UTF-8. With PostgreSQL, it is not necessary to convert character sets and strings to UTF-8. Moreover, UTF-8 syntax isn’t allowed in PostgreSQL.
IF and IFNULL vs. CASE Statements
In MySQL, it’s perfectly fine to use IF and IFNULL statements. In PostgreSQL, IF and IFNULL statements don’t work. You need to use a CASE statement instead.
In conclusion, choosing between MySQL and PostgreSQL often boils down to the following questions:
- Do you need a feature-rich database that can handle complex queries and massive databases? PostgreSQL could be your choice.
- Do you need a simpler database that’s relatively easy to set up and manage, fast, reliable, and well-understood? MySQL could be your choice.
That being said, as you’ve seen from this guide, your decision could be a little more nuanced than your answers to these simple questions. At Xplenty, we’re curious to know which of these DBMS solutions you’re currently using, and why you chose one over the other. Please drop us a line and let us know!
Xplenty: Data Integration Solutions for MySQL and PostgreSQL
Integrating data from a MySQL or PostgreSQL DBMS into your business intelligence platform could be a source of constant roadblocks and challenges. This is where Xplenty can help. At Xplenty, we offer an extremely powerful and intuitive ETL solution to extract information from nearly any data source (whether you’re using MySQL, PostgreSQL, or something else), then we transform the data to seamlessly integrate with your BI data warehouse. Contact Xplenty now to learn how our solutions can solve your data integration challenges.