Re: DB size and TABLE sizes don't seem to add up

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 02/18/2014 12:14 AM, David Wall wrote:
I am running PG 9.2.4 and I am trying to figure out why my database size
shows one value, but the sum of my total relation sizes is so much less.

Basically, I'm told my database is 188MB, but the sum of my total
relation sizes adds up to just 8.7MB, which is 1/20th of the reported
total.  Where is the 19/20th of my data then?  We do make significant
use of large objects, so I suspect it's in there.  Is there a relation
size query that would include the large object data associated with any
OIDs in those tables?

You can use "select pg_total_relation_size('pg_largeobject')" to get the total size of the large objects. Attributing large objects to the tables that refer them is more difficult. For a single table, something like this:

select sum(pg_column_size(lo.data))
from lotest_stash_values t, pg_largeobject lo
where lo.loid = t.loid;

Replace "lotest_stash_values" with the table's name and lo.loid with the name of the OID column.

- Heikki


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux