PostgreSQL重复数据处理：从检测到安全删除的实战指南

最近又被群里的小伙伴问到“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换成ctid：

DELETE 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 或窗口函数结合 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 稳妥得多。数据这东西，删错了想找回，代价往往超乎你的想象。希望这篇内容能帮你少踩几个坑。