I've got a database which has approximately 43GB. When I count the size of pg_largeobject table it gives me 43GB too:
SELECT pg_size_pretty(pg_table_size('pg_largeobject'));
When I sum the sizes of all large objects by the following command:
SELECT sum(length(pg.data)) FROM pg_largeobject pg;
it gives me 32.5GB. I've tried to unlink all objects apart from the desirable ones by:
SELECT lo_unlink(l.loid)
FROM pg_largeobject l
GROUP BY loid
HAVING (NOT EXISTS (SELECT 1 FROM data_file t WHERE t.file_content = l.loid))
AND (NOT EXISTS (SELECT 1 FROM license t WHERE t.attachment_content = l.loid))
AND (NOT EXISTS (SELECT 1 FROM personal_license t WHERE t.attachment_content = l.loid))
AND (NOT EXISTS (SELECT 1 FROM scenario t WHERE t.scenario_file = l.loid))
AND (NOT EXISTS (SELECT 1 FROM service_result t WHERE t.content = l.loid));
and run VACUUM:
VACUUM FULL ANALYZE pg_largeobject;
When I tried it once, it reduced the size from 47GB to 43 but I cannot find the missing 10.5 GB Do you know what can be stored there or what should I do?
