Re: Schema Size

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​

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)"

David J.​