DBAs can be set in their ways. Oftentimes, we start with a particular flavor and from that moment until the end of time, it will always be “the best”. In some cases, the debate is actually based on matching the use case to the proper tech (I’m looking at you SQL vs NoSQL). Lately, I’m starting to see many teams working with multiple flavors of the same general technology which begs the question: which tech “is better”?
In the spirit of full disclosure, I’m one of those “set in my ways” DBAs that has worked extensively with one flavor. MySQL has been my focus for the better part of the last 15 years. While part of me wants to sit on the porch, waving my cane, and tell other flavors to “get off my lawn”, I’m instead using this as an opportunity to really examine PostgreSQL vs MySQL in an objective manner.
This is the first post in a series exploring PostgreSQL from the perspective of a MySQL DBA. I’ll start at a very high level – what are both technologies, how are they similar, how are they different, etc. In later posts, I’ll look at operational, schema, and other aspects as I work to learn more about PostgreSQL.
MySQL
MySQL is an RDBMS (Relational Database Management System). This means that it has all the standard features one would expect – tables, views, foreign keys, stored procedures, and ACID compliance (when using InnoDB). It works very well for most OLTP workloads and some OLAP workloads as well.
While I’ve seen some implementations doing very complicated and non-trivial workloads, MySQL tends to shine with standard, relational schemas, and web-based workloads. Simple asynchronous replication allows for easy read-scaling and report query offloading. Synchronous replication allows for straightforward HA while still maintaining ACID and high throughput.
PostgreSQL
PostgreSQL is an ORDBMS (Object Relational Database Management System). This takes all the standard RDBMS features and adds support for complex objects, table inheritance, and additional data types beyond JSON. This may seem like a small difference, but it allows PostgreSQL to support much more complex workloads and schema designs.
Similar to MySQL, replication allows teams to build out varying architectures. This can help with HA and read scalability. While it can definitely support standard OLTP/OLAP workloads, the vast community is constantly developing new features and functionality. This allows for easier adoption for a wider variety of workloads.
Key Feature Differences
A major difference between the two flavors is that PostgreSQL offers custom object definitions and table inheritance. This greatly extends the standard relational database model and provides support for very complex workloads.
Some other differences include:
- PostgreSQL supports more modern data types (JSON, XML, etc) compared to MySQL which only supports JSON
- PostgreSQL support Materialized Views
- PostgreSQL uses an open source license similar to BSD/MIT where MySQL uses the GPL license
Initial Takeaways
After looking through the high-level differences and similarities, both flavors definitely have their place in the open source database ecosystem. While both options work for basic relational workloads, MySQL excels in web-based applications while PostgreSQL shines with complex workloads. This matches a key trend I’ve been seeing – new applications/microservices tend to launch on MySQL as they are designed with very basic data structures and relationships. In contrast, PostgreSQL is commonly a target for migration from large, enterprise databases moving to open source.
So which is “better”? Like every good consulting answer, it depends. Both have strengths and weaknesses which make the target application the key deciding factor. This also leads to more hybrid environments as large enterprises modernize (aka rewrite) applications while also migrating legacy monolithic applications to open source to avoid licensing costs.
If you are having trouble deciding which flavor suits your needs, Percona’s Professional Services team can help analyze your application and requirements to look for the best fit. With support and distributions for both flavors, we don’t play favorites and will help get your business into the right solution!
