Thread: Difference in the size of database size and relations
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
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
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
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
Specifics can be found at: http://www.postgresql.org/docs/current/static/functions-admin.html
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
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
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
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