Why PostgreSQL Beats MySQL in High‑Performance Scenarios

Introduction

Today we discuss a classic technology selection question: why I recommend PostgreSQL over MySQL in high‑performance scenarios.

1. Architecture Design

1.1 MySQL Architecture Characteristics

MySQL uses a “one connection per thread” model, which can cause severe performance problems when the number of connections is large.

// MySQL connection pool configuration example
@Configuration
public class MySQLConfig {
    @Bean
    public DataSource mysqlDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(100); // connection limit
        config.setConnectionTimeout(30000);
        return new HikariDataSource(config);
    }
}

Problem Analysis:

Each connection requires a separate thread.

Thread context‑switch overhead is high.

Memory usage grows linearly with connection count.

1.2 PostgreSQL Architecture Advantages

PostgreSQL adopts a “process pool + multi‑process” architecture with a more advanced connection handling mechanism.

// PostgreSQL connection pool configuration
@Configuration
public class PostgreSQLConfig {
    @Bean
    public DataSource postgresqlDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/test");
        config.setUsername("postgres");
        config.setPassword("password");
        config.setMaximumPoolSize(200); // supports more connections
        config.setConnectionTimeout(30000);
        return new HikariDataSource(config);
    }
}

Core Advantages:

Process‑pool model handles concurrent connections more efficiently.

Supports a larger number of concurrent connections.

Better memory management and resource isolation.

2. Index Mechanism Comparison

Indexes are the core of database performance; let's compare the two.

2.1 MySQL Index Limitations

MySQL mainly uses B‑Tree indexes, which are limited in complex query scenarios.

-- MySQL query that cannot use index effectively
SELECT * FROM products
WHERE tags LIKE '%electronics%'
  AND price BETWEEN 100 AND 500
  AND JSON_EXTRACT(attributes, '$.color') = 'red';

MySQL Index Limitations:

Does not support arbitrary field queries in multi‑column indexes.

Full‑text search is weak.

JSON query performance is poor.

2.2 PostgreSQL Multi‑Index Strategies

PostgreSQL provides various index types for different query scenarios.

-- 1. B‑Tree index (basic)
CREATE INDEX idx_account_time ON transaction_records(account_id, transaction_time);

-- 2. GIN index (for JSON, arrays, etc.)
CREATE INDEX idx_product_tags ON products USING GIN(tags);
CREATE INDEX idx_product_attributes ON products USING GIN(attributes);

-- 3. BRIN index (for time‑series data)
CREATE INDEX idx_transaction_time_brin ON transaction_records USING BRIN(transaction_time);

-- 4. Partial index (indexes only part of the data)
CREATE INDEX idx_active_users ON users(user_id) WHERE status = 'ACTIVE';

Actual Performance Comparison Example:

-- PostgreSQL complex JSON query using multiple indexes
SELECT * FROM products
WHERE tags @> ARRAY['electronics']
  AND price BETWEEN 100 AND 500
  AND attributes @> '{"color": "red"}'::jsonb;

3. Complex Query Optimization Capability

3.1 MySQL Query Optimization Limitations

-- MySQL complex query with subqueries and poor performance
SELECT u.user_id, u.username,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) AS order_count,
       (SELECT SUM(amount) FROM payments p WHERE p.user_id = u.user_id) AS total_payment
FROM users u
WHERE u.create_time > '2023-01-01'
ORDER BY order_count DESC
LIMIT 100;

3.2 PostgreSQL Advanced Optimization Features

PostgreSQL offers stronger query optimization, including CTEs.

-- Using CTE (Common Table Expression) to optimize complex query
WITH user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
),
user_payments AS (
    SELECT user_id, SUM(amount) AS total_payment
    FROM payments
    GROUP BY user_id
)
SELECT u.user_id, u.username,
       COALESCE(uo.order_count, 0) AS order_count,
       COALESCE(up.total_payment, 0) AS total_payment
FROM users u
LEFT JOIN user_orders uo ON u.user_id = uo.user_id
LEFT JOIN user_payments up ON u.user_id = up.user_id
WHERE u.create_time > '2023-01-01'
ORDER BY uo.order_count DESC NULLS LAST
LIMIT 100;

Optimizer Advantages:

Supports more complex execution plans.

Better JOIN optimization.

Parallel query execution.

4. Data Types and Extensibility

4.1 MySQL Data Type Limitations

MySQL has weaker support for complex data types.

-- MySQL JSON operations are cumbersome
SELECT product_id,
       JSON_EXTRACT(properties, '$.dimensions.length') AS length,
       JSON_EXTRACT(properties, '$.dimensions.width') AS width
FROM products
WHERE JSON_EXTRACT(properties, '$.category') = 'electronics';

4.2 PostgreSQL Rich Data Types

PostgreSQL natively supports many complex data types.

-- Create table with complex data types
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    tags TEXT[],               -- array type
    dimensions JSONB,           -- binary JSON
    location POINT,            -- geometric type
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Efficient complex query
SELECT id, name,
       dimensions->>'length' AS length,
       dimensions->>'width' AS width
FROM products
WHERE tags && ARRAY['electronics']
  AND dimensions @> '{"category": "electronics"}'
  AND circle(location, 1000) @> point(40.7128, -74.0060);

5. Transaction Processing and Concurrency Control

5.1 MySQL MVCC Implementation

MySQL InnoDB uses MVCC but can suffer lock contention under high write concurrency.

// Java transaction example with potential lock wait
@Service
@Transactional
public void createOrder(Order order) {
    // high concurrency may cause lock waiting
    orderRepository.save(order);
    inventoryRepository.decrementStock(order.getProductId(), order.getQuantity());
    paymentRepository.createPayment(order.getOrderId(), order.getAmount());
}

5.2 PostgreSQL Advanced Concurrency Features

PostgreSQL uses a more advanced MVCC implementation with various isolation levels.

-- PostgreSQL SKIP LOCKED example
BEGIN;
SELECT * FROM orders
WHERE status = 'PENDING'
FOR UPDATE SKIP LOCKED
LIMIT 10;
COMMIT;

Concurrency Advantages:

Better lock management.

Supports advisory locks.

Finer‑grained transaction control.

6. Practical Performance Comparison

Benchmark shows PostgreSQL achieving about 12,000 TPS versus MySQL's 5,000 TPS, a 140% performance increase.

7. Migration Considerations and Compatibility

Guidelines for migrating from MySQL to PostgreSQL, including compatibility configuration and migration tools.

// Compatibility configuration example
@Configuration
public class MigrationConfig {
    // Use compatibility mode
    @Bean
    public PostgreSQLDialect postgreSQLDialect() {
        return new PostgreSQLDialect();
    }

    // Flyway migration tool configuration
    @Bean
    public Flyway flyway() {
        return Flyway.configure()
                .dataSource(dataSource())
                .locations("classpath:db/migration/postgresql")
                .load();
    }
}

Migration Strategy:

Run both systems in parallel and migrate gradually.

Use compatibility tools.

Phase migration: read‑only first, then write.

Conclusion

In high‑performance scenarios, PostgreSQL is generally recommended over MySQL due to superior architecture, indexing, query optimization, data type support, and concurrency handling.

When to choose PostgreSQL:

Complex queries and data analysis.

High performance requirements.

Complex data types (JSON, arrays, geometry).

Strict data consistency needs.

Extensibility requirements.

When to choose MySQL:

Simple CRUD operations.

Read‑heavy, write‑light workloads.

Rapid prototyping.

Heavy reliance on MySQL‑specific ecosystem.