日常如果跟 PostgreSQL 打交道DDL 和 DML 这两个词绝对绕不开。简单说DDL 管的是结构建表、加字段、改类型、删表DML 管的是数据插入、更新、删除、查询。我见过不少同事写业务 SQL 很溜但一到改大表结构、批量更新、误删恢复这种场景就犯怵根本原因是对这两类操作底层的行为差异没吃透——锁、事务、日志、MVCC 这些背后逻辑搞明白了线上操作才敢放手去做。这篇文章把我这几年在项目里反复用到的 PostgreSQL DDL 和 DML 操作完整梳理一遍既有最基础的语法细节也会把容易坑人的锁冲突、事务回滚、表膨胀这些点讲透。打算系统掌握 PostgreSQL 的开发者可以通读日常要维护数据的 DBA 或者后端同学也能直接当操作手册翻。1. 先把DDL和DML的边界理清楚1.1 两个概念到底各管什么很多人刚接触数据库时对 DDL 和 DML 的分类只有一个模糊印象真要说出哪些命令属于哪一类反而会卡壳。这里把边界划清楚DDLData Definition Language数据定义语言操作对象的结构和属性包括 CREATE、ALTER、DROP、TRUNCATE以及 COMMENT、GRANT、REVOKE 这类元数据和权限操作。说白了就是给房子改户型、装修、拆墙。DMLData Manipulation Language数据操纵语言操作对象里的数据内容包括 INSERT、UPDATE、DELETE、SELECT。严格分类时也有人把 SELECT 单独拆成 DQLData Query Language但实际工作里讨论 DML 时经常把查询也带进去本文核心还是落在增删改上。这个分类不止是概念游戏它直接决定了一个人能对数据库做什么。比如我给业务账号授权通常只给 INSERT、UPDATE、DELETE、SELECT 的权限DDL 权限几乎不给。这样即使应用被拖库或者有人误操作他也只能改数据动不了表结构伤害面缩小一大截。1.2 区分它们有什么用我刚开始做运维的时候也觉得这分类无所谓直到线上出了几次事故才明白区分 DDL 和 DML 是判断操作风险的第一依据。第一是锁行为差异。普通的 DML 语句拿的是 RowExclusiveLock表上的读写操作彼此基本不冲突业务可以并发跑。而 DDL 语句比如 ALTER TABLE、DROP TABLE拿的是更高级别的锁通常直接到 AccessExclusiveLock一旦执行就会阻塞这张表上所有的读写。一个是大家排队进出一个是直接封路施工风险等级完全不一样。第二是日志和复制开销。DML 会修改数据页产生大量 WAL这些日志要传给备库、要归档DDL 大多数时候只写系统元数据日志量小很多。但有例外比如 TRUNCATE 虽然不逐行记录数据但它本身也作为事务的一部分写日志而且它对表的锁影响比 DELETE 大得多。第三是变更管理流程。生产环境的表结构变更成熟团队都会走审批、评估锁时间、准备回滚方案本质就是因为 DDL 的风险是结构级的而数据变更更多依赖业务逻辑和 WHERE 条件的准确性。你把这两类操作分清楚后面所有排查和优化的思路就顺了。2. 常用DDL操作怎么用才靠谱2.1 CREATE TABLE建表时就把约束想全建表是 DDL 里最基础的操作但基础不代表随便写。很多人上来就是一把梭字段类型差不多就行约束能省则省结果线上跑了一阵才发现数据质量一塌糊涂。下面这张表基本能代表我日常建表的习惯CREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username VARCHAR(64) NOT NULL, email VARCHAR(255) UNIQUE, status SMALLINT NOT NULL DEFAULT 1 CONSTRAINT chk_users_status CHECK (status IN (0, 1, 2)), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() );几个要点说一下。自增主键我建议优先用GENERATED ALWAYS AS IDENTITY这是 SQL 标准写法比老式的BIGSERIAL更规范权限控制也更干净。如果你用的是高版本 PostgreSQL新表直接上 identity 就好。约束命名很多人会忽略但一旦表上线、数据多了想改约束却不知道约束叫什么名字就得先查系统表非常麻烦。像我上面写的那样把chk_users_status这种有意义的约束名直接写进建表语句后面执行ALTER TABLE ... DROP CONSTRAINT会省很多事。同理外键约束也建议显式命名。字段类型选型这块我的原则是能用数值就不用字符串存数字能用TIMESTAMPTZ就不用TIMESTAMP。TIMESTAMPTZ存的是带时区的时间点写入时自动按数据库时区解释查询时按客户端时区展示逻辑统一不出岔子。我接手过的项目里时间字段用错的例子太多了十有八九是时区问题引发的。如果是超大表建表时直接考虑分区。PostgreSQL 原生支持声明式分区CREATE TABLE logs ( id BIGINT NOT NULL, created_at TIMESTAMPTZ NOT NULL ) PARTITION BY RANGE (created_at); CREATE TABLE logs_2025_01 PARTITION OF logs FOR VALUES FROM (2025-01-01) TO (2025-02-01);分区表后面做数据清理直接DROP PARTITION完事比一条条 DELETE 不知道快到哪里去了。2.2 ALTER TABLE改表结构的坑都集中在锁和重写ALTER TABLE 是日常变更里最容易出问题的操作因为不是所有改结构动作都一样快。同样是加一个字段可能毫秒级完成也可能把几百万行数据的表重写一遍锁表几分钟甚至更久。先看最常见的加字段ALTER TABLE users ADD COLUMN nickname VARCHAR(50);在 PostgreSQL 11 之前如果你加字段时带一个常量默认值比如ADD COLUMN status SMALLINT DEFAULT 1大表会被强制全表重写目的是把默认值物理写入每行那个锁表时间在生产环境能让人血压拉满。PostgreSQL 11 之后做了优化加带常量默认值的字段不再重写表只改元数据执行速度飞快。但注意这个优化只对常量默认值有效。如果你加的是非常量默认值比如DEFAULT gen_random_uuid()或者DEFAULT now()这种PG 没法在元数据层面直接模拟每行的返回值仍然要把表重写一遍。所以对大表加这种字段更稳妥的做法是分三步走-- 第一步先加允许为空的字段不设默认值 ALTER TABLE users ADD COLUMN external_id UUID; -- 第二步分批填充数据不要一条UPDATE刷全表 UPDATE users SET external_id gen_random_uuid() WHERE external_id IS NULL AND id BETWEEN 10001 AND 20000; -- 第三步字段填完后再补默认值和NOT NULL约束 ALTER TABLE users ALTER COLUMN external_id SET DEFAULT gen_random_uuid(); ALTER TABLE users ALTER COLUMN external_id SET NOT NULL;这样每一步持有锁的时间都很短业务基本无感。改字段类型是另一个重灾区ALTER TABLE users ALTER COLUMN id TYPE BIGINT;任何类型转换都伴随着全表扫描和重写有些转换不写 USING 甚至会直接失败比如把VARCHAR改成INTEGERALTER TABLE users ALTER COLUMN age TYPE INTEGER USING age::INTEGER;USING 表达式定义了怎么把旧值转换成新值。改类型前先预估表大小和重写耗时别在生产高峰期干这件事。约束的添加也有讲究。突然给已有大量数据的表加 CHECK 约束或外键约束PG 默认会扫描整表验证期间持有 ACCESS EXCLUSIVE 锁。规范做法是两阶段ALTER TABLE users ADD CONSTRAINT chk_users_age CHECK (age 0) NOT VALID; ALTER TABLE users VALIDATE CONSTRAINT chk_users_age;第一阶段只加约束不校验元数据层面立刻完成锁很短第二阶段 VALIDATE 只需要 ShareLock 级别的锁不阻塞读写。两条命令之间的空档期如果有新写入的非法数据VALIDATE 会失败所以这种方案适合对数据错误容忍度高的场景严格的场景还是要在维护窗口做。2.3 DROP 和 TRUNCATE删东西之前先想清楚删表和清数据的操作风险等级完全不同。很多新手分不清 DROP TABLE 和 TRUNCATE 的区别这里直接列个对比表操作动结构动数据锁级别可回滚触发器DROP TABLE删除删除ACCESS EXCLUSIVE事务内可回滚不触发TRUNCATE保留清空ACCESS EXCLUSIVE事务内可回滚不触发行级触发器DELETE保留按条件删除ROW EXCLUSIVE事务内可回滚触发行级触发器很多人以为 TRUNCATE 是 DML其实在 PostgreSQL 里它属于 DDL 范畴拿的是 ACCESS EXCLUSIVE 锁执行期间所有对该表的读写都会被阻塞。它的优势是速度极快因为不是逐行删除数据而是直接把表的存储文件标记为废弃并重新分配。TRUNCATE 有两个容易踩的坑。第一如果这张表被其他表的外键引用直接 TRUNCATE 会报错提示存在外键依赖。这时候你可以选择TRUNCATE table_a, table_b CASCADE把引用它的表一起清空但这意味着你可能删掉了不该删的数据用之前一定要确认依赖关系。第二TRUNCATE 不触发行级触发器如果你依赖触发器做审计或者级联操作它不会生效。DROP TABLE 更不用说表结构和数据一起没。DROP TABLE xxx CASCADE会连带删除依赖这个表的所有对象比如视图、外键约束这个 CASCADE 我是能不用就不用真要用了也先查好依赖再动手。实操心得删表或清空数据之前先用\d table_name看一眼依赖再用BEGIN; ... ROLLBACK;包一层试运行。我在测试环境踩过 DROP 后才发现视图全没了的坑从那以后 CASCADE 慎用成了铁律。3. 常用DML操作学会少踩坑的做法3.1 INSERT从单条到批量INSERT 看着最简单但性能差距可以非常大。一条一条插入和批量插入之间可能差出几十倍的效率。单条插入不用多说重点是批量场景。批量插入有三种常见姿势-- 方式一一条语句多组VALUES INSERT INTO users (username, email) VALUES (alice, aexample.com), (bob, bexample.com), (carol, cexample.com); -- 方式二从另一张表查出来插入 INSERT INTO users_new (id, username, email) SELECT id, username, email FROM users WHERE status 1; -- 方式三COPY高速导入 COPY users (username, email) FROM /path/to/users.csv WITH (FORMAT csv, HEADER true);方式一减少了解析和提交开销适合几千行以内。方式二适合做表迁移或数据归档。方式三 COPY 是 PostgreSQL 导入文本数据最快的路径比逐条 INSERT 能快一个数量级大批量导数据首选。生产环境经常遇到的需求是有则更新无则插入PostgreSQL 的ON CONFLICT直接搞定不需要先查再决定INSERT INTO users (id, username, email) VALUES (1001, alice, aexample.com) ON CONFLICT (email) DO UPDATE SET username EXCLUDED.username, updated_at now();这里有个前提ON CONFLICT 后面括号里的列必须有唯一约束或唯一索引否则直接报错。EXCLUDED代表当前语句想插入的那行数据用它可以引用新值。INSERT 还有一个很实用的能力就是RETURNING。我经常用它拿到数据库生成的字段INSERT INTO users (username, email) VALUES (bob, bexample.com) RETURNING id, created_at;不用再发一条 SELECT 去查主键省一次往返逻辑也清爽。3.2 UPDATE别忽略WHERE别一把梭全表UPDATE 的教训主要集中在对 WHERE 的敬畏上。不带 WHERE 的 UPDATE 就是全表更新这个操作在生产环境出现一次基本就是一次事故。我自己的习惯是执行影响面大的 UPDATE 之前一定先跑一条同条件 SELECT 确认行数SELECT count(*) FROM users WHERE status 2; -- 确认是预期行数后 UPDATE users SET status 3 WHERE status 2;PostgreSQL 的 UPDATE 还支持跨表更新语法跟 MySQL 的 JOIN UPDATE 不一样是用 FROM 子句实现UPDATE orders o SET amount o.amount * 1.1 FROM customers c WHERE o.customer_id c.id AND c.level vip;这个写法实际工作中非常高频比如按用户等级批量调价。注意 FROM 子句会把所有满足条件的行关联进来如果关联后有重复更新结果可能不是你预期的值所以关联键必须能唯一标识目标行。批量更新大表是另一个难点。PostgreSQL 的 UPDATE 没有 LIMIT 子句直接一条语句更新几百万行会持有事务和锁很长时间WAL 会猛涨还会拖垮备库回放。常规做法是用主键分批UPDATE users SET status 3 WHERE status 2 AND id BETWEEN 1 AND 10000;每批几千到几万行批与批之间留出缓冲循环执行。这样每个事务短平快锁持有时间可控对业务影响最小。还要理解 UPDATE 的底层代价。PostgreSQL 用的是 MVCC 模型UPDATE 实际上不是改原行而是生成一个新版本的行旧版本行留待 VACUUM 回收。所以高频 UPDATE 会让表的数据文件膨胀如果 autovacuum 跟不上表会越来越大查询也越来越慢。这个问题后面章节细说。3.3 DELETE清理数据的正确姿势DELETE 的第一原则和 UPDATE 一样WHERE 条件必须想清楚。其次你要明白 DELETE 和 TRUNCATE 的取舍。只想清空整表用 TRUNCATE按条件删部分数据只能用 DELETE。生产环境按条件删大量数据时别一条 DELETE 梭哈要分批DELETE FROM audit_logs WHERE created_at now() - interval 90 days AND id IN ( SELECT id FROM audit_logs WHERE created_at now() - interval 90 days ORDER BY id LIMIT 5000 );为什么分批因为一个大事务 DELETE 几百万行期间持有大量行锁和事务快照不仅长时间阻塞相关读写而且如果中途失败回滚回滚本身也是一场灾难。分批删除能让每一批的锁开销和 WAL 量控制在合理范围。DELETE 的RETURNING也很香。清理数据前想留个底直接取回被删的行DELETE FROM temp_orders WHERE expired_at now() RETURNING id, expired_at;有次帮业务清理脏数据被删之前需要审计我就是用 RETURNING 把这些行直接写进了历史表的一条语句搞定插入和删除两个需求。DELETE 的另一个隐蔽开销是外键检查。如果子表有外键引用这张表每删一行都要扫描子表确认没有引用没有索引支持时这个扫描代价非常恐怖。删除大表数据之前检查一下外键列上有无索引没有的话考虑加一个。4. 事务和锁DDL与DML一起用时必须懂的知识4.1 PostgreSQL 的 DDL 能在事务里回滚PostgreSQL 和 MySQL 有一个很大的不同PG 的 DDL 是支持事务的。也就是说你执行CREATE TABLE、ALTER TABLE、甚至DROP TABLE只要包在事务里都可以回滚。BEGIN; ALTER TABLE users ADD COLUMN wechat VARCHAR(50); -- 发现不对劲 ROLLBACK; -- 表结构回到原来的样子这一点在生产变更脚本里价值巨大。我写结构变更脚本时默认都会套上事务脚本里每一步都验证只要任何一步报错或者数据校验不过直接 ROLLBACK不会留下半截变更。但要注意事务和锁是双刃剑。事务越长持有的锁时间越久阻塞其他会话的时间也越久。我见过一个同事把整个报表数据清洗放在一个事务里跑了一个小时结果对同一张表的业务写入全被卡死故障持续到事务结束。事务能救你也能坑你关键是控制它的大小。4.2 表级锁概览什么操作会堵住什么操作PostgreSQL 的表级锁模式很多日常真正需要记住的是下面几种锁模式典型操作和谁冲突ACCESS SHARESELECT只和 ACCESS EXCLUSIVE 冲突ROW SHARESELECT FOR UPDATE和 EXCLUSIVE、ACCESS EXCLUSIVE 冲突ROW EXCLUSIVEINSERT / UPDATE / DELETE和 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突SHARECREATE INDEX非并发和 ROW EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突ACCESS EXCLUSIVEALTER TABLE / DROP TABLE / TRUNCATE和所有锁模式冲突从这张表能看出两件关键事DML 语句之间不互相冲突所以业务读写能高并发。DDL 的 ARMAGEDDON一旦拿到 ACCESS EXCLUSIVE整张表的读写全部停摆直到 DDL 完成。排查锁等待的思路也很固定。遇到 DDL 卡住先用这条 SQL 看谁在等、等的是什么SELECT pid, wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE backend_type client backend ORDER BY pid;如果看到某条ALTER TABLE一直处于active但wait_event_type是Lock那就是在等其他事务释放锁。再用 pg_locks 关联查一下是哪个 pid 持有了这把冲突锁找对应的人确认业务是否跑完。能做的处理一般是等锁、终止阻塞会话、或者调整业务节奏。死锁方面 PostgreSQL 会自动检测发现死锁会回滚其中一个事务并输出提示。应用侧要做的不是等死锁发生而是统一加锁顺序、缩短事务时间从源头避免。4.3 MVCC和隔离级别理解DML行为的关键PostgreSQL 默认的隔离级别是 READ COMMITTED每条语句执行时都取一个新快照所以同一事务里两次 SELECT 可能看到不同数据。如果业务需要整个事务看到一致的快照就要手动调成 REPEATABLE READBEGIN ISOLATION LEVEL REPEATABLE READ; SELECT * FROM users WHERE status 2; -- 整个事务期间这个快照不变 COMMIT;REPEATABLE READ 下如果两个事务并发更新同一行后提交的一方会收到序列化失败错误需要在应用层重试。MVCC 带来的另一个生产问题是表膨胀。每一条 UPDATE 和 DELETE 都会留下死元组VACUUM 负责回收这些旧版本。如果某个长事务一直不提交它开启的快照之前的所有死元组都不能被清理这个事务跑得越久表的膨胀就越严重。所以尽量缩短事务时间别让事务空转。监控 autovacuum 是否正常工作不要轻易关闭它。对频繁更新的表可以考虑调整 fillfactor 参数给 UPDATE 留出页内空间。ALTER TABLE users SET (fillfactor 70);这个选项在建表或重置时生效之后的数据更新会在页内留出位置减少行版本跨页迁移的写放大。代价是每个页能装的行数变少查询要扫描更多页。适合频繁 UPDATE 的表不适合只读表。5. 性能优化和问题排查生产环境真实踩坑记录5.1 大表 DDL 怎么才能不打断业务生产环境最怕的就是对一张几千万行的表直接执行 ALTER TABLE 加索引或加约束一个锁就把业务全堵死。这里把我常用的几个不打断业务的方案列一下。创建索引用并发方式CREATE INDEX CONCURRENTLY idx_users_email ON users (email);CONCURRENTLY让建索引过程不阻塞表的读写但代价是耗时比普通方式长而且不能在事务块里和其他语句一起执行。还有一个坑如果执行过程中被中断PostgreSQL 会留下一个 INVALID 状态的索引查询不会使用它但会拖慢写入需要手动 DROP 之后再重建。SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE indisvalid false;加约束的分阶段方案NOT VALID 加 VALIDATE在前面 ALTER TABLE 部分已经讲了这里再强调一遍它对大表加 CHECK、外键约束来说几乎是标配做法能把锁持有时间从全表扫描那么久缩短到毫秒级。如果要做更大规模的重构比如改字段类型或者调整表结构没法避免全表重写那就得考虑影子表方案新建一张新结构表业务写入切换到新表同时把老表数据分批灌入最后切换访问入口老表退役。这套流程要应用层配合属于大工程但确实是通过在线方式完成大表结构升级的可靠路径。5.2 常见问题速查表把这些年踩过的坑整理成一张表排查的时候按图索骥现象可能原因排查命令处理思路DDL 执行卡住不动被其他事务的锁阻塞pg_stat_activity 看 wait_event找到持锁会话确认是否可终止UPDATE 之后表越来越大MVCC 死元组没被及时回收SELECT pg_stat_user_tables 查 n_dead_tup确认 autovacuum 开启必要时手动 VACUUM并发更新报 serialization failureREPEATABLE READ 下更新冲突看日志错误码 40001应用层捕获后重试事务批量插入非常慢索引、触发器、约束过多EXPLAIN ANALYZE 定位瓶颈导入前删索引导完重建误删了数据手滑或者 FORGOT WHERE无用备份做 PITR 时间点恢复前提是有备份触发了死锁事务之间锁顺序不一致日志中死锁详情统一资源加锁顺序缩短事务执行 ALTER TYPE 几分钟没反应正在全表重写pg_stat_progress 看重写进度等待或择低峰执行误删数据这里多说一句。PostgreSQL 没有像某些商业数据库那样开箱即用的闪回功能恢复依赖物理备份加 WAL 归档做时间点恢复PITR。所以生产库的备份策略和恢复演练是不能省的基础设施平时不演练出事必手忙脚乱。5.3 批量操作的实用调优手段批量 DML 性能优化我通常按优先级做这几件事第一导入大量数据前先砍掉非必要索引。每插入一行所有索引都要同步更新索引越多写入越慢。常规做法是先把索引 DROP 掉COPY 导入完成后再统一CREATE INDEX CONCURRENTLY重建。综合耗时往往比边插边维护索引快很多。第二调整 maintenance_work_mem。建索引、VACUUM、加外键这些维护操作会用这个参数决定内存窗口大小。默认值偏保守建大索引时可以按机器内存临时调大SET maintenance_work_mem 1GB; CREATE INDEX CONCURRENTLY idx_users_created_at ON users (created_at);注意这个参数是会话级的改大了只影响当前会话不用担心污染全局配置。第三大表频繁 DML 之后手动 VACUUM 一下。autovacuum 有触发阈值但高峰期可能跟不上。低峰期手动执行VACUUM (ANALYZE)能及时回收死元组并刷新统计信息让优化器拿到更准确的估算后续查询计划不容易跑偏。第四需要快速清理超大表时如果全表都不要了直接 TRUNCATE 而不是 DELETE这个前文提过。如果只要保留一部分也可以考虑建新表INSERT SELECT切换名称的模式比 DELETE 大部分数据快得多因为写新表是顺序写而 DELETE 是随机写旧表还要留死元组。6. 最后分享几个没写在文档里的实战心得这几年的经验总结下来其实就三句话。第一所有结构变更都要当发布来做而不是当命令来做。先测试环境验证估算耗时和锁影响准备好回滚语句。我在生产环境吃过没准备回滚的亏一条 ALTER 把老表的默认值逻辑改错了业务写入直接报错最后翻 LOG 才把问题定位回来。从那以后任何 DDL 变更脚本我一定是BEGIN; ... COMMIT;包好旁边备着逆操作的 ROLLBACK。第二DDL 和 DML 混用时要格外小心事务边界。有人喜欢在一个事务里先 DELETE 再 ALTER看起来一气呵成实际上把数据清理的长时间锁和结构变更的 ACCESS EXCLUSIVE 锁叠在一起阻塞面直接翻倍。正确姿势是拆成独立的事务每个事务短平快。第三监控和备份才是数据库安全感的来源。DDL 操作前看一眼pg_stat_activity有没有长事务DML 大批量操作前确认磁盘空间和 WAL 目录不会写爆日常每天做备份恢复演练。这些都是看不见的功夫但事故发生时只有它们能兜底。另外再给个实用小技巧拿不准某条 DDL 或 DML 在真实数据量上的表现时先在测试环境跑EXPLAIN ANALYZE看执行计划和耗时再在目标表上借用BEGIN; ... ROLLBACK;实测一次锁行为。这两步能过滤掉百分之八十的低级失误。PostgreSQL 这东西你越了解它的锁和 MVCC写出来的操作就越稳踩坑的次数就越少。 
长河君 · 2026/10/21 7:50:55 · 阅读 1.2k PostgreSQL DDL与DML实战：锁、事务与性能优化指南
日常如果跟 PostgreSQL 打交道DDL 和 DML 这两个词绝对绕不开。简单说DDL 管的是结构建表、加字段、改类型、删表DML 管的是数据插入、更新、删除、查询。我见过不少同事写业务 SQL 很溜但一到改大表结构、批量更新、误删恢复这种场景就犯怵根本原因是对这两类操作底层的行为差异没吃透——锁、事务、日志、MVCC 这些背后逻辑搞明白了线上操作才敢放手去做。这篇文章把我这几年在项目里反复用到的 PostgreSQL DDL 和 DML 操作完整梳理一遍既有最基础的语法细节也会把容易坑人的锁冲突、事务回滚、表膨胀这些点讲透。打算系统掌握 PostgreSQL 的开发者可以通读日常要维护数据的 DBA 或者后端同学也能直接当操作手册翻。1. 先把DDL和DML的边界理清楚1.1 两个概念到底各管什么很多人刚接触数据库时对 DDL 和 DML 的分类只有一个模糊印象真要说出哪些命令属于哪一类反而会卡壳。这里把边界划清楚DDLData Definition Language数据定义语言操作对象的结构和属性包括 CREATE、ALTER、DROP、TRUNCATE以及 COMMENT、GRANT、REVOKE 这类元数据和权限操作。说白了就是给房子改户型、装修、拆墙。DMLData Manipulation Language数据操纵语言操作对象里的数据内容包括 INSERT、UPDATE、DELETE、SELECT。严格分类时也有人把 SELECT 单独拆成 DQLData Query Language但实际工作里讨论 DML 时经常把查询也带进去本文核心还是落在增删改上。这个分类不止是概念游戏它直接决定了一个人能对数据库做什么。比如我给业务账号授权通常只给 INSERT、UPDATE、DELETE、SELECT 的权限DDL 权限几乎不给。这样即使应用被拖库或者有人误操作他也只能改数据动不了表结构伤害面缩小一大截。1.2 区分它们有什么用我刚开始做运维的时候也觉得这分类无所谓直到线上出了几次事故才明白区分 DDL 和 DML 是判断操作风险的第一依据。第一是锁行为差异。普通的 DML 语句拿的是 RowExclusiveLock表上的读写操作彼此基本不冲突业务可以并发跑。而 DDL 语句比如 ALTER TABLE、DROP TABLE拿的是更高级别的锁通常直接到 AccessExclusiveLock一旦执行就会阻塞这张表上所有的读写。一个是大家排队进出一个是直接封路施工风险等级完全不一样。第二是日志和复制开销。DML 会修改数据页产生大量 WAL这些日志要传给备库、要归档DDL 大多数时候只写系统元数据日志量小很多。但有例外比如 TRUNCATE 虽然不逐行记录数据但它本身也作为事务的一部分写日志而且它对表的锁影响比 DELETE 大得多。第三是变更管理流程。生产环境的表结构变更成熟团队都会走审批、评估锁时间、准备回滚方案本质就是因为 DDL 的风险是结构级的而数据变更更多依赖业务逻辑和 WHERE 条件的准确性。你把这两类操作分清楚后面所有排查和优化的思路就顺了。2. 常用DDL操作怎么用才靠谱2.1 CREATE TABLE建表时就把约束想全建表是 DDL 里最基础的操作但基础不代表随便写。很多人上来就是一把梭字段类型差不多就行约束能省则省结果线上跑了一阵才发现数据质量一塌糊涂。下面这张表基本能代表我日常建表的习惯CREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username VARCHAR(64) NOT NULL, email VARCHAR(255) UNIQUE, status SMALLINT NOT NULL DEFAULT 1 CONSTRAINT chk_users_status CHECK (status IN (0, 1, 2)), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() );几个要点说一下。自增主键我建议优先用GENERATED ALWAYS AS IDENTITY这是 SQL 标准写法比老式的BIGSERIAL更规范权限控制也更干净。如果你用的是高版本 PostgreSQL新表直接上 identity 就好。约束命名很多人会忽略但一旦表上线、数据多了想改约束却不知道约束叫什么名字就得先查系统表非常麻烦。像我上面写的那样把chk_users_status这种有意义的约束名直接写进建表语句后面执行ALTER TABLE ... DROP CONSTRAINT会省很多事。同理外键约束也建议显式命名。字段类型选型这块我的原则是能用数值就不用字符串存数字能用TIMESTAMPTZ就不用TIMESTAMP。TIMESTAMPTZ存的是带时区的时间点写入时自动按数据库时区解释查询时按客户端时区展示逻辑统一不出岔子。我接手过的项目里时间字段用错的例子太多了十有八九是时区问题引发的。如果是超大表建表时直接考虑分区。PostgreSQL 原生支持声明式分区CREATE TABLE logs ( id BIGINT NOT NULL, created_at TIMESTAMPTZ NOT NULL ) PARTITION BY RANGE (created_at); CREATE TABLE logs_2025_01 PARTITION OF logs FOR VALUES FROM (2025-01-01) TO (2025-02-01);分区表后面做数据清理直接DROP PARTITION完事比一条条 DELETE 不知道快到哪里去了。2.2 ALTER TABLE改表结构的坑都集中在锁和重写ALTER TABLE 是日常变更里最容易出问题的操作因为不是所有改结构动作都一样快。同样是加一个字段可能毫秒级完成也可能把几百万行数据的表重写一遍锁表几分钟甚至更久。先看最常见的加字段ALTER TABLE users ADD COLUMN nickname VARCHAR(50);在 PostgreSQL 11 之前如果你加字段时带一个常量默认值比如ADD COLUMN status SMALLINT DEFAULT 1大表会被强制全表重写目的是把默认值物理写入每行那个锁表时间在生产环境能让人血压拉满。PostgreSQL 11 之后做了优化加带常量默认值的字段不再重写表只改元数据执行速度飞快。但注意这个优化只对常量默认值有效。如果你加的是非常量默认值比如DEFAULT gen_random_uuid()或者DEFAULT now()这种PG 没法在元数据层面直接模拟每行的返回值仍然要把表重写一遍。所以对大表加这种字段更稳妥的做法是分三步走-- 第一步先加允许为空的字段不设默认值 ALTER TABLE users ADD COLUMN external_id UUID; -- 第二步分批填充数据不要一条UPDATE刷全表 UPDATE users SET external_id gen_random_uuid() WHERE external_id IS NULL AND id BETWEEN 10001 AND 20000; -- 第三步字段填完后再补默认值和NOT NULL约束 ALTER TABLE users ALTER COLUMN external_id SET DEFAULT gen_random_uuid(); ALTER TABLE users ALTER COLUMN external_id SET NOT NULL;这样每一步持有锁的时间都很短业务基本无感。改字段类型是另一个重灾区ALTER TABLE users ALTER COLUMN id TYPE BIGINT;任何类型转换都伴随着全表扫描和重写有些转换不写 USING 甚至会直接失败比如把VARCHAR改成INTEGERALTER TABLE users ALTER COLUMN age TYPE INTEGER USING age::INTEGER;USING 表达式定义了怎么把旧值转换成新值。改类型前先预估表大小和重写耗时别在生产高峰期干这件事。约束的添加也有讲究。突然给已有大量数据的表加 CHECK 约束或外键约束PG 默认会扫描整表验证期间持有 ACCESS EXCLUSIVE 锁。规范做法是两阶段ALTER TABLE users ADD CONSTRAINT chk_users_age CHECK (age 0) NOT VALID; ALTER TABLE users VALIDATE CONSTRAINT chk_users_age;第一阶段只加约束不校验元数据层面立刻完成锁很短第二阶段 VALIDATE 只需要 ShareLock 级别的锁不阻塞读写。两条命令之间的空档期如果有新写入的非法数据VALIDATE 会失败所以这种方案适合对数据错误容忍度高的场景严格的场景还是要在维护窗口做。2.3 DROP 和 TRUNCATE删东西之前先想清楚删表和清数据的操作风险等级完全不同。很多新手分不清 DROP TABLE 和 TRUNCATE 的区别这里直接列个对比表操作动结构动数据锁级别可回滚触发器DROP TABLE删除删除ACCESS EXCLUSIVE事务内可回滚不触发TRUNCATE保留清空ACCESS EXCLUSIVE事务内可回滚不触发行级触发器DELETE保留按条件删除ROW EXCLUSIVE事务内可回滚触发行级触发器很多人以为 TRUNCATE 是 DML其实在 PostgreSQL 里它属于 DDL 范畴拿的是 ACCESS EXCLUSIVE 锁执行期间所有对该表的读写都会被阻塞。它的优势是速度极快因为不是逐行删除数据而是直接把表的存储文件标记为废弃并重新分配。TRUNCATE 有两个容易踩的坑。第一如果这张表被其他表的外键引用直接 TRUNCATE 会报错提示存在外键依赖。这时候你可以选择TRUNCATE table_a, table_b CASCADE把引用它的表一起清空但这意味着你可能删掉了不该删的数据用之前一定要确认依赖关系。第二TRUNCATE 不触发行级触发器如果你依赖触发器做审计或者级联操作它不会生效。DROP TABLE 更不用说表结构和数据一起没。DROP TABLE xxx CASCADE会连带删除依赖这个表的所有对象比如视图、外键约束这个 CASCADE 我是能不用就不用真要用了也先查好依赖再动手。实操心得删表或清空数据之前先用\d table_name看一眼依赖再用BEGIN; ... ROLLBACK;包一层试运行。我在测试环境踩过 DROP 后才发现视图全没了的坑从那以后 CASCADE 慎用成了铁律。3. 常用DML操作学会少踩坑的做法3.1 INSERT从单条到批量INSERT 看着最简单但性能差距可以非常大。一条一条插入和批量插入之间可能差出几十倍的效率。单条插入不用多说重点是批量场景。批量插入有三种常见姿势-- 方式一一条语句多组VALUES INSERT INTO users (username, email) VALUES (alice, aexample.com), (bob, bexample.com), (carol, cexample.com); -- 方式二从另一张表查出来插入 INSERT INTO users_new (id, username, email) SELECT id, username, email FROM users WHERE status 1; -- 方式三COPY高速导入 COPY users (username, email) FROM /path/to/users.csv WITH (FORMAT csv, HEADER true);方式一减少了解析和提交开销适合几千行以内。方式二适合做表迁移或数据归档。方式三 COPY 是 PostgreSQL 导入文本数据最快的路径比逐条 INSERT 能快一个数量级大批量导数据首选。生产环境经常遇到的需求是有则更新无则插入PostgreSQL 的ON CONFLICT直接搞定不需要先查再决定INSERT INTO users (id, username, email) VALUES (1001, alice, aexample.com) ON CONFLICT (email) DO UPDATE SET username EXCLUDED.username, updated_at now();这里有个前提ON CONFLICT 后面括号里的列必须有唯一约束或唯一索引否则直接报错。EXCLUDED代表当前语句想插入的那行数据用它可以引用新值。INSERT 还有一个很实用的能力就是RETURNING。我经常用它拿到数据库生成的字段INSERT INTO users (username, email) VALUES (bob, bexample.com) RETURNING id, created_at;不用再发一条 SELECT 去查主键省一次往返逻辑也清爽。3.2 UPDATE别忽略WHERE别一把梭全表UPDATE 的教训主要集中在对 WHERE 的敬畏上。不带 WHERE 的 UPDATE 就是全表更新这个操作在生产环境出现一次基本就是一次事故。我自己的习惯是执行影响面大的 UPDATE 之前一定先跑一条同条件 SELECT 确认行数SELECT count(*) FROM users WHERE status 2; -- 确认是预期行数后 UPDATE users SET status 3 WHERE status 2;PostgreSQL 的 UPDATE 还支持跨表更新语法跟 MySQL 的 JOIN UPDATE 不一样是用 FROM 子句实现UPDATE orders o SET amount o.amount * 1.1 FROM customers c WHERE o.customer_id c.id AND c.level vip;这个写法实际工作中非常高频比如按用户等级批量调价。注意 FROM 子句会把所有满足条件的行关联进来如果关联后有重复更新结果可能不是你预期的值所以关联键必须能唯一标识目标行。批量更新大表是另一个难点。PostgreSQL 的 UPDATE 没有 LIMIT 子句直接一条语句更新几百万行会持有事务和锁很长时间WAL 会猛涨还会拖垮备库回放。常规做法是用主键分批UPDATE users SET status 3 WHERE status 2 AND id BETWEEN 1 AND 10000;每批几千到几万行批与批之间留出缓冲循环执行。这样每个事务短平快锁持有时间可控对业务影响最小。还要理解 UPDATE 的底层代价。PostgreSQL 用的是 MVCC 模型UPDATE 实际上不是改原行而是生成一个新版本的行旧版本行留待 VACUUM 回收。所以高频 UPDATE 会让表的数据文件膨胀如果 autovacuum 跟不上表会越来越大查询也越来越慢。这个问题后面章节细说。3.3 DELETE清理数据的正确姿势DELETE 的第一原则和 UPDATE 一样WHERE 条件必须想清楚。其次你要明白 DELETE 和 TRUNCATE 的取舍。只想清空整表用 TRUNCATE按条件删部分数据只能用 DELETE。生产环境按条件删大量数据时别一条 DELETE 梭哈要分批DELETE FROM audit_logs WHERE created_at now() - interval 90 days AND id IN ( SELECT id FROM audit_logs WHERE created_at now() - interval 90 days ORDER BY id LIMIT 5000 );为什么分批因为一个大事务 DELETE 几百万行期间持有大量行锁和事务快照不仅长时间阻塞相关读写而且如果中途失败回滚回滚本身也是一场灾难。分批删除能让每一批的锁开销和 WAL 量控制在合理范围。DELETE 的RETURNING也很香。清理数据前想留个底直接取回被删的行DELETE FROM temp_orders WHERE expired_at now() RETURNING id, expired_at;有次帮业务清理脏数据被删之前需要审计我就是用 RETURNING 把这些行直接写进了历史表的一条语句搞定插入和删除两个需求。DELETE 的另一个隐蔽开销是外键检查。如果子表有外键引用这张表每删一行都要扫描子表确认没有引用没有索引支持时这个扫描代价非常恐怖。删除大表数据之前检查一下外键列上有无索引没有的话考虑加一个。4. 事务和锁DDL与DML一起用时必须懂的知识4.1 PostgreSQL 的 DDL 能在事务里回滚PostgreSQL 和 MySQL 有一个很大的不同PG 的 DDL 是支持事务的。也就是说你执行CREATE TABLE、ALTER TABLE、甚至DROP TABLE只要包在事务里都可以回滚。BEGIN; ALTER TABLE users ADD COLUMN wechat VARCHAR(50); -- 发现不对劲 ROLLBACK; -- 表结构回到原来的样子这一点在生产变更脚本里价值巨大。我写结构变更脚本时默认都会套上事务脚本里每一步都验证只要任何一步报错或者数据校验不过直接 ROLLBACK不会留下半截变更。但要注意事务和锁是双刃剑。事务越长持有的锁时间越久阻塞其他会话的时间也越久。我见过一个同事把整个报表数据清洗放在一个事务里跑了一个小时结果对同一张表的业务写入全被卡死故障持续到事务结束。事务能救你也能坑你关键是控制它的大小。4.2 表级锁概览什么操作会堵住什么操作PostgreSQL 的表级锁模式很多日常真正需要记住的是下面几种锁模式典型操作和谁冲突ACCESS SHARESELECT只和 ACCESS EXCLUSIVE 冲突ROW SHARESELECT FOR UPDATE和 EXCLUSIVE、ACCESS EXCLUSIVE 冲突ROW EXCLUSIVEINSERT / UPDATE / DELETE和 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突SHARECREATE INDEX非并发和 ROW EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突ACCESS EXCLUSIVEALTER TABLE / DROP TABLE / TRUNCATE和所有锁模式冲突从这张表能看出两件关键事DML 语句之间不互相冲突所以业务读写能高并发。DDL 的 ARMAGEDDON一旦拿到 ACCESS EXCLUSIVE整张表的读写全部停摆直到 DDL 完成。排查锁等待的思路也很固定。遇到 DDL 卡住先用这条 SQL 看谁在等、等的是什么SELECT pid, wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE backend_type client backend ORDER BY pid;如果看到某条ALTER TABLE一直处于active但wait_event_type是Lock那就是在等其他事务释放锁。再用 pg_locks 关联查一下是哪个 pid 持有了这把冲突锁找对应的人确认业务是否跑完。能做的处理一般是等锁、终止阻塞会话、或者调整业务节奏。死锁方面 PostgreSQL 会自动检测发现死锁会回滚其中一个事务并输出提示。应用侧要做的不是等死锁发生而是统一加锁顺序、缩短事务时间从源头避免。4.3 MVCC和隔离级别理解DML行为的关键PostgreSQL 默认的隔离级别是 READ COMMITTED每条语句执行时都取一个新快照所以同一事务里两次 SELECT 可能看到不同数据。如果业务需要整个事务看到一致的快照就要手动调成 REPEATABLE READBEGIN ISOLATION LEVEL REPEATABLE READ; SELECT * FROM users WHERE status 2; -- 整个事务期间这个快照不变 COMMIT;REPEATABLE READ 下如果两个事务并发更新同一行后提交的一方会收到序列化失败错误需要在应用层重试。MVCC 带来的另一个生产问题是表膨胀。每一条 UPDATE 和 DELETE 都会留下死元组VACUUM 负责回收这些旧版本。如果某个长事务一直不提交它开启的快照之前的所有死元组都不能被清理这个事务跑得越久表的膨胀就越严重。所以尽量缩短事务时间别让事务空转。监控 autovacuum 是否正常工作不要轻易关闭它。对频繁更新的表可以考虑调整 fillfactor 参数给 UPDATE 留出页内空间。ALTER TABLE users SET (fillfactor 70);这个选项在建表或重置时生效之后的数据更新会在页内留出位置减少行版本跨页迁移的写放大。代价是每个页能装的行数变少查询要扫描更多页。适合频繁 UPDATE 的表不适合只读表。5. 性能优化和问题排查生产环境真实踩坑记录5.1 大表 DDL 怎么才能不打断业务生产环境最怕的就是对一张几千万行的表直接执行 ALTER TABLE 加索引或加约束一个锁就把业务全堵死。这里把我常用的几个不打断业务的方案列一下。创建索引用并发方式CREATE INDEX CONCURRENTLY idx_users_email ON users (email);CONCURRENTLY让建索引过程不阻塞表的读写但代价是耗时比普通方式长而且不能在事务块里和其他语句一起执行。还有一个坑如果执行过程中被中断PostgreSQL 会留下一个 INVALID 状态的索引查询不会使用它但会拖慢写入需要手动 DROP 之后再重建。SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE indisvalid false;加约束的分阶段方案NOT VALID 加 VALIDATE在前面 ALTER TABLE 部分已经讲了这里再强调一遍它对大表加 CHECK、外键约束来说几乎是标配做法能把锁持有时间从全表扫描那么久缩短到毫秒级。如果要做更大规模的重构比如改字段类型或者调整表结构没法避免全表重写那就得考虑影子表方案新建一张新结构表业务写入切换到新表同时把老表数据分批灌入最后切换访问入口老表退役。这套流程要应用层配合属于大工程但确实是通过在线方式完成大表结构升级的可靠路径。5.2 常见问题速查表把这些年踩过的坑整理成一张表排查的时候按图索骥现象可能原因排查命令处理思路DDL 执行卡住不动被其他事务的锁阻塞pg_stat_activity 看 wait_event找到持锁会话确认是否可终止UPDATE 之后表越来越大MVCC 死元组没被及时回收SELECT pg_stat_user_tables 查 n_dead_tup确认 autovacuum 开启必要时手动 VACUUM并发更新报 serialization failureREPEATABLE READ 下更新冲突看日志错误码 40001应用层捕获后重试事务批量插入非常慢索引、触发器、约束过多EXPLAIN ANALYZE 定位瓶颈导入前删索引导完重建误删了数据手滑或者 FORGOT WHERE无用备份做 PITR 时间点恢复前提是有备份触发了死锁事务之间锁顺序不一致日志中死锁详情统一资源加锁顺序缩短事务执行 ALTER TYPE 几分钟没反应正在全表重写pg_stat_progress 看重写进度等待或择低峰执行误删数据这里多说一句。PostgreSQL 没有像某些商业数据库那样开箱即用的闪回功能恢复依赖物理备份加 WAL 归档做时间点恢复PITR。所以生产库的备份策略和恢复演练是不能省的基础设施平时不演练出事必手忙脚乱。5.3 批量操作的实用调优手段批量 DML 性能优化我通常按优先级做这几件事第一导入大量数据前先砍掉非必要索引。每插入一行所有索引都要同步更新索引越多写入越慢。常规做法是先把索引 DROP 掉COPY 导入完成后再统一CREATE INDEX CONCURRENTLY重建。综合耗时往往比边插边维护索引快很多。第二调整 maintenance_work_mem。建索引、VACUUM、加外键这些维护操作会用这个参数决定内存窗口大小。默认值偏保守建大索引时可以按机器内存临时调大SET maintenance_work_mem 1GB; CREATE INDEX CONCURRENTLY idx_users_created_at ON users (created_at);注意这个参数是会话级的改大了只影响当前会话不用担心污染全局配置。第三大表频繁 DML 之后手动 VACUUM 一下。autovacuum 有触发阈值但高峰期可能跟不上。低峰期手动执行VACUUM (ANALYZE)能及时回收死元组并刷新统计信息让优化器拿到更准确的估算后续查询计划不容易跑偏。第四需要快速清理超大表时如果全表都不要了直接 TRUNCATE 而不是 DELETE这个前文提过。如果只要保留一部分也可以考虑建新表INSERT SELECT切换名称的模式比 DELETE 大部分数据快得多因为写新表是顺序写而 DELETE 是随机写旧表还要留死元组。6. 最后分享几个没写在文档里的实战心得这几年的经验总结下来其实就三句话。第一所有结构变更都要当发布来做而不是当命令来做。先测试环境验证估算耗时和锁影响准备好回滚语句。我在生产环境吃过没准备回滚的亏一条 ALTER 把老表的默认值逻辑改错了业务写入直接报错最后翻 LOG 才把问题定位回来。从那以后任何 DDL 变更脚本我一定是BEGIN; ... COMMIT;包好旁边备着逆操作的 ROLLBACK。第二DDL 和 DML 混用时要格外小心事务边界。有人喜欢在一个事务里先 DELETE 再 ALTER看起来一气呵成实际上把数据清理的长时间锁和结构变更的 ACCESS EXCLUSIVE 锁叠在一起阻塞面直接翻倍。正确姿势是拆成独立的事务每个事务短平快。第三监控和备份才是数据库安全感的来源。DDL 操作前看一眼pg_stat_activity有没有长事务DML 大批量操作前确认磁盘空间和 WAL 目录不会写爆日常每天做备份恢复演练。这些都是看不见的功夫但事故发生时只有它们能兜底。另外再给个实用小技巧拿不准某条 DDL 或 DML 在真实数据量上的表现时先在测试环境跑EXPLAIN ANALYZE看执行计划和耗时再在目标表上借用BEGIN; ... ROLLBACK;实测一次锁行为。这两步能过滤掉百分之八十的低级失误。PostgreSQL 这东西你越了解它的锁和 MVCC写出来的操作就越稳踩坑的次数就越少。