learnNote

主题

附录 C：PostgreSQL 踩坑案例集 ​

为什么要学踩坑案例？ ​

学会一个数据库的正确姿势只需要翻手册；真正让你区别于新手的，是踩过的坑。本集收录 PG 开发与运维中 30 个高频真实坑，覆盖 MVCC、索引、数据类型、事务、复制、VACUUM、配置、客户端、JSONB、备份十大主题。

每条案例采用 5 段式：现象 → 原因 → 复现 → 解决 → 预防。建议先浏览目录，遇问题再精读对应条目，末尾「监控告警清单」落地到生产。

目录 ​

  1. MVCC 与长事务踩坑
  2. 索引踩坑
  3. 数据类型踩坑
  4. 事务与锁踩坑
  5. 复制踩坑
  6. VACUUM / autovacuum 踩坑
  7. 配置参数踩坑
  8. psql / 客户端踩坑
  9. JSONB 踩坑
  10. 备份恢复踩坑

一、MVCC 与长事务踩坑 ​

PostgreSQL 的 MVCC 实现是"多版本元组直接放在堆里，旧版本靠 VACUUM 回收"。这个机制最怕的就是 长事务：只要一个快照不释放，所有表的死元组都回收不了。

1.1 长事务阻止 VACUUM，表 / 索引疯狂膨胀 ​

现象

  • 某张每天写入百万行的表 orders，物理大小每天涨几个 GB，\dt+ 看表大小是数据量的几倍。
  • pg_stat_user_tables 里 n_dead_tup 比 n_live_tup 还多，last_autovacuum 是几天前甚至空。

原因

  • PG 只能回收 "xmin < 全局最老活动快照 xmin" 的死元组。某个后端持有一个老快照（长事务 / idle in transaction / 未关闭的游标），VACUUM 就啥也回收不了。

复现

sql
-- 会话 A
BEGIN;
SELECT 1;   -- 不 COMMIT，仅此一条就拿了快照

-- 会话 B
UPDATE orders SET status = 'paid' WHERE id BETWEEN 1 AND 1e6;
VACUUM orders;
-- 观察 n_dead_tup 不减，VACUUM (VERBOSE) 会打印:
-- "there were N dead tuples which could not be removed yet"

解决

sql
-- 找长事务并干掉
SELECT pid, now()-xact_start AS age, left(query,120) FROM pg_stat_activity
 WHERE xact_start IS NOT NULL AND now()-xact_start > interval '10 min';
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
 WHERE state='idle in transaction' AND now()-xact_start > interval '10 min';

随后对受影响表 VACUUM (VERBOSE)；膨胀严重的大表低峰期 pg_repack。

预防：必设 idle_in_transaction_session_timeout = '10min' + statement_timeout；禁止"开事务等 MQ 回包"模型；监控长事务数量告警。

1.2 idle in transaction 连接不断，越积越多 ​

现象

  • pg_stat_activity 里 state = 'idle in transaction' 的会话越来越多；连接数接近上限；长事务告警狂响。

原因

  • 应用代码典型错误：BEGIN 后业务逻辑里做 RPC / 等人审批 / 发短信，忘了在异常路径 ROLLBACK；或者使用了连接池但没开启 "reset on return"。

复现

python
conn = psycopg.connect(...)
cur = conn.cursor()
cur.execute("UPDATE t SET x=1 WHERE id=1")
# 忘记 commit/close，连接回池子 → idle in transaction

解决 / 预防：临时 pg_terminate_backend 清掉；应用层用 with conn.transaction() 或 try/finally commit/rollback；PgBouncer 加 server_reset_query='DISCARD ALL'；idle_in_transaction_session_timeout 必设；Code Review 检查所有 BEGIN 路径是否有 finally。

1.3 xid wraparound 警告 / 强制进单用户模式 ​

现象

  • 日志报 WARNING: database "xxx" must be vacuumed within N transactions；更严重会直接 FATAL: database is not accepting commands to avoid wraparound data loss，必须单用户模式 VACUUM 才能起来。

原因

  • PG 的 xid 是 32 位的。若 autovacuum 长期跑不动（又是长事务 / 非常大的表 / autovacuum_freeze_max_age 达到 2 亿），会触发防冻结限制。

复现（理论）

sql
SHOW autovacuum_freeze_max_age;            -- 默认 200000000
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
-- 若某库 age > freeze_max_age，autovacuum 会进入激进防冻结模式；
-- 若继续不动 → 接近 20 亿时拒写。

解决 / 预防：紧急用 VACUUM (FREEZE, VERBOSE) 最老几张表；最糟糕情况进单用户模式 postgres --single -D $PGDATA db < VACUUM;。预防：监控 age(datfrozenxid) 每库 > 5亿 告警；大表调 autovacuum_freeze_max_age=100000000；禁止 10 小时级长事务。

1.4 只读长事务也会导致膨胀（经典反直觉） ​

现象

  • "我这查询又不改数据，应该没关系啊" → 结果运维说"你把我们 VACUUM 全卡了"。

原因

  • 只要是事务，就有快照；快照存在就压住 xmin horizon，VACUUM 无法回收在此之后被标记为"死"的元组。

复现

sql
-- 会话 A：只读事务
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT count(*) FROM big_table;  -- 跑 2 小时

-- 会话 B：写入
UPDATE big_table SET x = x + 1 WHERE id BETWEEN 1 AND 1e7;
VACUUM big_table;   -- 无法回收

解决 / 预防：长分析查询放只读备库跑（hot_standby_feedback=on 注意另一面陷阱）；分析与 OLTP 拆开；监控最老活动事务。

二、索引踩坑 ​

2.1 函数包裹列 → 不走索引 ​

现象

  • WHERE lower(email) = 'a@x.com' 扫全表；EXPLAIN 显示 Seq Scan。

原因

  • 普通 B-Tree 索引是按列原值建的；一旦列被函数包裹，就与索引的 key 表达式不匹配。

复现

sql
CREATE TABLE u (id int, email text);
INSERT INTO u SELECT g, 'user'||g||'@x.com' FROM generate_series(1,1e6) g;
CREATE INDEX ON u (email);
EXPLAIN SELECT * FROM u WHERE lower(email) = 'user1@x.com';   -- Seq Scan

解决 / 预防：要么改写不包裹；要么建表达式索引 CREATE INDEX ON u (lower(email))；或用 citext 大小写不敏感类型。新增 SQL 必过 EXPLAIN，WHERE 函数(列) 当 code smell。

2.2 隐式类型转换 → 不走索引 ​

现象

  • 列是 bigint，查询写成 WHERE user_id = '123'（字符串字面量），不走索引；或 WHERE phone = 13812345678（数字）对 text 列也不走。

原因

  • 类型不一致时，PG 可能把列转成字符串再比较，等同于"函数包裹列"。

复现

sql
CREATE TABLE t (phone text);
CREATE INDEX ON t (phone);
INSERT INTO t SELECT (13000000000 + g)::text FROM generate_series(1, 1e6) g;
EXPLAIN SELECT * FROM t WHERE phone = 13000000001;   -- 可能 Seq Scan

解决 / 预防：字面量加引号 WHERE phone='13000000001'；参数显式 WHERE phone=$1::text；ORM 参数绑定按列类型；代码审查紧盯类型不符。

2.3 JSONB 查询 ->> 等值比较不走 GIN ​

现象

  • CREATE INDEX ON t USING GIN (data); 后，WHERE data->>'user_id' = '123' 依然 Seq Scan。

原因

  • GIN on jsonb 支持的是 @> / ? / ?| / ?& 等操作符类，不是 ->> 的结果。

复现

sql
CREATE TABLE e (id int, data jsonb);
INSERT INTO e SELECT g, jsonb_build_object('uid', g, 'tag','x') FROM generate_series(1,1e5) g;
CREATE INDEX ON e USING GIN (data);
EXPLAIN SELECT * FROM e WHERE data->>'uid' = '1';           -- Seq Scan
EXPLAIN SELECT * FROM e WHERE data @> '{"uid":1}'::jsonb;   -- Bitmap Index Scan ✓

解决 / 预防：用 @>：WHERE data @> jsonb_build_object('uid',1)；或建表达式索引 CREATE INDEX ON e ((data->>'uid'))；提高选择性 USING GIN (data jsonb_path_ops)。团队约定"JSONB 过滤统一 @>"，所有 JSONB 查询 EXPLAIN。

2.4 超大表直接 CREATE INDEX → 锁表事故 ​

现象

  • 凌晨给 5 亿行的表加索引，CREATE INDEX idx ON big(col); → 瞬间所有写入被阻塞；业务端超时雪崩。

原因

  • 普通 CREATE INDEX 持 SHARE 锁，阻塞 INSERT/UPDATE/DELETE。

复现

sql
CREATE INDEX idx_ok ON big(col);          -- 阻塞写
-- 正确:
CREATE INDEX CONCURRENTLY idx_ok ON big(col);  -- 不阻塞写

解决 / 预防：立即 pg_cancel_backend；无效索引要 DROP。生产一律用 CONCURRENTLY（不能在事务内；失败会留 indisvalid=false 需清理；比普通慢 2~3 倍，低峰期做）。CI 加钩子：无 CONCURRENTLY 的 CREATE INDEX 直接拒。

2.5 无用索引从不删 + 索引膨胀 ​

现象

  • 表 10GB，索引 50GB；写入慢、VACUUM 慢；主库磁盘告警。

原因

  • 历史运维留下一堆"万一要用"的索引；或者频繁 UPDATE 非 HOT，B-Tree 老页碎片化。

复现排查

sql
-- 从未被用过的索引（idx_scan = 0）
SELECT schemaname||'.'||relname AS t, indexrelname AS i,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0
   AND indexrelname NOT LIKE '%pkey'
 ORDER BY pg_relation_size(indexrelid) DESC;

-- 索引膨胀（粗略用 pgstattuple 扩展）
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('idx_name');

解决 / 预防：未用的 DROP INDEX CONCURRENTLY；膨胀的 REINDEX INDEX CONCURRENTLY 或 pg_repack。每月跑"僵尸索引扫描"；写密表盯 n_tup_hot_upd/n_tup_upd 比例。

三、数据类型踩坑 ​

3.1 timestamp vs timestamptz 时区灾难 ​

现象

  • 生产库时间少 8 小时 / 跨时区服务时间混乱；跨系统导数据后全错。

原因

  • timestamp（= timestamp without time zone）不保存时区，存啥读啥；timestamptz 存储内部 UTC，按会话 timezone 展示。把本地时间写到 timestamp、再让带不同时区的客户端读，就乱了。

复现

sql
SET timezone = 'UTC';
CREATE TABLE t (a timestamp, b timestamptz);
INSERT INTO t VALUES ('2026-04-17 10:00', '2026-04-17 10:00');
SET timezone = 'Asia/Shanghai';
SELECT * FROM t;
-- a 仍是 2026-04-17 10:00（不变）
-- b 变成 2026-04-17 18:00（按上海时区展示）

解决 / 预防：字段全部改 timestamptz；历史迁移 ALTER TABLE t ALTER COLUMN a TYPE timestamptz USING a AT TIME ZONE 'Asia/Shanghai'；新表默认 created_at timestamptz NOT NULL DEFAULT now()；应用传 ISO8601 带时区；建库模板设置 timezone。

3.2 numeric 不限定精度，性能 / 空间差 ​

现象

  • numeric 字段占空间大、比较慢。

原因

  • numeric 不指定精度时是任意精度，存储为变长字节串；算数计算走软件实现，比 bigint / double 慢一个数量级。

复现

sql
CREATE TABLE a (x numeric);              -- 任意精度
CREATE TABLE b (x numeric(14, 2));       -- 定点

-- 塞入 1000 万行并比较大小、计算
-- pg_relation_size(a) >> pg_relation_size(b)

解决 / 预防：金额用 numeric(14,2) 限定；不精确用 double precision；计数 / ID 用 bigint。团队约定禁用无参 numeric。

3.3 text 无限制被塞入超大字符串 ​

现象

  • 接口可以"不限长度"地写入，结果一行 10MB；表变超大、TOAST 链长、查询更新变慢。

原因

  • text / varchar 不限长度时，PG 会自动 TOAST 超过 ~2KB 的值（压缩 + 外存），逻辑上没错，但业务语义上"一个名字字段存了 10MB"就离谱了。

复现

sql
CREATE TABLE profile (id int PRIMARY KEY, name text);
INSERT INTO profile VALUES (1, repeat('x', 10*1024*1024));   -- 10MB 名字

解决 / 预防：加 CHECK (length(name)<=128)；大对象单独表存；DDL 评审要求每个字符串字段有长度上限；接入层同样加最大长度校验。

3.4 UUID v4 作主键 → B-Tree 写放大 ​

现象

  • 用 UUID v4 做主键，写入 TPS 上不去；索引大、页分裂频繁。

原因

  • UUID v4 完全随机，B-Tree 叶子页按顺序排列时插入永远是"扔到随机位置"，大量页分裂与 buffer 驱逐。对比有序的 bigserial / UUID v7（时间有序），差距很大。

复现对照

sql
-- 随机 UUID v4：
INSERT INTO t_uuid4(id) SELECT gen_random_uuid() FROM generate_series(1,1e7);
-- 时间有序 UUID v7（PG17 自带，或扩展 / 应用侧生成）
-- 插入速度 / 索引大小明显更优

解决 / 预防：主键用 bigint IDENTITY，UUID 只作为对外 id 放唯一索引；必须用 UUID 选 UUID v7。"UUID v4 做主键"列入团队禁用清单。

3.5 char(n) 末尾空格陷阱 ​

现象

  • char(10) 列存了 'abc'，查 WHERE col = 'abc' 时匹配，但拼接字符串时多出一堆空格，或者在其他数据库里比较不匹配。

原因

  • char(n) 是定长的，不足 n 时自动右填空格（standard SQL 规定）；等值比较时 PG 内部按"语义相等"忽略尾空格，但拼接 / length() / 外部系统对接时不会。

复现

sql
CREATE TABLE c (x char(10));
INSERT INTO c VALUES ('abc');
SELECT '['||x||']', length(x) FROM c;   -- [abc       ]，length = 10
SELECT x = 'abc' FROM c;                -- true（语义相等）

解决 / 预防：一律用 varchar(n) 或 text；不得不用 char(n) 时读取时 rtrim(x)。团队约定禁 char(n)。

四、事务与锁踩坑 ​

4.1 事务中一条错 → 整个事务作废 ​

现象

  • 在事务里执行 10 条 SQL，第 3 条报错后，第 4 条立刻报 current transaction is aborted, commands ignored until end of transaction block。从 MySQL 迁过来的人第一次一定会中招。

原因

  • PG 的事务模型：一旦语句报错，事务进入"aborted"状态，必须 ROLLBACK 或回到 SAVEPOINT 才能继续。

复现

sql
BEGIN;
INSERT INTO t VALUES (1);
INSERT INTO t VALUES ('not a number');    -- 报错
INSERT INTO t VALUES (2);                 -- 直接拒绝："current transaction is aborted"
COMMIT;                                   -- 实际是回滚

解决

  • 可预期错误用 SAVEPOINT：
sql
BEGIN;
SAVEPOINT sp;
INSERT INTO t VALUES (1);
BEGIN
  INSERT INTO t VALUES ('not a number');
EXCEPTION WHEN others THEN
  ROLLBACK TO SAVEPOINT sp;   -- 回到保存点，事务继续
END;
INSERT INTO t VALUES (2);
COMMIT;
  • 或 psql 内加 \set ON_ERROR_ROLLBACK interactive（自动每条前打 savepoint）；应用端使用带重试 / 子事务的 ORM。

预防：迁移手册首页必写；Code Review 重点看批量导入、幂等写入场景。

4.2 ALTER TABLE ... ADD COLUMN DEFAULT 全表重写 ​

现象

  • 给 10 亿行表加一列带默认值，执行了 2 小时，锁表导致线上大面积超时（PG 10 及以下版本）。

原因

  • PG 10- 中 ADD COLUMN ... DEFAULT x 会把默认值"物化"到每一行，等于全表重写并持有 ACCESS EXCLUSIVE。
  • PG 11+ 做了优化：常量默认值变为元数据记录，执行秒级；但非常量默认值（如 now()）仍然会重写。

复现

sql
-- PG 11+
ALTER TABLE big ADD COLUMN flag boolean DEFAULT false;   -- 秒级 ✓
ALTER TABLE big ADD COLUMN ts timestamptz DEFAULT now(); -- 全表重写 ✗

解决

  • 分三步：
sql
ALTER TABLE big ADD COLUMN ts timestamptz;                 -- 只改元数据
ALTER TABLE big ALTER COLUMN ts SET DEFAULT now();         -- 设默认（未来行生效）
-- 分批回填（按主键范围，每批 commit，休息几秒）
UPDATE big SET ts = now() WHERE id BETWEEN 1 AND 10000;
-- 最后如有需要：
ALTER TABLE big ALTER COLUMN ts SET NOT NULL;

预防：DDL 评审必须预估锁与表大小；超大表推荐 pg_repack 在线重建。

4.3 SERIALIZABLE 下 serialization_failure 必须重试 ​

现象

  • 把隔离级别调到 SERIALIZABLE，业务频繁报 ERROR: could not serialize access due to read/write dependencies (SQLSTATE 40001)。

原因

  • PG 的可串行化靠 SSI（乐观），检测到潜在序列化异常就直接让一方失败。这是正常行为，不是 bug。

复现

sql
-- 会话 A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(amount) FROM balance WHERE uid = 1;
UPDATE balance SET amount = amount - 10 WHERE uid = 1;

-- 会话 B（并行）
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(amount) FROM balance WHERE uid = 1;
UPDATE balance SET amount = amount - 5 WHERE uid = 1;
COMMIT;

-- 会话 A COMMIT → 可能报 40001

解决 / 预防：业务代码必须捕获 40001 指数退避重试 3~5 次；把重试套路纳入通用 DAL；短事务 + 索引充分 + 合理 work_mem 可降低冲突。

4.4 SELECT FOR UPDATE 顺序不同 → 死锁 ​

现象

  • 两个后台任务交叉运行偶发 deadlock detected，一方被回滚。

原因

  • 两边拿行锁的顺序不同。比如事务 A 锁了 id=1 再去锁 id=2；事务 B 锁了 id=2 再去锁 id=1。

复现

sql
-- A
BEGIN; SELECT * FROM t WHERE id=1 FOR UPDATE; SELECT * FROM t WHERE id=2 FOR UPDATE;
-- B 同时
BEGIN; SELECT * FROM t WHERE id=2 FOR UPDATE; SELECT * FROM t WHERE id=1 FOR UPDATE;
-- 死锁

解决 / 预防：全局统一锁顺序 ... WHERE id IN (1,2) ORDER BY id FOR UPDATE；长任务 FOR UPDATE SKIP LOCKED 做队列；业务层捕获 40P01 重试；log_lock_waits=on 记录长等待。

五、复制踩坑 ​

5.1 复制槽不消费 → pg_wal 爆盘 ​

现象

  • 主库 pg_wal 目录从几 GB 涨到几百 GB，磁盘写满，库直接拒写。

原因

  • 复制槽（pg_replication_slots）保证了"在 subscriber / standby 确认消费前，WAL 不删"。一旦下游挂了或慢了，WAL 就一直堆在主库。

复现

sql
-- 主库创建一个逻辑复制槽但没人消费
SELECT pg_create_logical_replication_slot('ghost_slot', 'pgoutput');
-- 写入几百万行 → pg_wal 持续增长
-- 观察
SELECT slot_name, active, 
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retention
  FROM pg_replication_slots;

解决 / 预防：紧急 pg_drop_replication_slot('ghost_slot') 即可释放 WAL；监控 pg_replication_slots 滞留大小告警；PG13+ 可配 max_slot_wal_keep_size（超过自动丢弃槽，权衡使用）；一次性任务完成后必删槽。

5.2 同步复制配置错 → 主库 hang ​

现象

  • 半夜主库所有写入都 hang 住，pg_stat_activity 全是 SyncRep；备库已宕。

原因

  • synchronous_standby_names = 'ANY 1 (s1)' / 'FIRST 1 (s1)' 的同步模式下，若没有同步 standby 报 ACK，主库 commit 会等到超时或永远。

复现

  • 配置 synchronous_commit = remote_apply，synchronous_standby_names = 'FIRST 1 (only_one)'，然后停掉 only_one。

解决 / 预防：应急改 synchronous_standby_names='' + pg_reload_conf() 恢复可写；同步备至少 2 个 ANY 1 (s1,s2) 或上 Patroni；非 RPO=0 就别用 remote_apply；所有 standby 失败立即告警。

5.3 逻辑复制不复制 DDL ​

现象

  • 主库上加了个新列，备库（逻辑复制订阅端）开始报错 column "x" of relation "t" does not exist，复制停滞。

原因

  • PG 逻辑复制基于表结构匹配，不会复制 DDL。

复现

sql
-- 主库
ALTER TABLE t ADD COLUMN x int;
INSERT INTO t (id, x) VALUES (1, 1);
-- 订阅端没加 x 列 → 复制中断

解决 / 预防：订阅端先加相同列再重投；顺序 "订阅端 DDL → 主端 DDL → 涉及新列写入"；用 event trigger 抓 DDL 同步，或 liquibase 统一管理 schema。

5.4 跨大版本逻辑复制类型不兼容 ​

现象

  • 从 PG 11 逻辑复制到 PG 16，某些扩展类型（PostGIS 旧版几何、自定义 domain）订阅端报类型错误。

原因

  • 逻辑解码序列化使用类型 OID / 名称 匹配；不同版本 / 不同扩展版本下可能不兼容。

解决 / 预防：订阅端装相同版本扩展再开始；大升级顺序 先升订阅端 → 逻辑复制双写 → 切流量 → 下主库；UAT 先演练全量 + 增量。

六、VACUUM / autovacuum 踩坑 ​

6.1 autovacuum 跑不动 —— 大表一直"来不及" ​

现象

  • pg_stat_user_tables.last_autovacuum 几天前；n_dead_tup 持续涨；磁盘一天涨几 GB。

原因

  • 默认 autovacuum_vacuum_scale_factor = 0.2，一个 1 亿行的表要死 2000 万行才触发；一旦触发又被长事务 / I/O 限制 / autovacuum_max_workers 不够挡住。

解决 / 预防

sql
-- 大表收紧
ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.02,
                           autovacuum_vacuum_cost_limit    = 2000);
-- 全局
ALTER SYSTEM SET autovacuum_max_workers = 6;
ALTER SYSTEM SET autovacuum_naptime = '30s';
SELECT pg_reload_conf();

监控：n_dead_tup / (n_live_tup+n_dead_tup) > 20% 告警。

6.2 VACUUM FULL 锁表事故 ​

现象

  • 为了回收膨胀，深夜跑 VACUUM FULL big_table;，结果几 TB 的表跑了 5 小时，锁住所有读写，白天业务恢复上班后还没结束。

原因

  • VACUUM FULL 相当于重建整张表，持 ACCESS EXCLUSIVE 锁；代价巨大。

解决 / 预防：立刻 pg_cancel_backend(pid)；大表膨胀用 pg_repack（在线重建，只有极短切换锁）或 pg_squeeze；VACUUM FULL 只在小表 / 低峰期且提前估算时间时用。

6.3 未及时 ANALYZE → 执行计划走偏 ​

现象

  • 批量导入 1 亿行后查询突然变慢；EXPLAIN 选了奇怪的 Nested Loop 或 Seq Scan。

原因

  • 统计信息还停在"空表"状态，规划器估错行数。

复现

sql
CREATE TABLE t (id int PRIMARY KEY, x int);
-- 此时统计 0 行
INSERT INTO t SELECT g, g%100 FROM generate_series(1, 1e7) g;
-- pg_stat_user_tables.n_live_tup 还是 0 或 旧值
EXPLAIN SELECT * FROM t WHERE x = 1;   -- 估错

解决 / 预防：ANALYZE t; 立即修复；大批量导入脚本末尾加 ANALYZE；大表 autovacuum_analyze_scale_factor 收紧到 0.02；关键查询上 auto_explain。

七、配置参数踩坑 ​

7.1 shared_buffers 设过大反而慢 ​

现象

  • 128GB 内存机器把 shared_buffers 设到 96GB，期望飞起 → 实际写入 / checkpoint 抖动严重，TPS 反而下降。

原因

  • PG 的 shared_buffers 与 OS page cache 之间存在双缓存；过大时 checkpoint 要写大量脏页、bgwriter 压力大；锁竞争（buffer map lock）也上升。

解决 / 预防：经验值 shared_buffers = 内存 25%（OLTP 通常 8~32GB）；余下留给 OS page cache；配合 effective_cache_size（50~75%）帮助规划器选 Index Scan。

7.2 max_connections 巨大 → 进程切换雪崩 ​

现象

  • max_connections = 5000，应用连过来 3000 连接，CPU 被 context switch 打满。

原因

  • PG 是多进程，每连接一 backend；上千连接后调度压力剧增。

解决 / 预防：降 max_connections 到 200~500；前面加 PgBouncer（transaction 模式），应用连 pool，pool 连 PG；所有应用强制走连接池。

7.3 work_mem 设过大触发 OOM ​

现象

  • OS Killer 杀了 postgres；日志里 out of memory。

原因

  • work_mem 是每个排序 / Hash / Bitmap 节点的内存上限；一个查询可能有 10 个这样的节点；再乘上并发连接数，瞬时内存占用可能是 work_mem * 节点数 * 并发连接。

复现估算

  • work_mem = 512MB，并发 200 个连接每个 5 个节点 → 500GB 需求。显然爆。

解决 / 预防：默认 work_mem=4MB~16MB；重分析会话 SET LOCAL work_mem='256MB'；OS 设 vm.overcommit_memory=2；监控 temp_files/temp_bytes，不够才调。

7.4 fsync = off / synchronous_commit = off 乱关 ​

现象

  • 为了性能关掉 fsync，一次意外断电 → 数据文件损坏、无法启动，只能从备份恢复。

原因

  • fsync = off 意味着 WAL 写到 OS 缓存就返回；一旦断电直接数据丢失甚至损坏。full_page_writes = off 同理。

解决 / 预防：只在测试库临时关，生产绝对不关；DBA 变更审批一律拒绝；监控 pg_settings 基线，偏离告警。

八、psql / 客户端踩坑 ​

8.1 客户端与服务端字符集不一致 → 乱码 ​

现象

  • psql 显示中文全是 ???；Python 程序插入的中文变 \xe4\xb8。

原因

  • psql 的 client_encoding 与数据库实际编码 / 终端 LANG 不一致。

解决 / 预防：\encoding UTF8；终端 export LANG=zh_CN.UTF-8（Windows chcp 65001）；库统一 UTF8；连接串默认 client_encoding=UTF8。

8.2 \copy vs COPY 路径权限差异 ​

现象

  • COPY t FROM '/home/user/a.csv' ... 报 could not open file "/home/user/a.csv" for reading: Permission denied；从 MySQL 习惯过来的人很懵。

原因

  • COPY 是服务端命令，路径按 postgres 进程用户权限解读。\copy 是 psql 客户端元命令，路径在本地主机、当前 shell 权限解读。

解决 / 预防：从本地导用 \copy（推荐）；或 chmod 放到 postgres 可读路径再用 COPY；脚本统一 \copy。

8.3 SET vs SET LOCAL 作用域搞混 ​

现象

  • 写了 SET work_mem = '256MB'; 后紧跟一条大查询，发现并没生效，或者影响到了事务外其它查询。

原因

  • 在事务内 SET 生效到会话结束；SET LOCAL 只在当前事务结束时失效。在 PgBouncer transaction 模式下尤其要注意 "跨事务 SET 不保留"。

复现

sql
-- PgBouncer transaction 模式
SET work_mem = '256MB';     -- 事务结束就没了
-- 建议
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT ...;
COMMIT;

解决 / 预防：统一 SET LOCAL + BEGIN ... COMMIT；持久设置用 ALTER ROLE x SET work_mem='16MB' 或 ALTER DATABASE。

8.4 SET ROLE 后忘记 RESET ROLE ​

现象

  • 跑完审计脚本 SET ROLE audit; 后继续干业务，写入竟然以 audit 身份写；对象 owner 是 audit；权限混乱。

解决 / 预防：脚本首尾成对 SET ROLE / RESET ROLE；用 session_user 校验；psql 提示符显示当前 role；生产禁止长连接里随意 SET ROLE。

九、JSONB 踩坑 ​

9.1 data->'k' = 'v' 不走 GIN ​

现象

  • 详见 2.3。此处强调写法本身：
sql
-- 不走 GIN
WHERE data -> 'role' = '"admin"';
WHERE data ->> 'role' = 'admin';

-- 走 GIN
WHERE data @> '{"role":"admin"}'::jsonb;

预防

  • 团队代码规约：jsonb 过滤只用 @> 或显式表达式索引。
  • 上线前 EXPLAIN。

9.2 深层嵌套 jsonb_set → 写放大 ​

现象

  • UPDATE t SET data = jsonb_set(data, '{a,b,c,d}', '"1"') 即使只改最深一个字段，整行 JSONB 都要重写；频繁更新触发严重写放大。

原因

  • jsonb 是整体二进制，不支持"局部更新"。一条 UPDATE 就是一条新元组 + 索引变更 + WAL 写入。

解决 / 预防：频繁改的字段拆为普通列；必须改就批量合并减少更新次数；设计阶段识别高频更新字段，不要埋深在 JSONB 里。

9.3 用 text 存 JSON 而不用 jsonb ​

现象

  • 为了"省点空间"或"迁移方便"用 text 存序列化后的 JSON，结果所有过滤只能 LIKE，性能一塌糊涂。

解决 / 预防

  • 永远用 jsonb；
  • json（文本）只在"保持原始字节序"极个别场景用。

十、备份恢复踩坑 ​

10.1 pg_dump 不备份角色 / 权限 ​

现象

  • 从 A 库 pg_dump 到 B 库 pg_restore，业务跑起来就一堆 "role does not exist"。

原因

  • pg_dump 只备份库内对象（表、索引、数据）；不备份集群级的 roles、表空间等。

解决 / 预防：全量迁移先 pg_dumpall -g > globals.sql，目标端 psql -f globals.sql，再 pg_dump -F c + pg_restore；只迁单库时也要手工准备相关 role 的 CREATE / GRANT。

10.2 pg_basebackup 没带 WAL → 无法启动 ​

现象

  • 物理备份拷到备机，pg_ctl start 一启动就 FATAL: could not locate required checkpoint record。

原因

  • pg_basebackup 默认需要 --wal-method=fetch|stream 把备份期间产生的 WAL 一起拿回来，否则备份期间的 WAL 缺失导致无法一致。

解决 / 预防：pg_basebackup --wal-method=stream --slot=slot_backup；再配 archive_mode=on + archive_command 双保险。

10.3 从不演练恢复 ​

现象

  • 备份跑了两年，真要灾难恢复时才发现备份损坏 / 缺脚本 / 复原要 12 小时远超 RTO。

解决 / 预防：备份不演练等于没备份；至少每季度全量演练一次"从备份恢复到可对外提供服务"；记录 RTO / RPO 做差距分析；每周 pg_restore --list / 随机抽 1 张表恢复校验。

我自己怎么搭建监控告警来提前发现坑 ​

把上面这些坑翻译成监控指标，下面是最小可用清单（Prometheus + postgres_exporter + Grafana 即可落地）。

指标告警阈值对应坑
长事务数（xact_start > 5min）> 0 WARN，> 3 CRIT1.1, 1.2, 1.4
idle in transaction 会话数> 5 WARN1.2
max(age(datfrozenxid))> 5 亿 WARN，> 10 亿 CRIT1.3
死元组占比 n_dead_tup/(n_live+n_dead)> 20% WARN1.1, 6.1
last_autovacuum 距今大表 > 24h WARN6.1
temp_bytes 速率异常飙升 WARN7.3
复制槽滞留大小> 10GB WARN，> 50GB CRIT5.1
replay_lag> 30s WARN5.2
备库存活数（state='streaming'）< 期望值 CRIT5.2
连接数占比 numbackends/max_connections> 80% WARN7.2
未授予锁 / 锁等待数持续 > 5 CRIT1.1, 4.4
deadlocks 新增每分 > 0 WARN4.4
索引命中率< 95% WARN7.1
表 / 索引大小环比一夜 > 30% WARN1.1, 2.5
pg_wal 磁盘使用率> 80% CRIT5.1
慢查询 mean_exec_time TopN超业务 SLO 即报2.*, 6.3
关键参数漂移（fsync 等）偏离基线 CRIT7.4

加分项

  • CI 加 SQL lint：检测 CREATE INDEX 无 CONCURRENTLY、numeric 无精度、函数包裹列等反模式；
  • 建 ops_runbook.md：每条 CRIT 告警对应"排查 SQL + 应急处理 + 根因分析模板"；
  • DDL/VACUUM/REINDEX 前有锁范围预估清单。

📌 最后一句话：PostgreSQL 的坑大多数都是"MVCC 的代价 + 配置的自由度 + 对 MySQL 经验的惯性"造成的。理解了 MVCC / WAL / 锁 / autovacuum 四条主线，加上这份 30 条清单，你基本已经绕开了 80% 的生产事故。