PostgreSQL vs MySQL: Performance Comparison

When it comes to relational databases, two names dominate the conversation: PostgreSQL and MySQL. Both are powerful, open-source RDBMS solutions that have powered millions of applications worldwide. But when performance is your primary concern, which one should you choose?

In this comprehensive guide, we'll dive deep into the performance characteristics of both databases, examining query execution, indexing strategies, concurrency handling, and real-world benchmarks to help you make an informed decision for your next project.

Architecture & Core Differences

Before diving into benchmarks, it's crucial to understand the fundamental architectural differences between PostgreSQL and MySQL. These differences directly impact how each database handles queries, manages data, and scales under load.

PostgreSQL: The "Enterprise" Approach

PostgreSQL follows a process-per-connection model where each client connection spawns a new server process. This provides excellent isolation but can be resource-intensive with many concurrent connections. Key architectural features include:

MySQL: The "Flexible" Approach

MySQL uses a thread-per-connection model which is generally more memory-efficient for handling many connections. Its pluggable storage engine architecture allows choosing the right engine for each table:

Query Performance Comparison

Query performance is where you'll see the most significant differences between PostgreSQL and MySQL. Let's examine various query types and how each database handles them.

Simple SELECT Queries

For simple SELECT queries with proper indexing, both databases perform similarly. MySQL has a slight edge in raw throughput for basic queries:

-- Simple SELECT with index lookup
SELECT id, name, email, created_at
FROM users
WHERE id = 12345;

-- Both databases execute this in ~0.1ms with proper indexing

Complex JOINs and Aggregations

This is where PostgreSQL's advanced query planner shines. For complex queries involving multiple JOINs, subqueries, and window functions, PostgreSQL often produces better execution plans:

-- Complex query with multiple JOINs and window functions
SELECT 
    o.id AS order_id,
    c.name AS customer_name,
    p.name AS product_name,
    oi.quantity,
    oi.price,
    SUM(oi.quantity * oi.price) OVER (PARTITION BY c.id) AS customer_total,
    RANK() OVER (PARTITION BY p.category_id ORDER BY oi.quantity DESC) AS category_rank
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories cat ON p.category_id = cat.id
WHERE o.created_at >= '2026-01-01'
    AND c.status = 'active'
ORDER BY customer_total DESC
LIMIT 100;

-- PostgreSQL: ~15ms average
-- MySQL: ~22ms average (window functions improved in MySQL 8.0)

Window Functions & CTEs

PostgreSQL has historically had superior support for advanced SQL features. While MySQL 8.0 added window functions and CTEs, PostgreSQL's implementation is more mature and performant:

-- PostgreSQL: Recursive CTE with optimization
WITH RECURSIVE category_tree AS (
    -- Base case
    SELECT id, name, parent_id, 1 AS level,
           ARRAY[id] AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Recursive case
    SELECT c.id, c.name, c.parent_id, ct.level + 1,
           ct.path || c.id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE ct.level < 10  -- Prevent infinite recursion
)
SELECT * FROM category_tree
ORDER BY path;

-- PostgreSQL optimizes CTEs with materialization hints:
-- WITH category_tree AS MATERIALIZED (...) 
-- WITH category_tree AS NOT MATERIALIZED (...)

-- MySQL 8.0+: Recursive CTE
WITH RECURSIVE category_tree AS (
    -- Base case
    SELECT id, name, parent_id, 1 AS level,
           CAST(id AS CHAR(200)) AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Recursive case
    SELECT c.id, c.name, c.parent_id, ct.level + 1,
           CONCAT(ct.path, ',', c.id)
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE ct.level < 10
)
SELECT * FROM category_tree
ORDER BY path;

-- MySQL always materializes CTEs
-- No optimization hints available

Indexing Strategies & Performance

Indexing is crucial for database performance. Both databases offer B-tree indexes as their default, but their additional indexing capabilities differ significantly:

Practical Indexing Examples

-- Create a GIN index for JSON containment queries
CREATE INDEX idx_users_metadata ON users USING GIN (metadata jsonb_path_ops);

-- Blazing fast JSON queries
SELECT * FROM users 
WHERE metadata @> '{"subscription": "premium", "verified": true}';

-- Performance: ~0.5ms for millions of rows with GIN index
-- Without index: ~2000ms (full table scan)

-- Index only active users (saves space and improves performance)
CREATE INDEX idx_active_users ON users (email) 
WHERE status = 'active' AND deleted_at IS NULL;

-- Partial index for recent orders only
CREATE INDEX idx_recent_orders ON orders (customer_id, created_at)
WHERE created_at > '2026-01-01';

-- MySQL doesn't support partial indexes natively
-- Workaround: Add a generated column and index that

-- BRIN index: tiny size, great for naturally ordered data
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- Index size comparison for 100M rows:
-- B-tree: ~2.1 GB
-- BRIN:   ~500 KB (4000x smaller!)

-- Perfect for time-series data, append-only tables
SELECT * FROM logs 
WHERE created_at BETWEEN '2026-03-01' AND '2026-03-12';

Concurrency & Transaction Performance

How databases handle concurrent access and transactions significantly impacts application performance. Let's compare the concurrency models:

-- postgresql.conf
autovacuum_vacuum_scale_factor = 0.05  -- Default: 0.2
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_delay = 2ms

Benchmark: Concurrent Transactions

Write Performance & Bulk Operations

Write performance differs significantly between the two databases due to their underlying storage architectures:

Single-Row INSERT Performance

Bulk INSERT Performance

-- PostgreSQL's COPY is extremely fast for bulk loading
COPY users (name, email, created_at) 
FROM '/path/to/users.csv' 
WITH (FORMAT csv, HEADER true);

-- Or use COPY FROM STDIN for programmatic loading
COPY users FROM STDIN WITH (FORMAT csv);

-- Performance: ~500,000 rows/second

-- MySQL's LOAD DATA INFILE
LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(name, email, created_at);

-- Performance: ~400,000 rows/second

JSON & Advanced Data Types

Modern applications often require flexible data storage. Both databases support JSON, but with different capabilities:

-- PostgreSQL JSONB with indexing
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    attributes JSONB
);

-- GIN index for fast containment queries
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- Rich query operators
SELECT * FROM products 
WHERE attributes @> '{"color": "red"}';          -- Contains
SELECT * FROM products 
WHERE attributes ? 'warranty';                    -- Has key
SELECT * FROM products 
WHERE attributes ->> 'price' > '100';            -- Extract and compare
SELECT * FROM products 
WHERE attributes @? '$.specs.cpu ? (@ like_regex "Intel")';  -- JSON path

-- Update nested values
UPDATE products 
SET attributes = jsonb_set(attributes, '{specs,memory}', '"32GB"')
WHERE id = 1;

-- MySQL JSON
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    attributes JSON
);

-- Index via generated column (workaround)
ALTER TABLE products 
ADD COLUMN color VARCHAR(50) GENERATED ALWAYS AS (attributes->>'$.color'),
ADD INDEX idx_color (color);

-- Function-based queries
SELECT * FROM products 
WHERE JSON_CONTAINS(attributes, '"red"', '$.color');
SELECT * FROM products 
WHERE JSON_EXTRACT(attributes, '$.price') > 100;
SELECT * FROM products 
WHERE JSON_SEARCH(attributes, 'one', 'Intel', NULL, '$.specs.cpu') IS NOT NULL;

-- Update nested values
UPDATE products 
SET attributes = JSON_SET(attributes, '$.specs.memory', '32GB')
WHERE id = 1;

Scaling & Replication

Both databases offer various scaling strategies, each with different trade-offs:

When to Choose Each Database

Based on our performance analysis, here are clear recommendations for when to choose each database:

Performance Optimization Checklist

Regardless of which database you choose, these optimizations will improve performance:

Final Verdict

After extensive benchmarking and analysis, here's the summary:

Bottom line: PostgreSQL excels at complex workloads with advanced SQL features, while MySQL shines for simpler, high-throughput applications. Both are excellent choices—the "right" database depends on your specific requirements, team expertise, and growth plans.

"The best database is the one your team knows how to operate well. Performance differences often matter less than operational expertise."

Mayur Dabhi

Full Stack Developer with 5+ years of experience in Laravel, React, and database optimization. Passionate about building high-performance web applications.