从入门到擅长 DML，这对 PostgreSQL 16 像是老生常谈，但实际生产环境里，我见过太多次因为一条 UPDATE 漏了 WHERE、一条 DELETE 条件写反引发的线上事故。这系列教程写到第 8 篇，终于把增删改这三板斧掰开来讲。今天这篇不是简单念文档，而是结合我自己在 PostgreSQL 16 上的日常使用，把 INSERT、UPDATE、DELETE 的语法细节、背后原理、性能陷阱、实战案例一次说透。刚学会 SELECT、准备上手写业务代码的读者，或者已经写了一段时间但总感觉 DML 不够稳的开发者，这篇都值得认真读一遍。
1. 数据维护三件事：为什么 DML 操作值得掰开揉碎地学
1.1 从 SELECT 思维到 DML 思维的转变
很多新手学完 SELECT 就觉得数据库入门了，这是挺危险的错觉。SELECT 属于"读"的范畴，它最多让你的数据库累一点；而 INSERT、UPDATE、DELETE 属于"写"的范畴，每一次写操作都在改变数据库的持久状态。读错了可以重查一遍，写错了就是数据被污染，可能在几小时后、几天后、甚至几个月后才爆出问题。
我见过最快的翻车案例是这样的：同事写了一段 UPDATE，想把某个字段的空值补上，结果漏了 WHERE 条件，整张表几千行全被改成了同一个默认值。当时系统没有立即报错，直到次日出报表才发现数据全乱了。好在有备份，花了两个小时才恢复。从那以后我养成了一个习惯：凡是 UPDATE 和 DELETE，先写 SELECT 版本看影响范围，再加 RETURNING 做执行确认，最后才落库。这个习惯后面会详细展开。
DML 和 SELECT 还有一个本质区别：写操作会触发一连串的副作用。行锁、MVCC 多版本、索引更新、约束检查、触发器、外键联动、WAL 日志写入，每一步都有开销。同样是一条语句，写操作比读操作慢一个数量级是正常现象。所以在设计写入逻辑时，考虑的不只是"能不能写对"，还有"写得多快""会不会锁冲突""事务要开多久"。
1.2 PostgreSQL 16 在 DML 层面的改进对日常开发意味着什么
先说清楚：PostgreSQL 16 没有在 DML 语法上搞什么惊天动地的革命，用 INSERT 插入数据的基本写法跟前几个大版本没有变化。但 16 对写密集场景的底层支撑做了不少实打实的改进，其中有两个对日常开发影响最大。
第一个是 vacuum 冻结操作的 I/O 开销大幅度降低，这在官方 release notes 里是很靠前的改进点。数据库里的行被 UPDATE 或 DELETE 之后，旧版本不是立刻物理删除，而是留给 vacuum 后台回收。如果表很大、历史变更很多，vacuum freeze 会扫描大量页面并产生不少 I/O。16 换了一种更聪明的处理方式，旧表、大表的冻结维护明显变轻。这意味着跑了很多 UPDATE/DELETE 的老库，升级到 16 之后维护成本会更低，升级前后体感差异能感觉得到，特别是在凌晨自动 vacuum 的时间窗口上。
第二个是大量并发写入场景下的性能优化，16 在多核并行处理 WAL 写入和索引插入上的表现比 15 更稳。对业务系统来说，高并发下单、库存扣减、批量状态更新这类写密集场景，是能直观感受到吞吐量改善的。所以如果你是做业务开发的，不用总是怀疑 PostgreSQL 性能不如商业数据库，很多瓶颈其实出在 SQL 写法本身，写不好，再强的引擎也白搭。
2. 插入数据：从单行 INSERT 到高效批量导入
2.1 手写 INSERT 的基础语法：显式列名比省事更重要
INSERT 有三种主要写法，最常见的是列名加 VALUES：
INSERT INTO users (name, email, created_at) VALUES ('张三', 'zhangsan@example.com', now());也可以完全省略列名：
INSERT INTO users VALUES (102, '李四', 'lisi@example.com', now());第二种写法看起来省事，但我强烈不建议在生产代码里这么写。原因很简单：一旦表结构发生变化（比如新增了一个字段、调整了字段顺序），这种省略列名的 INSERT 会静默地把数据写错位置，或者直接报错。显式列名就像是给数据装了固定坐标，无论表结构怎么调整，只要列名还在，数据就会进入正确的位置。
日常工作中还有几个容易被忽略的写法：
- 想让某列使用默认值时，在 VALUES 里写
DEFAULT，而不是传一个 NULL。两者语义完全不同。 - 想插入 NULL 而列定义是 NOT NULL 时，会直接报约束错误，这是好事。NULL 是"未定义"，空字符串是"有值但为空"，DML 设计里这两者不能混为一谈。
- 一次插入多行时，VALUES 后面跟多组括号，逗号分隔，这是最简单直接的批量写法：
INSERT INTO users (name, email) VALUES ('王五', 'wangwu@example.com'), ('赵六', 'zhaoliu@example.com'), ('钱七', 'qianqi@example.com');PostgreSQL 16 里我推荐把自增主键用integer GENERATED ALWAYS AS IDENTITY来定义（或generated by default），这是比serial更符合现代 SQL 标准的做法。用 IDENTITY 列时，插入语句完全不用关心 id 字段，数据库会自动分配。
2.2 RETURNING 子句：把"插完再查"变成"插完就拿"
传统写法里，插入一条数据后，如果要拿到数据库生成的自增主键和时间戳，还得再执行一条 SELECT，这是非常典型的冗余操作。PostgreSQL 的 RETURNING 子句就是干这个的：
INSERT INTO users (name, email) VALUES ('孙八', 'sunba@example.com') RETURNING id, created_at;执行这条语句后，数据库直接返回插入行的 id 和 created_at，不用再查一遍。RETURNING 在 PostgreSQL 里对 INSERT、UPDATE、DELETE 都可用，返回的是操作完成后的行数据，后面实战部分会多次用到。
这个特性的价值在异步写入和日志记录场景里特别突出。举例：你插入一条订单，马上要在另一个接口里返回订单编号给前端，RETURNING 拿到 id 之后直接接着用，少一次往返查询，数据库压力和代码复杂度都降下来了。有人可能会说"一次查询而已，也没多大开销"，但别忘了每次查询都有网络往返、解析、计划生成，写高频业务时这些累加起来相当可观。
RETURNING 还可以配合*返回整行数据，但我不建议在生产环境这么干，返回需要的列就够了，减少网络传输和日志量。
2.3 ON CONFLICT：让插入逻辑具备幂等能力
业务系统里最常见的插入场景之一就是"数据可能已存在，要么跳过、要么更新"。PostgreSQL 提供ON CONFLICT子句来优雅处理唯一约束冲突，这个功能在后端开发里实用性极高。
先看最简单的DO NOTHING：
INSERT INTO users (email, name) VALUES ('zhangsan@example.com', '张三') ON CONFLICT (email) DO NOTHING;只要 email 上有唯一索引，这条语句在碰到已存在的邮箱时会直接忽略，不报错，不中断事务。这个写法特别适合外部数据源同步的幂等操作：同样的数据导两次，不会产生重复记录。
再进阶一点，用DO UPDATE SET实现"存在就更新，不存在就插入"（通常叫 upsert）：
INSERT INTO users (email, name, last_login_at) VALUES ('zhangsan@example.com', '张三', now()) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, last_login_at = EXCLUDED.last_login_at;这里的EXCLUDED是一个特殊的记录，代表"本来打算插入的那行数据"。DO UPDATE SET name = EXCLUDED.name的含义就是：当 email 冲突时，把已有行的 name 改成这次打算插入的 name。这个特性让同步逻辑变得极其简洁，不用先查再判断再决定插入还是更新，一条语句原子完成。
写 ON CONFLICT 时有两点必须注意：
- 冲突判定的列（ON CONFLICT 括号里的列）必须存在唯一索引或唯一约束，否则直接报错"there is no unique or exclusion constraint matching the ON CONFLICT specification"。这是 PostgreSQL 强制要求的，别在没加唯一索引的列上写这个子句。
- 如果是
DO UPDATE SET，WHERE 条件可以做精细化控制，比如只在某些条件下才更新，这样能减少不必要的新版本产生。
2.4 批量插入的性能要诀：COPY 与事务分批
如果你要一次性插入几万甚至几十万行，直接用 INSERT 逐行插入是性能灾难。PostgreSQL 里最快的批量导入方式是COPY命令，它绕过了很多逐行插入的开销。
# 从 CSV 文件导入 COPY users (name, email, created_at) FROM '/path/to/users.csv' WITH (FORMAT csv, HEADER true);如果是程序里大批量写入，也可以用COPY ... FROM STDIN，配合驱动（比如 Python 的 psycopg2 或者 pgjdbc）的批量 API，吞吐量比逐行execute高出数倍。我在处理数据迁移任务时，几十万行的表用 COPY 通常是几秒钟完成，而逐行 INSERT 可能要跑几分钟。
如果业务逻辑还在用 INSERT 拼接多行 VALUES，也有提升空间。原则是：一次事务里尽量不要只插一条，攒够 500~1000 行再执行一次多行 INSERT。1000 条一批和 1 条一批相比，总耗时能差出好几倍，因为每条语句的事务提交、WAL fsync、语句解析开销都摊薄了。
还有一个被很多人忽略的点：往一个带多个索引和触发器的表做超大批量插入时，可以先ALTER TABLE ... DISABLE TRIGGER？不对，这个在 PG 里是ALTER TABLE table_name DISABLE TRIGGER ALL，但要注意这在普通用户下不可用，会锁表。更稳妥的做法是分批插入，避免一个超大事务长时间持有锁。关于大事务的问题，第 6 章会专门讲。
3. 更新数据：UPDATE 语法细节与那些容易踩的坑
3.1 基础 UPDATE：表达式更新才是日常主角
UPDATE 的基础语法几乎人人都知道：
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;但实际业务里，大部分 UPDATE 不是"改成固定值"，而是基于原有值做计算。最常见的场景是计数器累加，比如文章浏览数：
UPDATE articles SET view_count = view_count + 1 WHERE id = 123;这个写法在并发情况下也是安全的。PostgreSQL 对单条 UPDATE 的操作是对行加排他锁的，两个并发任务同时执行view_count = view_count + 1时，后到的任务会等先到的提交后再处理，最终结果就是 +2，不会丢失更新。不要试图先把旧值查出来，在应用层加一，再 UPDATE 回去，这中间如果并发，就会出现经典的 "lost update"（丢失更新）问题。
UPDATE 一次更新多列时，表达式是同时计算还是按顺序计算？在 PostgreSQL 中，所有 SET 表达式使用的是该行更新前的旧值。也就是说：
UPDATE items SET quantity = quantity - 1, updated_quantity = quantity WHERE id = 10;这里updated_quantity拿到的quantity是更新前的旧值，不是减一后的新值。很多人第一次写这种 SQL 时会踩这个坑，以为后面一列拿到的是前面一列更新后的结果。要拿到新值，只能让后一列直接引用同样的表达式，或者用 RETURNING。
3.2 关联更新：用 FROM 子句从别的表取数
PostgreSQL 的 UPDATE 支持一个非常实用的FROM子句，可以在更新时引用其他表的数据，这是做关联更新的主力语法：
UPDATE orders o SET status = s.ship_status FROM shipments s WHERE o.shipment_id = s.id AND s.ship_status = 'delivered';这条语句的含义是：把订单表和物流表关联起来，凡是物流记录状态为 delivered 的订单，订单状态同步更新为 delivered。注意FROM子句出现在 SET 之后、WHERE 之前，这是 PostgreSQL 特有的语法结构（MySQL 用的是 JOIN 更新，写法不同）。
FROM后面不限于真实表，也可以是一个子查询。这样能实现更灵活的逻辑，比如用聚合结果更新主表：
UPDATE accounts a SET balance = sub.total_balance FROM ( SELECT account_id, SUM(amount) AS total_balance FROM transactions GROUP BY account_id ) sub WHERE a.id = sub.account_id;这种写法比逐行去应用层计算效率高很多，一条 SQL 完成跨表数据同步。需要注意一点：如果FROM子查询中一个 id 对应多行结果，那么 UPDATE 语句会拿其中一行（不确定是哪一行）来更新主表，这属于隐式行为，容易产生奇怪结果。写关联更新时，务必确认关联键在右侧表是唯一的。
3.3 两个特别容易出错的细节：SET 列限定与别名陷阱
先说 SET 子句里写不写表名。PostgreSQL 官方文档明确不建议在 SET 子句中包含表名，实际测试中如果你写：
UPDATE orders o SET o.status = '已发货' WHERE o.id = 101;会直接报错，因为 SET 左侧只接受裸列名。正确写法是：
UPDATE orders o SET status = '已发货' WHERE o.id = 101;这个细节翻过车的人不少。新的开发者经常照着 SELECT 的风格给 SET 列加前缀，结果一执行就是 error。记住：SET 后面写裸列名，WHERE 里才用别名限定条件。
另一个细节和值来源有关。UPDATE 时如果不加 RETURNING，数据库不会告诉你影响了多少行——实际上会的，会返回一个UPDATE count标签，但很多驱动默认不往上抛。
UPDATE orders SET status = '已发货' WHERE id = 101 RETURNING id, status, updated_at;推荐在生产环境的关键更新操作里加上 RETURNING，它既能确认修改是否命中目标行，又能拿到计算后的新值。如果是异步任务在批量更新后需要返回处理结果给上层，RETURNING 更是不可替代。
3.4 UPDATE 的隐藏成本：表膨胀与索引维护
UPDATE 在 PostgreSQL 的 MVCC 机制里不是"原地改"，而是生成一个新的行版本，旧版本行会保留在数据页中，直到后台 vacuum 把它清理掉。这意味着：
- 频繁 UPDATE 会让表空间膨胀，旧版本行占用的空间不会立刻释放。
- UPDATE 涉及索引字段时，要同时维护索引的新版本，增加写放大。
- 更新高频字段的次数越多，vacuum 负担越重，极端情况下会导致表膨胀严重、查询变慢。
所以业务上要尽量避免"反复更新"同一行。有些系统设计喜欢用一个状态字段反复流转，比如订单状态从"待支付"到"已支付"到"已发货"到"已完成"，这是合理需求，但要注意状态字段更新的频率。如果一张千万行的大表每天高频更新同一个字段，就要开始考虑性能监控了。
如果发现表已经膨胀，可以用VACUUM FULL TABLE物理压缩，但那是排他操作，会锁表，必须放在维护窗口执行。平时让它靠 autovacuum 自动维持就好，这也是 PostgreSQL 16 里冻结优化能帮上忙的地方：对老数据冻结效率更高，膨胀回收更及时。
4. 删除数据：DELETE、TRUNCATE 与"删错"的后悔药
4.1 DELETE 基础语法：WHERE 条件就是生命线
DELETE 语法非常简单：
DELETE FROM orders WHERE id = 101;如果不加 WHERE，就是把整张表数据全部删除。我见过一个悲剧：线上环境执行DELETE FROM table_name;后才发现条件漏了，当场整个业务表被清空。这属于"删两次"级的事故，一旦发生，考验的就是备份和恢复能力。
所以 DELETE 的第一守则就一句话：不写 WHERE 的 DELETE，必须人肉审。哪怕你的表只有三条测试数据，也建议养成"先 SELECT 后 DELETE"的习惯。
-- 第一步：确认要删除的是哪些行 SELECT id, status, updated_at FROM orders WHERE status = 'cancelled' AND updated_at < now() - interval '90 days'; -- 第二步：删除前再确认行数和预期一致 DELETE FROM orders WHERE status = 'cancelled' AND updated_at < now() - interval '90 days' RETURNING id;RETURNING 在 DELETE 里也有奇效。它会返回每一条被删除的行，相当于一个"删除清单"。在生产脚本里，可以用它把删除的对象记录写入日志表，方便事后审计。
4.2 关联删除：DELETE 与 USING 的配合
PostgreSQL 的 DELETE 同样支持USING子句进行关联删除：
DELETE FROM orders o USING order_items i WHERE o.id = i.order_id AND i.status = 'closed';这条语句删除所有存在已关闭明细项的订单。比子查询写法直观不少。如果不用 USING，等价的子查询写法是：
DELETE FROM orders WHERE id IN ( SELECT order_id FROM order_items WHERE status = 'closed' );两种写法都可以，但 USING 在涉及多表条件时，执行计划更可控，写起来也更短。注意 DELETE 的别名规则和 UPDATE 不同：DELETE FROM orders o里的o放在表名后面，在 WHERE 和 USING 子句里都能用，这就是 DELETE 别名的作用域规则。
4.3 DELETE 与 TRUNCATE：清空全表时性能与锁的区别
如果想清空整张表，别用 DELETE，用 TRUNCATE：
TRUNCATE TABLE temp_orders;两者的区别对新手来说很容易混淆：
| 对比项 | DELETE | TRUNCATE |
|---|---|---|
| WHERE 条件 | 支持，按条件删除 | 不支持，只能清空全表 |
| 事务回滚 | 支持，可回滚 | 支持，可回滚 |
| 删除方式 | 逐行标记，旧版本留待 vacuum 回收 | 直接重置表存储文件，立即释放空间 |
| 触发触发器 | 会触发行级触发器 | 不会触发行级触发器 |
| 主键序列 | 不会重置 | 默认会重置（可指定 CONTINUE IDENTITY） |
| 锁级别 | 行锁，并发影响小 | 表级排他锁，阻塞所有并发访问 |
TRUNCATE 看起来更"暴力"，但清空全表场景下性能远好于 DELETE，因为它不涉及逐行删除标记和 vacuum 积累。需要重置自增主键时，TRUNCATE 默认就是 RESTART IDENTITY，相当于顺手把序列归零。如果表被其他表外键引用，TRUNCATE 默认会报错，需要加上 CASCADE，但使用 CASCADE 时务必清楚它会把引用表的数据也清掉。
我写过一个教训深刻的案例：某临时表是主表 A 引用的子表，某个清理任务执行TRUNCATE TABLE temp_orders;直接报外键错误。之后改成了TRUNCATE TABLE temp_orders CASCADE;，结果把引用它的 A 表也一起清空，好在是测试环境。所以 TRUNCATE 操作前，先看依赖关系再决定是否用 CASCADE。
4.4 误删数据后的第一时间：别慌，先查事务能不能回滚
误删数据之后的黄金操作顺序值得刻在脑子里：
- 如果删除操作还在一个未提交的事务里，立刻 ROLLBACK，一切恢复原样。
- 如果已经提交，尝试从备份或归档日志做 PITR（point-in-time recovery）恢复。PostgreSQL 配合 WAL 归档和连续归档，可以将数据库恢复到任意时间点。前提是提前做好了基础备份并开启了归档，没有这个前提，误删后找数据会非常被动。
- 如果都没有，部分场景下可以从 WAL 日志用第三方工具解析出原始 SQL 和数据内容，但步骤繁琐且不保证成功，不能作为常规手段依赖。
所以日常开发阶段就要养成在一个事务里做变更、验证、提交的习惯：
BEGIN; DELETE FROM orders WHERE status = 'test'; SELECT count(*) FROM orders WHERE status = 'test'; -- 确认删除效果 ROLLBACK; -- 或者 COMMIT5. 综合实战：一个订单状态流转的数据维护场景
5.1 场景设定
光讲语法不够，现在我用一个电商订单状态维护的例子把这些语法串联起来。假设有两张表：
CREATE TABLE orders ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_no text NOT NULL, status text NOT NULL DEFAULT 'pending', total_amount numeric(10,2) NOT NULL, updated_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE order_logs ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id integer NOT NULL REFERENCES orders(id), status text NOT NULL, logged_at timestamptz NOT NULL DEFAULT now() );业务需求是：每天从外部物流系统拿到一批发货信息，需要把对应订单状态更新为 shipped，同时往日志表里追加一条状态变更记录。
5.2 用带 RETURNING 的 INSERT 落库新订单
新订单进来时，用 INSERT 写入并返回数据库生成的 id 和默认时间，方便后续组装业务对象：
INSERT INTO orders (order_no, total_amount) VALUES ('SN20250101001', 199.00) RETURNING id, order_no, status, created_at::text;实际业务中经常是一个接口接收多笔订单，用多行 VALUES 一次插入：
INSERT INTO orders (order_no, total_amount) VALUES ('SN20250101002', 299.00), ('SN20250101003', 99.00), ('SN20250101004', 599.00) RETURNING id, order_no;RETURNING 会返回所有插入行的 id 和单号，拿到后可以直接在应用层继续加工，不用再回表查询。
5.3 更新订单状态并自动写日志：CTE 组合拳
这是最有实战价值的一个技巧。过去我们写订单状态更新，需要两条 SQL：一条 UPDATE 改状态，一条 SELECT 或者 INSERT 写日志。在多并发环境下，两条 SQL 之间数据可能不一致，而且逻辑重复。PostgreSQL 支持把UPDATE ... RETURNING放进 CTE 里，再用其结果直接 INSERT 日志，一条语句完成"更新主表 + 写日志"。
WITH updated_orders AS ( UPDATE orders SET status = 'shipped', updated_at = now() WHERE order_no IN ('SN20250101001', 'SN20250101002') RETURNING id, order_no, status, updated_at ) INSERT INTO order_logs (order_id, status, logged_at) SELECT id, status, updated_at FROM updated_orders;这条语句的执行顺序是：先执行 WITH 里的 UPDATE，把订单状态改成 shipped，并 RETURNING 返回被更新行的 id、状态和时间；然后这些返回值直接作为数据源插入日志表。整个逻辑在一条语句、一个事务里完成，要么全部成功，要么全部回滚，不会出现"主表改了但日志没写"的中间状态。
我经常把这种写法推荐给刚接触 PostgreSQL 的开发者，它比在应用层先 UPDATE 再 INSERT 优雅太多。CTE 里不仅能放 UPDATE，还能放 DELETE，组合起来可以实现复杂的级联变更逻辑，而且性能上也不会比分开执行差。
5.4 用 ON CONFLICT 处理物流信息幂等回填
订单发货后，物流系统会推送运单号。有时候推送接口重试了多次，同一个订单可能收到重复的更新请求。用 ON CONFLICT 能保证逻辑幂等：
INSERT INTO shipments (order_id, tracking_no, carrier) VALUES (1001, 'SF1234567890', '顺丰') ON CONFLICT (order_id) DO UPDATE SET tracking_no = EXCLUDED.tracking_no, carrier = EXCLUDED.carrier;前提是 shipments 表的 order_id 上有唯一约束。这样即使同一订单被推送几百次，数据库也只会保留一条最新物流记录。在对接外部系统的场景里，这个模式非常实用，既不会报错打断流程，也不会产生重复数据。
5.5 清理过期数据：DELETE 与 TRUNCATE 的分工
最后是数据清理。业务上通常会约定：订单完成后 90 天，把明细数据迁移到归档表，并从主表删除。这时候 DELETE 配合条件删除，在事务里分批执行是标准做法。
先删除过期且已完成的订单日志，再删除主订单：
BEGIN; DELETE FROM order_logs WHERE order_id IN ( SELECT id FROM orders WHERE status = 'completed' AND updated_at < now() - interval '90 days' ) RETURNING id, order_id; DELETE FROM orders WHERE status = 'completed' AND updated_at < now() - interval '90 days' RETURNING id, order_no; COMMIT;如果是一张维护用的临时表，夜跑任务每次都要清空重灌，用 TRUNCATE 清空比 DELETE 高效得多：
TRUNCATE TABLE tmp_order_export;注意 TRUNCATE 会重置自增序列，如果你的临时表 id 序号不重要，这是可以接受的；如果序号要保持连续增长，需要用TRUNCATE TABLE tmp_order_export CONTINUE IDENTITY;。
6. 写给生产环境的数据变更习惯
6.1 每次变更都要有清晰的事务边界
PostgreSQL 默认每条语句都有隐式事务，但多条变更操作要保证"同生共死"，就必须显式开启事务。下单场景包含扣库存、生成订单、写流水等多次写入，只要其中一步失败，整个事务必须回滚，不能让用户拿到一张订单但库存没扣成功。
事务边界过长的危害比很多人想的大。长事务期间，vacuum 无法清理被变更过的行版本，会导致表膨胀；同时长时间持有锁会让其他会话的更新和删除任务排队阻塞，表现就是数据库"莫名其妙卡住"。所以事务内的业务逻辑要精简，只放必要的变更和验证，别在事务里做耗时的外部调用，比如调用第三方支付接口、发送 HTTP 请求。外部调用的耗时不可控，会让事务悬挂在"已更新、未提交"状态，非常危险。
6.2 大表数据变更的正确姿势：分批处理
假设要对一张千万级订单表做一次大规模状态更新，直接一条 UPDATE 全量更新会产生一个超大事务：锁大量行、WAL 日志暴涨、vacuum 回暖慢，严重影响在线业务。更稳妥的方式是分批执行，每批只处理少量行：
-- 用 ctid 分页，每批处理 1000 行 DELETE FROM orders WHERE ctid IN ( SELECT ctid FROM orders WHERE status = 'obsolete' AND updated_at < now() - interval '90 days' LIMIT 1000 );这里用ctid（行的物理位置标识）来圈定批次，是因为它自带索引，适合做分页。每批次执行完成后，让数据库喘息一下，再执行下一批。配合循环脚本，一个大任务可以被拆成几百个小事务，锁冲突和膨胀问题都能得到控制。
同样，大表 UPDATE 的思路也是一样的。先圈定要更新的 id 范围，按主键区间分批更新，每批控制在几千行，既不会长时间锁表，也不会让 WAL 暴涨。
6.3 几条个人积累的 DML 铁律
最后分享几条我做后端开发和数据库维护这几年总结出来、真正救过命的习惯：
- 所有 DELETE 和 UPDATE 必须先有 WHERE，没有 WHERE 的语句永远不进生产。哪怕逻辑上确实要清空全表，也写成显式的 TRUNCATE，让人一眼看清意图。
- 变更敏感表前，先开事务，后执行语句，用 EXPLAIN 看影响行数，确认无误再 COMMIT。用 SELECT 版本验证影响范围，这个习惯能挡住大多数低级误操作。
- 启用 RETURNING 作为执行凭证。关键变更脚本执行后，把 RETURNING 的结果记录下来，既是审计凭证，也是排错依据。我的脚本里几乎每条 DML 后都会带着数据落日志。
- 高频更新的字段和行，设计上要想办法降低更新频率。比如浏览量可以先在内存/缓存里累加，定期汇总落库，而不是每次访问都 UPDATE 数据库。PostgreSQL 能扛住高并发读，但高并发"反复更新同一行"永远是最容易被击穿的场景之一。
- 版本升级永远不要把核心库裸奔升级。先在一个和线上数据量相当的测试库跑一遍 DML 压力测试，确认 16 的并发写性能表现符合预期，再规划生产升级。
PostgreSQL 16 的 DML 语法并不难，难的是在真实业务场景里用得稳、用得对、用得省。数据维护没有小事，每一次 INSERT、UPDATE、DELETE 背后都是对数据完整性的承诺。把这套知识消化掉，你的数据库写入代码会比之前可靠得多。遇到具体问题别怕翻文档，优先用 EXPLAIN 看执行计划，用 RETURNING 做验证，用事务兜底，数据库的反馈永远比直觉更准确。
