MySQL or PostgreSQL: Which is Better?

https://www.percona.com/blog/wp-content/uploads/2023/06/MySQL-or-PostgreSQL-150×150.pngMySQL or PostgreSQL

For more than a quarter of a century, people have been discussing “Which is better, MySQL or PostgreSQL?” — with no resolution. When people ask me which is better, I have to ask them what they want to do and how they want to do it. 

I’ll explain using a bad analogy: 

What type of car is best? This depends on your needs. If you want to go fast, a top fuel dragster will set you back close to a million dollars by the time you buy the chassis, spare engines, tooling, and transporter. That is for a car that goes a quarter-mile at a time. If you want fast, it will do more than 300 miles per hour for about 1,000 feet. But you cannot parallel park it or use it to run down to the store for some chips and beer.

A small economy car is better for that store run and is operated at a fraction of the cost of the dragster. You do not need to wear a flame-resistant suit to drive it or repack the parachutes each time you want to stop after a trip. For most purposes, this is enough of a car for most people. If you are a drag racer, that car will not be competitive. 

Both MySQL and PostgreSQL do the basics very well

From a high level, one relational database management system is pretty much like every other relational database management system. Pick either PostgreSQL or MySQL, and you can be happy, leading a fulfilling and satisfying life full of joy. Both store data more than adequately and will do at least eighty percent of what you want to do with ease.

However, if you have certain data processing needs, budget requirements, limitations in the skill level of support staff, or infrastructure issues, then you need to be a little pickier.  

MySQL was criticized for years for doing dumb things with data such as allowing bad calendar dates, truncating data without warning, and other idiosyncrasies that its users learned to avoid. These problems have been rectified, but the old reputation lives on in the annals of the Internet and the memories of critics. It does not follow the SQL Standard as closely as other databases, and some useful functions such as MERGE() are missing.

PostgreSQL has enjoyed a reputation of being the open source database closest to the SQL Standard, but close might not be good enough for you. When you look at the multi-version concurrency control (MVCC) design that PostgreSQL uses and compare it to the designs of other databases, it looks chaotic. Dealing with dead tuples can be tricky but is much improved with automatic vacuuming, as long as the automated process runs well. And frankly, index bloat seems like something that should have been fixed years ago.

Neither is perfect, and each has its peccadillos that must be honored. Those will be covered a little later herein, but first, in honest fashion, you should ask yourself: “What do I need in a database?”

Determining whether MySQL or PostgreSQL better suits your needs

The vast majority of databases being used today do not get close to using all of their capabilities. Most of the work is CRUD — Create, Read (SELECT), Update, and Delete queries — which will probably never scratch the surface of the advanced features found in that database. Both of the databases being considered here do this type of work exceedingly well.

If you need a feature found in one but not the other, such as JSON_TABLE() in MySQL or MERGE() in PostgreSQL, then your choice has been made for you, maybe. JSON_TABLE() may make it into PG 17 in 2024 after being dropped at the last moment from PG 15. Or if you process a lot of JSON formatted data, you have two choices in PostgreSQL with JSON or JSONB that have their quirks while MySQL has one JSON datatype.

The question becomes this: Is either database good enough for your needs? And these: Does it have the functions you need? Window Functions are great for analytics, but is that something you are going to use? Both MySQL and PostgreSQL have Window Functions, but PostgreSQL is a little more elaborate in its offerings. Both have various ways of replicating data to other servers, but the implementation details of one particular approach might be unpalatable for you. The more exacting your needs, the easier it becomes to identify your choice.

If at this point you realize that you could conceivably use either database, at least in some abstract theoretical way, then we should make the next big step — to the care and feeding of your database.

Care and feeding — which database is easier to manage?

Databases are the toddlers of the software world. While other products can be installed and ignored without worry, databases need attention, constant attention, and lots of it. Ignoring your database can be disastrous.

MySQL is easier to take care of and administer in most cases. MySQL distributions, like Percona’s, tend to be one-stop offerings. The server, client, connectors, etc., are usually offered in one place.”

With PostgreSQL, it can be harder to get all the requisite components because you might have to visit several websites. There are several options for connection poolers, load balancers, and replication packages from various. Installing extensions to the server is easy, but does that new extension work with other parts of your server? In this case, you have to do the testing, whereas MySQL tests its components as a group.

PostgreSQL has benefitted from a lot of engineering in the past several years, which helps make it perform overhead tasks much easier. MySQL has automated most of those overhead tasks so you do not have to worry about them. For example, in PostgreSQL, copies of outdated rows in a table must be vacuumed separately to avoid bloat while InnoDB in MySQL handles this automatically. That alone makes MySQL easier to administer than PostgreSQL.

There are differences in connecting to the server to submit a query. MySQL uses a pool of threads, which is much less work for the server than PostgreSQL’s needing to fork off a process to make the connection. That is a higher load on the server, but it can be rectified by using a connection pooler.

Backups are a necessary part of owning a database. Both databases have many backup tools, which again requires you to make another choice. In the MySQL arena, Percona XtraBackup is the best tool hands down, and I am not just saying that as a Percona employee, but as someone who has used the product. 

PostgreSQL has many options, but no one product stands head and shoulders above the rest. If your database instance is in the cloud, then you can peruse the feature set of your cloud vendor’s offering. But I advise you to make copies of your backups off-premise or off-cloud, no matter your choice.

I used to be the Certification Manager for MySQL AB (and Sun Microsystems and Oracle) and spoke frequently to hiring managers who routinely told me it was very hard to find qualified MySQL DBAs. And they said it was impossible to find qualified PostgreSQL DBAs. If you and your staff have experience and skill in one of the databases, then you will probably skew your criteria in that direction 

MySQL’s InnoDB Cluster is the best thought-out and easiest-to-implement replication architecture. PostgreSQL is playing catchup, as its alternatives are not as simple to implement. Both do logical replication well, but Oracle’s product is more polished.

PostgreSQL is a richer environment with more data types and more operators, and it’s closer to the SQL standard implementation. I am a big fan of the MERGE() function, as I spent part of my career in the processing of cash register transaction logs where this function shines. This might seem like a trivial thing unless you are processing similar data and then it becomes of major importance. PostgreSQL has an almost embarrassing number of index types and the ability to index only some values in a column.  

The PostgreSQL and MySQL communities

Both MySQL and PostgreSQL have large, thriving communities. There are meetups, conferences, mailing lists, slack channels, and tutorials galore for both. One big difference is that PostgreSQL is pretty much developed by contributors using mailing lists while MySQL is mainly produced by Oracle’s MySQL Engineers. The difference is also notable in that Oracle determines the future of upstream MySQL, while PostgreSQL is vendor-neutral.

In both cases, a few hundred individuals work on the main server code. The main difference in the development is that PostgreSQL’s new functionality is open for observation (if you are on the right mailing list) while Oracle often provides little or no notice of something new. 

Conclusion: Do I choose PostgreSQL or MySQL?

Right now, both PostgreSQL and MySQL are great choices for a database. MySQL is easier to implement and run but might lack the features you need. PostgreSQL is feature-rich but needs more care to configure and operate.

Another option is Percona software for MySQL, which has enterprise features such as data basking, at-rest encryption, RocksDB, and an improved connection pooler. The software is also freely available. Percona software for PostgreSQL is also a high-quality offering with many of the most popular extensions already available, making it easier to run PostgreSQL in your production and mission-critical environments. 

Learn more about Percona software for MySQL

 

Learn more about Percona software for PostgreSQL

Percona Database Performance Blog