MySQL 老手转 PostgreSQL：那些必须重新校准的直觉

我之前基本一直在使用 MySQL，最近有些场景会使用 PostgreSQL，其中大部分东西照搬就能用，但总有那么几个地方，按 MySQL 的老习惯会有一些问题或者不适配。

这篇文章就是记录一下这些内容：其中的内容主要分三类——建表时定下的（事后极难改），静默丢数据与错结果（你以为对了，其实没有），静默变慢（资源被悄悄吃光）。全文按这三类分组，你可以按分类跳着看。

所有数字都是我在自己的 PG 18.6 上实测验证过的（10 核 / 7.8 GB Docker 容器，百万行级实验表），不是抄文档。绝对值会随机器配置浮动，倍数关系不受影响。

一、建表时定下的：事后极难改

表上的其他东西，选错了都能慢慢补救：索引可以后加，SQL 可以改写，连接池可以调。唯独类型和命名不行，它们是写在建表语句里的，事后想改代价很高。改列类型，PG 一般要把整张表重写一遍（放宽 varchar 长度这类纯元数据调整除外），大表上锁几个小时是常事。

更麻烦的是这些错误大多不会立刻报错。它们会安静地工作一段时间，然后在一次跨时区报表、一次金额对账上暴露出来。

#	维度	MySQL 直觉	PG 该写	为什么
1	时间	`DATETIME`	`timestamptz`	`timestamp` 不指向任何绝对时刻
2	字符串	`VARCHAR(255)`	`text`	同一套存储，`n` 只是写入时的检查
3	主键	`AUTO_INCREMENT` / `serial`	`bigint GENERATED ALWAYS AS IDENTITY`	`serial` 是宏：挡不住手写 id，序列要单独授权
4	主键 ID	随机 UUID 是灾难	`bigint` 或双 ID	数据页不分裂，代价只在主键索引
5	状态	`ENUM(...)`	`text` + `CHECK`	ENUM 是独立类型对象，加取值要 `ALTER TYPE`
6	金额	`DECIMAL`	`numeric(19,4)`	显式标度；`money` 和浮点都是坑
7	命名	驼峰 / `User` 这类表名	蛇形小写	标识符会折叠成小写，`user` 是保留字

前两条最反直觉，先展开。

第 1 条：`timestamptz` 存的是时刻，`timestamp` 存的是墙上时间

名字骗人。它和 timestamp 一样不存时区，存的是一个 UTC 微秒数——一个绝对时刻，没有任何"用户当时在哪个时区"的信息。tz 指的是"它参与时区换算"，不是"它记住时区"。

SET TIME ZONE 'UTC';
CREATE TABLE demo (tstz timestamptz, ts timestamp);
INSERT INTO demo VALUES (now(), now());
SELECT tstz, ts FROM demo;
-- 2026-11-06 01:30:00+00 | 2026-11-06 01:30:00

SET TIME ZONE 'Asia/Shanghai';
SELECT tstz, ts FROM demo;
-- 2026-11-06 09:30:00+08 | 2026-11-06 01:30:00   ← 左边只是换了个显示，右边字面值锁死

同一行数据，换个会话再看：左边（timestamptz）显示跟着会话变，但指向的还是同一时刻；右边（timestamp）的字面值永远不变，可它到底代表哪个时刻，全看读它的人用哪个时区去解读——写入和读取的时区不同，8 小时的偏差就悄悄进来了。

写入的时候：now()::timestamp 是"把此刻按当前会话时区折算成墙上时间，再扔掉时区标签"，它跟着会话变——同一个时刻，上海会话算出 09:30，UTC 会话算出 01:30，写进 timestamp 列的值就会不同，其间差了 8 小时。

规则：每个"发生时间"用 timestamptz；纯日历值（生日、账期日）用 date；真正无时区的墙上规则（“门店每天 09:00 开门”）用 time。timestamp 在业务表里几乎没有位置。

第 2 条：`varchar(n)` 不比 `text` 省任何东西

PG 里所有可变长类型（text、varchar、varchar(n)、numeric……）在磁盘上都是同一套变长存储：数据前面加个长度头，太长的自动压缩、超长的挪去 TOAST 旁表。varchar(255) 和 text 存同样的内容，逐字节一样大（11 个字符两边都占 12 字节），查询、索引也没有任何区别。n 不在数据里，只在元数据里，作用就是写入时多做一次长度检查。

所以一般情况下可以默认 text。确实有业务上限就写 CHECK (char_length(x) <= 200)——上限进了约束，以后放宽、收紧都只是换条约束，不碰类型、不重写表。同理别用 char(n)：定长、右侧补空格，比较语义反直觉。

第 3 条：主键用 `IDENTITY`，不是 `serial`

MySQL 里是 AUTO_INCREMENT，PG 里你会看到 serial 和 IDENTITY 两种写法，网上老文章大多是前者。

serial 严格来说不是一个类型，它是个宏。写 id serial，PG 实际替你做了三件事：

CREATE TABLE t (id serial);

-- 展开是三件事：
--   CREATE SEQUENCE t_id_seq;
--   CREATE TABLE t (id integer NOT NULL DEFAULT nextval('t_id_seq'));
--   ALTER SEQUENCE t_id_seq OWNED BY t.id;    ← 关键是这一步

关键是第三步 OWNED BY：它让序列归这一列所有。序列一旦归了列，删列时它自动跟着删，ALTER TABLE ... OWNER TO 把表转给别人时它也跟着走——命运和列绑在一起，这部分和 IDENTITY 没差别。网上常说"serial 的序列会残留"，那是手写 CREATE SEQUENCE 又忘了写 OWNED BY 的情况；serial 生成的序列都带这行，不会残留。

真正的差别在三个地方：

差别	`serial`	`IDENTITY`
写法	PG 私有缩写	SQL 标准，迁库和通用工具更友好
显式写 id	挡不住：`INSERT INTO t (id) VALUES (999)` 插得进去，但计数器不会跳到 999，数到 999 就撞主键冲突	`ALWAYS` 直接拒绝
序列权限	取号走列默认值，用的是应用角色的权限：光有表的 `INSERT` 不够，序列的 `USAGE` 要单独授，漏了运行时报 permission denied	数据库内部维护，不用单独授权

两个决定现在就要做掉：用 ALWAYS 而不是 BY DEFAULT（挡住应用代码误写 id，导入数据时多写一句 OVERRIDING SYSTEM VALUE 只是一次性成本）；用 bigint 而不是 int（上限 21.4 亿，改主键类型是灾难级迁移）。另外 PG 没有 UNSIGNED，非负靠 CHECK (x >= 0)。

第 4 条：随机 UUID 主键的铁律，在 PG 打对折

MySQL 时代有条铁律：不要用随机 UUID 做主键。根源是 InnoDB 的聚簇索引——数据页按主键有序存放，自增主键永远追加到最后一张页，随机 UUID 却往中间插：页满就分裂，一半行挪去新页，填充率掉到 50~70%，表越来越碎、越插越慢，最后要 OPTIMIZE TABLE 重建。更糟的是二级索引的叶子存的是主键副本，16 字节的 UUID 会把每棵二级索引都拖宽。

PG 没有聚簇索引：表是无序的 heap，插入不看主键顺序，数据页永远不会因为随机主键而分裂；二级索引存的是 6 字节 ctid，不复制主键，宽主键不拖累别的索引。

代价没消失，只是搬进了主键索引自己（30 万行实测）：

主键	插入耗时	主键索引体积
`bigint IDENTITY`	326 ms	6.6 MB
`uuid` v7（有序）	505 ms	9.3 MB
`uuid` v4（随机）	576 ms	11 MB

规则：默认还是 bigint IDENTITY（第 3 条的两个决定继续有效）。确实需要对外不可枚举的 ID 时，用双 ID——内部 bigint 主键 + 对外 uuid 唯一列；或者直接上有序的 uuidv7()（PG 18+，1.55× 慢 / 1.4× 大，比 v4 的 1.77× / 1.67× 好一截）。另外别用 varchar(36) 存 UUID：原生 uuid 是 16 字节定长。

第 5 条：状态用 `text` + `CHECK`，不用 `ENUM`

ENUM 在 PG 里是独立类型对象，不是列上的约束。加一个取值要 ALTER TYPE ... ADD VALUE；删取值或改顺序基本等于重建整个类型，再把所有引用它的列迁过去。而应用层拿到的仍然只是字符串——类型安全的收益是零，运维成本是实的。写成 text + CHECK (status IN (...))，增删取值只是换一条约束。

第 6 条：金额用 `numeric(19,4)`，不用 `money` 和浮点

DECIMAL → numeric，这条 MySQL 经验能直接搬。要重新校准的是"图省事"的那一下：double precision 会让浮点误差直接进库，某次对账就变成一张查不平的报表；money 的输入、输出都跟着 locale 走（同一个值在德语环境显示成 123.456.789,00 €），标度固定 2 位；numeric 不写精度会允许不同入口写入不同标度。显式写 numeric(19,4)，Java 侧配 BigDecimal + @Column(precision = 19, scale = 4)。

第 7 条：标识符会被折叠成小写，保留字建不了表

PG 把不加引号的标识符统一折叠成小写：CREATE TABLE OrderItem 建出来是 orderitem。而 User 更糟——user 是 PG 的保留字，CREATE TABLE User 连语法都过不去，直接报错。我们那张表最终改名成了 app_user。

Spring Boot 3 下 Hibernate 默认会把 orderItem 映射成 order_item，这块通常没事。真正的坑在于手写 SQL、Flyway 脚本、以及从 MySQL 继承下来的驼峰列名——一旦某个列是用 "orderItem" 建的，之后每条 SQL 都必须带引号且严格匹配大小写。

结论：表名列名统一蛇形小写，不用引号，不用保留字。

一份可以直接抄的骨架

CREATE TABLE orders (
    id          bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   bigint        NOT NULL,
    order_no    text          NOT NULL,
    status      text          NOT NULL DEFAULT 'pending'
                              CHECK (status IN ('pending','paid','shipped','cancelled')),
    amount      numeric(19,4) NOT NULL CHECK (amount >= 0),
    currency    text          NOT NULL DEFAULT 'CNY' CHECK (char_length(currency) = 3),
    remark      text,
    created_at  timestamptz   NOT NULL DEFAULT now(),
    updated_at  timestamptz   NOT NULL DEFAULT now(),

    CONSTRAINT uq_orders_tenant_no UNIQUE (tenant_id, order_no)
);

CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at DESC);

二、静默丢数据与错结果：你以为对了，其实没有

这一组更阴：应用层收到的信号是"成功"“正常”，实际结果却并不是——数据丢了、更新没发生、顺序错了，全程不报错，日志里干干净净。

第 8 条：事务出错后整个事务作废，但 `commit()` 仍然"成功"

MySQL 里一条语句报错，你处理掉异常，后面的语句还能继续执行（死锁除外，那是整个事务回滚）。PG 不行：一旦事务内任何语句报错，整个事务进入 aborted 状态，之后每条语句都返回 25P02 current transaction is aborted。

更危险的是 JDBC 的表现：commit() 不抛异常。实测一条已 aborted 的事务，commit() 返回成功，但实际提交了 0 行。

在 Spring 里这意味着一件事：

@Transactional
public void transfer(Long from, Long to, BigDecimal amount) {
    try {
        debit(from, amount);
    } catch (Exception e) {
        log.warn("扣款失败，跳过", e);   // ← 致命
    }
    credit(to, amount);                  // 25P02，抛异常
}

如果你把所有异常都 catch 掉，Spring 认为一切正常 → 调用 commit() → PG 静默回滚 → 数据全丢，日志里一片祥和。

规矩：

不要在 @Transactional 里吞异常。要么让它冒泡，要么 throw new RuntimeException() 显式标回滚
慎用 noRollbackFor——它在 PG 上比在 MySQL 上危险得多
报错后需要继续做事，请新开一个事务（REQUIRES_NEW）

第 9 条：隔离级别的默认值和语义都不同

	MySQL	PostgreSQL
默认级别	REPEATABLE READ	READ COMMITTED
RR 的实现	快照 + gap lock	纯快照，无 gap lock
并发更新同一行	等锁后按最新版本照常改	RC 同左；RR 抛 `40001`，要重试

两个直接后果：

默认级别不同。你在 MySQL 里靠 RR 拿到的"同一事务内两次读结果一致"，在 PG 默认的 RC 下不成立——RC 是每条语句一个新快照。
RR 没有 gap lock，同一行上的并发修改靠报错解决。RR 事务里如果这行已被其他已提交事务改过，直接抛 40001 serialization_failure，应用层要重试（Spring 的 @Retryable 或手写循环）——MySQL 的 RR 是排队等锁照改，这里正相反。

这里最容易读错的一点：默认的 RC 其实和 MySQL 一样。后到的 UPDATE 会等先到的提交，然后按新版本重算 WHERE——还匹配就继续改，已被删或不匹配就跳过，可能静默更新 0 行。所以要检查受影响行数，别把"更新成功"当成一定发生了；这条经验两个库通用。

第 10 条：NULL 的排序方向和 MySQL 相反

PG：ASC 时 NULL 排最后。MySQL：ASC 时 NULL 排最前。

这是静默错误——不报错，只是分页结果不对、第一页全是空值。凡是排序列可能为 NULL，就显式写出来：

1	ORDER BY created_at ASC NULLS LAST

分页还有个连带问题：ORDER BY 一定要带一个唯一的兜底排序列（通常是主键），否则同值行的相对顺序不稳定，翻页会重复或漏行。

三、静默变慢：资源被悄悄吃光

这一组不丢数据、不报错，只是变慢、膨胀、把内存和连接吃光。等你在监控上看到，就已经晚了。

第 11 条：`ALTER TABLE` 会把整张表变成 FIFO 队列

ALTER TABLE 大部分操作要拿 ACCESS EXCLUSIVE，它和所有锁冲突，包括普通 SELECT。真正的杀手不是"它锁了表"，而是锁队列先来先得。

实测：会话 A 持有共享锁 6 秒，会话 B 执行 ALTER TABLE ADD COLUMN 排队，此时会话 C 跑一条完全无关的 SELECT——它和 A 根本不冲突，但它排在 B 后面：

1 2	没设 lock_timeout → SELECT 耗时 4.07 秒设了 lock_timeout=1s → SELECT 耗时 0.13 秒，ALTER 快速失败

这就是典型雪崩：凌晨跑一条"加个列"（实际只要 0.9 ms），某个报表长事务还开着 → ALTER 排队 → 所有查询排队 → 连接池 5 秒内占满。

所以每条 DDL 前都要加两行：

1 2	SET lock_timeout = '3s'; SET statement_timeout = '30s';

PG 没有 gh-ost 这类在线改表工具，但有更直接的替代：

变更	做法	实测代价
加列（常量默认值）	直接加	0.9 ms，不重写表
加索引	`CREATE INDEX CONCURRENTLY`（不进事务块）	153.9 → 203.0 ms
加约束	`NOT VALID` → `VALIDATE`	重锁时间 41.9 ms → 1.25 ms
改列类型	要重写，走影子列方案（建新列 + 后台分批回填 + 切换删旧）	393 ms（百万行）

第 12 条：`UPDATE` 会制造"死元组"，表体积会翻倍

MySQL 的 UPDATE 是原地改，旧值进 undo log。PG 是插入一个新版本、把旧版本标记成"死"，而旧版本还占着磁盘页。

实测 50 万行、78 MB 的表跑一次全表 UPDATE：

阶段	表大小	死行数
初始	78 MB	0
全表 `UPDATE`	156 MB	500,000
`VACUUM` 后	156 MB	0

注意最后一行：VACUUM 清掉了死元组，但文件不缩小。它只是把空间变成"可复用"——所以空间没浪费，第二次 UPDATE 不会再翻倍。

必须监控的两个数字：

-- 死元组占比（该 vacuum 了）
SELECT relname, n_dead_tup, n_live_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- 事务 ID 年龄（逼近上限会拒收新写入）
SELECT datname, age(datfrozenxid) FROM pg_database;

最坑的一点：只要长事务还开着，空间就一直回收不了。实测一个开着的事务让 20 万死元组一个都没回收，它提交后立刻归零。所以"跑个 2 小时报表 → 订单表胖 20 GB"在 PG 里是真实会发生的——MySQL 里你只会看到 undo 变大，业务表不受影响。

还有，autovacuum 默认阈值是表行数的 20%（50 + 0.2 × 行数）。1 亿行的表要变更 2000 万行才触发。大表必须单独调：

1	ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.01);

第 13 条：一个连接 = 一个进程 = 14 MB

MySQL 是线程模型，一个连接几百 KB。PG 是进程模型，每个连接 fork 一个后端进程。

实测：14 MB/连接，建立连接 6.35 ms。20 个 Pod × Hikari 默认 10 = 200 连接 = 2.8 GB 光连接开销。

所以：

Hikari 池要小（单实例 10～20 就够，不是越大越好）
多 Pod 场景上 PgBouncer（N:M 复用，把 200 个客户端连接压到 20 个后端进程）
Hikari 治延迟（省 6.35 ms），PgBouncer 治容量（省 14 MB）——互补，不是二选一

四、PG 白送你的东西

前面全是坑，但它也确实给了你 MySQL 没有的东西，大家有兴趣可以继续深入探索～

RETURNING —— INSERT ... RETURNING id 直接拿回主键，不用再折腾 useGeneratedKeys
部分索引 —— CREATE INDEX ... WHERE status = 'pending'，实测 3408 kB → 240 kB（50 万行，2% pending）
表达式索引 —— 直接索引 lower(email)，实测 17.7 ms → 0.033 ms（20 万行）
BRIN 索引 —— 块范围索引，按数据块存 min/max；时序数据上 24 kB 顶替 21 MB 的 B-tree（875 倍体积差）
CHECK 约束真的生效 —— MySQL 8.0.16 之前是解析后静默忽略
jsonb —— 可索引、可用 @>、可用 ? 查询（json 类型基本别用）
DDL 可以回滚 —— BEGIN; ALTER ...; ROLLBACK; 能完整撤销，MySQL 是隐式提交

一、建表时定下的：事后极难改

第 1 条：timestamptz 存的是时刻，timestamp 存的是墙上时间

第 2 条：varchar(n) 不比 text 省任何东西

第 3 条：主键用 IDENTITY，不是 serial