PostgreSQL：删除用户超100条的旧事件，保留最新100条（查询无响应）

嘿，我来帮你搞定这个事件表的清理问题！之前查询无响应大概率是因为没用到合适的索引，或者用了低效的写法导致数据库全表扫描、锁表卡死。下面给你一套可行的高效方案：

第一步：先建关键索引（必做！） #

要快速定位每个用户的最新100条事件，必须给user_id和date建一个复合索引，这样数据库不用全表扫就能快速排序分组：

CREATE INDEX idx_events_user_date ON events (user_id, date DESC);

如果已经有类似的索引可以跳过，但确保是user_id在前、date倒序的复合索引。

第二步：选择合适的删除方式 #

根据你的表数据量大小，选下面两种方式之一：

方式1：一次性删除（适合数据量不大的情况） #

用窗口函数ROW_NUMBER()给每个用户的事件按日期倒序编号，编号大于100的就是要删除的旧数据：

WITH ranked_events AS (
    SELECT 
        id,
        -- 按用户分组，日期倒序排，最新的是第1条
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
    FROM events
)
DELETE FROM events
WHERE id IN (SELECT id FROM ranked_events WHERE rn > 100);

执行前可以先跑下面的查询确认要删除的行数，避免误删：

SELECT COUNT(*) 
FROM (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
    FROM events
) sub
WHERE rn > 100;

方式2：分批删除（适合大数据量表，避免锁表） #

如果你的events表有几百万甚至几千万条数据，一次性删除会导致事务过长、锁表影响业务，这时候分批删更稳妥：

DO $$
DECLARE
    deleted_rows INT;
BEGIN
    LOOP
        WITH ranked_events AS (
            SELECT id
            FROM (
                SELECT 
                    id,
                    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
                FROM events
                LIMIT 10000  -- 每次处理1万条，可根据你的数据库性能调整
            ) sub
            WHERE rn > 100
        )
        DELETE FROM events
        WHERE id IN (SELECT id FROM ranked_events)
        RETURNING 1 INTO deleted_rows;
        
        -- 没数据可删就退出循环
        EXIT WHEN deleted_rows = 0;
        -- 每批提交一次，释放锁，避免长时间占用资源
        COMMIT;
    END LOOP;
END $$;

为什么之前的查询无响应？ #

大概率是这几个原因：

• 没有建上述的复合索引，导致数据库做全表扫描+排序，数据量大时直接卡死
• 用了低效的子查询关联写法（比如DELETE FROM events e WHERE EXISTS (SELECT ...)），没有索引支撑时性能极差
• 一次性删除的行数太多，导致事务日志暴涨，数据库来不及处理

额外注意事项 #

• 尽量在业务低峰期执行删除操作，避免影响正常业务
• 如果表有外键关联，需要先处理关联数据，或者暂时禁用外键约束（生产环境谨慎操作）
• 执行完删除后，可以用VACUUM ANALYZE events;来清理表空间并更新统计信息，提升后续查询性能

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