When it comes to choosing a database for your web application, most tech professionals will compare MySQL vs Postgres. MySQL can be found in almost every industry out there. It is used in aerospace and defence companies such as Boeing, NASA, Lockheed Martin and even the US Navy. It is trusted in bank applications at Bank of America, Citibank, U.S. Bancorp and Square. To date, there are over 158,856 companies using MySQL.
With Postgres, there are only 3,750 companies using it. Companies such as Uber, Netflix and Spotify are heavily invested in it and with its impressive features, developers are describing it as the open source version of Oracle.
MySQL vs Postgres: Key differences
There are many similarities between MySQL and Postgres. One for example is that both databases are ACID compliant. Both work well in BI applications , however Postgres works exceptionally well in data warehousing and data analysis applications.
As far as differences are concerned, this is how they differ:
- MySQL is a pure relational database vs Postgres is an object-relational database.
- MySQL is the product of Oracle Corporation vs Postgres is the product of Global Development Group.
- MySQL is not extensible vs Postgres is highly extensible.
- MySQL does not provide the Data Domain Object vs Postgres does provide the Data Domain Object.
High Availability: MySQL vs Postgres
Replication is one of the most popular features used in today’s RDBMS and is used to provide disaster recovery purpose or to provide a high availability of data. Replication is available in both MySQL and Postgres. While MySQL has offered replication for a number of years, Postgres has only offered its replication as a built-in feature recently.
It is important to note that replication in MySQL is different than in Postgres. In MySQL, object, data and security operations run on the master are copied to the master server’s binary log. A user has the option of replicating an entire server, one or more databases or just a selection of tables. The slave server obtains information from the master server’s binary log over the network then copies the commands and/ or data and applies the changes to the slave’s relay binary log first before applying it to the slave database and its binary log.
Postgres replication is based on a mature and long used technology called ‘Write Ahead Log (WAL) Archiving’. With the latest version of Postgres, there has been some significant enhancements producing extremely fast WAL processing that results in near real-time replication.
All objects, data (including the schema) and security operations executed on the master are written to the WAL log directly on the slave machine for safety. WAL also ensures that no transaction is committed on the master until a successful write of the WAL log has occurred. The slave then applies the WAL log by directly rewriting the raw table data on disk which is a lot faster than statement based replication used in MySQL.
Not all replication strategies are fault-less. Data loss and corruption can occur in MySQL but depending on the configuration, data loss can be limited to a single transaction.
Write Speed – MySQL vs Postgres
PostgreSQL is widely used in large systems where read and write speeds are crucial and data needs to be validated. In addition, it supports a variety of performance optimizations that are available only in commercial solutions such as Geospatial data support, concurrency without read locks, and more.
Overall, PostgreSQL performance is utilized best in systems requiring execution of complex queries often found in data warehousing applications and business intelligence analysis and reporting. PostgreSQL performs well in OLTP/OLAP systems when read/write speeds are required and extensive data analysis is needed.
PostgreSQL also works well with Business Intelligence applications but is better suited for Data Warehousing and data analysis applications that require fast read/write speeds.
MySQL is a widely chosen for web based projects that need a database simply for straightforward data transactions. It is common for MySQL to underperform when strained by heavy volumes or when attempting to complete complex SQL queries or subqueries that are often found in data analysis and heavy computations.
MySQL performs well in OLAP/OLTP systems when only read speeds are required. That’s why a lot of banks trust using MySQL for banking applications.
MySQL provides very good read/write speeds for OLTP scenarios. Overall, MySQL performs well with high concurrency scenarios.
MySQL is reliable and works well with Business Intelligence applications, as business intelligence applications are typically read-heavy.
Query Language difference in MySQL vs Postgres
SQL is usually the common language to query a relational database. The Postgres query language known as PostQuel is a variant of the SQL standard but is closer to the standard than with MySQL. Out of the 179 mandatory features required for full Core conformance, Postgres satisfies about 160 features.
The use of the query language in both MySQL and Postgres are similar but there are differences in the syntax. One of the things missing in MySQL is the CHECK constraint. The CHECK constraint is used to validate data before it is committed to the database. Another thing missing in MySQL is the materialized view, something that Postgres has. Other things that MySQL lacks include:
- Full outer joins.
- Intersect.
- Except.
Indexes
Indexes enhance database performance as they aid the database server in locating and retrieving rows must faster than without an index. But unfortunately, indexes add a certain overhead when it comes to inserts and updates. Without an index, the database server must start with row one and scan the entire table, one row at a time to look for a specific condition. This is known as a linear search and linear searches are the slowest among all searches.
MySQL offers a primary key index, unique index, full text that all utilizes a B-TREE. It also offers an index on spatial data types that utilizes a R-TREE. With a B-TREE, all values are sorted then an upper and lower boundary is determined. The search is done between the upper and lower values resulting in a faster search than a linear search.
Postgres offers three types of indexes that MySQL lacks. They are partial indexes, bitmap indexes and expression indexes. Even with additional index types, Postgres is still slower than MySQL when it comes to retrieval.
Deployment – MySQL vs Postgres
Almost every hosting company on the internet offers access to MySQL on the cloud. Because popularity is growing in Postgres, there are some upcoming web hosts that also offers Postgres on the cloud. Cloud hosting has come a long way and countless companies are hosting database servers on cloud based servers. Since cloud hosts are highly configurable, the right size and power can be selected for the specific needs of the database for the right cost.
Depending on the hosting option, new hosts can be provisioned quickly. When choosing a host, look for a dedicated host vs a shared host if you require an extremely high load database.
Use Cases: MySQL vs Postgres
Uber
Uber Engineering recently changed from Postgres to MySQL Uber has built a schema-less framework on top of MySQL
Skype
Skype uses Postgres to store all Skype chat and call activities. Calls are routed through an external VOIP service which also uses Postgres.Skype chose Postgres because it wanted to scale its business to over a billion users.
Etsy
Etsy is a market for handmade goods. Struggling to scale the business from a 2005 startup, Etsy has tried multiple projects with Postgres but failed. Finally in 2011, Etsy has moved to MySQL
Disclosure of Material Connection: Some of the links in the post above are “affiliate links.” This means if you click on the link and purchase the item, I will receive an affiliate commission. Regardless, I only recommend products or services I use personally and believe will add value to my readers.
