PostgreSQL vs MySQL in 2024: A Practical Comparison

postgresqlmysqldatabasescomparison

The PostgreSQL vs MySQL debate has been going on for decades. Here’s my take after using both in production.

The Short Answer

Choose PostgreSQL if: You need advanced features, complex queries, or JSON handling.

Choose MySQL if: You’re building a read-heavy web app and want simplicity.

Now let’s dig deeper.

Data Types

PostgreSQL wins on richness:

-- PostgreSQL: Native array type
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);
SELECT * FROM posts WHERE 'sql' = ANY(tags);

-- PostgreSQL: JSONB with indexing
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);
CREATE INDEX idx_events_data ON events USING GIN(data);
SELECT * FROM events WHERE data @> '{"type": "click"}';

MySQL added JSON support, but it’s not as mature. No GIN indexes, fewer operators.

Query Capabilities

PostgreSQL’s query planner is more sophisticated:

-- Common Table Expressions (CTEs) - both support these now
WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id;

-- Window functions - PostgreSQL had these for years before MySQL
SELECT 
    name,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

-- Lateral joins - PostgreSQL only
SELECT u.*, recent.*
FROM users u
LEFT JOIN LATERAL (
    SELECT * FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 5
) recent ON true;

ACID Compliance

Both are ACID-compliant with the right configuration.

PostgreSQL: ACID by default.

MySQL: Depends on storage engine. InnoDB is ACID. MyISAM is not.

-- MySQL: Make sure you're using InnoDB
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB;

Replication

MySQL has simpler replication setup:

  • Built-in binary log replication
  • Easy primary-replica configuration
  • Group Replication for HA

PostgreSQL offers more flexibility:

  • Streaming replication
  • Logical replication (replicate specific tables)
  • More complex but more powerful

Performance Characteristics

Reads

MySQL with InnoDB often edges out PostgreSQL for simple reads. Its query cache (though deprecated in 8.0) and optimizer are tuned for typical web workloads.

Writes

PostgreSQL handles concurrent writes better with its MVCC implementation. Less lock contention on heavy write workloads.

Complex Queries

PostgreSQL’s planner shines with complex joins, subqueries, and CTEs. If your queries are sophisticated, PostgreSQL usually wins.

JSON Handling

PostgreSQL’s JSONB is a killer feature:

-- Efficient storage and querying
CREATE TABLE api_logs (
    id SERIAL PRIMARY KEY,
    request JSONB,
    response JSONB
);

-- Index specific JSON paths
CREATE INDEX idx_api_logs_method ON api_logs ((request->>'method'));

-- Powerful operators
SELECT * FROM api_logs WHERE request @> '{"method": "POST", "path": "/api/users"}';

MySQL’s JSON is catching up but lacks:

  • Partial updates (fixed in 8.0)
  • GIN indexing
  • As many operators

Extensions & Ecosystem

PostgreSQL’s extension system is unmatched:

  • PostGIS: Geospatial queries
  • pg_trgm: Fuzzy text search
  • TimescaleDB: Time-series data
  • Citus: Distributed PostgreSQL

MySQL relies more on external tools and forks (MariaDB, Percona).

Cloud Support

Both are well-supported:

  • PostgreSQL: AWS RDS/Aurora, Google Cloud SQL, Azure, Supabase, Neon
  • MySQL: AWS RDS/Aurora, Google Cloud SQL, Azure, PlanetScale

Aurora PostgreSQL and Aurora MySQL are both excellent managed options.

When I Choose Each

PostgreSQL:

  • Complex domain logic
  • Heavy JSON usage
  • Need for advanced SQL features
  • Geospatial requirements
  • Data integrity is critical

MySQL:

  • Simple CRUD applications
  • WordPress/Drupal (required)
  • Team already knows MySQL
  • Read-heavy, simple queries
  • Legacy system integration

The Real Answer

Both are production-ready, battle-tested databases. You probably can’t go wrong with either for most applications.

If you’re starting fresh and don’t have strong constraints, I lean PostgreSQL. The feature gap keeps widening in its favor.

But if MySQL works for your use case and your team knows it well? Ship it. Database choice matters less than shipping software.