BUG #17201: Pg_largeobject table grows uncontrollably

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: mutanoidsgame(at)gmail(dot)com
Subject: BUG #17201: Pg_largeobject table grows uncontrollably
Date: 2021-09-23 08:30:12
Message-ID: 17201-6eadff707f06f791@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17201
Logged by: Mutanoids Game
Email address: mutanoidsgame(at)gmail(dot)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.

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2021-09-23 21:14:06 BUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is)
Previous Message Budi Setiawan 2021-09-23 04:44:46 Re: BUG #17183: missing websearch_to_tsquery