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.
