To: pgsql-admin@postgresql.org
From: =?UTF-8?B?0K/QvdGH0LXQvdC60L4g0JLQu9Cw0LTQuNC80LjRgA==?=
 <vyanchenko@naumen.ru>
Subject: Strange size of pg_largeobject
Message-ID: <ee359485-255b-864c-dea9-e1d2754c0ab7@naumen.ru>
Date: Thu, 20 Jul 2017 20:59:12 +0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------5D108064646E980B76EF8D33"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------5D108064646E980B76EF8D33
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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


--------------5D108064646E980B76EF8D33
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Hi all,<br>
    <br>
    I found that my pg_largeobject table is bigger than it should be:<br>
    <br>
    <b>Size:</b><br>
    mybase=# SELECT
    pg_size_pretty(pg_total_relation_size('pg_largeobject'));<br>
     18 GB<br>
    mybase=# SELECT
    pg_size_pretty(pg_total_relation_size('pg_largeobject_loid_pn_index'));<br>
     162 MB<br>
    <br>
    <b>Rows count and large objects count:</b><br>
    mybase=# select count(*) from pg_largeobject<br>
    38406<br>
    mybase=# select count(*) from pg_largeobject where pageno = 0;<br>
    778<br>
    <br>
    <b>The largest objects:</b><br>
    mybase=# select loid, count(pageno) from pg_largeobject group by
    loid order by count(pageno) limit 5 desc;<br>
       loid    | count <br>
    -----------+-------<br>
     406485914 |  2188<br>
     167710689 |  1114<br>
     412765517 |   996<br>
     307045064 |   842<br>
     167714441 |   645<br>
    <br>
    I write the largest lo to the file system and check their sizes:<br>
    select lo_export(167710689, '/tmp/1');<br>
    select lo_export(406485914, '/tmp/2');<br>
    ls -lh /tmp/<br>
    -rw-r--r-- 1 postgres postgres <b>4,3M</b> июл 20 15:22 123<br>
    -rw-r--r-- 1 postgres postgres <b>2,2M</b> июл 20 15:23 1234<br>
    <br>
    therefore, maximum size of my table should be 2.2*777 + 4.3 = 1713.7
    mb (size2*[large objects count] + size1).<br>
    <br>
    Before I execute queries, that i mentioned above, I run vacuum full.
    But it does not effect. Now table bloating factor about 20%.<br>
    <br>
    <br>
    Please, help me, if you have any suggestions about my problem.<br>
    <pre class="moz-signature" cols="72">--
Best regards,
Vladimir Yanchenko
Tech support engineer
Naumen
</pre>
  </body>
</html>

--------------5D108064646E980B76EF8D33--