From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mutanoidsgame@gmail.com
Subject: BUG #17201: Pg_largeobject table grows uncontrollably
Date: Thu, 23 Sep 2021 08:30:12 +0000
Message-ID: <17201-6eadff707f06f791@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, mutanoidsgame@gmail.com
Subject: Re: BUG #17201: Pg_largeobject table grows uncontrollably
In-Reply-To: <17201-6eadff707f06f791@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox