pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDifference in the size of database size and relations
8+ messages / 4 participants
[nested] [flat]
* Difference in the size of database size and relations
@ 2013-12-10 11:33 Luis <luisico@gmail.com>
2013-12-10 12:35 ` Re: Difference in the size of database size and relations Ian Lawrence Barwick <barwick@gmail.com>
2013-12-10 18:07 ` Re: Difference in the size of database size and relations Jerry Sievers <gsievers19@comcast.net>
0 siblings, 2 replies; 8+ messages in thread
From: Luis @ 2013-12-10 11:33 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have noticed a weird thing in different databases.
When I run pg_database_size on some databases they report a pretty
high value and if I take a look to all relations the sum of all of
them is not even the half of the one reported by pg_database_size.
Although I've seen this in different databases, this is a specific example.
This are the sizes reported by this query:
SELECT pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
size
---------
99 MB
45 MB
10 MB
8528 kB
5280 kB
4352 kB
3184 kB
1304 kB
1152 kB
896 kB
544 kB
528 kB
336 kB
336 kB
336 kB
304 kB
296 kB
288 kB
272 kB
248 kB
And this is the database size reported by this query:
SELECT pg_size_pretty(pg_database_size('mydb'));
pg_size_pretty
----------------
3539 MB
I've read about LOBs but I don't seem to have any.
Any idea where the space is being used?
Thanks
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Difference in the size of database size and relations
2013-12-10 11:33 Difference in the size of database size and relations Luis <luisico@gmail.com>
@ 2013-12-10 12:35 ` Ian Lawrence Barwick <barwick@gmail.com>
2013-12-10 12:43 ` Re: Difference in the size of database size and relations Luis <luisico@gmail.com>
2013-12-10 14:09 ` Re: Difference in the size of database size and relations Ian Lawrence Barwick <barwick@gmail.com>
1 sibling, 2 replies; 8+ messages in thread
From: Ian Lawrence Barwick @ 2013-12-10 12:35 UTC (permalink / raw)
To: Luis <luisico@gmail.com>; +Cc: pgsql-admin
2013/12/10 Luis <luisico@gmail.com>:
> Hi,
>
> I have noticed a weird thing in different databases.
> When I run pg_database_size on some databases they report a pretty
> high value and if I take a look to all relations the sum of all of
> them is not even the half of the one reported by pg_database_size.
>
> Although I've seen this in different databases, this is a specific example.
>
>
> This are the sizes reported by this query:
>
> SELECT pg_size_pretty(pg_relation_size(C.oid)) AS "size"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> ORDER BY pg_relation_size(C.oid) DESC
> LIMIT 20;
>
>
> size
> ---------
(…)
> And this is the database size reported by this query:
> SELECT pg_size_pretty(pg_database_size('mydb'));
>
> pg_size_pretty
> ----------------
> 3539 MB
>
> I've read about LOBs but I don't seem to have any.
>
> Any idea where the space is being used?
Indexes for a start. Try:
SELECT C.relname, pg_size_pretty(pg_relation_size(C.oid)) AS "size",
pg_size_pretty(pg_indexes_size(C.oid)) AS "idx_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE relkind='r'
AND nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
Regards
Ian Barwick
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Difference in the size of database size and relations
2013-12-10 11:33 Difference in the size of database size and relations Luis <luisico@gmail.com>
2013-12-10 12:35 ` Re: Difference in the size of database size and relations Ian Lawrence Barwick <barwick@gmail.com>
@ 2013-12-10 12:43 ` Luis <luisico@gmail.com>
2013-12-10 14:04 ` Re: Difference in the size of database size and relations bricklen <bricklen@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Luis @ 2013-12-10 12:43 UTC (permalink / raw)
To: Ian Lawrence Barwick <barwick@gmail.com>; +Cc: pgsql-admin
Thanks Ian,
Actually the query I used to get the relations sizes also included
indexes and even pg_toast. This query gives me more detail about the
indexes related to every table but still can't see where the space is.
size | idx_size
------------+----------
45 MB | 15 MB
8528 kB | 1360 kB
4352 kB | 752 kB
3184 kB | 144 kB
1304 kB | 832 kB
896 kB | 1192 kB
248 kB | 344 kB
80 kB | 120 kB
8192 bytes | 16 kB
0 bytes | 16 kB
0 bytes | 16 kB
Thanks
2013/12/10 Ian Lawrence Barwick <barwick@gmail.com>:
> 2013/12/10 Luis <luisico@gmail.com>:
>> Hi,
>>
>> I have noticed a weird thing in different databases.
>> When I run pg_database_size on some databases they report a pretty
>> high value and if I take a look to all relations the sum of all of
>> them is not even the half of the one reported by pg_database_size.
>>
>> Although I've seen this in different databases, this is a specific example.
>>
>>
>> This are the sizes reported by this query:
>>
>> SELECT pg_size_pretty(pg_relation_size(C.oid)) AS "size"
>> FROM pg_class C
>> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
>> WHERE nspname NOT IN ('pg_catalog', 'information_schema')
>> ORDER BY pg_relation_size(C.oid) DESC
>> LIMIT 20;
>>
>>
>> size
>> ---------
> (…)
>
>> And this is the database size reported by this query:
>> SELECT pg_size_pretty(pg_database_size('mydb'));
>>
>> pg_size_pretty
>> ----------------
>> 3539 MB
>>
>> I've read about LOBs but I don't seem to have any.
>>
>> Any idea where the space is being used?
>
> Indexes for a start. Try:
>
> SELECT C.relname, pg_size_pretty(pg_relation_size(C.oid)) AS "size",
> pg_size_pretty(pg_indexes_size(C.oid)) AS "idx_size"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> WHERE relkind='r'
> AND nspname NOT IN ('pg_catalog', 'information_schema')
> ORDER BY pg_relation_size(C.oid) DESC
> LIMIT 20;
>
> Regards
>
> Ian Barwick
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Difference in the size of database size and relations
2013-12-10 11:33 Difference in the size of database size and relations Luis <luisico@gmail.com>
2013-12-10 12:35 ` Re: Difference in the size of database size and relations Ian Lawrence Barwick <barwick@gmail.com>
2013-12-10 12:43 ` Re: Difference in the size of database size and relations Luis <luisico@gmail.com>
@ 2013-12-10 14:04 ` bricklen <bricklen@gmail.com>
2013-12-10 14:09 ` Re: Difference in the size of database size and relations Luis <luisico@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: bricklen @ 2013-12-10 14:04 UTC (permalink / raw)
To: Luis <luisico@gmail.com>; +Cc: pgsql-admin
On Tue, Dec 10, 2013 at 4:43 AM, Luis <luisico@gmail.com> wrote:
> Actually the query I used to get the relations sizes also included
> indexes and even pg_toast.
>
Your query appears to be using "pg_relation_size()", try it again with
"pg_total_relation_size()" to include indexes and toast tables.
Specifics can be found at:
http://www.postgresql.org/docs/current/static/functions-admin.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Difference in the size of database size and relations
2013-12-10 11:33 Difference in the size of database size and relations Luis <luisico@gmail.com>
2013-12-10 12:35 ` Re: Difference in the size of database size and relations Ian Lawrence Barwick <barwick@gmail.com>
2013-12-10 12:43 ` Re: Difference in the size of database size and relations Luis <luisico@gmail.com>
2013-12-10 14:04 ` Re: Difference in the size of database size and relations bricklen <bricklen@gmail.com>
@ 2013-12-10 14:09 ` Luis <luisico@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Luis @ 2013-12-10 14:09 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-admin
Sorry, my bad. I did run that query with pg_total_relation_size before
but somehow I sent by email the one a query without the total.
The result is basically the same:
size
---------
100 MB
60 MB
10 MB
9928 kB
5280 kB
5144 kB
3360 kB
2144 kB
1152 kB
2128 kB
544 kB
528 kB
336 kB
336 kB
336 kB
304 kB
296 kB
288 kB
288 kB
101 MB
thanks
2013/12/10 bricklen <bricklen@gmail.com>:
>
> On Tue, Dec 10, 2013 at 4:43 AM, Luis <luisico@gmail.com> wrote:
>>
>> Actually the query I used to get the relations sizes also included
>> indexes and even pg_toast.
>
>
> Your query appears to be using "pg_relation_size()", try it again with
> "pg_total_relation_size()" to include indexes and toast tables.
>
> Specifics can be found at:
> http://www.postgresql.org/docs/current/static/functions-admin.html
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Difference in the size of database size and relations
2013-12-10 11:33 Difference in the size of database size and relations Luis <luisico@gmail.com>
2013-12-10 12:35 ` Re: Difference in the size of database size and relations Ian Lawrence Barwick <barwick@gmail.com>
@ 2013-12-10 14:09 ` Ian Lawrence Barwick <barwick@gmail.com>
2013-12-10 14:29 ` Re: Difference in the size of database size and relations Luis <luisico@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Ian Lawrence Barwick @ 2013-12-10 14:09 UTC (permalink / raw)
To: Luis <luisico@gmail.com>; +Cc: pgsql-admin
2013/12/10 Luis <luisico@gmail.com>:
> Thanks Ian,
>
> Actually the query I used to get the relations sizes also included
> indexes and even pg_toast.
Doh, sorry I didn't notice there was no relkind qualification on pg_class
in your query.
> This query gives me more detail about the
> indexes related to every table but still can't see where the space is.
Table/index bloat?
Ian Barwick
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Difference in the size of database size and relations
2013-12-10 11:33 Difference in the size of database size and relations Luis <luisico@gmail.com>
2013-12-10 12:35 ` Re: Difference in the size of database size and relations Ian Lawrence Barwick <barwick@gmail.com>
2013-12-10 14:09 ` Re: Difference in the size of database size and relations Ian Lawrence Barwick <barwick@gmail.com>
@ 2013-12-10 14:29 ` Luis <luisico@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Luis @ 2013-12-10 14:29 UTC (permalink / raw)
To: Ian Lawrence Barwick <barwick@gmail.com>; +Cc: pgsql-admin
Good one.
Unfortunately it doesn't show anything weird either.
Using any of these two scripts:
http://wiki.postgresql.org/wiki/Show_database_bloat
http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html
I can only see about 90MB
Thanks
2013/12/10 Ian Lawrence Barwick <barwick@gmail.com>:
> 2013/12/10 Luis <luisico@gmail.com>:
>> Thanks Ian,
>>
>> Actually the query I used to get the relations sizes also included
>> indexes and even pg_toast.
>
> Doh, sorry I didn't notice there was no relkind qualification on pg_class
> in your query.
>
>> This query gives me more detail about the
>> indexes related to every table but still can't see where the space is.
>
> Table/index bloat?
>
>
> Ian Barwick
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Difference in the size of database size and relations
2013-12-10 11:33 Difference in the size of database size and relations Luis <luisico@gmail.com>
@ 2013-12-10 18:07 ` Jerry Sievers <gsievers19@comcast.net>
1 sibling, 0 replies; 8+ messages in thread
From: Jerry Sievers @ 2013-12-10 18:07 UTC (permalink / raw)
To: Luis <luisico@gmail.com>; +Cc: pgsql-admin
Luis <luisico@gmail.com> writes:
> Hi,
>
> I have noticed a weird thing in different databases.
> When I run pg_database_size on some databases they report a pretty
> high value and if I take a look to all relations the sum of all of
> them is not even the half of the one reported by pg_database_size.
>
> Although I've seen this in different databases, this is a specific example.
>
>
> This are the sizes reported by this query:
>
> SELECT pg_size_pretty(pg_relation_size(C.oid)) AS "size"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> ORDER BY pg_relation_size(C.oid) DESC
> LIMIT 20;
>
Try just comparing what pg_database_size() tells you vs a sum() over
relpages*8192 (assuming standard page size). You may have to do some
casting to bigint depending how large your DB is.
> size
> ---------
> 99 MB
> 45 MB
> 10 MB
> 8528 kB
> 5280 kB
> 4352 kB
> 3184 kB
> 1304 kB
> 1152 kB
> 896 kB
> 544 kB
> 528 kB
> 336 kB
> 336 kB
> 336 kB
> 304 kB
> 296 kB
> 288 kB
> 272 kB
> 248 kB
>
> And this is the database size reported by this query:
> SELECT pg_size_pretty(pg_database_size('mydb'));
>
> pg_size_pretty
> ----------------
> 3539 MB
>
> I've read about LOBs but I don't seem to have any.
>
> Any idea where the space is being used?
>
> Thanks
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2013-12-10 18:07 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-12-10 11:33 Difference in the size of database size and relations Luis <luisico@gmail.com>
2013-12-10 12:35 ` Ian Lawrence Barwick <barwick@gmail.com>
2013-12-10 12:43 ` Luis <luisico@gmail.com>
2013-12-10 14:04 ` bricklen <bricklen@gmail.com>
2013-12-10 14:09 ` Luis <luisico@gmail.com>
2013-12-10 14:09 ` Ian Lawrence Barwick <barwick@gmail.com>
2013-12-10 14:29 ` Luis <luisico@gmail.com>
2013-12-10 18:07 ` Jerry Sievers <gsievers19@comcast.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox