PostgreSQL实现5分钟间隔删除idle事件 保留区间首尾记录

该方案完全可行。相比PHP脚本拉取数据到应用层计算、再回库删除的实现模式，直接在PostgreSQL内做计算可以避免海量数据跨网络传输，配合合适的索引，处理速度比PHP脚本高1~2个数量级，也不会出现PHP脚本常见的内存溢出、长执行时间超时问题。

• 操作前必须全量备份目标表，400GB级数据删错后恢复成本极高
• 建立联合索引避免全表扫描，考虑到表持续有新数据写入，用CONCURRENTLY模式建索引不会锁表阻塞业务：

CREATE INDEX CONCURRENTLY idx_chip_topic_createtime ON chip_event (chip_mac, topic, created_at);

注：以下SQL默认事件表名为chip_event，存储芯片MAC/唯一标识的字段为chip_mac，如果你的实际表名、字段名不同，直接替换为对应名称即可。

核心处理逻辑完全匹配你的规则要求：

• 先按芯片分组，筛选出每个芯片的第一条idle事件时间，作为5分钟间隔的计算基准
• 对每个芯片的所有idle事件，以首个idle时间为起点，每300秒（5分钟）划分为一个时间桶
• 每个时间桶内仅保留时间最早（间隔起点）、时间最晚（间隔终点）的2条记录，桶内其余idle记录标记为待删除
• 采用分批删除模式执行，避免单次删除数据量过大打满IO、触发长事务锁表

第一步：逻辑校验 #

先执行以下查询，核对待保留、待删除的记录是否符合规则，确认无误后再执行删除操作：

WITH chip_first_idle AS (
    -- 取每个芯片的第一条idle事件时间作为计算基准
    SELECT 
        chip_mac,
        MIN(created_at) AS first_idle_time
    FROM chip_event
    WHERE topic = 'idle'
    GROUP BY chip_mac
),
idle_record_with_bucket AS (
    -- 为每条idle记录计算所属的5分钟分桶
    SELECT 
        ce.id,
        ce.chip_mac,
        ce.created_at,
        FLOOR(EXTRACT(EPOCH FROM (ce.created_at - cfi.first_idle_time)) / 300) AS bucket_num
    FROM chip_event ce
    INNER JOIN chip_first_idle cfi 
        ON ce.chip_mac = cfi.chip_mac
    WHERE ce.topic = 'idle'
),
keep_record AS (
    -- 每个分桶内仅保留最早（起点）、最晚（终点）的记录
    SELECT id
    FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY chip_mac, bucket_num ORDER BY created_at ASC) AS rn_asc,
            ROW_NUMBER() OVER (PARTITION BY chip_mac, bucket_num ORDER BY created_at DESC) AS rn_desc
        FROM idle_record_with_bucket
    ) t
    WHERE rn_asc = 1 OR rn_desc = 1
)
-- 查询待删除的记录总数，可加chip_mac过滤条件抽查单个芯片的保留结果是否符合预期
SELECT COUNT(*) AS will_delete_count
FROM chip_event ce
WHERE 
    ce.topic = 'idle'
    AND ce.id NOT IN (SELECT id FROM keep_record);

你可以在上述查询末尾增加AND chip_mac = 'MAC-ADDRESS1'这类过滤条件，抽查单个芯片的保留记录，确认和示例规则完全匹配后再执行删除。

第二步：分批执行删除 #

因为表数据量达400GB，禁止一次性删除所有待清理记录，单次删除10000条，循环执行直到语句影响行数为0即可。建议在业务低峰期执行，每次删除间隔1~2秒，避免IO尖峰影响正常业务：

WITH chip_first_idle AS (
    SELECT 
        chip_mac,
        MIN(created_at) AS first_idle_time
    FROM chip_event
    WHERE topic = 'idle'
    GROUP BY chip_mac
),
idle_record_with_bucket AS (
    SELECT 
        ce.id,
        ce.chip_mac,
        ce.created_at,
        FLOOR(EXTRACT(EPOCH FROM (ce.created_at - cfi.first_idle_time)) / 300) AS bucket_num
    FROM chip_event ce
    INNER JOIN chip_first_idle cfi 
        ON ce.chip_mac = cfi.chip_mac
    WHERE ce.topic = 'idle'
),
keep_record AS (
    SELECT id
    FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY chip_mac, bucket_num ORDER BY created_at ASC) AS rn_asc,
            ROW_NUMBER() OVER (PARTITION BY chip_mac, bucket_num ORDER BY created_at DESC) AS rn_desc
        FROM idle_record_with_bucket
    ) t
    WHERE rn_asc = 1 OR rn_desc = 1
)
DELETE FROM chip_event
WHERE id IN (
    SELECT id
    FROM chip_event ce
    WHERE 
        ce.topic = 'idle'
        AND ce.id NOT IN (SELECT id FROM keep_record)
    LIMIT 10000
);

• 清理完成后可以配置定时任务，按天/按周执行小批量清理，避免数据堆积到数百GB再处理，大幅降低清理耗时
• 如果该表按时间做了分区，直接在对应分区上执行清理逻辑，效率会进一步提升
• 清理完成后可以根据业务需要，对表做VACUUM FULL回收磁盘空间，注意这个操作会锁表，务必在低峰期执行

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