Skip site navigation (1) Skip section navigation (2)

Re: db size and tables size difference

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: db size and tables size difference
Date: 2009-09-23 03:07:09
Message-ID: 11836.1253675229@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
Isabella Ghiurea <isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca> writes:
> I'm trying to understand why  there are  GB's difference  when checking 
> for db size using pg_size_pretty()  and  querying for  tables + indexes 
> size. .

You are not counting everything --- the total DB size is clearly 12GB,
so the question is where are the other 5.5GB?  Your first query shows
that schema caom accounts for 6+GB, but the second one does not prove
that schema caom contains all the big hogs.  My guesses are:

1. Toast tables for tables that aren't in caom --- you used
pg_relation_size not pg_total_relation_size, and excluded toast
tables, so you are missing those.

2. pg_largeobject ... got any large objects?

3. Bloat in other system catalogs.  5GB of catalog bloat would be
pretty awful, but maybe that's what it is.

Try that last query without the namespace restrictions.

			regards, tom lane

In response to

Responses

pgsql-admin by date

Next:From: Naomi WalkerDate: 2009-09-23 03:28:23
Subject: Re: db size and tables size difference
Previous:From: Tom LaneDate: 2009-09-23 02:44:40
Subject: Re: could not open relation with OID 2610

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group