Re: db size and tables size difference

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

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

Isabella(dot)Ghiurea(at)nrc-cnrc(dot)gc(dot)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