#Comparison of new features between MySQL and PostgreSQL - analysis of the latest features in 2024

#Introduction

Database selection is one of the most important technical decisions in the early stages of a project. MySQL and PostgreSQL, as two top open source relational-databases, have evolved powerful new features in 2024. This article will use actual development scenarios as clues to compare their differences in JSON support, partition tables, parallel processing, security, etc., to help you quickly determine which one is more suitable for your project.

#1. Version development overview

#1.1 MySQL version evolution

MySQL ushered in an "awakening" update in version 8.0, which completed many modern SQL features that were missing before. Subsequent minor versions continue to optimize performance and stability.

• 8.0: Window functions, recursive CTE, role management, invisible index, histogram statistics, etc.
• 8.0.x subsequent versions: Continuous enhancements to JSON operations, security, and query optimizer

#1.2 PostgreSQL version evolution

PostgreSQL has always maintained a high-speed iteration rhythm, with one major version every year, and its functions are constantly stacked, especially in advanced-features and scalability.

• 10+: Declarative partitioning, logical replication, stronger parallel queries
• 14+: JSONB performance leap, instant compression, richer window functions
• 15-16: Comprehensive performance improvements, enhanced logical replication, and more enterprise-level security features

#2. JSON supports comparison

In modern applications, semi-structured data storage has become a necessity. Both databases support the JSON type, but there are significant differences in implementation methods and capabilities.

#2.1 MySQL JSON features

MySQL 8.x provides a basic JSON data type and a set of built-in functions to meet simple reading and writing needs. However, in terms of advanced queries and indexes, they need to be implemented indirectly with the help of "virtual columns".

-- 创建包含 JSON 列的表
CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    profile JSON
);

-- 插入 JSON 数据
INSERT INTO user_profiles VALUES (
    1, 
    JSON_OBJECT(
        'name', 'John Doe',
        'skills', JSON_ARRAY('Python', 'JavaScript')
    )
);

-- 基本查询：提取 JSON 字段
SELECT 
    id, 
    JSON_EXTRACT(profile, '$.name') AS name
FROM user_profiles
WHERE JSON_EXTRACT(profile, '$.age') > 25;

-- 索引实现：通过生成列构建 B-Tree 索引
ALTER TABLE user_profiles 
ADD COLUMN primary_skill VARCHAR(50) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.skills[0]'))) STORED;
CREATE INDEX idx_primary_skill ON user_profiles(primary_skill);

MySQL JSON summary:

• useJSON_EXTRACTFunction extracts values, the syntax is more traditional
• JSON internal field indexes are not natively supported, and columns must be generated virtually to save the country.
• Suitable for JSON storage and light query, complex JSON path operation is more cumbersome

#2.2 PostgreSQL JSON features

PostgreSQL providesjsonandjsonbTwo types. injsonbIt is a binary optimized version, supports efficient query and indexing, and is the recommended type.

-- 创建表并选择 jsonb 类型
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    profile_jsonb JSONB
);

-- 插入数据，使用 ::jsonb 进行类型转换
INSERT INTO user_profiles (profile_jsonb) VALUES (
    '{
        "name": "John Doe",
        "skills": ["Python", "JavaScript"]
    }'::jsonb
);

-- 丰富的操作符查询
SELECT 
    id,
    profile_jsonb->>'name' AS name,          -- 提取文本值
    profile_jsonb->'skills' AS skills       -- 提取 JSON 数组
FROM user_profiles
WHERE profile_jsonb @> '{"skills": ["Python"]}'::jsonb;  -- 包含查询

-- 原生 GIN 索引，直接加速 JSON 内部元素检索
CREATE INDEX idx_profile_gin ON user_profiles USING GIN (profile_jsonb);

PostgreSQL JSON summary:

• adopt->、->>、@>Wait for concise operators to perform path queries and inclusion queries.
• Native GIN index can be directly established on JSON internal fields, making query efficiency extremely high
• Suitable for scenarios that require frequent querying and updating of JSON internal data, especially complex document models

#2.3 JSON function comparison summary

#3. Window functions and CTE

#3.1 MySQL window function (8.0+)

MySQL 8.0 finally supports window functions and recursive CTE, eliminating the need for self-joins or stored procedures for many complex reports and hierarchical queries.

-- 窗口函数：部门内按薪水排名
SELECT 
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 递归CTE：组织架构层次查询
WITH RECURSIVE org_hierarchy AS (
    SELECT employee_id, manager_id, 1 AS level
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.employee_id, e.manager_id, oh.level + 1 
    FROM employees e 
    JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy;

#3.2 PostgreSQL window functions and CTE

PostgreSQL's window function support is more comprehensive, and CTE can also be specified asMATERIALIZEDorNOT MATERIALIZEDto control optimization behavior, which is very useful for performance tuning of complex queries.

-- 更丰富的窗口函数：分位数和分组编号
SELECT 
    employee_id,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS salary_percentile,
    NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;

-- 物化CTE：将子查询结果暂时固化，避免重复计算
WITH large_dataset AS MATERIALIZED (
    SELECT * FROM very_large_table WHERE condition = 'value'
)
SELECT * FROM large_dataset WHERE other_condition = 'other_value';

#4. Partition table function

When the amount of data in a single table reaches tens of millions or even billions, partition tables are a powerful tool for managing big data.

#4.1 MySQL partition (8.0+)

MySQL supports range, list, and hash partitioning viaPARTITION BY RANGEand other grammatical statements. The disadvantage is that partition maintenance operations (such as splitting and merging) are relatively cumbersome.

-- 按年份范围分区
CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

#4.2 PostgreSQL Partitioning (10+)

PostgreSQL uses declarative partitioning. The parent table defines the partitioning method, and the child table passesPARTITION OFBinding. This approach makes the boundaries between partitions clearer, and indexes can be automatically propagated from parent tables to child tables.

-- 声明式分区父表
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);

-- 创建具体分区子表
CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 在父表上创建索引，会自动应用到所有分区
CREATE INDEX idx_sales_amount ON sales (amount);

Partition comparison summary:

• MySQL partition syntax is simple and suitable for splitting large tables with fixed rules
• PostgreSQL declarative partitioning logic is clearer, supports automatic index propagation, and makes later maintenance more convenient

#5. Parallel processing capability

Parallel queries can significantly improve performance when processing large data set aggregation, sorting, and scanning.

• MySQL: Parallel processing capabilities are relatively conservative, mainly relying on the automatic judgment of the query optimizer, and there are few user-controllable parameters.
• PostgreSQL: has a mature parallel execution framework, supports parallel sequential scanning, parallel aggregation, parallel hash join, etc., and can bemax_parallel_workers_per_gatherFlexible control of other parameters.

-- PostgreSQL 并行查询执行计划查看
EXPLAIN (ANALYZE, BUFFERS) 
SELECT department_id, COUNT(*), AVG(salary)
FROM employees 
GROUP BY department_id;

In PostgreSQLEXPLAINIn the output, if you seeParallel Seq ScanorParallel HashWords such as , indicate that the query is effectively utilizing multi-core CPUs.

#6. Security Features

#6.1 MySQL security enhancement

MySQL 8.0 strengthens account management and password security policies to effectively prevent brute force cracking.

-- 安装密码强度校验组件
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = MEDIUM;

-- 创建带密码过期和登录失败锁定的账户
CREATE USER 'app_user'@'%' 
IDENTIFIED BY 'StrongPassword123!'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 3;

#6.2 PostgreSQL security features

One of the highlights of PostgreSQL is Row-Level Security (RLS), which can enforce fine-grained data isolation at the database level and is very practical in multi-tenant SaaS applications.

-- 开启行级安全
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

-- 创建策略：每个租户只能看到自己部门的数据
CREATE POLICY employee_isolation_policy ON employees
    FOR ALL
    TO app_user
    USING (department_id = current_setting('app.current_dept')::integer);

In addition, PostgreSQL also supports multiple strong authentication methods such as GSSAPI and SCRAM-SHA-256, making the security system more complete.

#7. Performance optimization features

#7.1 MySQL performance optimization

MySQL 8.0 provides two useful tools: Invisible Index to safely test index effectiveness, and Histogram Statistics to help the optimizer more accurately estimate query costs.

-- 创建不可见索引，优化器默认不使用
CREATE INDEX idx_invisible ON employees (hire_date) INVISIBLE;

-- 更新直方图统计信息
ANALYZE TABLE employees UPDATE HISTOGRAM ON salary, department_id;

#7.2 PostgreSQL performance optimization

PostgreSQL's indexing mechanism is richer:

-- 部分索引：只索引满足条件的行，节省空间并提高速度
CREATE INDEX idx_active_employees ON employees (hire_date) 
WHERE status = 'active';

-- 覆盖索引：包含额外列，避免回表查询
CREATE INDEX idx_covering ON employees (department_id, salary) 
INCLUDE (first_name, last_name);

#8. Extensibility and plug-in system

One of the real killer features of PostgreSQL is its plug-in ecosystem, which allows developers to add various capabilities such as GIS, full-text search, and time series data in the form of plug-ins. MySQL's expansion is relatively limited, and it usually requires relying on third-party tools or storage engines.

-- PostgreSQL 常用扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;   -- 查询性能统计
CREATE EXTENSION IF NOT EXISTS pg_trgm;              -- 三元组文本相似度
CREATE EXTENSION IF NOT EXISTS postgis;               -- 地理空间数据支持

#9. Comparison summary of new features

#10. Select suggestions

#Select MySQL scenario

• Classic web applications and CRUD projects, integrated with PHP and Python web frameworks for high development efficiency
• Rely on cloud service provider's hosting solution (such as Amazon RDS, Google Cloud SQL), low operation and maintenance cost
• The team already has a lot of MySQL operation and maintenance experience, or the project budget has limited investment in senior DBA
• Data relationships are relatively simple and do not require frequent use of complex SQL features

#Select PostgreSQL scenario

• Need to handle complex data relationships and rely on advanced SQL functions (window analysis, recursive queries, materialized views, etc.)
• Applications involving semi-structured data (JSONB) or geospatial data (PostGIS) require efficient dedicated queries
• Multi-tenant SaaS system with strong requirements for data isolation and row-level security
• Enterprise-level applications, pursuing extremely high data integrity and reliability guarantees
• Hope to use the rich expansion ecosystem to continuously evolve database capabilities

#Related tutorials

• 视图、函数和过程
• 索引优化策略
• Python接入MySQL与PostgreSQL数据库

#Summarize

In 2024, the gap between MySQL 8.x and PostgreSQL 14+ is gradually narrowing, but each still maintains its distinctive features:

• MySQL: simple, easy to use, mature ecosystem, stable performance in web applications and cloud hosting scenarios
• PostgreSQL: complete functions, strong scalability, obvious advantages in complex business, data analysis and fields with high data quality requirements

The final choice depends on your project needs, team technology stack, and future expansion plans. There is no absolute "best", only the database that best suits your current and future goals. I hope the comparison in this article can help you make a clearer judgment.