最近又被群里的小伙伴问到“PostgreSQL 怎么删重复数据”说实话这个问题我前前后后帮人处理过不下十次。乍一听很简单就是查找加删除嘛可真到了生产环境你会发现事情远没有 SQL 语句看起来那么单纯——性能、锁、事务大小、数据备份、甚至 NULL 值的坑随便一个都够你折腾半天。这篇文章我打算用一套完整的案例从“怎么查”到“怎么删”再到“删完怎么办”把 PostgreSQL 里去重这件事讲透。如果你平时用的是 MySQL这里的思路同样有参考价值但 PostgreSQL 里有几个特性比如 ctid、MVCC是它独有的处理逻辑会不太一样。无论你是后端开发、数据分析师还是刚入门 PostgreSQL 的运维同学这篇文章都能给你一套可以直接抄作业的套路。1. 动手之前先搞清楚重复数据到底有哪几种很多人上来就写 SQL结果删完之后发现数据不对——不是删多了就是删错了。这往往不是因为 SQL 写错而是因为没有先区分“重复”的类型。我习惯把重复数据分成三种处理方式完全不同。1.1 完全重复整行所有字段都一模一样这种是最“老实”的重复整行数据每个字段的值都完全相同。常见于反复导入同一份文件、两个表做 UNION 之后没去重、或者定时任务重复跑批把相同数据又插了一遍。完全重复处理起来很简单每一组重复行保留任意一条即可。难点只在于怎么高效地把重复的行挑出来这个后面会展开。1.2 部分字段重复业务键相同但其他字段不同这是实际工作中最常遇到的情况。比如用户表里同一个手机号出现了两次但昵称、注册时间可能不一样订单表里同一个订单编号出现了两条记录但状态字段不同。这种重复不能简单“去重”必须先确定业务上的唯一键是什么——对用户表来说是手机号对订单表来说是订单编号——然后决定每组保留哪一条比如按时间保留最新的一条或者按 id 保留最大/最小的一条。这里容易出问题的点是如果除了业务键以外其他字段也有差异直接 DELETE 可能把有用的信息丢掉。我一般的做法是先跟业务方确认到底是以哪几个字段作为判定重复的唯一依据再动手。1.3 空值导致的“伪重复”PostgreSQL 在唯一约束里有个容易踩坑的小特性NULL 值不参与唯一性判断。也就是说你在某一列上建了唯一索引那这一列里多个 NULL 是可以共存的。但如果你用 GROUP BY 去查多个 NULL 又会被当成一组。举个例子订单表里有个字段叫coupon_code优惠券编码对没使用优惠券的订单来说这个字段是 NULL。如果业务上要求每个优惠券编码只能使用一次你给这个字段建了唯一索引那 NULL 的订单可以随便插但你要是拿 GROUP BY coupon_code 来查重复就会把一堆 NULL 当成“重复”。如果你没有意识到这个空值特性很可能会误删大量正常数据。所以查找重复之前先想清楚业务上的唯一性到底包不包括空值。提示PostgreSQL 里判断“是否重复”有一套自己的逻辑和 MySQL 的默认行为并不完全一样。建议先在小表上试跑 SELECT确认结果无误后再执行 DELETE。2. 定位重复数据从聚合到窗口函数一次学透确定重复类型之后下一步就是把重复的行找出来。PostgreSQL 查重复数据最常用的是两种思路GROUP BY 聚合和窗口函数。两者各有优劣下面我把每种都写出来你可以按实际场景选。2.1 GROUP BY HAVING快速确认哪些字段有重复先造一张测试表方便后面所有例子直接跑。CREATE TABLE users ( id SERIAL PRIMARY KEY, phone VARCHAR(20), name VARCHAR(50), created_at TIMESTAMP DEFAULT now() ); INSERT INTO users (phone, name, created_at) VALUES (13800001111, 张三, 2024-01-01 10:00:00), (13800001111, 张三, 2024-01-02 09:30:00), (13800002222, 李四, 2024-01-03 08:00:00), (13800002222, 李四, 2024-01-04 12:00:00), (13800002222, 李四, 2024-01-05 18:00:00), (13800003333, 王五, 2024-01-06 20:00:00);现在用最经典的 GROUP BY 写法找到重复的电话号码SELECT phone, COUNT(*) FROM users GROUP BY phone HAVING COUNT(*) 1;结果一眼就能看出13800001111重复了 2 次13800002222重复了 3 次。这种方法的优点是简单、直观、执行效率也还行。缺点是它只能告诉你“哪些值重复了”不能直接告诉你“具体是哪几行重复”。如果你想看重复行的完整记录还得再把这些 phone 值拿回去关联一次SELECT * FROM users WHERE phone IN ( SELECT phone FROM users GROUP BY phone HAVING COUNT(*) 1 ) ORDER BY phone, id;对于数据量不大的表这种两层查询完全够用。但如果你要在大表上做去重频繁的子查询会消耗不少资源这时候就该上窗口函数了。2.2 ROW_NUMBER() 窗口函数直接把每一行标记上序号窗口函数是 PostgreSQL 里处理去重场景的利器。它最大的好处是可以在不丢失原始字段信息的情况下对每一行计算一个“组内序号”然后再根据序号筛选。SELECT *, ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY id ) AS rn FROM users;来看下结果逻辑PARTITION BY phone 相当于把相同电话号码的记录分到同一组ORDER BY id 决定组内从 1 开始编号的顺序。这样每组里的第一条rn 1就是保留项rn 1 就是要删除的重复项。如果你想把“保留最新一条”改成“保留最早一条”只要调整 ORDER BY 的方向即可ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY id DESC ) AS rn窗口函数的优势是既能看到原始行数据又能同时得到重复标记。不过要注意直接在几十亿行的大表上做窗口函数排序内存和临时文件的开销都不小后面删数据时我会专门讲怎么分批处理。2.3 不能只看重复行数还要做一次规模评估再分享一个习惯动手删之前我几乎一定会先跑一条统计看看重复数据到底占多大比例。如果重复率很低直接小批量删除问题不大如果重复率特别高比如一张 1 亿行的表里 30% 都是重复那就要考虑重建表而不是逐行删除。SELECT COUNT(*) AS total_rows, COUNT(DISTINCT (phone, name)) AS distinct_rows, COUNT(*) - COUNT(DISTINCT (phone, name)) AS duplicate_rows FROM users;确认重复规模和分布之后再去选删除方案。这一步其实花不了多少时间但能避免很多后患。注意COUNT(DISTINCT (col1, col2)) 这种写法 PostgreSQL 支持MySQL 也支持它可以把多列拼接成一个复合值去重。不过如果列里面含 NULL统计结果可能会有偏差这点心里有数就行。3. 删除重复数据三种主流方案怎么选定位完成之后真正动手删除的时候方案选择比想象中更重要。我实际用下来主流方案大致有三种临时表重建法、ctid 删除法、窗口函数一步到位删除法。各有各的适用场景下面逐一拆开讲。3.1 临时表法最稳适合数据量大或结构复杂的场景思路很简单把去重后的数据放进一张临时表然后清空原表再插回去。-- 第一步创建临时表保存去重后的数据 CREATE TABLE users_dedup AS SELECT DISTINCT ON (phone) * FROM users ORDER BY phone, id; -- 第二步确认临时表数据无误后删除原表或 TRUNCATE TRUNCATE TABLE users; -- 第三步把去重后的数据写回原表 INSERT INTO users SELECT * FROM users_dedup; -- 第四步清理临时表 DROP TABLE users_dedup;这里用到了 PostgreSQL 特有的DISTINCT ON (phone)语法它的作用是按 phone 分组返回每组里按 ORDER BY 排序后的第一条。这个写法比“子查询 窗口函数”更简洁。临时表法的优点非常突出即使原表结构复杂、字段很多、有外键约束也不用担心 DELETE 语句写错导致误删因为数据先在临时表里躺着确认无误再回写。缺点是需要额外的存储空间而且在 TRUNCATE 到 INSERT 之间表上的数据对业务来说是不可见的会有短暂的空窗期必须安排在低峰期操作。如果你的表有外键被其他表引用TRUNCATE 可能会因为外键约束而失败或者级联删除关联数据。这时候我建议你放弃临时表法改用下面的 ctid 删除法。3.2 利用 ctid 原地删除不走索引也能精确删ctid 是 PostgreSQL 里的一个隐藏物理行标识它表示每一行在数据页里的物理位置。即使表里没有主键每一行也一定有一个唯一的 ctid在正常不移动的情况下。这特性用来删重复数据非常方便因为你可以指定“每组保留 ctid 最小的一条或最大的一条其余全部删掉”。DELETE FROM users WHERE ctid NOT IN ( SELECT MIN(ctid) FROM users GROUP BY phone );这条 SQL 的含义是按 phone 分组在每个组里找 ctid 最小的那一行然后把不属于“每组的 MIN(ctid)”的行全部删除。由于每组最小 ctid 是稳定存在的所以实际效果就是每组保留一条其余全删。这个方案的优点是不需要临时表、不锁全表、可以在线执行对表结构没有任何额外要求。缺点是逻辑上需要理解 ctid 是什么而且在大表上NOT IN的性能不够好——子查询返回的行数越多外层 DELETE 扫描就越慢。所以 ctid 方案更适合中小表或者你已经通过前面的 SELECT 确认重复行数量可控的情况。如果你要删除的重复行非常多可以把它改成“分批删除”的办法DELETE FROM users WHERE ctid IN ( SELECT ctid FROM ( SELECT ctid, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY id) AS rn FROM users ) t WHERE t.rn 1 LIMIT 10000 );每次只删 1 万行配合循环或者多次执行能有效避免长事务和表锁问题。3.3 一条 DELETE 加窗口函数代码最优雅但要控好事务还有一种比较“炫”的写法把窗口函数直接嵌在 DELETE 里一步到位DELETE FROM users WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY id ) AS rn FROM users ) t WHERE t.rn 1 );这条 SQL 会把相同 phone 分组后 rn 大于 1 的所有记录全部删除每组保留 id 最小的那条。如果你没有 id 字段可以把id换成ctidDELETE FROM users WHERE ctid IN ( SELECT ctid FROM ( SELECT ctid, ROW_NUMBER() OVER ( PARTITION BY phone ORDER BY ctid ) AS rn FROM users ) t WHERE t.rn 1 );这种写法最优雅但一个 DELETE 会扫描全表还会对涉及的行加锁。表很大的时候事务可能执行很久甚至拖垮主库。我一般只在确认重复数据量占比较低、表规模可控的情况下才会用。3.4 三种方案对比与选型建议为了方便你决策我把三种方案的特点整理成了表格方案优点缺点适合场景临时表法最安全、可控不会误删需要额外空间有空窗期数据量大、结构复杂、重复率高ctid 删除法不需要主键可在线执行大表 NOT IN 性能一般中小表、重复行数较少窗口函数 DELETE写法简洁、一步到位全表扫描、锁范围大小表、低峰期操作选型逻辑总结一下如果你不确定怎么选优先用临时表法它是“下限最高”的方案如果表特别大且要求在线操作就用 ctid 或窗口函数结合 LIMIT 分批删如果数据重复率极高临时表法几乎是唯一合理的选择。提示不管用哪种方案动手前都建议先执行一遍等价的 SELECT确认“将要删除的行数”和“将保留的行数”符合预期再改写成 DELETE 执行。这个习惯能帮你避免绝大多数低级事故。4. 实战中的坑为什么删完问题还在方案选好、SQL 也写完了很多人以为到这里就结束了。实际上我在实战中踩过不少坑有些问题甚至是在删除完成之后才暴露出来的。下面这些情况如果你没遇到过算我输。4.1 表里明明有主键为什么还能产生重复数据这是被问得最多的一个问题。很多人很困惑users 表不是已经有 id 主键了吗为什么 phone 还能重复原因很简单主键只保证 id 不重复并不会自动约束 phone 不能重复。如果你希望 phone 也不重复需要额外在 phone 上建唯一索引CREATE UNIQUE INDEX idx_users_phone ON users (phone);如果你的业务场景确实需要 phone 唯一建了这个索引之后以后再插入重复数据会被数据库直接拒绝从源头上杜绝重复。但要注意如果表里现在已经有重复数据直接建唯一索引会报错必须先清理干净再建。还有一种情况是数据是绕过了约束被插进来的比如导数据时设置了session_replication_role replica或者临时关闭了触发器唯一索引也有可能漏掉重复值所以不要以为有索引就万事大吉。4.2 删除完之后表空间为什么没变小这是最让人抓狂的问题之一。明明 DELETE 删了几千万行看表里的数据量确实少了但磁盘空间一点没释放。原因是 PostgreSQL 的多版本并发控制MVCC机制。DELETE 并不会立刻把数据从磁盘上物理抹掉只是把这些行标记为“已删除”让后续的事务看不到而已。这些被标记的行仍然占据着原来的磁盘空间需要等 VACUUM 来清理。如果你只是想让空间尽快释放出来可以执行VACUUM FULL users;注意VACUUM FULL会重写整张表执行期间会锁表业务读写都会受影响。所以这个方法只能在低峰期执行。如果只是想让空间能被后续插入复用不急着还给操作系统跑一次普通的VACUUM就够了成本低很多。另一个容易忽略的点是如果删完之后表上的索引也变得很大比如索引膨胀严重你还需要重建索引REINDEX TABLE users;索引膨胀会导致查询变慢这在处理完大量删除后非常常见。我处理大表去重之后一般会把 VACUUM FULL 和 REINDEX 一起纳入操作清单。4.3 大批量删除时的死锁和锁等待如果你按 3.2 或 3.3 的做法在大表上执行 DELETE很容易遇到锁等待超时甚至死锁。这是因为 DELETE 的扫描顺序和数据页的物理顺序不完全一致多个并发事务去删不同行的时候可能会导致互相等待锁。我自己的一个习惯是如果删除任务会持续较长时间就分批执行每次提交一个事务并在两次删除之间停顿一小会。这样做的好处是事务不会太大锁的持有时间短其他业务 SQL 不容易被长时间阻塞。一个简单的分批循环写法在 psql 里可以用 DO 块实现DO $$ DECLARE batch_count INT; BEGIN LOOP DELETE FROM users WHERE ctid IN ( SELECT ctid FROM ( SELECT ctid, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY id) AS rn FROM users ) t WHERE t.rn 1 LIMIT 5000 ); GET DIAGNOSTICS batch_count ROW_COUNT; RAISE NOTICE Deleted % rows, batch_count; EXIT WHEN batch_count 5000; COMMIT; PERFORM pg_sleep(0.1); END LOOP; END $$;这里的 COMMIT 在 DO 块里其实不会立即生效实际生产环境建议用外部脚本比如 Python 或 Shell 循环逐批执行 DELETE每批单独提交。思路是控制单次删除量避免长事务。另外如果你要删除的表有外键被其他表引用删除期间可能因为外键检查导致锁的范围扩大此时最好先跟业务确认关联表的情况或者选择在维护窗口执行。4.4 空值数据和 DISTINCT 的坑再补充一个细节前面我在 1.3 里提到空值会导致“伪重复”这里再补充一个删除时容易踩坑的点。假设你要用 DISTINCT ON 或者 GROUP BY 去重而判定重复的字段里有 NULL那么 PostgreSQL 会把所有 NULL 归为一组。比如你执行SELECT DISTINCT ON (coupon_code) * FROM orders ORDER BY coupon_code, id;没使用优惠券的订单coupon_code 为 NULL会被当成同一组最终只保留一条。这肯定不是你想要的结果。所以处理前先明确空值那一组的保留策略是什么。如果业务上空值代表“没有使用优惠券”它们本来就是合法存在那你就应该用COUPON_CODE IS NOT NULL先过滤掉空值记录只对非空值做去重DELETE FROM orders WHERE coupon_code IS NOT NULL AND ctid NOT IN ( SELECT MIN(ctid) FROM orders WHERE coupon_code IS NOT NULL GROUP BY coupon_code );这样空值记录完全不受影响。类似的情况还包括空字符串和 NULL 混存的情况处理逻辑要按业务语义来定。5. 删除完成后的收尾检查清单很多人删完数据就以为任务结束了但我建议再做一轮收尾检查避免留下隐患。5.1 核对删前删后的行数这是最基础的一步。删除前记录表的总行数删除后再次统计确认“剩余行数 去重后的行数”。千万别只凭 DELETE 命令返回的影响行数来判断那个数字只能说明删了多少行不能说明剩下的是否正确。SELECT COUNT(*) FROM users;如果结果符合预期再随机抽查几个重复组的记录确保保留的是你想要的那一条。这一步说起来简单但在生产环境真的帮过我避免几次大事故。5.2 补充唯一约束防止重复再次产生重复数据清理干净之后如果业务上确实要求这些字段唯一一定要补上唯一索引。不然过段时间重复数据又会长出来白忙活一场。CREATE UNIQUE INDEX idx_users_phone ON users (phone);如果判定重复的字段有多个列就建复合唯一索引。注意先确认没有 NULL 重复的坑再来建索引。5.3 检查表膨胀和索引健康度删除大量数据后表的膨胀可能会让你后续的查询越来越慢。可以查一下表的实时状态SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname users;如果 n_dead_tup 很大说明积累了不少死元组该 VACUUM 了。如果表物理文件很大该 VACUUM FULL 就 VACUUM FULL。这些操作我通常安排在维护窗口执行。提示VACUUM FULL 会锁表在线业务环境慎用。如果只是想让死元组占比降下来先跑 VACUUM看看效果不行再考虑 VACUUM FULL。写在最后的一点个人体会PostgreSQL 删除重复数据这件事表面上是几条 SQL 的问题实际上是“确认业务规则、评估数据规模、选择安全策略、执行后清理验证”的一整套流程。我刚开始处理这类需求时也吃过亏有一次直接跑了一版大 DELETE把一张订单表锁了将近十分钟业务方差点炸毛。后来学乖了每次都先备份、再分批、再验证一套流程走下来稳得一批。如果你也在处理类似问题我最想强调的就三个字先备份。无论是 CREATE TABLE 备份一份原表还是先 SELECT 确认影响行数都比直接执行 DELETE 稳妥得多。数据这东西删错了想找回代价往往超乎你的想象。希望这篇内容能帮你少踩几个坑。