PostgreSQL下people表软去重的SQL实现方案问询

云原生阿岚

2026-5-15

PostgreSQL原生SQL实现千万级People表软去重（每日Cron调度）

Got it, let's tackle this soft deduplication problem for your massive (10M+ rows) people table. Replacing your slow script with native PostgreSQL SQL is the right move for scale, and we can make it run reliably via daily cron. Here's a step-by-step, optimized solution:

1. 先定义相似度计算函数

我们先创建一个可复用的函数，按照你的规则计算两条记录的相似度得分：匹配列加A1，不匹配列减A2。我假设你的people表是常见结构（可根据实际表结构调整字段）：

CREATE TABLE people (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(100),
    address TEXT,
    duplicate_ids TEXT[], -- 用数组存重复ID比字符串更易维护
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

相似度计算函数：

CREATE OR REPLACE FUNCTION calculate_similarity(p1 people, p2 people, A1 INT, A2 INT)
RETURNS INT AS $$
DECLARE
    score INT := 0;
BEGIN
    -- 逐列对比，按规则计算得分
    IF p1.name = p2.name THEN score := score + A1; ELSE score := score - A2; END IF;
    IF p1.phone = p2.phone THEN score := score + A1; ELSE score := score - A2; END IF;
    IF p1.email = p2.email THEN score := score + A1; ELSE score := score - A2; END IF;
    IF p1.address = p2.address THEN score := score + A1; ELSE score := score - A2; END IF;
    -- 如果表中有其他字段，在这里继续添加对比逻辑
    RETURN score;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

标记为IMMUTABLE是告诉PostgreSQL：相同输入会返回相同结果，这有助于查询优化。

2. 高效匹配重复对（拒绝全表扫描！）

千万级数据下，全表笛卡尔积是性能杀手。我们需要先通过候选过滤条件缩小匹配范围——比如只对比姓名相同或手机号前缀相同的记录，只在这些有重复可能性的组内计算相似度，把计算量降到原来的几十分之一。

WITH candidate_pairs AS (
    SELECT
        p1.id AS main_id,
        p2.id AS duplicate_id,
        calculate_similarity(p1, p2, 5, 2) AS similarity_score -- 替换成你的A1/A2值
    FROM people p1
    JOIN people p2 ON 
        -- 核心：先缩小候选范围，避免全表关联
        (p1.name = p2.name OR LEFT(p1.phone, 7) = LEFT(p2.phone, 7))
        AND p1.id > p2.id -- 避免重复配对（比如p1-p2和p2-p1只算一次）
        AND p1.is_active = TRUE
        AND p2.is_active = TRUE
    WHERE calculate_similarity(p1, p2, 5, 2) > 10 -- 替换成你的阈值X
),
-- 为每个重复记录确定最新的主记录（按创建时间排序）
main_records AS (
    SELECT
        duplicate_id,
        FIRST_VALUE(main_id) OVER (
            PARTITION BY duplicate_id 
            ORDER BY (SELECT created_at FROM people WHERE id = main_id) DESC
        ) AS final_main_id
    FROM candidate_pairs
)

窗口函数FIRST_VALUE确保每个重复记录只会关联到最新的主记录，避免出现冲突的关联关系。

3. 执行软去重更新操作

接下来更新主记录的duplicate_ids数组，并标记旧记录为停用：

-- 第一步：将重复ID追加到主记录的duplicate_ids数组中
UPDATE people p_main
SET duplicate_ids = ARRAY_APPEND(COALESCE(p_main.duplicate_ids, '{}'::TEXT[]), mr.duplicate_id)
FROM main_records mr
WHERE p_main.id = mr.final_main_id;

-- 第二步：标记重复记录为停用状态
UPDATE people p_dup
SET is_active = FALSE
FROM main_records mr
WHERE p_dup.id = mr.duplicate_id;

COALESCE处理了duplicate_ids为NULL的情况（初始状态），默认用空数组开始追加。

4. 千万级数据的性能优化要点

关键字段加索引：给候选过滤字段和状态字段加索引，加速查询过滤：

CREATE INDEX idx_people_name ON people(name);
CREATE INDEX idx_people_phone_prefix ON people(LEFT(phone, 7));
CREATE INDEX idx_people_active_created ON people(is_active, created_at);

分批处理：如果一次更新锁表时间太长，按ID范围拆分批次（比如每次处理10万条）：
```
-- 示例：在candidate_pairs的WHERE中添加批次过滤
AND p1.id BETWEEN 1 AND 100000
```
开启并行查询：确保PostgreSQL配置中max_parallel_workers_per_gather设置合理（比如8），利用多核CPU提升查询速度。
避免长事务：把更新拆成多个小事务，防止长时间持有锁影响业务。

5. 每日Cron调度配置

创建一个shell脚本soft_deduplicate.sh来执行SQL：

#!/bin/bash
# 配置PostgreSQL凭据（也可以用.pgpass文件更安全）
export PGPASSWORD='你的数据库密码'
psql -U 你的数据库用户 -d 你的数据库名 -f /path/to/你的去重脚本.sql

然后添加到crontab，设置每日执行（比如凌晨2点低峰期）：

# 编辑crontab
crontab -e

# 添加以下内容，每天凌晨2点执行
0 2 * * * /path/to/soft_deduplicate.sh >> /var/log/deduplication.log 2>&1

日志文件可以帮助你监控每日任务的执行情况。

重要注意事项

先测试再上线：先在 staging 环境用测试数据验证相似度计算和去重逻辑，避免误标非重复记录。
执行前备份数据：运行大规模更新前一定要备份people表，以防意外。
定期监控：抽查duplicate_ids和is_active字段，确保逻辑执行符合预期。

内容的提问来源于stack exchange，提问作者oldhomemovie

PostgreSQL下people表软去重的SQL实现方案问询

1. 先定义相似度计算函数 #

2. 高效匹配重复对（拒绝全表扫描！） #

3. 执行软去重更新操作 #

4. 千万级数据的性能优化要点 #

5. 每日Cron调度配置 #