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