如何用PostgreSQL查询未被数据库引用的对象存储文件

嘿，我完全懂你的需求——你现在的SQL逻辑确实搞反方向了，咱们来把它调整到你想要的轨道上😉

核心问题梳理 #

你需要的是从对象存储传入的文件列表（也就是$1数组）里，找出那些没有被events和categories表中任何图片字段引用的文件，而不是找数据库里那些字段值不在$1里的记录。

正确的SQL实现思路 #

我们可以先把数据库中所有被实际使用的图片路径收集起来，再从$1数组里排除这些路径，剩下的就是可以安全清理的“垃圾文件”。这里提供两种常用的实现方式：

方式一：使用NOT IN（简洁直观）

SELECT file
FROM unnest($1) AS file
WHERE file NOT IN (
    -- 收集所有被events表引用的图片路径，过滤空值避免干扰
    SELECT e.image FROM events e WHERE e.image IS NOT NULL
    UNION ALL
    SELECT e.cropped_image FROM events e WHERE e.cropped_image IS NOT NULL
    UNION ALL
    SELECT e.cropped_image_thumb FROM events e WHERE e.cropped_image_thumb IS NOT NULL
    UNION ALL
    SELECT e.promoted_img FROM events e WHERE e.promoted_img IS NOT NULL
    -- 收集所有被categories表引用的图片路径
    UNION ALL
    SELECT c.header_image FROM categories c WHERE c.header_image IS NOT NULL
    UNION ALL
    SELECT c.list_image FROM categories c WHERE c.list_image IS NOT NULL
)

方式二：使用LEFT JOIN（大数据集下性能更稳定）

如果你的数据库里图片引用记录非常多，LEFT JOIN的方式通常比NOT IN更高效，也能避免一些空值相关的潜在问题：

SELECT file
FROM unnest($1) AS file
LEFT JOIN (
    SELECT e.image AS used_file FROM events e WHERE e.image IS NOT NULL
    UNION ALL
    SELECT e.cropped_image FROM events e WHERE e.cropped_image IS NOT NULL
    UNION ALL
    SELECT e.cropped_image_thumb FROM events e WHERE e.cropped_image_thumb IS NOT NULL
    UNION ALL
    SELECT e.promoted_img FROM events e WHERE e.promoted_img IS NOT NULL
    UNION ALL
    SELECT c.header_image FROM categories c WHERE c.header_image IS NOT NULL
    UNION ALL
    SELECT c.list_image FROM categories c WHERE c.list_image IS NOT NULL
) AS used_files ON file = used_files.used_file
WHERE used_files.used_file IS NULL

适配你的分批处理方案 #

这个查询完美契合你的FaaS分批逻辑：每次你传入100个文件的子集作为$1，运行后就会直接返回这100个文件里未被数据库引用的那些，你可以直接把这些结果标记为待删除的垃圾文件。

额外优化建议 #

1. 路径一致性检查：确保数据库里存储的路径和对象存储的路径完全一致（比如大小写、前缀/后缀、斜杠格式等），避免因为格式差异导致误判。
2. 性能优化（可选）：如果数据库规模很大，可以创建一个物化视图定期刷新所有被引用的图片路径，垃圾回收时直接查询物化视图，能大幅提升查询速度。
3. 空值过滤：一定要保留WHERE ... IS NOT NULL的条件，否则空值会干扰NOT IN或JOIN的匹配逻辑。

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