Why PostgreSQL Beats MySQL in High‑Performance Scenarios

Preface

In high‑performance scenarios I recommend PostgreSQL over MySQL and explain why.

1. Architecture Design

1.1 MySQL Architecture Characteristics

MySQL uses a “one connection per thread” model, which can cause severe performance problems when many connections are open.

Example of a MySQL connection pool configuration:

// MySQL连接池配置示例
@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); // 连接数有限
        config.setConnectionTimeout(30000);
        return new HikariDataSource(config);
    }
}

Problem analysis:

Each connection requires a dedicated thread.

High context‑switch overhead.

Memory usage grows linearly with connection count.

1.2 PostgreSQL Architecture Advantages

PostgreSQL uses a process‑pool and multi‑process model with a more advanced connection handling mechanism.

Example of a PostgreSQL connection pool configuration:

// PostgreSQL连接池配置
@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); // 支持更多连接
        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

2.1 MySQL Index Limitations

MySQL mainly provides B‑Tree indexes, which are limited for complex queries.

-- MySQL中，以下查询无法有效使用索引
SELECT * FROM products 
WHERE tags LIKE '%electronics%' 
AND price BETWEEN 100 AND 500 
AND JSON_EXTRACT(attributes, '$.color') = 'red';

MySQL index limitations:

No support for arbitrary field queries in multi‑column indexes.

Weak full‑text search.

Poor JSON query performance.

2.2 PostgreSQL Multi‑Index Strategies

PostgreSQL offers several index types to suit different query scenarios.

-- 1. B-Tree索引（基础索引）
CREATE INDEX idx_account_time ON transaction_records(account_id, transaction_time);

-- 2. GIN索引（用于JSON、数组等复杂数据类型）
CREATE INDEX idx_product_tags ON products USING GIN(tags);
CREATE INDEX idx_product_attributes ON products USING GIN(attributes);

-- 3. BRIN索引（用于时间序列数据）
CREATE INDEX idx_transaction_time_brin ON transaction_records USING BRIN(transaction_time);

-- 4. 部分索引（只索引部分数据）
CREATE INDEX idx_active_users ON users(user_id) WHERE status = 'ACTIVE';

3. Complex Query Optimization

3.1 MySQL Query Optimization Limits

-- MySQL中，这个复杂查询需要多次子查询，性能很差
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 provides stronger query optimization capabilities, such as CTEs.

-- 使用CTE（公共表表达式）优化复杂查询
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 limited support for complex data types.

-- MySQL中的JSON操作较为繁琐
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 arrays, JSONB, geometric types, and more.

-- 创建包含复杂数据类型的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    tags TEXT[], -- 数组类型
    dimensions JSONB, -- 二进制JSON
    location POINT, -- 几何类型
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 高效的复杂查询
SELECT
    id,
    name,
    dimensions->>'length' as length,
    dimensions->>'width' as width
FROM products
WHERE tags && ARRAY['electronics'] -- 数组包含查询
AND dimensions @> '{"category": "electronics"}' -- JSON包含查询
AND circle(location, 1000) @> point(40.7128, -74.0060); -- 几何查询

5. Transaction Processing and Concurrency Control

5.1 MySQL MVCC Implementation

InnoDB uses MVCC but can suffer lock contention under high‑write workloads.

// Java中的事务示例
@Service
@Transactional
public class OrderService {
    public void createOrder(Order order) {
        // 高并发下可能出现锁等待
        orderRepository.save(order);
        inventoryRepository.decrementStock(order.getProductId(), order.getQuantity());
        paymentRepository.createPayment(order.getOrderId(), order.getAmount());
    }
}

5.2 PostgreSQL Advanced Concurrency Features

PostgreSQL’s MVCC is more advanced and supports advisory locks, SKIP LOCKED, and finer‑grained control.

BEGIN;
-- 使用SKIP LOCKED避免锁等待
SELECT * FROM orders
WHERE status = 'PENDING'
FOR UPDATE SKIP LOCKED
LIMIT 10;
COMMIT;

Concurrency advantages:

Better lock management mechanism.

Support for advisory locks.

Finer‑grained transaction control.

6. Practical Performance Comparison

Benchmark shows MySQL around 5,000 TPS while PostgreSQL reaches about 12,000 TPS, a 140 % improvement.

7. Migration Considerations and Compatibility

Practical advice for migrating from MySQL to PostgreSQL, including compatibility mode and migration tools.

// 兼容性配置示例
@Configuration
public class MigrationConfig {
    // 使用兼容模式
    @Bean
    public PostgreSQLDialect postgreSQLDialect() {
        return new PostgreSQLDialect();
    }

    // 数据迁移工具配置
    @Bean
    public Flyway flyway() {
        return Flyway.configure()
                .dataSource(dataSource())
                .locations("classpath:db/migration/postgresql")
                .load();
    }
}

Run both systems in parallel and migrate gradually.

Use compatibility tools.

Migrate in phases: read‑only first, then write.

Conclusion

For high‑concurrency, complex queries, advanced data types, and strict consistency requirements, PostgreSQL is the preferred choice; MySQL remains suitable for simple CRUD, read‑heavy workloads, rapid prototyping, or when the ecosystem depends heavily on MySQL.