PostgreSQL 12大对象内存分配不符排查与空间释放咨询

一、排查2.6GB大对象空间分配细节 #

1. 统计所有大对象的实际占用 #

直接查询pg_largeobject计算每个大对象的真实大小（大对象默认页大小为2048字节）：

SELECT lo AS lob_oid,
       count(*) * 2048 AS total_bytes,
       pg_size_pretty(count(*) * 2048) AS total_size
FROM pg_largeobject
GROUP BY lo
ORDER BY total_bytes DESC;

将所有total_bytes求和，对比是否接近2.6GB，确认大对象总占用的准确性。

2. 关联业务表验证引用的LOB总大小 #

针对你那5个关联LOB的业务表，逐个统计每个表引用的LOB总占用（替换示例中的tableX和lob_oid_column为实际表名和字段名）：

-- 示例：统计单个业务表的LOB总占用
SELECT 'table1' AS table_name,
       sum(lob_stats.total_bytes) AS total_bytes,
       pg_size_pretty(sum(lob_stats.total_bytes)) AS total_size
FROM table1 t
JOIN (
    SELECT lo, count(*) * 2048 AS total_bytes
    FROM pg_largeobject
    GROUP BY lo
) lob_stats ON t.lob_oid_column = lob_stats.lo;

将5个表的统计结果求和，与pg_largeobject的总占用对比，判断是否存在未被业务表引用的LOB（即使vacuumlo未检测到）。

3. 校验大对象元数据与数据一致性 #

查询pg_largeobject_metadata（大对象元数据表），验证元数据与实际数据的匹配性：

SELECT count(*) AS total_lob_count,
       pg_size_pretty(sum((SELECT count(*) * 2048 FROM pg_largeobject lo WHERE lo.lo = lom.oid))) AS total_lob_size
FROM pg_largeobject_metadata lom;

如果这里的总大小与pg_largeobject统计的不一致，可能存在元数据异常，需要进一步检查。

4. 检查表空间分布 #

确认大对象是否分布在多个表空间：

SELECT ts.spcname AS tablespace_name,
       pg_size_pretty(count(*) * 2048) AS lob_size
FROM pg_largeobject lo
JOIN pg_class c ON c.oid = 'pg_largeobject'::regclass
JOIN pg_tablespace ts ON ts.oid = c.reltablespace
GROUP BY ts.spcname;

二、释放大对象占用空间 #

1. 手动清理孤儿LOB #

如果业务表关联统计与大对象总占用存在差值，手动查询未被引用的孤儿LOB：

SELECT lom.oid AS orphan_lob_oid
FROM pg_largeobject_metadata lom
LEFT JOIN table1 t1 ON lom.oid = t1.lob_oid_column
LEFT JOIN table2 t2 ON lom.oid = t2.lob_oid_column
LEFT JOIN table3 t3 ON lom.oid = t3.lob_oid_column
LEFT JOIN table4 t4 ON lom.oid = t4.lob_oid_column
LEFT JOIN table5 t5 ON lom.oid = t5.lob_oid_column
WHERE t1.lob_oid_column IS NULL
  AND t2.lob_oid_column IS NULL
  AND t3.lob_oid_column IS NULL
  AND t4.lob_oid_column IS NULL
  AND t5.lob_oid_column IS NULL;

若查询到结果，执行删除：

SELECT lo_unlink(orphan_lob_oid) FROM (上面的查询语句) AS orphan_lobs;

2. 整理大对象碎片 #

即使没有孤儿LOB，大对象更新/删除操作可能产生内部碎片，执行VACUUM FULL整理（注意：此操作会锁表，需在业务低峰期执行）：

VACUUM FULL pg_largeobject;

3. 确认空间释放 #

清理完成后，检查大对象表的已用/未用空间：

SELECT pg_size_pretty(pg_relation_size('pg_largeobject')) AS used_size,
       pg_size_pretty(pg_total_relation_size('pg_largeobject') - pg_relation_size('pg_largeobject')) AS unused_size;

VACUUM会将空间标记为数据库内部可复用，若需要立即归还操作系统，VACUUM FULL是最直接的方式（针对文件系统表空间）。

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