The following bug has been logged on the website:
Bug reference: 17201
Logged by: Mutanoids Game
Email address: mutanoidsgame@gmail.com
PostgreSQL version: 10.6
Operating system: windows
Description:
Hello.
Faced such a problem that the pg_largeobject and pg_largeobject_metadata
tables take up a lot of space. It did not happen immediately, after 4
months.
At the moment, I have one table with a link to pg_largeobject, in which 400
small pictures are stored in the form of a blob, each picture is about 300 -
500 kb, but the pg_largeobject table is 15 GB, pg_largeobject_metadata is 1
GB.
As a result, I solved this problem by getting rid of blob-type fields in the
database and remaking all such fields into bytea. After that, I ran the
script
"SELECT lo_unlink (l.oid) FROM pg_largeobject_metadata"
After a while, pg_largeobject cleared itself.
I solved the problem, but I would like to know what is happening with the
pg_largeobject table, since as a result, there were more than 10,000,000
records, how can this be if there were only about 400 links.
Also tried vacuum in vaccumlo - it didn't help. I just have another project
in which there are already a lot of tables with links to pg_largeobject, and
the pg_largeobject and pg_largeobject_metadata tables also grow very quickly
and up to 100 GB, although there is not so much data in the files. Maybe
there are some settings that I don't know about. All the advice that I found
on the sites and forums did not help, nothing was cleared, only the
transition to bytea helped.
Thanks in advance for your reply.