A very short guide to choosing between MySQL and PostgreSQL.

Written with Nick Arner.

The road forks. You have two choices: MySQL or PostgreSQL.

PostgreSQL and MySQL have a lot in common. They’re both extremely flexible and have myriad use cases. So, it’s not that there’s a wrong choice per se—usually this boils down to making a more precise choice. And as any good developer knows, precision will only make your life easier in the long run.

What MySQL and PostgreSQL have in common…

MySQL

MySQL is a simple and user-friendly relational database. If you’re working on projects where speed, simplicity, and easy management are key, you’ll probably want to stick with MySQL.

It’s known for being great for beginners, fast, reliable, and secure. It’s primarily used for websites and web applications.

• It’s popular, and there’s no shortage of online support.
• It’s secure.
• It’s fast.
• It’s reliable.
• It supports different types of replication.
• It’s flexible and scalable.
• It has a more extensive set of tools, including GUI tools and enterprise support.

Languages supported:

• Node.js
• C/C++
• Java
• Ruby
• Perl
• PHP
• .NET
• R
• Go
• Lisp
• Python
• Erlang

MySQL index types:

• 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.

MySQL is best for these use cases:

• Simpler databases.
• Distributed / clustered database setups (PostreSQL does not support these types of setups natively).
• Websites or web-applications, particularly eCommerce.

PostgreSQL

PostgreSQL is an object-relational database. This means PostgreSQL can support objects, classes, and inheritance like an Object-Oriented databases and to support data types and tabular structures like a Relational database.

It has a steeper learning curve but is better at handling complex queries and large volumes of information.

• It’s feature-rich and supports a variety of data types.
• Because it’s an object-relational database, it’s better for supporting more complex data structures.
• It supports multi-version concurrency control (meaning that a database transaction will be protected from viewing inconsistent data while a concurrent transaction is also updating that data).
• It preserves the integrity of your data on the transactional level.
• It has a large extensions system which add many useful features.

Languages supported:

PostgreSQL supports a wide range of languages. Some of the more popular ones include…

• JavaScript
• C/C++
• Go
• Java
• .Net Languages
• Python
• R

PostgreSQL index types include:

• Numeric, String, and Date/Time types; just like MySQL
• Hash indexes and B-tree indexes.
• JSON
• 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.

And many more index types! The Postgres docs also provide a full list.

PostgreSQL is better for these use cases:

• Anything where you’re working with complicated or large volumes of data
• Analytics
• General-purpose transaction databases
• Geospatial databases via PostGIS

Both MySQL and PostgreSQL are extremely versatile, though in different ways.  MySQL can interact with a wide array of applications and web-browsers, as well as interface with a variety of different programming languages.

Since PostgreSQL has the benefits of being both a relational and object-oriented database; it can work with a rich range of different data types - from the standard types that MySQL supports to user-defined data types. This allows for diverse applications using unique data types to be built on top of a solid, well-maintained database. To be fair, not everyone agrees.

While MySQL and PostgreSQL have many things in common, you should make sure you understand the differences between them in order to make sure you select the right one for your specific application.