4. PostgreSQL实战：MVCC实现、并行查询、表继承与分区表、与MySQL的对比选型

好，咱们直接进入正题。PostgreSQL 在量化交易系统里，到底能扛多大事？

我个人的经验是：如果你做的是多品种、多策略、高并发的交易系统，PG 的 MVCC 和并行查询能力，能让你少掉很多头发。今天咱们就掰开揉碎，把 MVCC、并行查询、表继承、分区表这几个核心点讲透，最后再跟 MySQL 做个硬碰硬的对比。

4.1 MVCC 实现：为什么 PG 读不堵写？

MVCC，全称多版本并发控制。说白了，就是让读写操作互不干扰。

我记得刚入行时，用 MySQL 的 MyISAM 引擎，一写数据整个表就锁住了，查询全排队。后来换成 PG，才发现什么叫「读写自由」。

4.1.1 PG 的 MVCC 实现机制

PG 的 MVCC 靠的是「元组版本链」。每行数据有多个版本，每个版本都带着事务 ID。

• 插入：新数据生成一个新版本，标记当前事务 ID
• 更新：旧版本标记为「已删除」，新版本插入
• 删除：只标记删除，不立即物理删除

每个事务启动时，会拿到一个「快照」。这个快照里记录了当前所有活跃的事务 ID。查询时，只看到快照之前已提交的版本。

4.1.2 实战中的坑

我曾经在回测系统里，同时跑 20 个策略的写入和 50 个查询。一开始没注意，结果发现表越来越大——因为 PG 的 MVCC 会产生大量死元组。

-- 查看表膨胀情况
SELECT schemaname, tablename, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;

-- 手动 VACUUM
VACUUM ANALYZE your_table;

4.2 并行查询：让多核 CPU 真正干活

量化交易系统里，最耗时的操作是什么？

我告诉你，是历史数据回测和全市场扫描。单线程跑，一个品种 10 秒，100 个品种就是 1000 秒，根本没法用。

4.2.1 PG 的并行查询机制

PG 从 9.6 开始支持并行查询，到 14 版本已经非常成熟。它会自动把一个大查询拆成多个子任务，分给多个 worker 进程并行执行。

• 并行顺序扫描：大表全表扫描时，多个 worker 各扫一段
• 并行聚合：每个 worker 先算局部结果，再汇总
• 并行 JOIN：哈希 JOIN 时，多个 worker 同时构建哈希表

-- 查看并行查询配置
SHOW max_parallel_workers_per_gather;
SHOW parallel_tuple_cost;
SHOW parallel_setup_cost;

-- 强制使用并行查询（如果优化器没选）
SET parallel_tuple_cost = 0;
SET parallel_setup_cost = 0;

-- 一个典型的并行查询示例
EXPLAIN ANALYZE
SELECT symbol, AVG(close), MAX(high), MIN(low)
FROM tick_data
WHERE ts BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY symbol;

4.2.2 实战效果

我记得有一次做全市场 5000 只股票的日线数据聚合。单线程跑了 47 秒，开启 4 个并行 worker 后，直接降到 12 秒。你想想看，这差距有多大。

4.3 表继承与分区表：数据管理的两种思路

多品种交易系统，数据天然就是分层的。比如：

• 按品种分：股票、期货、期权
• 按时间分：2024年数据、2025年数据
• 按策略分：趋势策略、套利策略

PG 提供了两种方案：表继承和分区表。我两个都用过，各有适用场景。

4.3.1 表继承

表继承是 PG 的传统特性。你可以创建一个父表，然后创建多个子表继承它。查询父表时，会自动包含所有子表的数据。

-- 创建父表
CREATE TABLE trades (
    trade_id    BIGSERIAL,
    symbol      TEXT NOT NULL,
    price       NUMERIC(10,2),
    volume      INT,
    trade_time  TIMESTAMPTZ NOT NULL
);

-- 创建子表，继承父表
CREATE TABLE trades_stock () INHERITS (trades);
CREATE TABLE trades_futures () INHERITS (trades);

-- 给子表加约束
ALTER TABLE trades_stock ADD CHECK (symbol LIKE '6%');
ALTER TABLE trades_futures ADD CHECK (symbol LIKE 'F%');

-- 查询父表，自动包含子表
SELECT * FROM trades WHERE trade_time > '2024-01-01';

4.3.2 分区表（推荐方案）

从 PG 10 开始，原生分区表正式上线。到 PG 14，已经非常稳定。我强烈建议新项目直接用分区表，别再用表继承了。

-- 创建分区表（按时间分区）
CREATE TABLE trades_partitioned (
    trade_id    BIGSERIAL,
    symbol      TEXT NOT NULL,
    price       NUMERIC(10,2),
    volume      INT,
    trade_time  TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (trade_time);

-- 创建分区
CREATE TABLE trades_2024_q1 PARTITION OF trades_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE trades_2024_q2 PARTITION OF trades_partitioned
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- 分区裁剪：查询只扫描相关分区
EXPLAIN SELECT * FROM trades_partitioned
WHERE trade_time BETWEEN '2024-02-01' AND '2024-02-28';

4.3.3 实战选型建议

场景	推荐方案	原因
按时间归档历史数据	分区表（RANGE）	分区裁剪高效，归档方便
按品种隔离数据	分区表（LIST）	查询时自动过滤品种
需要全局唯一约束	分区表	表继承不支持跨子表唯一
旧系统迁移，已有继承结构	表继承（过渡方案）	兼容旧代码，逐步迁移

4.4 与 MySQL 的对比选型

这个问题，我几乎每次技术选型都会被问到。说实话，没有绝对的谁好谁坏，关键看场景。

4.4.1 核心差异对比

特性	PostgreSQL	MySQL
MVCC 实现	元组版本链，读不堵写	InnoDB 的 undo log，读不堵写
并行查询	原生支持，成熟稳定	8.0 开始支持，但功能有限
分区表	原生支持，功能完整	支持，但限制较多（如外键）
表继承	原生支持	不支持
数据类型	丰富（数组、JSONB、范围类型）	基础类型为主
索引类型	B-tree、Hash、GiST、GIN、BRIN	B-tree、Hash、全文索引
复制方案	流复制，逻辑复制	主从复制，Group Replication
运维复杂度	中等（需要调优 VACUUM）	较低（开箱即用）

4.4.2 量化交易场景的选型建议

我个人习惯这样选：

• 多品种、多策略、复杂查询：选 PG。它的并行查询、丰富索引、JSONB 支持，能让你少写很多代码。
• 高并发简单写入：选 MySQL。InnoDB 的行锁和 MVCC 在简单场景下更高效。
• 需要复杂分析（窗口函数、CTE）：选 PG。MySQL 的分析能力差一截。
• 团队运维能力有限：选 MySQL。PG 的 VACUUM 和参数调优需要一定经验。

4.4.3 避坑指南

4.5 本章知识体系总览

下面这张图，是我自己总结的 PG 在量化交易系统中的核心能力图谱。你一看就明白。

嗯，这张图基本把 PG 在量化交易中的核心能力串起来了。你想想看，MVCC 保证并发安全，并行查询提升分析速度，分区表管理海量历史数据——这三板斧下来，大部分多品种交易系统的数据问题都能解决。

---