Re: db size and tables size difference - Mailing list pgsql-admin

Hi All,

Please,  see more info my env:  PG 8.3.6 on RHE5-64bits.

 1. there are more than one schemas, but the size of the tables is close
to 30-40kB, see some samples
schemaname |  tablename  | size_pretty | total_size_pretty
------------+-------------+-------------+-------------------
 tap_schema | tables      | 8192 bytes  | 32 kB
 tap_schema | columns     | 8192 bytes  | 32 kB
 tap_schema | schemas     | 8192 bytes  | 32 kB
 tap_schema | keys        | 8192 bytes  | 24 kB
 tap_schema | key_columns | 8192 bytes  | 8192 bytes

2.   There are no BLOB's  data type in db at this time :
cvodb=# select * from pg_largeobject;
 loid | pageno | data

------+--------+------
(0 rows)


3. As  Tom suggested ,  I  excluded the table space restriction  and
changed  to pg_total_relation_size  my  original SQL :


SELECT 'the table  size without table space restrictions';
SELECT nspname || '.' || relname AS
"relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
relname)) AS "s
ize"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  ORDER BY pg_relation_size(nspname || '.' || relname) DESC
  LIMIT 20;
the table  size without table space restrictions
(1 row)

         relation          |  size
---------------------------+---------
 caom.spatialentity        | 3216 MB
 caom.artifact             | 2150 MB
 caom.plane                | 677 MB
 caom.artifact_i1          | 171 MB
 caom.simpleobservation    | 202 MB
 caom.spatialentity_i1     | 162 MB
 caom.positionsample       | 219 MB
 caom.plane_psi2           | 86 MB
 caom.temporalentity       | 86 MB
 caom.spectralentity       | 73 MB
 caom.plane_energy_i1      | 67 MB
 caom.plane_time_i1        | 58 MB
 caom.plane_position_i2    | 48 MB
 caom.metric               | 70 MB
 caom.polarizationentity   | 33 MB
 caom.simpleobservation_i2 | 25 MB
 caom.plane_psi1           | 23 MB
 caom.metric_i2            | 18 MB
 caom.metric_i1            | 18 MB
 caom.plane_position_i3    | 15 MB
(20 rows)


4. Where are the  rest of 5,5 GB been used ? How can I get the system
catalog   correct size ?



Thank you,
Isabella

Tom Lane wrote:
>
> Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.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
>


--
-----------------------------------------------------------
Isabella A. Ghiurea

Isabella.Ghiurea@nrc-cnrc.gc.ca
Canadian Astronomy Data Centre | http://www1.cadc-ccda.hia-iha.nrc-cnrc.gc.ca/
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045