PostgreSQL vs MySQL — the Aiven championships

PostgreSQL and MySQL are both widely used open source database management systems (DBMS) that also support some noSQL features. Coincidentally, or not, both are available through Aiven.

When you’re looking for a reliable managed database solution, which one should you pick?

How are managed PostgreSQL and MySQL different?

PostgreSQL and MySQL are very similar in the uses they are put to — well, they’re relational databases, you can’t really use MySQL as a coat rack ( although it can make toast). That said, we can dig out some differences.

Applications

One big limitation on your freedom of choice is applications that expect to find a particular database. Flask, for example, likes PostgreSQL, so you’re better off using that. Conversely, Drupal goes well with MySQL. (That being said, both have community-driven extensions that allow other databases.)

Conformance

Of the two, PostgreSQL comes closer to full SQL:2016 Core conformance. PostgreSQL 12 supports 160 of the 179 required features. MySQL, on the other hand, strays further from the fold with only 54 supported features.

Both PostgreSQL and MySQL are ACID compliant (atomicity, consistency, isolation, durability): transactions are protected and queries maintain data integrity. With MySQL, this requires using InnoDB.

Languages and data types

In terms of supported programming languages, PostgreSQL offers a wider range, but not by much. MySQL ticks the boxes for C/C++, Delphi, Erlang, Go, Java, Lisp, Node.js, Perl, PHP and R; to these, PostgreSQL adds JavaScript, .Net, Python and Tcl.

For stored procedures, PostgreSQL supports, for example, Ruby, Perl, Python, TCL, PL/pgSQL, SQL and JavaScript. MySQL is only able to counter here with SQL:2003 syntax.

Performance/Speed

Common wisdom holds that where MySQL is lithe and quick, PostgreSQL is full-featured and versatile. This would make MySQL more suited to heavy loads performing straightforward tasks, and PostgreSQL a better fit for complex use cases.

While kind of true, this picture is a little simplistic.

PostgreSQL focuses heavily on performance improvements in its development. PostgreSQL 13 alone listed 12 performance-enhancing changes, continuing a trend from PostgreSQL 12. At the same time, MySQL’s extensive community actively develops new features. However, increased complexity presents performance challenges. So there’s less of a difference now than once there was.

User interfaces

MySQL has MySQL Workbench; you can also use it for PostgreSQL, or opt for e.g. pgAdmin or SQL Workbench.

Extensions

Aiven offers a comprehensive list of extensions for PostgreSQL. These include TimescaleDB, which, when used as a PostgreSQL extension, gives you access to features developed for time series data without having to move over to a full-blown TSDB. Then there’s PostGIS, which adds location data features by supporting geographic objects.

But there are loads more. Check out the full list of PostgreSQL extensions at Aiven in our Help article!

Data security and authentication

MySQL implements user management with roles and privileges. For authentication, you can use Linux PAM, LDAP, and native Windows services. Similarly, PostgreSQL has robust user and group management, but it also includes a pluggable config file. It supports PAM and Kerberos. SSL is used in PostgreSQL, but not in the more weakly-encrypted MySQL.

Coding

Coding with PostgreSQL or MySQL is slightly different. MySQL is not case sensitive, and happily eats IF and IFNULL statements. It also requires UTF-8 input, though, which PostgreSQL will not accept. PostgreSQL won’t like your IFs and IFNULLs, either (you have to use CASE statements instead).

Support

MySQL has a larger community, so when you get in trouble you can count on more robust support; at least as far as your peer group goes. However, it can be argued that PostgreSQL, as the more standards-conformant and robust system, doesn’t require as much support… And it also has a dedicated community.

(Besides — if you come to Aiven, we’ll give you bonus cookies, in the form of 24/7 expert support. Just saying.)

Wrapping up: when to use which

For developers in need of a rich feature set and admins requiring protected, fault-tolerant environments, PostgreSQL is a good choice. It’s a little complex to set up and manage, but if you get it fully managed from Aiven, neither of those issues will be a headache.

For those needing a fast, lightweight, proven database solution that’s customizable with a little work, MySQL could be the right companion. It also has a vibrant community to answer any questions you may have. Then again, get it managed from Aiven and we can certainly answer those questions for you.

Not using Aiven services yet? Sign up now for your free trial at https://console.aiven.io/signup!

In the meantime, make sure you follow our changelog and blog RSS feeds or our LinkedIn and Twitter accounts to stay up-to-date with product and feature-related news.

Originally published at https://aiven.io.

--

--

--

Your database in the cloud, www.aiven.io

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

8 Most Useful Shortcuts Key on Visual Studio and Symbols for Mac

Running a spark submit command on docker

Introducing Type Hinting: The Ultimate Python Superpower

An Intro to Couchbase Analytics

AlterNET Studio 8.0 released — now with support for Visual Studio 2022 and .NET 6

SLOs for everyone with Sloth

goto statement (C# Guide)

Splash Screen Flutter — Create customize Splash Screen for flutter(Android/iOS)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Aiven

Aiven

Your database in the cloud, www.aiven.io

More from Medium

Synonyms in Elasticsearch

How to secure your GraphQL API (Part 2, the Typetta way)

How to make Redis play nice with your data

Configure your NodeJS Application with IBM Cloud App Configuration using nconf-appconfig