From: | Янченко Владимир <vyanchenko(at)naumen(dot)ru> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Strange size of pg_largeobject |
Date: | 2017-07-20 15:59:12 |
Message-ID: | ee359485-255b-864c-dea9-e1d2754c0ab7@naumen.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-07-20 16:50:56 | Re: Strange size of pg_largeobject |
Previous Message | Bear Giles | 2017-07-19 16:01:41 | Re: Recovery of corrupted database |