MySQL is also an open-source relational database. The difference is that MySQL is maintained by Oracle and not volunteers like PostgreSQL. MySQL also offers several tools and environments for sale to enterprise customers, but the tool’s basic functionality is always free.

Now let’s jump into the similarities because these two databases undeniably have much in common.

Similarities of PostgreSQL and MySQL

• Operating system
  Both databases run on all major operating systems. Suppose you want to run your database on something non-traditional. In that case, PostgreSQL might be your choice because, in addition to the usual suspects (Linux, Windows, OS), PostgreSQL also supports some Unix-like systems. If you have a deep desire to see if your refrigerator’s operating system can support a database, the answer might be yes.
• Reliability
  In terms of reliability, both MySQL’s default storage engine (innoDB) and PostgreSQL are ACID compliant. This means that you have industry-wide standard crash-recovery mechanisms in place with either so you won’t have corrupted data in the event of software failure or hardware malfunction. In other words, both databases are super reliable.

On to the differences of PostgreSQL vs. MySQL, in which the differences between these two systems end up defining them.

• Performance
  The benchmark tests for a simple MySQL implementation are lighting fast. In terms of performance, think of MySQL as a streamlined racehorse versus PostgreSQL as a powerful plow horse. While robust enough for most use cases, MySQL doesn’t have the depth of capabilities of PostgreSQL, but the decision not to include these things is purposeful and with a view toward speed.
  
  Indexes speed up query performance but use too many or poorly implement them and they slow down inserts, updates, and deletes. Indexing then is one way where the give and take between advanced functionality and performance can play out. PostgreSQL has some advanced indexes, including partial indexing, which MySQL does not support. Why would you want a partial index? Say you have a customer table that includes several churned or removed accounts; your partial index could ignore these “soft deleted” records. The slimmed-down MySQL index offering gives you fewer ways to affect performance potentially negatively.
• Architecture
  When I talk about PostgreSQL capabilities, I mean specifically the inclusion of classes, inheritance, complex data types, functions and other features that either don’t exist in MySQL or are implemented in a limited way. PostgreSQL, with these object-oriented features, is sometimes referred to as object-relational. It is in a special class of relational databases.

• Architecture
  When I talk about PostgreSQL capabilities, I mean specifically the inclusion of classes, inheritance, complex data types, functions and other features that either don’t exist in MySQL or are implemented in a limited way. PostgreSQL, with these object-oriented features, is sometimes referred to as object-relational. It is in a special class of relational databases.
  
  Just taking an example, inheritance, which is the very handy ability in PostgreSQL for a child table to inherit data from a parent table, doesn’t exist in MySQL. I will avoid repeating the documentation exactly by just saying that you can think of inheritance as a way of avoiding data duplication and over complexity by making it easy to query and update parent and child tables at the same time—a truly developer-friendly feature that is one of many available in PostgreSQL.
  
  Below is a non-exhaustive list of a few more:
  
  • Advanced date handling, including timezone-aware timestamps
  • Super customizability: custom data types, operators and index types
  • Advanced data types: GIS, network address types, UUID, HStore
• Security
  Databases are tasty snacks for hackers. Secure implementation is the only sure protection against external negative forces. Both MySQL and PostgreSQL are considered highly secure, and both have robust features available to a security-minded sysadmin.
  
  An interesting differentiator on the database security level is that PostgreSQL has row-level security. Say, for example, you want to share your sales table with your customers but only want them to be able to view the rows that pertain to them. Row-level security lets you do that.
• Support
  MySQL is more widely used than PostgreSQL. While choosing a database isn’t a popularity contest, this can make a difference as to whether you can find third-party tools and database administrators that can work on your database.
• Scalability
  It is becoming more and more common to host both MySQL and PostgreSQL on virtual machines. In this type of environment, resources can be scaled up or down depending on need. With this adaptability comes challenges. While it is undeniable that PostgreSQL can hold its own in this space, MySQL has one or two features that give a slight advantage in scalability.
  For example, MySQL can auto-detect the system memory on a dedicated server and adjust appropriately without having to edit configuration files. What does this really mean? Well, the database administrators that you were easily able to find when you choose MySQL as your database might have less to do.
• Common Applications
  MySQL is often used for web applications that securely handle smaller amounts of data, while PostgreSQL is often utilized for larger and more complex datasets. Another frequent application for MySQL is to support a read-only reporting/visualization layer like the database you choose to power your BI tool or internal metrics. Does this mean that you can’t use MySQL for your analytics project? No, and the global popularity of MySQL bears that out.

Both database tools are reliable, secure and respected in the larger data community. Both are good choices. The decision becomes harder when you factor in that both are open-source and your decision can’t be made with your wallet.

In the end, the differences between PostgreSQL vs. MySQL only become truly relevant at the bleeding edge of use cases. In the vast majority of cases, either one would be suitable. In the end, the decision might come down to factors outside of the ones listed here; in the modern world, our data doesn’t live in isolation. What are the preferences of your developers? What applications do you currently use that can connect to the database of your choice?

Whichever database solution you choose, StreamSets can connect your organization to its data.