引言

"我们要不要把数据库从 MySQL 换成 PostgreSQL？"——这个问题在我们团队被反复问了三次：第一次是听说同行都在用 PG，第二次是要做向量检索，第三次是被一个复杂报表 SQL 折磨了一周之后。三次的答案不一样：第一次没迁（纯跟风没有痛点），第二次新业务直接选了 PG（pgvector），第三次核心库做了完整评估后只迁了分析库。

"要不要迁"从来不是技术优劣题，而是成本收益题。PG 确实有大量 MySQL 没有或更强的能力（JSONB、物化视图、PostGIS、扩展生态），但 MySQL 也有它的优势：运维人才多、主从复制简单、大厂云服务成熟、简单读写下性能稳定。盲目迁移的团队往往在第一个月就被"ON UPDATE 居然不自动更新时间戳""字符串不能隐式等于数字"这些差异绊倒。这篇文章回答两个问题：PG 到底强在哪（七个能力点逐项对比），以及动手前必须完成的五个评估，文末附一份可以直接贴在 wiki 上的 MySQL vs PG 语法差异速查表。

一、先给结论：什么情况值得迁

一句话：MySQL 是"优秀的通用 OLTP 数据库"，PG 是"可扩展的对象关系型数据平台"。前者把 80% 的常见场景做到简单可靠，后者把剩下 20% 的复杂场景做到极致。迁移的驱动力应该是具体的功能痛点，而不是"PG 更高级"。

二、功能对比：PG 强在哪

2.1 JSONB vs MySQL JSON：半结构化数据的代差

MySQL 5.7 开始支持 JSON 类型，解决了"JSON 能存"的问题；PG 有两种类型：json（存文本）和 jsonb（存解析后的二进制），差距在查询和索引：

-- PostgreSQL：jsonb + GIN 索引，JSON 条件查询直接走索引
CREATE TABLE orders (
    id      BIGINT PRIMARY KEY,
    detail  JSONB NOT NULL
);

-- 在整个 JSONB 上建 GIN 索引（通用倒排）
CREATE INDEX idx_orders_detail_gin ON orders USING gin (detail);

-- 包含查询：detail 里存在 address.city = 上海 的订单，走索引
SELECT * FROM orders
WHERE detail @> '{"address": {"city": "上海"}}';

-- 是否包含某个 key / 任意元素
SELECT * FROM orders WHERE detail ? 'couponCode';

-- 按 JSON 字段表达式建索引（只索引某个路径）
CREATE INDEX idx_orders_userid ON orders
    (((detail ->> 'userId')::bigint));

实际体验：JSON 结构灵活、查询键不固定的场景（订单快照、营销规则、画像标签），JSONB + GIN 几乎不需要提前设计索引；MySQL 下要为每个查询路径建虚拟列索引，漏一个就全表扫。

2.2 全文检索：内置 tsvector，中文需配分词

-- PG 内置全文检索：文档转向量 + 查询词 + GIN 索引
ALTER TABLE articles ADD COLUMN tsv tsvector;
CREATE INDEX idx_articles_tsv ON articles USING gin (tsv);

-- 英文/默认分词直接可用
UPDATE articles SET tsv = to_tsvector('english', title || ' ' || body);

SELECT title FROM articles
WHERE tsv @@ plainto_tsquery('english', 'database tuning')
ORDER BY ts_rank(tsv, query) DESC;

注意：中文场景两边都需要额外分词组件，PG 的 zhparser（基于 scws）生态成熟；如果中文全文检索是唯一需求，差距没有英文场景大。但 PG 可以把全文检索向量和其他过滤条件（时间、租户）组合进同一条高效查询。

2.3 窗口函数与 CTE：复杂分析 SQL 的表达力

说明：MySQL 8.0 已支持窗口函数和 CTE，这两项不再是"PG 独有"，但 PG 的成熟度和高级用法仍然领先：

-- 窗口函数：每个部门薪资排名（两者写法基本一致）
SELECT name, dept, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk
FROM employee;

-- PG 进阶能力：窗口子句（ROWS/RANGE/GROUPS）、frame_exclusion
-- 移动平均、累计占比、同比环比在 PG 里都是标准写法
SELECT dt, gmv,
       round(gmv * 100.0 / SUM(gmv) OVER (), 2) AS pct_of_total,
       avg(gmv) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_sales;

-- CTE 里直接写 DML 并把结果传给后续语句（MySQL 不支持）
WITH moved AS (
    DELETE FROM cart WHERE expire_at < now()
    RETURNING user_id, sku_id
)
INSERT INTO cart_archive SELECT * FROM moved;

-- 递归 CTE：树形/图查询（组织树、评论盖楼）
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 1 AS lvl FROM menu WHERE parent_id IS NULL
    UNION ALL
    SELECT m.id, m.parent_id, m.name, t.lvl + 1
    FROM menu m JOIN tree t ON m.parent_id = t.id
)
SELECT * FROM tree;

-- MATERIALIZED 提示：控制 CTE 是否物化（优化器 fence），应对复杂查询计划劣化
WITH hot AS MATERIALIZED (SELECT ... )

做报表、对账、层级数据查询时，PG 能用一条 SQL 表达 MySQL 里要存储过程+临时表才能完成的逻辑。

2.4 物化视图：MySQL 没有的东西

MySQL 没有原生物化视图（要用定时任务+普通表模拟）。PG 原生支持：

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date(created_at) AS d, count(*) AS cnt, sum(amount) AS gmv
FROM "order"
GROUP BY date(created_at);

CREATE UNIQUE INDEX idx_mv_daily ON mv_daily_sales(d);

-- 全量刷新（锁表）/ 并发刷新（不阻塞读，需唯一索引）
REFRESH MATERIALIZED VIEW mv_daily_sales;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

注意 PG 物化视图本身不自动刷新（这是设计哲学：刷新时机由你控制），需要配定时任务（pg_cron 扩展）或触发器方案。

2.5 分区表：声明式分区已成熟

PG 10 开始声明式分区，现已支持 RANGE/LIST/HASH 和多级子分区，且分区裁剪、默认分区、分区主键约束都完善：

CREATE TABLE log_event (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    ts TIMESTAMPTZ NOT NULL,
    msg TEXT
) PARTITION BY RANGE (ts);

CREATE TABLE log_event_2026_09 PARTITION OF log_event
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE log_event_default PARTITION OF log_event DEFAULT;

2.6 PostGIS：地理信息没有对手

CREATE EXTENSION postgis;

CREATE TABLE store (
    id BIGINT PRIMARY KEY,
    name TEXT,
    location GEOMETRY(Point, 4326)     -- SRID 4326 = 经纬度
);
CREATE INDEX idx_store_geo ON store USING gist (location);

-- 查用户 3 公里内的门店，按距离排序，走 GiST 空间索引
SELECT name,
       ST_DistanceSphere(location, ST_MakePoint(121.47, 31.23)) AS dist_m
FROM store
WHERE ST_DWithin(location::geography, ST_MakePoint(121.47, 31.23)::geography, 3000)
ORDER BY location <-> ST_MakePoint(121.47, 31.23)
LIMIT 10;

MySQL 有基础 GIS 类型和空间索引，但 PostGIS 是行业事实标准：数百个空间函数（缓冲区、相交、投影转换、路网拓扑、栅格），外卖配送范围、附近的人、电子围栏、地图类业务基本不用比较。

2.7 其他被低估的差异

三、MySQL 也不是吃素的：迁移会失去什么

客观列一下 MySQL 的优势，这些都是迁移成本：

1. 运维生态与人：DBA 市场保有量大，主从复制、半同步、MGR、binlog 备份恢复的资料和工具最多；PG 的 vacuum、复制槽、WAL 膨胀是新学习曲线。
2. CDC 生态成熟：Canal、MaxWell 监听 binlog 的生态遍地都是；PG 逻辑复制（wal2json/Debezium）能用，但运维复杂度略高，复制槽积压会导致 WAL 磁盘爆掉。
3. 简单高可用门槛低：主从切换工具链成熟；PG 的 Patroni + etcd（或 Consul）方案更强但组件更多。
4. 类型宽容：MySQL 隐式转换方便（where phone = 138... 数字字符串不报错），PG 严格类型，老代码里大量"碰巧能跑"的 SQL 会直接报错——这是迁移返工的最大来源。
5. 写入模型心智简单：没有 vacuum，不用担心表膨胀、事务 ID 回卷（PG 已大幅改善 autovacuum，但仍需监控）。

四、迁移前的 5 个评估

4.1 评估 1：团队的 SQL 习惯与维护能力

4.2 评估 2：对 MySQL 特有特性的依赖程度（返工重灾区）

把全库 SQL/代码搜一遍，统计以下特性的使用量：

经验值：MyBatis 项目里手写 SQL 越多，迁移工作量越大；JPA/MyBatis-Plus 标准 CRUD 占比越高，越轻松。我们迁移分析库时，40% 的工时花在 ON CONFLICT 语义核对和日期函数改写。

时间戳触发器模板（ON CURRENT_TIMESTAMP 的替代品）：

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_updated
    BEFORE UPDATE ON "order"
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

4.3 评估 3：第三方工具与中间件兼容性

4.4 评估 4：停机窗口与迁移方案

pgloader 是 MySQL→PG 的标准工具（不是自己写 JDBC 脚本）：自动处理大部分类型映射、批量 COPY 加载、支持迁移索引/外键，还能把不兼容的类型用 cast 规则自定义：

# migrate.load 示例
LOAD DATABASE
     FROM mysql://user:pwd@mysql-host:3306/shop
     INTO postgresql://user:pwd@pg-host:5432/shop

WITH include drop, create tables, create indexes, reset sequences,
     workers = 8, concurrency = 1,
     batch rows = 5000, prefetch rows = 10000

CAST TYPE datetime TO timestamptz USING zero-dates-to-null,
     TYPE tinyint TO boolean USING tinyint-to-boolean;

ALTER SCHEMA 'shop' RENAME TO 'public';

割接流程（中大型库）：

T0       全量：pgloader 迁存量
T0~T1    增量：Debezium 读 MySQL binlog 持续同步到 PG（或自研双写）
T1       校验：行数、checksum、抽样表对比、关键报表双跑对账
T2       停写 MySQL（只读公告/网关切只读）
T3       等待 CDC 延迟归零
T4       应用切 PG 连接，放开写入
T5       观察期（见评估 5）

4.5 评估 5：回滚方案

没有回滚方案的割接都是赌博。回滚的核心问题是：切到 PG 后产生的新数据怎么回 MySQL？

建议的切流节奏：灰度 1% → 10% → 50% → 100%，每一档观察业务指标和慢查询/错误日志；回滚时按灰度逆序撤回。

五、迁移后最常踩的 5 个坑

1. 标识符大小写：PG 对不加引号的标识符折叠为小写，建表写 CREATE TABLE Order 后实际叫 order，查询 SELECT * FROM Order 报错——规范就是全部小写+下划线，永不加引号。
2. 严格类型：WHERE phone = 13800000000（bigint 列与 numeric 字面量侥幸，varchar 列直接报错）、MyBatis 传 String 给 int 参数全报错。迁移期在测试环境开启全量回归。
3. GROUP BY 更严格：SELECT 里的非聚合列必须出现在 GROUP BY 中（MySQL 8 默认 ONLY_FULL_GROUP_BY 也是这规则，但历史代码有大量违规）。
4. vacuum/膨胀没人管：迁移后只做备份不监控膨胀，三个月后表膨胀 3 倍性能暴跌——上线即配 autovacuum 参数 + 膨胀监控（pgstattuple）。
5. 连接数打满：MySQL 几百连接轻松扛，PG 每连接一个进程，连接数过高上下文切换拖垮整机——应用侧 HikariCP 小池 + 全局 PgBouncer。

六、MySQL vs PG 语法差异速查表（可直接贴 wiki）

七、常见问题

7.1 MySQL 8 已经这么强了，PG 还有不可替代性吗？

对纯 OLTP CRUD 没有——选哪个都行，团队熟悉哪个用哪个。PG 不可替代的是"扩展平台"能力：pgvector（向量）、PostGIS（地理）、TimescaleDB（时序）、pg_trgm（模糊/相似度）、自定义类型和函数、丰富的全文检索与物化视图。如果你未来 1~2 年确定要碰 AI 检索、地理围栏、复杂分析这三类中的任意一个，PG 的价值就不只是"另一个数据库"。

7.2 能不能两个数据库共存而不是整体迁移？

完全可以，而且这是大多数团队的实际路径：核心交易库继续 MySQL，新建的向量库/画像库/分析库/地理服务用 PG；跨库分析通过 ETL/CDC 汇聚到 PG 数仓。数据库是工具不是信仰，按业务边界选库比"统一技术栈"更重要。代价是团队要维护两套技能和运维体系，小团队慎选。

7.3 pgloader 能保证数据 100% 迁对吗？

不能，它解决 80% 的机械工作，剩下 20% 必须校验：① 类型边界（零日期 0000-00-00 PG 不支持要用 zero-dates-to-null、tinyint 语义、无符号整数溢出 PG 没有 unsigned）；② 保留字冲突（order、user 等表名需要处理）；③ 字符集和 emoji；④ 索引/外键/默认值表达式差异；⑤ 时区（datetime→timestamptz 的时区解释）。校验手段：行数对账、主键分段 checksum、关键业务报表双跑、应用层影子流量双读对比。

7.4 迁完后性能不如 MySQL 是怎么回事？

90% 是三个原因：① 没跑统计信息：PG 查询计划依赖 ANALYZE 统计，大批量导入后必须 ANALYZE（pgloader 一般会做，手工 COPY 别忘了），否则计划器以为是空表乱选计划；② 索引漏迁或写法不匹配（函数包裹列、隐式转换使索引失效）；③ 连接数没控住（没上 PgBouncer，几百连接把内存和 CPU 吃在进程切换上）。用 pg_stat_statements 抓 TOP SQL 逐个分析，不要凭感觉下结论。

7.5 MyBatis 项目迁移工作量怎么量化评估？

三步盘点：① 全局搜索 XML 反引号、ON DUPLICATE、LIMIT m,n、DATE_FORMAT、IFNULL、GROUP_CONCAT、INSERT IGNORE、tinyint——统计命中文件数；② 统计 Mapper 中数据库方言函数的出现频次和涉及的核心链路；③ 按"每个 Mapper 文件 0.5~1 人天 + 集成测试 1 倍人天"粗估。如果手写 SQL 集中在少数大模块（报表/营销），可以只迁这几个库；如果均匀散布在每个交易链路，工作量要乘上完整回归测试的成本。

7.6 PG 的 vacuum 真的很可怕吗？

不可怕但需要理解。PG 的 MVCC 是"更新即插新行+旧行标死"，死元组靠 autovacuum 回收。现代 PG（13+）默认 autovacuum 已经相当智能，正常业务开箱即用。需要主动管理的是：高频更新的表调 autovacuum_vacuum_scale_factor（默认 20% 太迟钝，热点表设 1%~5%）、监控膨胀率（pgstattuple/nagios 脚本）、长事务会阻止 vacuum 回收（监控 pg_stat_activity 里的老事务和复制槽积压）。把这三件事加进巡检清单即可。

八、总结

决策速查卡

值得迁：JSONB 半结构化重度查询 / 物化视图+复杂分析 / 向量 / 地理 / 扩展生态
别瞎迁：标准 CRUD + 团队只会 MySQL + 依赖 binlog 生态 + 没有具体痛点
五评估：团队SQL习惯 → MySQL特性依赖(ON UPDATE/ON DUPLICATE/隐式转换)
       三方工具(MyBatis手写SQL/CDC/PgBouncer) → 停机窗口(pgloader+CDC)
       回滚方案(反向CDC/双写/灰度逆序)
三大坑：标识符小写折叠 / 严格类型报错 / vacuum 膨胀 + 连接数

一句话

"要不要迁 PostgreSQL"的正确答案从来不是 PG 比 MySQL 好，而是你的痛点是否落在 PG 的能力半径里：JSONB 配 GIN 索引让任意键的半结构化查询直接走索引、原生物化视图和支持 DML 的 CTE 让复杂报表一条 SQL 搞定、PostGIS 在地理场景没有对手、pgvector 让 AI 向量和业务数据同库——这四个是"迁了立刻有收益"的硬理由；而窗口函数和 CTE MySQL 8 已经补齐，不算理由。但收益的另一半是成本：ON UPDATE CURRENT_TIMESTAMP 要自己写触发器、ON DUPLICATE KEY 要逐条核对改 ON CONFLICT、LIMIT 参数顺序相反、PG 严格类型让所有依赖隐式转换的 SQL 当场报错、反引号和日期函数全量机械替换，MyBatis 手写 SQL 越多返工越大；运维侧还要吃下 vacuum/膨胀、WAL 复制槽、PgBouncer 连接池三套新心智。所以动迁之前老老实实干完五个评估——团队 SQL 习惯和 PG 运维能力、MySQL 方言特性依赖清单、三方中间件兼容性、pgloader 全量加 Debezium 增量的停机方案、以及带反向 CDC/双写和明确触发条件的回滚方案——然后最稳的路径永远是：新业务用 PG 试点、分析库先迁、核心交易库最后动甚至不动。数据库是工具不是信仰，按业务边界选库，让功能痛点而不是技术潮流拍板。

给团队的建议

互动话题：你们公司有从 MySQL 迁 PG 的经历吗？最痛的差异是哪一个？是整体迁还是两库共存？评论区聊聊。

参考资料

• PostgreSQL 官方文档（与 MySQL 差异、SQL 语法）
• MySQL 官方文档（迁移工具与兼容性章节）
• pgloader 官方文档（MySQL → PostgreSQL）
• Debezium PostgreSQL Connector（逻辑复制 CDC）
• PostgreSQL Wiki：Don't Do This（从其他库迁入的常见错误）
• pgvector / PostGIS / TimescaleDB 官方文档