Re: Schema Size

On Tue, Mar 1, 2016 at 6:07 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> You should read the definitions for the functions you are using to
> retrieve the sizes.
>
> ​http://www.postgresql.org/docs/current/static/functions-admin.html​
>
> +1, you've gotta be careful with each of these, they all tend to hide
different, yet critical components of size that you may be having trouble
resolving.

The other thing to consider is that this isn't including any on-disk space
required for your change traffic in the WAL. Your $PGDATA will always be
larger than the sum of all your databases sizes...

> On Tue, Mar 1, 2016 at 3:48 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com
> > wrote:
>
>> Hi there
>>
>> Wanna see how size a schema is in my PostgreSQL 9.2
>>
>> Got two queries - they return different values... can u please check?
>>
>> cheers;
>>
>> Query 1:
>> SELECT schema_name,
>> pg_size_pretty(sum(table_size)::bigint) as "disk space",
>> (sum(table_size) / pg_database_size(current_database())) * 100
>> as "percent"
>> FROM (
>> SELECT pg_catalog.pg_namespace.nspname as schema_name,
>> pg_relation_size(pg_catalog.pg_class.oid) as table_size
>> FROM pg_catalog.pg_class
>> JOIN pg_catalog.pg_namespace
>> ON relnamespace = pg_catalog.pg_namespace.oid
>> ) t
>> GROUP BY schema_name
>> ORDER BY schema_name
>>
>>
> ​​pg_relation_size: "Disk space used by the specified fork ('main', 'fsm',
> 'vm', or 'init') of the specified table or index"
>
> The 'init' fork is (I think) non-zero but extremely small.
> TOAST for a given relation is considered its own table
>
>
>> Query 2:
>> select schemaname,
>> pg_size_pretty(sum(pg_table_size(schemaname||'.'||relname))::bigint) as s
>> from pg_stat_user_tables
>> group by schemaname
>>
>
> pg_table_size: "Disk space used by the specified table, excluding indexes
> (but including TOAST, free space map, and visibility map)"
>

Personally, I'm a huge fan of 'pg_total_relation_size' which is all of
pg_table_size + indexes. It really depends on specifically what you're
trying to count. If you're looking for the total disk space required by
your tables in a schema, I always [personally] want to include indexes in
this count to make sure I understand the total impact on disk of accessing
my relations.

>
> David J.​
>
>