On 2/18/2014 12:34 AM, Heikki Linnakangas wrote:
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.
Thanks, Heikki. It's generally even trickier for us because we have a
blob table that other components use for storing
large/binary/unstructured objects (the code handles
compression/decompression and encryption/decryption options for us). So
those tables have an UUID that points to a row in that table that
contains the actual LOID. I'll use your technique to at least tell me
the size for specific tables where I can build the query like you've
described.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
