Re: Schema Size

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

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.lucas@xxxxxxxxx <drum.lucas@xxxxxxxxx> 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.​