From: Янченко Владимир <vyanchenko@naumen.ru>
To: pgsql-admin@postgresql.org
Subject: Strange size of pg_largeobject
Date: Thu, 20 Jul 2017 20:59:12 +0500
Message-ID: <ee359485-255b-864c-dea9-e1d2754c0ab7@naumen.ru> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hi all,
I found that my pg_largeobject table is bigger than it should be:
*Size:*
mybase=# SELECT pg_size_pretty(pg_total_relation_size('pg_largeobject'));
18 GB
mybase=# SELECT
pg_size_pretty(pg_total_relation_size('pg_largeobject_loid_pn_index'));
162 MB
*Rows count and large objects count:*
mybase=# select count(*) from pg_largeobject
38406
mybase=# select count(*) from pg_largeobject where pageno = 0;
778
*The largest objects:*
mybase=# select loid, count(pageno) from pg_largeobject group by loid
order by count(pageno) limit 5 desc;
loid | count
-----------+-------
406485914 | 2188
167710689 | 1114
412765517 | 996
307045064 | 842
167714441 | 645
I write the largest lo to the file system and check their sizes:
select lo_export(167710689, '/tmp/1');
select lo_export(406485914, '/tmp/2');
ls -lh /tmp/
-rw-r--r-- 1 postgres postgres *4,3M* июл 20 15:22 123
-rw-r--r-- 1 postgres postgres *2,2M* июл 20 15:23 1234
therefore, maximum size of my table should be 2.2*777 + 4.3 = 1713.7 mb
(size2*[large objects count] + size1).
Before I execute queries, that i mentioned above, I run vacuum full. But
it does not effect. Now table bloating factor about 20%.
Please, help me, if you have any suggestions about my problem.
--
Best regards,
Vladimir Yanchenko
Tech support engineer
Naumen
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-admin@postgresql.org
Cc: vyanchenko@naumen.ru
Subject: Re: Strange size of pg_largeobject
In-Reply-To: <ee359485-255b-864c-dea9-e1d2754c0ab7@naumen.ru>
* 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