PostgreSQL中如何删除pg_largeobject表内指定OID对应数据

当业务表包含oid类型的大对象引用列时，执行truncate操作只会清空表内的行数据，不会自动触发对应大对象的清理流程，最终会在pg_largeobject系统表中残留无引用的分片数据，可通过以下规范流程完成清理。

核心注意事项 #

禁止直接对pg_largeobject表执行裸DELETE操作。大对象的存储分为两层：

• 底层分片数据存储在pg_largeobject表
• 元数据记录存储在pg_largeobject_metadata表
  直接删除pg_largeobject中的数据只会留下孤立的元数据记录，长期运行会造成系统表冗余，正确的清理方式是调用PostgreSQL内置的lo_unlink函数，该函数会自动完成两层存储的完整删除。

针对department表场景的操作步骤 #

针对department表（含description OID类型列）的清理流程，建议按以下顺序执行：

1. 先在truncate业务表前，遍历取出所有现存的description列对应的大对象OID
2. 对每一个OID调用lo_unlink函数，完成对应大对象（含pg_largeobject中关联数据）的删除
3. 确认大对象全部清理完成后，再执行truncate操作清空业务表

对应的可执行示例代码：

-- 批量清理department表中description列关联的所有大对象
DO $$
DECLARE
  target_oid oid;
BEGIN
  -- 遍历取出所有非空的大对象引用ID
  FOR target_oid IN SELECT description FROM department WHERE description IS NOT NULL LOOP
    -- 执行完整的大对象清理，自动删除pg_largeobject及对应元数据
    PERFORM lo_unlink(target_oid);
  END LOOP;
END $$;

-- 大对象清理完成后执行表截断
TRUNCATE TABLE department;

已误执行truncate后的残留数据清理 #

如果已经提前完成了表截断，无法从业务表拿到原有的大对象OID，可通过系统表反查无引用的孤儿大对象，再批量完成清理：

-- 查找所有没有被department.description列引用的孤儿大对象OID
SELECT oid FROM pg_largeobject_metadata
WHERE oid NOT IN (SELECT description FROM department WHERE description IS NOT NULL);

拿到孤儿OID列表后，批量调用lo_unlink完成删除即可。

常见操作坑点 #

• 执行lo_unlink的数据库账号需要为对应大对象的属主，或拥有超级用户权限，否则会触发权限不足报错
• 若后续需要长期使用OID类型列存储大对象，可开启lo_perform自动清理开关，删除业务表行时会自动触发关联大对象的删除，避免残留垃圾数据
• 不要尝试直接操作pg_largeobject的主键索引跳过元数据检查，这类操作不在PostgreSQL的系统表保护机制内，极端场景下可能造成系统表损坏

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