PostgreSQL vs MySQL: Performance Comparison

You start a new project, someone asks "Postgres or MySQL?", and the discussion turns into a pile of benchmark screenshots that don't match your workload. One person points at a chart where MySQL serves more simple reads per second. Someone else points at a reporting query that takes seconds on MySQL and milliseconds on PostgreSQL. Both can be true at once.

This post goes through where the two actually differ in performance: architecture, query planning, indexes, concurrency, writes, JSON and scaling. I'll also point out the operational gotchas that matter more than raw speed once you're in production.

Architecture & Core Differences

Most of the benchmark differences you'll see trace back to a few design decisions: how connections are handled, how old row versions are stored, and how much effort the planner puts into a query. Those are worth knowing before you look at any numbers.

PostgreSQL: one process per connection

Every client connection in PostgreSQL gets its own server process. That gives you good isolation (one crashing backend doesn't take the others' memory with it), but each connection costs real memory and a fork. Hundreds of idle connections from a PHP app hurt. The other parts that shape its performance:

MySQL: one thread per connection

MySQL handles each connection with a thread inside a single server process. Threads are cheaper than processes, so MySQL usually copes better with lots of connections out of the box. It also has pluggable storage engines, though today you'll use InnoDB for nearly everything:

Query Performance Comparison

Query type is where the gap shows up. A quick note on the bars below: they show the typical relative pattern you'll find in public pgbench and sysbench comparisons, not a lab run of mine. Exact numbers swing a lot with hardware, config and data shape, so run your own queries before trusting anyone's chart.

Simple SELECT Queries

For indexed point lookups, the two are close. MySQL often edges ahead on raw throughput for this kind of query:

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

-- Both answer this in well under a millisecond; the network round trip usually costs more

Complex JOINs and Aggregations

Here the planner starts to matter. Once you have several JOINs, subqueries and window functions in one statement, PostgreSQL tends to find a better plan, and on reporting queries that can be the difference between "fine" and "time out":

-- 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;

-- Window functions need MySQL 8.0+; on 5.7 this query won't run at all
-- Compare real timings on your data with EXPLAIN ANALYZE in both

Window Functions & CTEs

PostgreSQL has had window functions and recursive CTEs for well over a decade. MySQL only got them in 8.0, and it still gives you less control over how CTEs are executed:

-- 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;

-- For non-recursive CTEs (PostgreSQL 12+) you can choose:
-- 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 materializes recursive CTEs
-- Non-recursive ones can be merged; the MERGE / NO_MERGE hints control it

Indexing Strategies & Performance

Both default to B-tree indexes, and for most tables that's all you need. The difference is what else is available when a B-tree doesn't fit the data:

Practical Indexing Examples

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

-- Containment query that can use the index above
SELECT * FROM users
WHERE metadata @> '{"subscription": "premium", "verified": true}';

-- With the index: a bitmap index scan, typically milliseconds
-- Without it: a sequential scan, which grows with the table
-- Note: jsonb_path_ops only supports @> (and jsonpath), not the ? key operators

-- Index only active users (smaller index, cheaper writes for the rest)
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);

-- Rough index size for 100M rows:
-- B-tree: a few GB
-- BRIN:   often well under a few MB

-- Works when rows are inserted in time order (logs, events, append-only tables)
SELECT * FROM logs 
WHERE created_at BETWEEN '2026-03-01' AND '2026-03-12';

Concurrency & Transaction Performance

Both use MVCC, but they keep old row versions in different places, and that changes what goes wrong under heavy write load:

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

Concurrent Transactions

The chart shows the usual shape rather than measured numbers: throughput climbs with connections, peaks, then falls as the server spends its time switching between them. Where the peak sits depends mostly on your CPU count. The practical lesson for PostgreSQL is to put PgBouncer in front of it before you need it. Without a pooler, the first sign of trouble is often a burst of FATAL: sorry, too many clients already during a traffic spike (MySQL's version is ERROR 1040: Too many connections). If you use PgBouncer in transaction mode with Laravel or any PDO app, keep in mind that session state like server-side prepared statements and SET commands doesn't survive between transactions.

Write Performance & Bulk Operations

For single-row writes, the two are close. InnoDB writes into a clustered primary key index; PostgreSQL appends to a heap and updates each index separately, which is why tables with many indexes cost more to update in Postgres.

Single-Row INSERT Performance

Bulk INSERT Performance

-- COPY is far faster than row-by-row INSERTs 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);

-- COPY FROM a file reads the path on the database server, not your laptop.
-- From a client, use psql's \copy instead

-- 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);

-- Often fails with ERROR 1290 (--secure-file-priv) on default installs.
-- Put the file in the secure_file_priv directory, or use LOAD DATA LOCAL INFILE

JSON & Advanced Data Types

If you store settings, product attributes or webhook payloads as JSON and then filter on them, this is the section that matters. Both support JSON. They differ a lot in how you index it:

-- 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';            -- Careful: this compares TEXT
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;

One gotcha in the PostgreSQL tab: ->> returns text, so attributes ->> 'price' > '100' is a string comparison and '99' counts as greater than '100'. Cast it in real code: (attributes ->> 'price')::numeric > 100. It won't error, it just returns the wrong rows, so it tends to slip through review.

Scaling & Replication

Both scale reads with replicas without much trouble. Writes and high availability are where the tooling differs:

When to Choose Each Database

Putting the sections above together, this is roughly how I'd split it:

Performance Optimization Checklist

Whichever one you pick, these give you more than switching databases would:

When I'd pick which (and when I wouldn't)

Here's the rough scorecard, before the caveats:

For a new app with real business logic, reporting, or JSON you'll filter on, I'd start with PostgreSQL. Transactional DDL alone is worth a lot: if a migration fails halfway in PostgreSQL, the whole thing rolls back. In MySQL, every ALTER TABLE commits on its own, so a half-applied Laravel migration leaves you fixing the schema by hand.

I wouldn't pick PostgreSQL when the app is WordPress or another tool built around MySQL, when you're on shared hosting that only offers MySQL, or when nobody on the team is going to watch autovacuum and connection counts. A badly run Postgres is slower than a well-run MySQL, and bloat or "too many clients" errors don't show up until you have real traffic.

I wouldn't pick MySQL for analytics-heavy products, for anything that needs PostGIS, or when the schema leans on partial indexes, arrays or ad hoc JSON queries. You can work around each one, but the workarounds pile up.

If you already run one of them well, the performance gap is rarely a good enough reason to migrate. Find the slow queries first. Most of the time the fix is an index or a pooler, not a new database.

Mayur Dabhi

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