PostgreSQL vs MySQL: Which Database is Best for Your Needs?

Relational databases power many of the applications and online services we use every day. Two of the most popular open source database options are PostgreSQL and MySQL. Both have been around for over 20 years and offer robust feature sets. However, there are some key technical and architectural differences that make each database better suited for certain use cases.

This in-depth guide compares PostgreSQL and MySQL across critical categories to help you decide which option may be best for your next project. We’ll look at:

Brief histories of PostgreSQL and MySQL
How they differ in SQL syntax compliance and features
Data types, flexibility, scalability and performance
Ease of use and learning curves
Industries and use cases where each excels
Companies using PostgreSQL vs MySQL
Cost, support and licensing considerations

By the end, you’ll have the knowledge to determine whether PostgreSQL or MySQL (or neither!) is better for your application and infrastructure needs. Let’s dive in.

Brief Backgrounds

First, a quick history on both databases…

What is PostgreSQL?

PostgreSQL, also known as Postgres, is an object-relational database management system (ORDBMS). It was created at UC Berkeley in 1996 by a team led by Professor Michael Stonebreaker, a pioneer in database research.

Over its 25+ year history PostgreSQL has built a reputation for being a highly stable, feature-rich and standards-compliant open source database. It enables complex SQL processing and can handle high volumes of data and traffic for enterprise applications.

What is MySQL?

MySQL is a relational database management system (RDBMS) developed by the Swedish company MySQL AB in 1995. It was acquired by Sun Microsystems in 2008, which was then purchased by Oracle in 2010.

Despite now being owned by Oracle, MySQL has remained open source and free to use under the GNU GPL license. It powers many complex web and enterprise applications due to its speed, reliability and ease of use.

SQL Compliance and Feature Differences

A key technical consideration when comparing databases is their SQL syntax support.

SQL Standards Compatibility

PostgreSQL aims to fully comply with all SQL standards, including supporting advanced SQL:2011 features. This makes it highly flexible.

MySQL’s SQL support is more customized. It diverges from standards compliance in certain aspects to optimize for performance and usability.

As a result, some complex SQL capabilities are only possible in PostgreSQL, while unconventional queries may only run on MySQL.

Sample Advanced SQL Queries

Here are some examples of advanced SQL functionality that PostgreSQL can handle that MySQL cannot:

FULL OUTER JOINS

Returns all records from both tables, matching where available. Useful for creating reports without missing data:

SELECT * 
FROM Table_A
FULL OUTER JOIN 
Table_B
ON Table_A.id = Table_B.id

INTERSECT Operator

Returns only rows that exist across two queries:

SELECT column_name(s) FROM table1 
INTERSECT
SELECT column_name(s) FROM table2;

Recursive CTEs

Enables queries that reference themselves to model hierarchical data:

WITH RECURSIVE hierarchy AS (
  SELECT id, parent_id, name  
  FROM tree
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name  
  FROM tree c
    INNER JOIN hierarchy p ON c.parent_id = p.id 
)
SELECT * FROM hierarchy;

These operations can be inefficient or difficult to replicate in MySQL without procedural logic.

Data Types and Flexibility

In addition to SQL support, the range and flexibility of data types is an important consideration when evaluating database systems.

PostgreSQL provides native support for a wider variety of data types than MySQL:

Numeric: Integers, floating point, serials, big integers, etc
Monetary: Money and currency types
Character: Text, varchar, char, etc
Date/Time: Timestamps, time with timezones, intervals, etc
Boolean: Simple true/false
JSON: Native JSON columns
Spatial/GIS: Geometry and geographic types
And more: Arrays, ranges, UUIDs, XML, etc

Many of these require additional plugins or configuration in MySQL.

The extensive data type support allows PostgreSQL to adapt more gracefully to evolving application requirements. It’s ideal for future proofing.

MySQL offers versatility through a range of storage engines optimized for different data models or performance demands. But it has a smaller set of native data types, which can present portability issues when data schemas evolve.

Performance Benchmarks

Performance is dependent on many variables, so isolated benchmarks should be considered as helpful data points rather than universal truths.

That said, PostgreSQL has faster performance than MySQL across common benchmarks:

Source

Latency is lower and throughput higher in PostgreSQL relative to MySQL, suggesting it’s optimized for more complex queries. PostgreSQL also scales more efficiently with additional computing resources.

These patterns hold up across diverse hardware configurations.

That said, both PostgreSQL and MySQL can sustain high volumes and throughput for real world applications with proper infrastructure, indexing, and tuning.

Ease of Use and Learning Curve

For developers and DBAs, the learning curve and ease of use also factor into database preference.

In this aspect MySQL generally has broader appeal for its simplicity and approachability. The MySQL command set more closely resembles traditional programming logic.

PostgreSQL is considered more challenging to master. It enables statements like recursion and advanced programmatic functions that take time to learn.

However, PostgreSQL’s consistency with SQL standards means expertise gained transfers well across other databases like Oracle, SQL Server and others. Time invested learning Postgres concurrently trains SQL development skills.

The learning curve depends considerably on developers’ previous SQL experience. Those already comfortable with SQL standards may gain Postgres proficiency faster.

When to Use PostgreSQL vs MySQL

With the comparisons above as background, we can start delineating what types of applications lend themselves to PostgreSQL vs MySQL.

Use Cases Better Suited to PostgreSQL

PostgreSQL excels in use cases that demand:

Strict standards compliance and portability: PostgreSQL avoids proprietary extensions to the SQL standard. This pays dividends when migrating across database systems.
Data integrity: Features like enabled-by-default MVCC support simultaneous processes without overlapping data.
Complex transactions: Sophisticated services like banking require guaranteed outcomes across nested operations.
Analytic or scientific processing: Advanced SQL window functions and algorithmic capabilities are ideal for analysis.
Custom data types and schemas: Purpose-built types for geospatial, JSON, key-value and more fit Postgres without extensions.

Industries where PostgreSQL has gained traction include:

Financial and fintech
Geospatial and telecommunications
Healthcare and electronic records
Public sector and nonprofit
Retail and ecommerce

Use Cases Better Suited to MySQL

MySQL’s advantages shine for:

High traffic read-intensive apps: Optimized to sustain near-linear scalability across cheap commodity hardware.
Apps already using PHP/LAMP stack: Tight PHP integration lowers latency.
Speed to prototype and iterate: Simpler SQL dialect and schema changes keep iteration rapid.
Legacy migration: Can directly replace orphaned SQLite and Access installations.

MySQL remains ubiquitous across:

Web/mobile applications
Ecommerce and SaaS sites
Digital marketing and analytics platforms
Media streaming and content delivery

It’s the default choice for PHP-based sites thanks to efficient binding with web development frameworks like Laravel.

Overlapping Use Cases

Of course, PostgreSQL and MySQL also overlap significantly for general purpose applications like:

Company websites
Web apps and services
Multi-user productivity software
Inventory, logistics and operational systems
And essentially any application in need of a battle-tested RDBMS!

The large web properties using both PostgreSQL and MySQL further demonstrate their interchangeable utility for common database workloads.

Notable Companies Using PostgreSQL

PostgreSQL has earned considerable credibility among prominent internet brands. Major entities that use Postgres in some capacity include:

Reddit – one of the highest trafficked sites worldwide
Apple – iTunes store and other services
Instagram – over 1 billion active monthly users
Spotify – popular music and podcast platform
Twitch – pioneered game streaming ecosystem

Additionally NASA, NTT Communications, DreamWorks and thousands more utilize PostgreSQL tailored to their complex data workloads.

Notable Companies Using MySQL

As one of the original open source RDBMS platforms, it’s perhaps no surprise MySQL boasts comparable brand name adoption. Consider that:

Uber – relies upon MySQL for rapidly geotracking rides
Netflix – streaming service with over 200 million subscribers
Airbnb – global lodging marketplace connecting travelers
GitHub – software development and version control platform
Slack – business chat app with over 150,000 paying teams
Twitter – popular social network sending trillions of Tweets

Alongside countless dynamic web apps, MySQL remains the ticket for massive scale on commodity infrastructure.

Cost and Licensing Considerations

Lastly, licensing costs may represent another differentiation point:

PostgreSQL is completely free and open source across all versions and applications.

MySQL offers dual licensing – users can choose between the freely available Community Edition or pay for a Commercial License from Oracle which unlocks additional proprietary extensions.

Commercial licensing is primarily beneficial for exotic deployment configurations demanding Oracle’s specialized support and cluster replication capabilities. Typical web applications rarely require these premium features.

PostgreSQL and MySQL share more commonalities than differences. Both deliver battle tested performance, stability and scalability required for most web and enterprise applications.

However, specific use cases lend themselves toward one platform over the other.

To recap the differentiators highlighted in this guide:

For PostgreSQL

Favors standards compliance over all else
Leads benchmarks across read/write and transactions
Better supports advanced SQL operations
Preferred by organizations dealing with financial data

For MySQL

Optimized to handle extremely high traffic loads
Simpler schema and built-in PHP integration
Easier for junior developers to learn
Huge libraries of resources available

For specialized applications:

PostgreSQL for analytics/science
MySQL for high volume web properties

And both remain interchangeable for general purpose use.

The database with the best performance, security, and scalability for YOU depends on your infrastructure, traffic patterns and size, development team skills, and application complexity.

No universally “best” database exists (although zealots on both sides may attempt to convince you otherwise!)

Weigh the factors above against your unique needs to decide if PostgreSQL or MySQL is a better foundational database choice as you develop your next application.