PostgreSQL 表删除与优化操作笔记

摘要：本文总结了在 PostgreSQL 中如何安全地删除多个关联表中的数据，并优化性能和磁盘空间的使用。重点包括删除顺序、索引优化、分批执行、后台任务设置以及 VACUUM 的正确使用。

1. 删除顺序与逻辑

      在删除多个关联表（如 torrent_files, torrents, torrent_contents）时，必须遵循以下顺序：

1. torrent_files
2. torrents
3. torrent_contents（最后删除）

      原因：确保在判断哪些 info_hash 需要删除时，torrent_contents 表仍然存在且数据完整。

2. 索引优化

      为提高查询和删除效率，建议创建以下索引：

-- 加速 name LIKE '%VR%' 查询
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_torrents_name_trgm ON torrents USING gin (name gin_trgm_ops);

-- 加速 info_hash 查询
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_torrent_contents_info_hash ON torrent_contents(info_hash);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_torrent_files_info_hash ON torrent_files(info_hash);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_torrents_info_hash ON torrents(info_hash);

-- 加速 content_type 查询
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_torrent_contents_content_type ON torrent_contents(content_type);

3. 分批删除与后台执行

      为了避免超时和锁表问题，推荐采用分批删除的方式，每次处理少量数据，并结合事务控制。

DO $$
BEGIN
    LOOP
        DELETE FROM torrent_files
        USING torrent_contents tc
        WHERE torrent_files.info_hash = tc.info_hash
          AND (tc.content_type IS NULL OR tc.content_type = 'xxx');

        EXIT WHEN NOT FOUND;
        COMMIT;
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

      可以通过脚本或定时任务（如 cron 或 pg_cron）实现后台运行。

4. 磁盘空间释放

      执行 DELETE 后，磁盘空间不会立即释放，因为 PostgreSQL 使用 MVCC 机制。需要通过 VACUUM 或 VACUUM FULL 来清理死元组并释放空间。

VACUUM torrent_files;
VACUUM torrents;
VACUUM torrent_contents;

-- 如果需要立即释放空间给操作系统
VACUUM FULL torrent_files;
VACUUM FULL torrents;
VACUUM FULL torrent_contents;

5. 注意事项

• 不要先删除 torrent_contents，否则无法判断哪些 info_hash 需要删除。
• 避免使用 NOT IN 删除其他表的数据，容易误删。
• 使用 USING 或 EXISTS 替代 IN 可提升性能。
• 定期执行 VACUUM 和 REINDEX 以维护数据库性能。

6. 总结

      在 PostgreSQL 中删除关联表数据时，需遵循正确的顺序，利用索引优化查询性能，分批执行以避免超时，并通过 VACUUM 释放磁盘空间。这些步骤能确保数据安全、操作高效。