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.
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 |