Choosing a database management system is often an afterthought when starting a new project, especially on the Web. Most frameworks come with some object-relational mapping (ORM) tool that more or less hides the differences between different platforms and makes them all equally slow. Using the default option (MySQL in most cases) is rarely wrong, but it’s worth considering. Don’t fall into the trap of familiarity and comfort: a good developer should always make informed decisions between the different options, their benefits and drawbacks.
Historically, MySQL has had a reputation for being an extremely fast database for read-heavy workloads, sometimes at the cost of concurrency when mixed with write operations.
PostgreSQL, also known as Postgres, is billed as “the
world’s most advanced open source relational database.” It was built to be feature-rich, extensible and standards-compliant. In the past, Postgres performance was more balanced: reads were generally slower than MySQL, but it was able to write large amounts of data more efficiently and handled concurrency better.
The performance differences between MySQL and Postgres have been largely erased in recent versions. MySQL is still very fast at reading data, but only if you use the old MyISAM engine. If you use InnoDB (which allows transactions, key constraints, and other important features), the differences are negligible (if any). These features are absolutely critical for enterprise or consumer-scale applications, so using the old engine is not an option. On the other hand, MySQL has also been optimized to reduce the gap when it comes to heavy data writes.
When choosing between MySQL and PostgreSQL, performance shouldn’t be a factor for most common applications: it will be good enough in any case, even if you consider expected future growth. Both platforms are perfectly capable of replication, and many cloud providers offer scalable managed versions of either database. Therefore, it’s worth considering the other advantages of Postgres over MySQL before starting your next project with the default database settings.
Postgres
is a relational database
of objects, while MySQL is a purely relational database. This means that Postgres includes features such as table inheritance and function overloading, which can be important for certain applications. Postgres also adheres more closely to SQL standards.
Postgres handles concurrency better than MySQL for multiple reasons:
Postgres
implements Multiversion Concurrency Control (MVCC) without read locks Postgres supports parallel query plans that can use multiple CPUs/cores Postgres can create indexes in a non-blocking manner (through the CREATE INDEX CONCURRENT syntax), and you can create partial indexes (for example, if you have a model with soft deletions, you can create an index that ignores records marked as deleted) Postgres is known for protecting the integrity of Data at the transaction level. This makes it less vulnerable to data corruption.
Default installation and
extensibility
of Postgres and MySQL
The default installation of Postgres generally works better than the default installation of MySQL (but you can modify MySQL to compensate). MySQL has some completely strange default settings (for example, for encoding and character collection).
Postgres is highly extensible. It supports a number of advanced data types that are not available in MySQL (geometric/GIS, network address types, JSONB that can be indexed, native UUID, timestamps with time zone recognition). If this is not enough, you can also add your own data types, operators, and index types.
Postgres is truly open source and community-driven, while MySQL has had some licensing issues. It was started as a company product (with a free and a paid version) and Oracle’s acquisition of MySQL AB in 2010 has raised some concerns among developers about its future open source status. However, there are several open source forks of the original MySQL (MariaDB, Percona, etc.), so this is not considered a big risk at this time.
When
to use MySQL Despite
all these advantages, there are still some small drawbacks to using
Postgres that you should consider. Postgres
is still less popular than MySQL (despite catching up in recent years), so there are fewer 3rd party tools or developers/database administrators available. Postgres
forks a new process for each new client connection that allocates a nontrivial amount of memory (approximately 10 MB).
Postgres is built with extensibility, standards compliance, scalability, and data integrity in mind, sometimes at the expense of speed. So, for simple, read-heavy workflows, Postgres might be a worse choice than MySQL.
These are just some of the factors a developer might want to consider when choosing a database. Also, your platform provider might have a preference, for example, Heroku prefers Postgres and offers operational benefits to run it. Your framework may also prefer one over the other by offering better drivers. And as always, your coworkers may have opinions!
If you have an opinion on database selection, please add a comment below – we’d love to hear your opinions. If you liked this, you should follow us on Twitter. Check out our YouTube channel where we post screencasts and other videos.
