附录 A：PostgreSQL vs MySQL 对比速查表

工具书用法：遇到"PG 和 MySQL 到底哪里不一样"的问题，按目录跳着查即可。对比版本：PostgreSQL 16 vs MySQL 8.0 / InnoDB。

总体定位
进程 / 线程模型
连接处理
数据类型
DDL 差异
DML 差异
索引体系
事务与隔离级别
MVCC 实现
锁机制
日志体系
复制与高可用
分区
备份恢复
存储过程 / 触发器 / 函数
JSON 支持
全文检索
扩展生态
常用工具链
选型建议
常见误区 / 速查口诀

1. 总体定位

维度	PostgreSQL	MySQL
开源协议	PostgreSQL License（类 BSD / MIT，宽松）	GPLv2（Oracle 持有商业版）
维护方	PGDG 社区（无单一公司）	Oracle（分支 MariaDB / Percona）
SQL 标准遵循度	高（贴近 SQL:2016）	中（方言多）
设计哲学	对象-关系（ORDBMS）+ 可扩展	关系（RDBMS）+ 可插拔存储引擎
可扩展性	`CREATE EXTENSION` 一行装扩展	靠存储引擎（InnoDB/RocksDB 等）
默认端口 / 管理员	5432 / `postgres`	3306 / `root`

2. 进程 / 线程模型

维度	PostgreSQL	MySQL (InnoDB)
基本模型	多进程（每连接一个 backend）	多线程（每连接一个 thread）
单连接开销	较重（~10MB 起）	较轻（几 KB ~ 几 MB）
崩溃隔离	进程崩只影响自己	线程 core 可能拖垮实例
共享内存	`shared_buffers`	`innodb_buffer_pool_size`
后台进程	checkpointer / bgwriter / walwriter / autovacuum	InnoDB master / purge / page cleaner
高并发短连接	必须加连接池	thread cache 可扛

3. 连接处理

维度	PostgreSQL	MySQL
`max_connections` 推荐	100~300	300~3000
连接池	强烈必要（PgBouncer / pgcat）	可选（HikariCP 即可）
连接池模式	session / transaction / statement	一般 session
空闲连接	每个占 ~10MB RSS	占用少
SSL 参数	`sslmode=require/verify-full`	`--ssl-mode=REQUIRED`

4. 数据类型

4.1 整型 / 浮点 / 定点

场景	PostgreSQL	MySQL	备注
1 字节整型	无（用 `smallint`）	`TINYINT`	PG 无 1 字节整数
2/4/8 字节整型	`smallint` / `integer` / `bigint`	`SMALLINT` / `INT` / `BIGINT`	-
自增	`SERIAL` / `IDENTITY`（推荐 `IDENTITY`）	`AUTO_INCREMENT`	PG 基于 sequence
浮点	`real` / `double precision`	`FLOAT` / `DOUBLE`	-
定点	`numeric(p,s)`（无参 = 任意精度）	`DECIMAL(p,s)`（默认 `(10,0)`）	PG 禁用无参 numeric，性能差
货币	`money`（受 locale 影响，不推荐）	用 `DECIMAL`	-

4.2 字符串

类型	PostgreSQL	MySQL	备注
定长	`char(n)` 补空格	`CHAR(n)` 补空格	两边都有尾空格陷阱
变长	`varchar(n)` / `text`（推荐）	`VARCHAR(n)` / `TEXT`/`MEDIUMTEXT`/`LONGTEXT`	PG `varchar` 和 `text` 性能一致
最大长度	1GB（超大值自动 TOAST）	65535 / 16MB / 4GB 分级	-
字符集	库级统一（推荐 `UTF8`）	库 / 表 / 列 / 连接四层	PG 简单

4.3 日期时间（含时区）

类型	PostgreSQL	MySQL	备注
日期 / 时间	`date` / `time`	`DATE` / `TIME`	-
时间戳（无时区）	`timestamp`	`DATETIME`	-
时间戳（带时区）	`timestamptz`（推荐）	`TIMESTAMP`（行为类似）	`TIMESTAMP` 有 2038 问题
时间间隔	`interval` 原生	无（用 `DATE_ADD`）	PG 独有
时间戳范围	4713 BC ~ 294276 AD	1970 ~ 2038 / 1000 ~ 9999	-

4.4 其它类型（PG 大招区）

类型	PostgreSQL	MySQL	备注
布尔	`boolean`（true/false/null）	`BOOLEAN` = `TINYINT(1)`	MySQL 伪布尔
UUID	`uuid` 原生 16 字节	`CHAR(36)` / `BINARY(16)`	PG 有专属函数
JSON	`json`（文本）	`JSON`（二进制）	PG 用 `json` 不推荐
JSONB	`jsonb` 二进制，GIN 索引 + 运算符丰富	`JSON`	PG 杀手锏
数组	`int[]` / 任意类型[]	无	PG 原生 + `unnest`
范围	`int4range` / `tsrange` / `numrange`	无	预约系统友好
枚举	`CREATE TYPE ... AS ENUM(...)` 独立类型	`ENUM(...)` 列级	PG 可跨表复用
网络地址	`inet` / `cidr` / `macaddr`	无	-
几何	内置几何 + PostGIS	内置 Spatial	PostGIS 远强
向量	`pgvector`（`vector(1536)`）	8.0 无官方	AI/RAG 决定性优势

5. DDL 差异

维度	PostgreSQL	MySQL	备注
自增列	`IDENTITY`（推荐）/ `SERIAL`	`AUTO_INCREMENT`	回滚都不回退号
建库语法	`CREATE DATABASE x OWNER u ENCODING 'UTF8' TEMPLATE template0;`	`CREATE DATABASE x DEFAULT CHARSET utf8mb4;`	PG 可克隆模板库
CHECK 约束	原生	8.0.16+ 才生效	-
外键	支持 + `DEFERRABLE` 延迟检查	支持，不支持延迟	PG 延迟约束很有用
ALTER ADD COLUMN	PG11+ 带常量默认值秒级；带 `now()` 仍重写	8.0 `INSTANT` 秒级	两家都优化了
ALTER 改类型	大多重写，但可 `USING 表达式` 转换	大多重写	-
DROP TABLE CASCADE	支持（自动删依赖）	手工删	PG 更方便
DDL 事务	所有 DDL 可回滚	DDL 隐式提交	PG 迁移脚本超爽

6. DML 差异

维度	PostgreSQL	MySQL
UPSERT	`INSERT ... ON CONFLICT (col) DO UPDATE SET ...`	`INSERT ... ON DUPLICATE KEY UPDATE ...`
忽略冲突	`ON CONFLICT DO NOTHING`	`INSERT IGNORE`
RETURNING	`INSERT/UPDATE/DELETE ... RETURNING ...`	无，靠 `last_insert_id()`
UPDATE ... FROM	`UPDATE t SET ... FROM o WHERE t.id=o.id`	`UPDATE t JOIN o ... SET ...`
DELETE ... USING	`DELETE FROM t USING o ...`	`DELETE t FROM t JOIN o ...`
LIMIT	`LIMIT n OFFSET m`	`LIMIT m, n`（MySQL 方言）
CTE / 递归 CTE	原生、可控物化	8.0+ 支持
窗口函数	齐全（FILTER / GROUPS 帧）	8.0+ 基本集
批量导入	`COPY` / `\copy`（极快）	`LOAD DATA INFILE`
GROUP BY 严格度	严格（SELECT 列必须在 GROUP BY 或聚合）	默认宽容
`FOR UPDATE SKIP LOCKED`	9.5+ 支持	8.0+ 支持

7. 索引体系

维度	PostgreSQL	MySQL (InnoDB)
主要索引	B-Tree（堆表 + 索引分离）	B+Tree（聚簇，数据随主键物理排序）
其它索引类型	Hash / GIN / GiST / SP-GiST / BRIN	Hash（Memory）/ R-Tree / Fulltext
覆盖索引	`CREATE INDEX ... INCLUDE (col)`	复合索引覆盖
表达式索引	`CREATE INDEX ON t ((lower(name)))`	8.0+ 函数索引
部分索引	`CREATE INDEX ... WHERE deleted = false`	不支持
并发建索引	`CREATE INDEX CONCURRENTLY`	`ALGORITHM=INPLACE, LOCK=NONE`
全文	GIN + `tsvector`	FULLTEXT
数组 / JSONB	GIN 成熟	8.0 多值索引
地理 / 向量	GiST + PostGIS / pgvector HNSW	R-Tree（弱）
无序插入代价	堆表追加到满足 FSM 的页（几乎无代价）	聚簇随机插 → 页分裂

8. 事务与隔离级别

维度	PostgreSQL	MySQL
默认隔离级别	READ COMMITTED	REPEATABLE READ
READ UNCOMMITTED	等同 RC（不真脏读）	真脏读
REPEATABLE READ	事务级快照，不可重复读 / 幻读都杜绝	可能幻读（Next-Key 缓解）
SERIALIZABLE	SSI（乐观）	Gap/Next-Key 锁（悲观）
保存点	`SAVEPOINT` / `ROLLBACK TO`	同
两阶段提交	`PREPARE TRANSACTION`	`XA` 事务
事务失败行为	语句出错，整个事务作废，配 SAVEPOINT 化解	只有错误语句失败，事务可继续
事务 ID	32 位 xid（需 VACUUM 防冻结）	InnoDB 64 位

9. MVCC 实现

维度	PostgreSQL	MySQL (InnoDB)
多版本位置	堆表里多版本元组（xmin/xmax）	Undo Log + 聚簇最新版本
旧版本回收	`VACUUM` / `autovacuum`	Purge 线程回收 Undo
长事务影响	表 / 索引膨胀	Undo 链 / history list 膨胀
可见性判断	比较 xmin/xmax 与快照	比较 trx_id 与 ReadView
更新代价	HOT：同页更新不改索引；非 HOT：新元组 + 索引更新	聚簇 in-place + 写 Undo
可观察元数据	`xmin/xmax/ctid` 系统列可直接查	不直接可见

10. 锁机制

维度	PostgreSQL	MySQL (InnoDB)
表锁模式数	8 种（ACCESS SHARE / ROW SHARE / ROW EXCLUSIVE / SHARE UPDATE EXCLUSIVE / SHARE / SHARE ROW EXCLUSIVE / EXCLUSIVE / ACCESS EXCLUSIVE）	3 种 + 意向锁（IS/IX/S/X）
行锁实现	元组头 `xmax` 标记（零内存开销）	索引行锁结构（内存）
行锁语法	`FOR UPDATE` / `FOR SHARE` / `FOR NO KEY UPDATE` / `FOR KEY SHARE`	`FOR UPDATE` / `FOR SHARE`
间隙锁 / Next-Key	无（靠 SSI）	RR 下默认有
咨询锁	`pg_advisory_lock(key)`	`GET_LOCK('name')`
死锁	`deadlock_timeout` 后检测	立即检测
锁查看	`pg_locks` / `pg_blocking_pids()`	`performance_schema.data_locks`

11. 日志体系

维度	PostgreSQL	MySQL
数量	1 套：WAL	3 套：Redo + Undo + Binlog
崩溃恢复	WAL	Redo
MVCC 旧版本	不单列 Undo（旧版本在堆里）	Undo
复制逻辑层	复用 WAL（`wal_level=logical`）	Binlog
内部两阶段提交	不需要	需要（Redo prepare / Binlog / Redo commit 保证一致）
同步级别参数	`fsync` / `synchronous_commit`	`innodb_flush_log_at_trx_commit` / `sync_binlog`
日志查看	`pg_waldump`	`mysqlbinlog`

12. 复制与高可用

维度	PostgreSQL	MySQL
物理复制	流复制（基于 WAL 字节流）	无直接对等
逻辑复制	`PUBLICATION` / `SUBSCRIPTION`	Binlog 复制（默认逻辑）
同步模式	async / remote_write / remote_flush / remote_apply / quorum	async / semi-sync / MGR
复制延迟观察	`pg_stat_replication.lag`	`Seconds_Behind_Master`
多主	BDR / pglogical（第三方）	Group Replication（MGR）
高可用方案	Patroni（+ etcd）主流	MGR / InnoDB Cluster / MHA / Orchestrator
DDL 复制	逻辑复制不复制 DDL	Binlog 复制 DDL

13. 分区

维度	PostgreSQL	MySQL
分区方式	RANGE / LIST / HASH（PG10+ 声明式）	RANGE / LIST / HASH / KEY
分区裁剪	默认开启	支持
默认分区	`PARTITION OF t DEFAULT`	无
子分区	多级支持	subpartition
分区上的外键	PG12+ 完整支持	不支持
自动分区	pg_partman + pg_cron	无
水平分片	Citus	Vitess

14. 备份恢复

维度	PostgreSQL	MySQL
逻辑备份	`pg_dump` / `pg_dumpall`	`mysqldump` / `mysqlpump`
物理备份	`pg_basebackup`（在线自带）	`xtrabackup`（Percona）
专业工具	pgBackRest / Barman / wal-g	xtrabackup / clone plugin
增量	pgBackRest / wal-g	xtrabackup incremental
PITR	`recovery_target_time` + WAL 归档	Binlog + 基础备份 replay
角色 / 权限	`pg_dump` 不备份，需 `pg_dumpall -g`	`mysqldump` 不备份 mysql 库

15. 存储过程 / 触发器 / 函数

维度	PostgreSQL	MySQL
过程化语言	PL/pgSQL + PL/Python / Perl / V8 / Java	SQL/PSM
FUNCTION vs PROCEDURE	函数有返回；PROCEDURE（PG11+）可 COMMIT	有 FUNCTION / PROCEDURE
返回多行	`RETURNS TABLE / SETOF`	游标 / 临时表
触发器	BEFORE / AFTER / INSTEAD OF（视图） / 语句级	BEFORE / AFTER，仅行级
事件触发器	`CREATE EVENT TRIGGER`（DDL 触发，PG 独有）	无对等
LISTEN / NOTIFY	内置发布订阅（PG 独有）	无
规则系统	`CREATE RULE`	无

16. JSON 支持

运算	PostgreSQL (JSONB)	MySQL (JSON)
取字段（返 json）	`data -> 'k'`	`JSON_EXTRACT(data,'$.k')` / `data->'$.k'`
取字段（返 text）	`data ->> 'k'`	`data->>'$.k'`
路径取值	`data #> '{a,b}'` / `#>>`	`JSON_EXTRACT(data,'$.a.b')`
包含判断	`data @> '{"k":"v"}'` ✅走 GIN	`JSON_CONTAINS(...)`
键存在	`data ? 'k'` / `?	`/`?&`
合并	`a
设置路径	`jsonb_set(data,'{a,b}','1'::jsonb)`	`JSON_SET(data,'$.a.b',1)`
构造对象 / 数组	`jsonb_build_object/array`	`JSON_OBJECT/ARRAY`
聚合	`jsonb_agg` / `jsonb_object_agg`	`JSON_ARRAYAGG` / `JSON_OBJECTAGG`
展开为行	`jsonb_array_elements`	`JSON_TABLE`
路径查询（SQL:2016）	`jsonb_path_query` / `_exists`	`JSON_EXTRACT` 简单路径
索引	GIN on jsonb（成熟）	多值索引（8.0+）

17. 全文检索

维度	PostgreSQL	MySQL
类型	`tsvector` / `tsquery`	列直接索引
索引	GIN / GiST on `tsvector`	FULLTEXT
中文分词	`zhparser` / `pg_jieba`	`ngram`
查询	`to_tsvector('zh',body) @@ to_tsquery('zh','关键词')`	`MATCH(col) AGAINST('关键词')`
高亮	`ts_headline` 内置	无

18. 扩展生态

场景	PostgreSQL	MySQL 对标
地理 / GIS	PostGIS	MySQL Spatial
向量检索	pgvector	无官方（MariaDB 11.6+）
时序	TimescaleDB	无
分布式分片	Citus	Vitess
定时任务	pg_cron	`EVENT SCHEDULER`
分区管理	pg_partman	无
统计扩展	pg_stat_statements（必装）	Performance Schema
外表 FDW	`postgres_fdw` / `mysql_fdw` / `oracle_fdw`	FEDERATED（受限）
Web 接口	PostgREST / pg_graphql	无
Oracle 兼容	Orafce	无

19. 常用工具链

类别	PostgreSQL	MySQL
CLI	`psql`	`mysql`
图形客户端	pgAdmin / DBeaver / DataGrip / Navicat	Workbench / DBeaver / Navicat
连接池	PgBouncer / pgcat / Odyssey	ProxySQL / MaxScale
监控	`pg_stat_statements` + postgres_exporter + Grafana	mysqld_exporter / PMM
高可用	Patroni / Stolon	MHA / Orchestrator / InnoDB Cluster
备份	pgBackRest / Barman / wal-g	xtrabackup / clone
在线改表	`pg_repack`	`gh-ost` / `pt-osc`
SQL 审核	Archery / Bytebase	Archery / Yearning / Bytebase

20. 选型建议

业务场景	推荐	理由
传统 OLTP（电商、CRM）	均可	看团队
复杂 SQL / 报表分析	PG	窗口函数 / CTE / 物化视图
JSONB / 数组 / 枚举 / 范围	PG	原生类型丰富
地理 / 地图	PG + PostGIS	碾压级优势
向量检索 / AI RAG	PG + pgvector	MySQL 无官方
时序	PG + TimescaleDB 或专业 TSDB	-
多租户 SaaS	PG	Schema 隔离 + RLS + 逻辑复制
极端写入 QPS	MySQL	线程 + 聚簇略占优
存储过程密集 / Oracle 迁移	PG	PL/pgSQL + Orafce
需要 DDL 事务	PG	所有 DDL 可回滚
团队只会 MySQL	MySQL	成本最低

一句话口诀：

业务后端 → 越发推荐 PG。
数据分析 / 数仓边缘 → PG。
AI / 向量 / 地理 → PG 无悬念。
老系统 / 人力有限 → 留 MySQL。

21. 常见误区 / 速查口诀

十大误区

"PG 性能不如 MySQL" —— 通用 OLTP 差距极小，复杂场景 PG 更强。
"PG 没有聚簇索引所以慢" —— PG 可用 CLUSTER 手工聚簇；且避免二级索引回表。
"autovacuum 开着就行" —— 大写业务必须调 scale_factor / naptime / cost_limit。
"PG 连接开 5000 也没事" —— 超过 300 必须 PgBouncer。
"RR 比 RC 更安全" —— PG 的 RC 已够用；强一致用 SERIALIZABLE（SSI）。
"直接用 json 存" —— 永远用 jsonb。
"timestamp 就行" —— 用 timestamptz。
"PG 不支持增量备份" —— pgBackRest / wal-g 早支持。
"逻辑复制会复制 DDL" —— 不会，需手动双发。
"只读长事务没关系" —— 照样阻止 VACUUM、推高 xid age。

速查口诀

进程 vs 线程：PG 进程 / MySQL 线程，PG 必配连接池。
多版本位置：PG 堆里 / MySQL Undo 里，PG 要 VACUUM。
默认隔离：PG = RC / MySQL = RR。
存储结构：PG 堆 + 索引分离 / MySQL 聚簇 + 二级索引。
UPSERT：PG `ON CONFLICT` / MySQL `ON DUPLICATE KEY`。
自增：PG `IDENTITY` / MySQL `AUTO_INCREMENT`。
RETURNING：PG 有 / MySQL 无。
JSONB + GIN + @>：PG 全家桶。
数组/范围/枚举/UUID/网络地址：PG 原生。
地理/向量/时序：PG 扩展三件套。
DDL 事务：PG 可回滚 / MySQL 不可。
长事务危险：PG 表膨胀 + xid 回卷 / MySQL Undo 炸裂。
SERIALIZABLE：PG 乐观 SSI / MySQL 悲观锁。
间隙锁：PG 无 / MySQL 有。
复制：PG 物理 + 逻辑 / MySQL binlog。
备份：PG pg_basebackup + pgBackRest / MySQL xtrabackup。
扩展：PG `CREATE EXTENSION` 一行搞定。

📌 一句话总结：PostgreSQL 是"功能大成的学院派"，MySQL 是"运维优雅的互联网派"。遇到选型问题，按本附录场景表直接秒答。

附录 A：PostgreSQL vs MySQL 对比速查表 ​

目录 ​

1. 总体定位 ​

2. 进程 / 线程模型 ​

3. 连接处理 ​

4. 数据类型 ​

4.1 整型 / 浮点 / 定点 ​

4.2 字符串 ​

4.3 日期时间（含时区） ​

4.4 其它类型（PG 大招区） ​

5. DDL 差异 ​

6. DML 差异 ​

7. 索引体系 ​

8. 事务与隔离级别 ​

9. MVCC 实现 ​

10. 锁机制 ​

11. 日志体系 ​

12. 复制与高可用 ​

13. 分区 ​

14. 备份恢复 ​

15. 存储过程 / 触发器 / 函数 ​

16. JSON 支持 ​

17. 全文检索 ​

18. 扩展生态 ​

19. 常用工具链 ​

20. 选型建议 ​

21. 常见误区 / 速查口诀 ​

十大误区 ​

速查口诀 ​

附录 A：PostgreSQL vs MySQL 对比速查表

目录

1. 总体定位

2. 进程 / 线程模型

3. 连接处理

4. 数据类型

4.1 整型 / 浮点 / 定点

4.2 字符串

4.3 日期时间（含时区）

4.4 其它类型（PG 大招区）

5. DDL 差异

6. DML 差异

7. 索引体系

8. 事务与隔离级别

9. MVCC 实现

10. 锁机制

11. 日志体系

12. 复制与高可用

13. 分区

14. 备份恢复

15. 存储过程 / 触发器 / 函数

16. JSON 支持

17. 全文检索

18. 扩展生态

19. 常用工具链

20. 选型建议

21. 常见误区 / 速查口诀

十大误区

速查口诀