PostgreSQL 16 vs MySQL 8.3 - Production Workload Performance Comparison

Objective

Compare PostgreSQL 16.1 and MySQL 8.3.0 across representative production workloads including OLTP transactions, analytical queries, JSON operations, full-text search, and concurrent connection handling.

Test Setup

Hardware & Environment

• Server: AWS db.r6i.2xlarge (8 vCPU, 64GB RAM)
• Storage: 1TB gp3 SSD (16,000 IOPS, 1,000 MB/s throughput)
• OS: Ubuntu 22.04 LTS (for self-hosted comparison)
• Network: 10 Gbps

Database Versions & Configuration

• PostgreSQL: 16.1 with default settings + tuned shared_buffers (16GB), work_mem (256MB)
• MySQL: 8.3.0 InnoDB with default settings + tuned innodb_buffer_pool_size (40GB)
• Both configured with equivalent memory allocation

Test Dataset

• Users Table: 10 million records
• Orders Table: 50 million records with foreign keys
• Products Table: 1 million records with JSONB/JSON metadata
• Logs Table: 100 million timestamped entries
• Total Database Size: ~85GB per database

Test Methodology

Using sysbench, pgbench, and custom workloads:

• Each test run for 10 minutes after 2-minute warmup
• Results are median of 5 runs
• Cache cleared between runs
• Connection pooling (pgBouncer for PG, ProxySQL for MySQL)

OLTP Performance

Simple Point SELECT

SELECT * FROM users WHERE id = ?;

Results (1,000 concurrent connections):

Winner: PostgreSQL (2.5% faster)

INSERT Performance

INSERT INTO orders (user_id, product_id, amount, created_at)
VALUES (?, ?, ?, NOW());

Results (500 concurrent connections):

Winner: MySQL (7% faster for writes)

UPDATE Performance

UPDATE users SET last_login = NOW() WHERE id = ?;

Results:

Winner: PostgreSQL (6.8% faster, less lock contention)

Transaction Throughput (sysbench oltp_read_write)

Mixed workload: 80% reads, 20% writes with transactions

Winner: MySQL (7.7% higher TPS for OLTP)

Analytical Queries

Complex JOIN with Aggregation

SELECT
    u.country,
    COUNT(*) as order_count,
    AVG(o.amount) as avg_amount,
    SUM(o.amount) as total_revenue
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
GROUP BY u.country
ORDER BY total_revenue DESC
LIMIT 100;

Results:

Winner: PostgreSQL (72% faster)

Window Functions

SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn,
    AVG(amount) OVER (PARTITION BY user_id) as user_avg
FROM orders
WHERE created_at >= '2024-01-01';

Results:

Winner: PostgreSQL (38% faster window functions)

Full Table Scan

SELECT COUNT(*), AVG(amount) FROM orders;

Winner: PostgreSQL (30% faster with parallelization)

JSON Operations

JSON Query Performance

-- PostgreSQL
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- MySQL
SELECT * FROM products
WHERE JSON_CONTAINS(metadata, '{"category": "electronics"}');

Results (1M records with JSON):

Winner: PostgreSQL (112% faster with JSONB and GIN indexes)

JSON Aggregation

SELECT
    metadata->>'category' as category,
    COUNT(*) as count
FROM products
GROUP BY category;

Winner: PostgreSQL (71% faster)

Full-Text Search

Simple Text Search

PostgreSQL (ts_vector):

SELECT * FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('laptop & gaming');

MySQL (FULLTEXT):

SELECT * FROM products
WHERE MATCH(description) AGAINST('+laptop +gaming' IN BOOLEAN MODE);

Results (1M product descriptions):

Winner: PostgreSQL (49% faster, better features)

Concurrent Connections

Maximum Simultaneous Connections

Note: Both require connection pooling for > 500 connections in production

Connection Overhead

Time to establish 1,000 new connections:

Winner: PostgreSQL (40% faster with pooling)

Replication & High Availability

Streaming Replication Lag

Write 100,000 transactions, measure replica lag:

Winner: PostgreSQL (51% less lag)

Logical Replication Throughput

Winner: PostgreSQL (33% faster logical replication)

Storage & Compression

Table Size (same data)

Winner: PostgreSQL (12% smaller database size)

VACUUM vs OPTIMIZE

Maintenance time for 50M row table:

Winner: PostgreSQL (50% faster, non-blocking)

Feature Comparison

Cost Analysis (AWS RDS 1-year reserved)

Note: Pricing identical for equivalent AWS RDS instances

Interpretation

Key Findings

1. OLTP Workloads: MySQL edges out PostgreSQL by ~7% for pure write-heavy transactional workloads
2. Analytics: PostgreSQL dominates analytical queries (30-72% faster) thanks to parallelization
3. JSON Operations: PostgreSQL's JSONB is 112% faster than MySQL's JSON with better indexing
4. Full-Text Search: PostgreSQL's built-in FTS is 49% faster with superior language support
5. Connection Handling: PostgreSQL handles concurrent connections more efficiently
6. Storage: PostgreSQL databases are ~12% smaller for the same data

When to Choose PostgreSQL

✅ Complex analytical queries and reporting ✅ JSONB/document store requirements ✅ Advanced full-text search needs ✅ Geospatial applications (PostGIS) ✅ Data warehousing and OLAP ✅ Logical replication / CDC pipelines ✅ Advanced SQL features (CTEs, window functions)

When to Choose MySQL

✅ Pure OLTP workloads with heavy writes ✅ Existing MySQL expertise in team ✅ Replication to many read replicas ✅ Simpler deployment requirements ✅ WordPress, Drupal, or other PHP CMS ✅ Maximum compatibility with legacy apps

Caveats

• Performance varies significantly with workload and tuning
• MySQL's new features (8.0+) narrow the gap considerably
• PostgreSQL requires more expertise to tune optimally
• Both databases are production-ready for most applications
• Real-world performance depends heavily on schema design

Recommendations

For new projects in 2025:

• Choose PostgreSQL for analytics-heavy applications, JSON data, or microservices
• Choose MySQL for pure OLTP, simple CRUD apps, or if team has MySQL expertise

For existing projects:

• Benchmark YOUR specific workload before migrating
• Consider PostgreSQL for read-heavy analytical workloads
• Consider MySQL if you're already invested in the ecosystem

Reproducibility

Benchmark scripts and data generation available at: https://github.com/staticblock/postgres-vs-mysql-2025

Running the Benchmarks

# Clone repository
git clone https://github.com/staticblock/postgres-vs-mysql-2025
cd postgres-vs-mysql-2025

# Generate test data (requires Docker)
./scripts/generate-data.sh

# Run PostgreSQL benchmarks
./scripts/bench-postgres.sh

# Run MySQL benchmarks
./scripts/bench-mysql.sh

# Compare results
python scripts/compare.py

Last Updated: January 24, 2025 Database Versions: PostgreSQL 16.1, MySQL 8.3.0