Skip to content

Making Sense of PostgreSQL vs MySQL: Which Should You Use and Why?

As an IT professional, you rely on database management systems to store, organize and retrieve critical application data that powers much of the software we use daily. Two of the most proven open source relational database options are PostgreSQL and MySQL. They have much in common – yet also important technical differences under the hood.

So how do you determine if Postgres or MySQL is better suited for your next project? By the end of this comprehensive guide, you’ll understand the key capabilities, strengths and limitations of both to make the optimal choice based on your needs.

Understanding What They Do

First, it helps to understand what purpose databases like PostgreSQL and MySQL serve. At the most basic level, they are servers that provide structured storage and retrieval services for application data so app developers don’t have to build this functionality from scratch.

Some examples of data that apps commonly store in these databases include:

  • User profiles in a social network or email platform
  • Product listings in an ecommerce catalog
  • Analytics tracking site visitors, purchases and usage metrics
  • Payment transaction history in a billing system
  • Location data in a ridesharing or mapping app

This data is stored in an organized format known as tables, with columns and rows, similar to a spreadsheet. The database allows apps to perform operations like:

  • Efficiently query and filter through millions of rows
  • Run aggregate commands to group and sum data
  • Process many read and write requests simultaneously via concurrency
  • Replicate data across servers for scale and reliability

By offloading these complex data storage and retrieval tasks to battle-tested databases like PostgreSQL or MySQL, developers can focus more on building application logic and the user experience.

Origins and History

PostgreSQL and MySQL have taken fairly divergent paths in their three decade long histories:

PostgreSQL Begins at UC Berkeley

PostgreSQL traces its origins back to UC Berkeley in California in 1986. The preliminary database project was named POSTGRES and later evolved into PostgreSQL.

The initial design priorities included:

  • Emphasis on standards compliance – both the relational model and SQL standards
  • Extensibility to new types and application workload requirements
  • Backwards compatibility between major versions

These priorities stem from PostgreSQL’s academic origins focused on research, experimentations and new approaches.

Over the past 30+ years, PostgreSQL matured from an academic project into an enterprise-grade open source relational database system stewarded by the PostgreSQL Global Development Group.

Let’s now switch over and look at the history of MySQL.

MySQL Developed to Power Web Apps

Unlike PostgreSQL’s more research-oriented history, MySQL was designed from the start to be a fast, reliable and easy to use database option specifically targeted for web applications.

The founders Michael “Monty” Widenius and David Axmark first released MySQL in 1995 to power their early web app ideas.

Over the next decade, MySQL pioneered the concept of open source relational databases – gaining widespread adoption powering applications for high traffic web sites thanks to:

  • Speed optimized for read-intensive web workloads
  • Ease of configuration – simpler than alternatives at the time
  • Lower cost than commercial closed-source options

In 2008, Sun Microsystems acquired MySQL which then became part of Oracle’s portfolio when they purchased Sun Microsystems. While no longer fully open source, MySQL remains a widely used database option.

So in summary – PostgreSQL came from academia; MySQL sprang from solving real-world web scalability hurdles in the 90s internet boom. These origins manifest themselves in technical and philosophical differences that continue impacting database choices today between PostgreSQL vs MySQL.

Key Differences and Capabilities

Now that we’ve covered a brief history on both PostgreSQL and MySQL, where do they differ when it comes to actual functionality and capabilities?

There are four main technical areas where Postgres and MySQL diverge:

  • SQL Language and Standards Support
  • Data Types and Structures
  • Reliability Mechanisms
  • Scalability and Flexibility

I’ll break down each area in the sections below with examples – but first, let’s kick things off with a high level capabilities primer.

At a Glance: PostgreSQL vs MySQL Features

Here’s a head-to-head overview before we dive into more details:

Features PostgreSQL MySQL
SQL Standards Compliance Full Partial
Data Types 40+ built-in 20-30
Reliability Mechanisms MVCC, WAL etc Limited
Scalability Replication, sharding Replication
Flexibility Table inheritance, procedures More rigid

To dig deeper on the meaning behind this table and what you really need to know – read on!

SQL Standards Support

SQL (Structured Query Language) is the programming language used to interact with relational databases for storing, modifying or accessing data as needed. Both PostgreSQL and MySQL understand SQL for common operations like:

SELECT title, pages FROM book WHERE author = ‘Stephen King’;

UPDATE customer SET email = ‘[email protected]’ WHERE customer_id = 2334545;

However when looking at advanced SQL functionality, PostgreSQL shines over MySQL in standards compliance and breadth of language features supported. Let’s analyze some examples.

Recursive Queries

Consider a multi-location retailer tracking the organizational relationships between corporate HQ, regional offices, local storefronts and their managers using a table like:

 location | parent_location | manager
----------+-----------------+----------
    HQ    |        NULL     | Margaret
   West   |        HQ       |    Sam
  SF_Store|       West      | Sandra
 East     |        HQ       | Tyler
NJ_Store  |       East      | Carrie

To fetch a combined hierarchy listing from all locations rollup to HQ, we can use a recursive SQL query like:

WITH RECURSIVE hierarchy as (
      SELECT location, parent_location, manager, 1 AS depth  
      FROM locations
      WHERE parent_location IS NULL
   UNION ALL
      SELECT l.location, l.parent_location, l.manager, p.depth + 1
      FROM locations l
      INNER JOIN hierarchy p ON l.parent_location = p.location
)
SELECT * FROM hierarchy;

Output:

 location | parent_location | manager | depth
----------+-----------------+----------+-------
    HQ    |        NULL     | Margaret|     1
   West   |        HQ       | Sam     |     2 
  SF_Store|       West      | Sandra  |     3
 East     |        HQ       | Tyler   |     2
NJ_Store  |       East      | Carrie  |     3

This outputs the hierarchical representation we want in a single query without complex application code.

The key thing to note – MySQL does NOT support recursive CTEs but PostgreSQL does thanks to more robust SQL standards support.

Beyond advanced queries, PostgreSQL also supports compound data types, stored procedures and advanced integrity constraints – areas where MySQL only offers partial coverage.

Data Types and Structures

The data types and custom type extensions supported by a database engine also impact what application data it can store, query and process efficiently.

PostgreSQL provides native support for a wider array of data types than MySQL – over 40 built-in types versus 20-30 in MySQL.

For example, PostgreSQL supports:

  • Network address data types like INET for IPs and CIDR for subnet masks
  • Spatial data types like POINT to store geographic objects
  • Native JSON and array structures to store semi-structured data
  • Advanced date/time span types like TIMESTAMP WITH TIME ZONE

This flexibility helps model data accurately for more use cases without complex processing in application code.

MySQL until recently lacked many advanced data types. Newer versions now offer JSON columns, expanded floats and decimals but still trail PostgreSQL in areas like arrays, geospatial and network address data.

Another structural difference is PostgreSQL allows table inheritance – where new tables can inherit some or all fields from a parent table. This provides more flexibility modeling entities with shared attributes. MySQL lacks table inheritance completely.

So in summary, PostgreSQL provides greater breadth and depth when it comes to applying different data types and structures – though MySQL covers the most common cases. Know your requirements.

Reliability Mechanisms

For mission critical applications storing valuable business data, the reliability and durability guarantees of the underlying database are paramount.

Both PostgreSQL and MySQL offer fundamental reliability capabilities like ACID compliant transactions and backups.

However, PostgreSQL emphasizes stability with advanced concurrency control, multi-versioning and write-ahead log based recovery:

  • Multi-Version Concurrency Control (MVCC) – Allow greater concurrency and isolation between reads/writes
  • Write-Ahead Logging (WAL) – Recover reliably after crashes and enables replication
  • Sophisticated caching – Minimize disk I/O with loadable cache managers

The MVCC capability in particular provides strong concurrency and performance when handling frequent transactions compared to locking schemes used in most other databases.

MySQL historically achieved high performance through simplicity rather than robust concurrency models. Until version 8.0, reliability capabilities like data checksums and crash-recovery via binary logging were also eventually added.

So while MySQL offers many common backup and recovery features, PostgreSQL bakes in cutting edge reliability architecture directly into its DNA – optimized over 30+ years stemming from its academic origins.

Scalability and Flexibility

Modern applications require databases that can grow with increasing data volumes and users without sacrificing performance. Scalability and flexibility are crucial evaluation criteria.

Replication – Both PostgreSQL and MySQL can scale out reads using master-slave replication servers. This allows directing read queries like reports, analytics and backups to replicated slaves while the master focuses on writes.

However, PostgreSQL offers more advanced master-master and multi-master configurations allowing horizontal scalability of writes too by automatically sharding data across servers. Tools like PostgresXL provide these capabilities.

MySQL historically relied on scale-up via vertical scaling with bigger machines but now offers Group Replication in later versions for some multi-master functionality. However, auto-sharding data remains a gap without application level logic.

Beyond replication, PostgreSQL also provides more fine-grained flexibility configuring storage layouts with:

  • Tablespaces – Define file layouts table-by-table
  • Column oriented storage via concepts like zones maps

Add in upcoming Just-In-Time compilation, and, in most cases PostgreSQL provides equal or greater data scale-out mechanisms compared to MySQL counterparts.

Sample Production Deployments

We’ve covered the key technical capabilities and architectures of PostgreSQL and MySQL across areas like query language, data types, reliability and scalability.

But which applications and use cases are companies directly leveraging these databases for today?

Let’s take a look at some sample production deployments at leading consumer web and enterprise companies.

PostgreSQL Powers Apple’s Global Cloud Services

At Apple, PostgreSQL serves as the foundational database behind:

  • iCloud – syncing photos, emails, documents across devices
  • Siri – powering many query and predictive functions
  • App Store and Apple Music – managing commerce data
  • Location and Mapping infrastructure

Cumulatively, Apple deals with extreme data volume across device users worldwide via these services. Reliability and uptime are also vital for consumer trust.

After years of Oracle deployments worth billions, Apple is aggressively moving database workloads to open source PostgreSQL powering the world‘s highest trafficked cloud properties and iOS backbone.

The NBA Improves Stats and Analytics with PostgreSQL

The National Basketball Association (NBA) relies on real-time sports data and analytics to track players, team performance, emerging trends and game outcomes.

Initially the NBA relied on MySQL but found it resource intensive needing expensive hardware to provide sufficient performance at scale. Query response times were also slow for the rapid pace required.

By migrating to PostgreSQL, the NBA improved query speeds by 66% with just a quarter of the infrastructure costs. PostgreSQL‘s native JSON support also eased the shift towards semi-structured data. Analytic dashboard latency reduced from 45 seconds to sub 5 seconds even with years more data growth!

For ad hoc analytics or unpredictable workloads in data science, finance or other domains, PostgreSQL often provides performance, flexibility and scalability.

Web-Scale Media Sites Trust MySQL

MySQL remains a solid choice powering many massive web properties including:

  • Facebook – Managing access tokens and some ranking functions
  • Twitter – Storing tweets attachments and metadata at scale
  • Spotify – Logging real-time streams of song playback data
  • Netflix – Recording metrics on video consumption like bookmarks and completion rates
  • Airbnb – Payment data and booking transactions

Engineering leaders at these web-native companies emphasize MySQL’s speed and horizontal scalability for simpler read/write intensive workloads. JSON support and other new capabilities ease earlier functionality gaps too.

The simplicity and streamlined performance that helped MySQL profoundly scale web apps in the 2000s makes it still well suited for modern social, mobile and commerce data demands.

Key Takeaways Comparing PostgreSQL and MySQL

We covered a lot of ground comparing PostgreSQL and MySQL! Let’s recap the key high level takeaways:

  • PostgreSQL prioritizes functionality depth, standards compliance, extensibility and proven stability – Thanks to its origins around research from UC Berkeley
  • MySQL focuses more on no fuss simplicity, speed and ease of use – Optimized for high traffic web apps early on
  • Both databases now scale out decently via replication but PostgreSQL offers more enterprise-grade configuration options
  • PostgreSQL supports full SQL and provides more advanced data types – Better for analytics or flexibility needs
  • MySQL only complies partly with SQL standards but nails core functionality – Great for web/mobile apps

So while PostgreSQL excels on paper technically, MySQL remains simple and fast at web-scale for many modern apps.

Recommendations: Picking the Right Database

So when should you pick PostgreSQL vs MySQL for YOUR next project?

While specific requirements might make the ideal choice more obvious in some cases, in general I would recommend:

Consider PostgreSQL For:

  • Advanced analytics or business intelligence needs
  • Situations demanding maximum data integrity
  • Applications requiring flexibility in data types and structures
  • Long term enterprise deployments where standards support aids longevity

Choose MySQL When:

  • Leveraging existing expertise on your team
  • Simple speed rather than advanced functionality is key priority
  • Seeking proven database for consumer web or mobile apps
  • You want a hassle-free database managed as-a-service without heavy lifting

As you evaluate databases for upcoming applications, keep these guidelines in mind while still listing your unique technical and business priorities.

If meeting advanced SQL functionality and data modeling needs – PostgreSQL should edge out MySQL. However, MySQL remains a nimble choice when web-scale simplicity and familiarity drives decisions.

Explore Further with Hands-On Demos

With open source options like PostgreSQL and MySQL, you can easily spin up free installations and run test queries to experience capabilities firsthand.

Download PostgreSQL and work through tutorial data models to get exposure. MySQL also provides a hands-on sandbox.

Additionally, explore managed cloud database options like Amazon RDS, Google Cloud SQL or Azure Database for PostgreSQL where you can launch instances and only pay per hour of active usage while testing.

First-hand experience trying SQL functionality, data models and queries will complement the architectural knowledge from this guide – helping cement the right platform pick.

Closing Thoughts

I hope mapping out the history, technical architecture, sample real-world usage and recommendations around PostgreSQL vs MySQL proved helpful.

If you have any other questions, don’t hesitate to [reach out to me directly](). I’m always happy to dig deeper into application data architecture discussions with readers.

Regardless of which database you choose for your next project, remember that priorities will evolve over time as software brings new requirements. So keep evaluating capabilities against your roadmap rather than locking into a platform based on initial needs alone.

Here’s to building world class applications powered by databases like PostgreSQL or MySQL that can store, scale and deliver critical application data with reliability over the long haul.

Tags: