★ FEATURED ARTICLE 工作里跟 PostgreSQL 打交道久了你会发现真正天天用的其实就两块——改表结构DDL和操作数据DML。写个 SELECT、插入几行数据都不难可一旦碰上“给线上大表加字段”“批量更新几十万行”“DELETE 没删干净反而把表撑大了”这类场景很多人就开始没底。这篇文章整理的是我日常反复用到、也反复踩坑的 PostgreSQL 常见 DDL 和 DML 操作语法、适用场景、注意事项都会讲到更重要的是每一条我都会解释“为什么这么做”。适合刚从 MySQL 转过来的开发也适合每天跟数据库打交道的运维和 DBA。不会涉及太冷门的功能基本围绕高频操作展开但无论你是新手还是老手应该都能从中读出点有用的东西。先说一个 PostgreSQL 和很多数据库不一样的地方它的 DDL 操作是支持事务回滚的。这意味着你执行一半发现建错表、改错字段直接 ROLLBACK 就行不会留下残局。这个特性在实际运维里帮过我大忙后面我会专门讲。现在先从最基础的建表开始。1. 先聊 DDL建表、改表、删表的正确姿势1.1 建表时容易被忽略的细节建表看着简单但生产环境里很多坑都是建表阶段埋下的。我习惯把表设计当成“数据结构设计”来做而不是简单地把需求字段罗列上去。CREATE TABLE users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username text NOT NULL, email text , status smallint NOT NULL DEFAULT 1, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (username) );这段建表语句里藏了几个关键选择。第一个是主键类型。bigint配GENERATED ALWAYS AS IDENTITY是我现在的默认配置替代了老项目里的SERIAL。区别在于 IDENTITY 更符合 SQL 标准而且不允许用户手动插入值覆盖自增列除非用OVERRIDING SYSTEM VALUE用起来更省心。如果是像订单、流水这种日志型数据就别用自增主键了用业务订单号或时间戳组合唯一键更合适。第二个是字段类型。text在 PostgreSQL 里和varchar(n)并没有性能差异不需要为了“节省空间”去限制长度。你只需要在真正需要约束长度的地方比如用户名用VARCHAR(32)其余一律text就行。status这类状态字段我建议用smallint而不是int虽然只差两个字节但表大了之后每一行省一点累计起来也很可观。第三个是默认值。created_at timestamptz NOT NULL DEFAULT now()这种写法属于基本操作但我见过不少人漏掉NOT NULL导致业务代码里出现空值判断非常被动。字段的默认值尽量在数据库层做兜底不要依赖应用层传入否则一旦应用有 bug脏数据就进来了。建表阶段还有一个容易被忽略的点表的存储参数。对更新非常频繁的表可以考虑调整FILLFACTOR比如设定为 70 或 80。它的作用是让每个数据页预留一部分空闲空间这样更新行时新版本可以放回原位减少索引分裂和表膨胀。默认值 100 适合只读或插入为主的表如果你有个表 UPDATE 特别多建表时可以带上CREATE TABLE order_flow ( id bigint PRIMARY KEY, data jsonb ) WITH (fillfactor 80);这个参数不是拍脑袋定的调低以后页内预留空间变大空间占用会小幅上升但能明显减少更新时的行迁移。具体多少合适要看业务一般 70 到 85 之间比较常用。1.2 ALTER TABLE 高频操作与锁表风险线上改表结构最常干的几件事是加字段、改字段类型、改默认值、加约束。这里有一个我特别想强调的认知ALTER TABLE 的每一种操作背后对应的是不同程度的表锁和是否重写表。先看加字段ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login timestamptz;IF NOT EXISTS是防止重复脚本报错的保险迁移脚本里加上没坏处。如果是加一个带默认值的字段PostgreSQL 11 之前会全表重写行数多的时候相当要命11 之后只要默认值是一个非常量表达式比如now()就不会触发重写瞬间完成。这个改进让“给大表加默认值字段”从高危操作变成了低风险操作。再看改字段类型ALTER TABLE users ALTER COLUMN status TYPE int;这个动作在绝大多数情况下会触发全表重写因为行内二进制格式变了。重写期间表会被锁住应用侧的写入会全部阻塞。所以我的原则是改字段类型之前先确认这条 SQL 会不会导致表重写。怎么确认看数据量如果表只有几万行无所谓如果表有上千万行就要考虑别直接在生产环境跑。更稳妥的思路是先加一个新字段写双写逻辑数据迁移完再切换最后删旧字段。虽然工程量大但对核心业务表来说值得。有人问“我改个 varchar(20) 到 varchar(50)也算重写吗”算因为 PG 的 varchar 长度限制是元数据的一部分类型变更都会触发重写。所以建表时字符串类型直接上 text 或给足余量能少很多后面的事。改名操作没啥风险ALTER TABLE users RENAME COLUMN last_login TO last_seen_at;要注意的是改名后旧的触发器、视图、外键会受影响尤其是视图和函数里引用了这个列名容易在查询时报column does not exist。迁移前先查一下依赖关系。1.3 DROP 与 TRUNCATE 的实际区别很多新手会把 DELETE、TRUNCATE、DROP 混在一起讲但在 PostgreSQL 里它们的语义差别很大。DROP TABLE users是删除整张表结构及数据不可回滚除非你在事务内执行了这行并且 ROLLBACK。它的潜台词是这个表以后不再用了。如果这个表被外键引用DROP 会报错需要先处理依赖或者用DROP TABLE xxx CASCADE强制删除关联对象。我强烈建议别随手写 CASCADE因为你不知道它会连带删掉哪些视图或触发器。有一次同事为了删一张废弃表写了个 CASCADE结果把旁边一个统计视图也带没了回滚倒是能回滚但线上已有一阵子查询异常。TRUNCATE TABLE users是清空表数据保留表结构速度比DELETE FROM users快几个数量级。原因在于 TRUNCATE 是直接标记数据页为可用不逐行写日志而 DELETE 每行都要生成事务日志还要处理索引项。TRUNCATE 还有一个隐藏行为它会重置 IDENTITY 或 SERIAL 序列。也就是说清空后插入的新数据主键会从 1 重新开始。有些业务可能期待这个行为有些则完全不希望比如流水表要保证全局主键不重复。如果不想重置序列可以这么做TRUNCATE TABLE users RESTART IDENTITY; -- 明确重置 TRUNCATE TABLE users CONTINUE IDENTITY; -- 不重置在 PostgreSQL 14 之前默认行为是不重置但14以后默认仍是不重置。为了避免歧义我习惯明确写 CONTINUE IDENTITY 或 RESTART IDENTITY防止不同环境行为不一致。TRUNCATE 还支持CASCADE会递归清空所有通过外键引用本表的表。这个要比 DROP CASCADE 更危险我建议永远不要在外键链复杂的表上直接用先看关联再操作。2. 索引、约束与分区DDL 里的进阶操作2.1 创建索引的锁表问题与 CONCURRENTLY建索引是 DDL 里最容易被低估的操作。普通CREATE INDEX在 PostgreSQL 里执行时会获取表的SHARE锁这段时间内其他事务不能写数据只能读。对小表没啥感觉对千万级甚至亿级的大表可能要锁很久业务写入会积压、超时。解决方式就是CREATE INDEX CONCURRENTLY idx_users_email ON users (email);并发创建索引不会阻塞表的读写但有两个代价耗时比普通创建更长而且如果创建过程中出了问题会留下一个无效索引。更麻烦的是CREATE INDEX CONCURRENTLY不能在事务块内执行。所以我的做法是准备好这条 SQL在一个单独的连接里跑并配合监控视图观察进度SELECT phase, round(blocks_done::numeric / nullif(blocks_total, 0) * 100, 2) AS progress FROM pg_stat_progress_create_index;如果建到一半确实失败了清理残留索引用DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;这里加不加 CONCURRENTLY 也有讲究。普通 DROP INDEX 会锁表并发环境下建议也用DROP INDEX CONCURRENTLY但同样不能在事务块里执行。我见过不少线上事故都是加索引没加 CONCURRENTLY 引起的所以给大表加索引的 SQL 模板我都是写死带 CONCURRENTLY 的。还有一个常见问题是索引建了但查询没走。多半是表达式不匹配。比如查询条件是WHERE lower(email) xy.com而你建的是普通(email)索引就完全用不上。这种情况需要建表达式索引CREATE INDEX CONCURRENTLY idx_users_email_lower ON users (lower(email));建完以后可以顺手检查一下索引是否被使用在低峰期跑一下EXPLAIN ANALYZE看看索引扫描是不是真的生效了。2.2 给大表加约束NOT VALID 和 VALIDATE 的价值加了字段之后往往还要加约束比如非空、唯一、外键。直接加约束时PostgreSQL 会扫描整张表校验现有数据是否满足约束。对超大表来说这个校验期间会持有锁影响很大。我们可以用分两阶段的方式把锁时间控制在窗口内。第一阶段添加约束但不校验存量数据ALTER TABLE users ADD CONSTRAINT users_email_not_null CHECK (email IS NOT NULL) NOT VALID;这个操作很快因为它只登记了约束定义不去检查现有行。新写入的数据从此会被约束拦住但存量脏数据暂时不管。第二阶段在业务低峰期做校验ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;VALIDATE 期间虽然也要扫描表但不会阻塞写操作只是会增加一些 I/O 负载。这个技巧尤其适合给千万级大表加非空约束、CHECK 约束的场景。带外键的约束也可以用同样策略比如ALTER TABLE orders ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;外键本身的插入/更新开销会在数据行操作时产生但存量校验被延迟后上线压力小很多。唯一约束没有这种 NOT VALID 方式因为要保证唯一性必须维护索引结构通常只能直接在低峰创建 UNIQUE 索引用 CONCURRENTLY失败还能清理重试。2.3 分区表的 DDL加列、删区、挂载现在不少业务已经在用 PG 原生的声明式分区早期版本分区体验很差但从 PostgreSQL 12 起稳定性已经很好了。分区表的 DDL 有几个地方和普通表不一样值得单独说。创建分区表CREATE TABLE events ( event_id bigint GENERATED ALWAYS AS IDENTITY, created_at date NOT NULL, payload jsonb, PRIMARY KEY (event_id, created_at) ) PARTITION BY RANGE (created_at); CREATE TABLE events_2025_01 PARTITION OF events FOR VALUES FROM (2025-01-01) TO (2025-02-01);注意主键这一行我是故意把created_at放进主键里的因为 PostgreSQL 分区表上的 UNIQUE 索引必须包含分区键。这是为了保证能在每个分区内实现全局唯一性不包含分区键的唯一约束是不允许的。很多刚上手的人在这条规则上栽过跟头。加字段时普通表一句话搞定分区表也差不多ALTER TABLE events ADD COLUMN user_id bigint;这个命令会自动把字段加到所有子分区上所以实际执行时如果已有大量分区也需要关注锁和耗时。如果你想对某个具体分区单独加字段语法上是可以的但查询读取父表时并不会感知这个额外字段所以业务上不要这么干。删除旧分区我推荐直接 DROPDROP TABLE events_2025_01;比 DELETE 干净利落数据文件直接释放效率高得多。注意如果有 DEFAULT 分区新范围的数据会不声不响地落到 DEFAULT 分区里等你某天发现某个月的数据跑到 DEFAULT 分区再去拆分就很麻烦。建议两边排查一是定期检查 DEFAULT 分区有没有数据二是监控分区键边界。还有一个用得比较多的操作是把一张独立表挂成分区CREATE TABLE events_2025_02 (LIKE events INCLUDING DEFAULTS INCLUDING CONSTRAINTS); ALTER TABLE events ATTACH PARTITION events_2025_02 FOR VALUES FROM (2025-02-01) TO (2025-03-01);挂载前 PG 会校验数据是否落在分区范围内数据多的时候这一步也不轻。反过来要拆出去用DETACH PARTITIONALTER TABLE events DETACH PARTITION events_2025_02;这个操作在 PG 17 之后支持了FINALIZE但日常用的话知道 DETACH 会把分区变成一个普通表就够了。业务迁移时经常用这招先拆出去独立处理处理完再挂回来。3. DML 核心增删改里那些大杀器3.1 批量插入与 UPSERT 的高效姿势DML 里最常见的是 INSERT。单行插入聊价值不大真正要花心思的是批量插入。很多人写循环一条一条 INSERT或者一次性拼一个几千行的 VALUES 列表这两种都不是好方案。一次性拼大 VALUES 列表的问题在于单条 SQL 太长解析开销巨大而且一旦插入中间出错整批回滚。我更推荐分批插入每批 500 到 2000 行。这个区间是我测过比较稳定的范围批次太小时网络往返开销高批次太大时一个长事务锁定的行数太多容易阻塞同表其他操作。如果数据是从文件或接口大量导入直接上 COPYCOPY users (username, email, status) FROM /path/to/users.csv WITH (FORMAT csv, HEADER true);COPY 比 INSERT 快好几倍因为绕开了很多 SQL 层的解析和计划生成逻辑。不过生产环境一般用的是pg_copy或各类 ETL 工具的 COPY 封装原理都是这个。然后再看 UPSERT。业务里常见的“存在就更新不存在就插入”PostgreSQL 用ON CONFLICT处理INSERT INTO users (username, email, status) VALUES (alice, aliceexample.com, 1) ON CONFLICT (username) DO UPDATE SET email EXCLUDED.email, status EXCLUDED.status;EXCLUDED代表本次插入尝试但被冲突挡住的行。这里有个隐藏细节ON CONFLICT (username)必须对应一个真实的唯一约束或索引。如果 username 上只有普通索引没有唯一约束这个语法直接报错。另一个坑是如果冲突发生在其他唯一约束上比如 email 也建了唯一索引那么并发插入时可能报“ON CONFLICT does not match any unique constraint”解决方式是列出你想捕获的冲突目标或者干脆省略冲突目标写成ON CONFLICT DO UPDATE SET ...但那样所有唯一冲突都会触发更新容易产生非预期结果。我的建议是尽量把冲突目标写明确。3.2 RETURNING 让 DML 不再白跑一趟PostgreSQL 的RETURNING是我最舍不得换掉的功能。用它可以在一条语句里把操作影响的行返回出来省掉一次额外的 SELECT。最常见的场景是插入后拿自增主键INSERT INTO users (username, email) VALUES (bob, bobexample.com) RETURNING id, created_at;以前用 MySQL 的时候要插入后调LAST_INSERT_ID()然后再查一次。用 PG 的 RETURNING一步到位而且拿到的就是这一行真实数据不会因为并发插入读错别人的主键。删除前归档这个场景也特别好用。假设你要删除一批满足条件的用户同时把它们的快照存到审计表WITH deleted AS ( DELETE FROM users WHERE status 0 AND created_at now() - interval 3 years RETURNING id, username, email, status ) INSERT INTO users_archive (id, username, email, status, deleted_at) SELECT id, username, email, status, now() FROM deleted;这条语句在一个事务里完成“删除 归档”不需要先 SELECT 再 DELETE中间没有时间窗口不会漏数据。我在做数据清理任务时非常依赖这种写法。UPDATE 加上 RETURNING 也常用但要记住一点UPDATE 的 RETURNING 返回的是更新后的新行不是旧值。如果你需要旧值做对比得在 UPDATE 之前先把旧值查出来或者用触发器别指望 RETURNING 给你旧值。有一次我在脚本里准备用RETURNING status拿到更新前的状态结果拿到的全是新状态排查了半天才注意到文档里的这句提醒。3.3 UPDATE 与 DELETE 的性能优化写法单表 UPDATE 按主键改没什么可说的但业务里经常出现“按另一张表的条件批量更新”的需求很多人会写成逐行 UPDATE性能很差。正确的做法是用 UPDATE ... FROM 把关联查询写在一条 SQL 里UPDATE users u SET status t.new_status FROM tmp_user_status t WHERE u.id t.user_id AND t.new_status IS NOT NULL;这里FROM tmp_user_status t不是交叉连接它的作用类似于给 UPDATE 提供关联行集合。PG 会把它优化成类似 JOIN 的执行计划一般走的是哈希连接或索引连接性能远好过逐条 UPDATE。注意 WHERE 条件必须写清楚不写 WHERE 的话会把 FROM 表里的每一行和 users 做笛卡尔匹配极容易批量改错数据。DELETE 也有对应的 FROM 写法DELETE FROM orders o USING refund_orders r WHERE o.id r.order_id AND o.refunded false;这里的语义是从 orders 里删除那些在 refund_orders 中存在且未打标为已退款的订单。复杂业务里这条语句可能涉及多表关联但记住它和 UPDATE FROM 一样关联关系和过滤条件一定要理清楚。还有一个细节值得注意大批量 UPDATE 或 DELETE 之后数据页里会产生大量空洞。如果你更新的行数占全表 20% 以上建议马上VACUUM (ANALYZE)一下。不要等 autovacuum 自己触发高负载下它经常跟不上。我在一次清理千万级订单表时删完 80% 数据后 VACUUM 就花了挺长时间但如果不做后续查询计划会因为统计数据偏差走得稀烂。4. 高并发场景下的坑与排查实录4.1 ALTER TABLE 卡住先查锁等待线上最让人头疼的就是执行一条 ALTER TABLE然后它一直不返回。绝大多数情况不是命令本身慢而是在等锁。PostgreSQL 的锁等待机制决定了后来者会被先来的事务挡住。如果有一个长事务一直没提交比如某个后台任务里开了事务忘了提交那么后续所有对同一张表的 DDL 都会排队。排查手段是查pg_stat_activity和pg_locksSELECT pid, state, wait_event_type, wait_event, query, now() - query_start AS query_duration FROM pg_stat_activity WHERE state idle ORDER BY query_start;重点关注wait_event_type Lock的那些会话。找到持有锁的源头进程后再决定是让它跑完还是把它 terminate。如果是误操作或业务长事务可以安全终止SELECT pg_terminate_backend(pid);终止进程会造成那个事务回滚业务侧需要重试但好过让整条表的 DDL 一直卡住。还有一种办法是提前预防在 DDL 语句里设置锁等待超时SET lock_timeout 5s; ALTER TABLE users ADD COLUMN mobile text;这个连接上如果 5 秒内拿不到锁直接放弃执行并报错。在自动化脚本里这是一个很实用的保护机制至少不会让任务无限期挂起。4.2 DML 引发的表膨胀与 autovacuum 跟不上PostgreSQL 的多版本并发控制MVCC机制决定了被 UPDATE 或 DELETE 的行不是物理删除而是标记为旧版本。旧版本行要等事务快照不再需要了再由 VACUUM 清理。如果你的业务里大量 UPDATE 同一批行又没有及时 VACUUM表就会越来越大查询越来越慢。这个现象叫“表膨胀”。排查表膨胀可以通过 pg_stat_user_tablesSELECT relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2) AS dead_ratio FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;当死元组比例超过 20% 甚至更高时基本可以断定 autovacuum 没跟上。常见原因有三个一是 autovacuum 阈值设得太保守二是有长事务在运行导致旧版本无法被清理三是系统 I/O 压力太大。对应的解决思路是先杀掉或等待长事务结束再手动执行VACUUM (ANALYZE, VERBOSE) users;这个操作会清理死元组并更新统计信息。如果膨胀已经非常严重比如表膨胀到原有空间的好几倍VACUUM 也没法把物理空间完全还给操作系统只能重建表ALTER TABLE users SET ( autovacuum_vacuum_scale_factor 0.01, autovacuum_vacuum_threshold 1000 );把参数调低之后清理频率会提高适合高并发更新表。不要滥用因为频繁 VACUUM 也会带来 CPU 开销。4.3 事务性 DDL 与 CONCURRENTLY 的边界开头我提到 PostgreSQL 的 DDL 可以回滚这个特性特别适合做变更窗口内的试错。你可以这样操作BEGIN; ALTER TABLE users ADD COLUMN mobile text; ALTER TABLE users ALTER COLUMN mobile SET NOT NULL; -- 发现不合适直接回滚 ROLLBACK;表上不会留下任何痕迹。这在 MySQL 里是做不到的MySQL 的 DDL 一旦执行即使后面 ROLLBACK 也不会还原结构变更。所以每次做复杂迁移我习惯先 BEGIN执行完所有 DDL检查没问题再 COMMIT。这个习惯帮我避免过好几次“改到一半发现约束搞错了”的尴尬。但要注意CREATE INDEX CONCURRENTLY和DROP INDEX CONCURRENTLY不能放在事务块里执行因为它们内部需要自己提交多个事务。如果硬要在事务里跑会直接报错。另外某些 ALTER TABLE 和 VACUUM 在事务里的行为也有限制。所以事务性 DDL 是保护伞不是万能符用之前先确认命令是否支持。4.4 高频报错速查与实践清单日常运维中下面这些报错出现频率最高我整理成了一张速查表。报错信息常见原因处理方法duplicate key value violates unique constraint插入的数据与唯一索引冲突检查是否存在并发重复写入是否误用 ON CONFLICTdeadlock detected多个事务以不同顺序锁定同一批行统一事务内对象操作顺序缩短事务时间canceling statement due to statement timeout语句执行超过 statement_timeout检查是不是锁等待或慢查询拆分大事务cannot alter type of a column used by a view or rule被视图依赖的列修改类型先 DROP 关联视图或修改依赖operator does not exist: bigint text字段类型与参数类型不匹配显式转换WHERE id text_col::bigintpermission denied for table xxx账号无表权限GRANT SELECT/INSERT/UPDATE/DELETE ON xxxthere is no unique or exclusion constraint matching the ON CONFLICT specificationON CONFLICT 目标没有唯一约束查看表上是否有对应的 UNIQUE 索引这张表只是抛砖引玉实际场景千奇百怪但排查思路是一致的先看错误信息里的对象和行为再连到 pg_stat_activity 或日志里定位上下文。不要一上来就重启连接或者重建表多数数据库问题都是逻辑层面而不是物理层面的。还有一条实践经验执行任何可能影响线上的 DDL 和大型 DML 前先看看数据库当前负载。用pg_stat_activity数一下活跃会话和长事务如果高峰期并发很高宁可在凌晨窗口执行。这个习惯比任何高级运维工具都管用。最后再分享一个让我印象深刻的场景。某次我需要对一张超过两千万行的业务表增加一个新字段并设置 NOT NULL 和默认值。当时正好赶上业务低峰期我先在预发布环境执行了一遍确认 PostgreSQL 11 之后的常量默认值不会重写表然后到生产环境用一条 SQL 完成变更整个操作不到一秒。但后来同事在另一张用 MySQL 的类似表上做同样的事情花了十分钟期间写入全部阻塞。这就是理解数据库底层行为带来的差别。工作里的经验说白了就是对每条语句背后锁和事务博弈的理解靠这些细节才能在关键时刻不慌。
阅读完成 · 觉得有帮助？
