Quick Links

Re: db size and tables size difference

From:	Isabella Ghiurea <isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca>
To:	pgsql-admin(at)postgresql(dot)org
Subject:	Re: db size and tables size difference
Date:	2009-09-23 16:24:21
Message-ID:	25531211.post@talk.nabble.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-admin

Hi All,
Tom, please see bellow are the results for the modified query with ORDER
BY

select pg_size_pretty(pg_database_size('db1')); ---->
pg_size_pretty
----------------
12 GB

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_total_relation_size(nspname || '.' || relname) DESC
LIMIT 1000;

Thank you
Isabella

Hi All,

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

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)

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

Hi Pg Admin list.
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. .
The sum of tables +index sizes is showing as aprox 6.5GB and
pg_size_pretty(dbname) is coming as 12GB, this are the results after a full
vacuum and reindexdb, also the sum of OS db files size is ~ 6.5GB. Any tips
what I'm missing : are some "unallocated" db pages or anything else ?

select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
----------------
12 GB
(1 row)
*** Check for tables size :
SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS
size,pg_total_relation_size(schemaname||'.'||tablename) AS
total_size FROM pg_tables) AS TABLES where schemaname='caom'
ORDER BY total_size DESC;
schemaname | tablename | size_pretty | total_size_pretty
------------+-----------------------------+-------------+-------------------
caom | spatialentity | 3053 MB | 3216 MB
caom | artifact | 1979 MB | 2150 MB
caom | plane | 413 MB | 677 MB
caom | positionsample | 110 MB | 219 MB
caom | simpleobservation | 165 MB | 202 MB
caom | temporalentity | 77 MB | 86 MB
caom | spectralentity | 68 MB | 73 MB
caom | metric | 33 MB | 70 MB
caom | polarizationentity | 29 MB | 33 MB
caom | harvestskip | 1576 kB | 2056 kB
caom | harveststate | 840 kB | 856 kB
caom | positionhole | 48 kB | 584 kB

*** OR : check for the biggest tables+index size:
SELECT ' Top 20 biggest tables and indexes'
;
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname !~ '^pg_toast'
AND pg_relation_size(nspname || '.' || relname)>0
ORDER BY pg_relation_size(nspname || '.' || relname) DESC
LIMIT 20;
(1 row)

Thank you
Isabella

--
View this message in context: http://www.nabble.com/Re%3A--ADMIN--db-size-and-tables-size-difference-tp25531211p25531211.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Responses

Re: db size and tables size difference at 2009-09-23 16:42:00 from Tom Lane
Re: db size and tables size difference at 2009-09-23 22:11:05 from Isabella Ghiurea

Browse pgsql-admin by date

	From	Date	Subject
Next Message	Tom Lane	2009-09-23 16:42:00	Re: db size and tables size difference
Previous Message	Tom Lane	2009-09-23 15:55:39	Re: db size and tables size difference