Thread: Schema Size

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

Query 2:

select schemaname, pg_size_pretty(sum(pg_table_size(schemaname||'.'||relname))::bigint) as s

from pg_stat_user_tables

group by schemaname

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

On Tue, Mar 1, 2016 at 6:07 PM, David G. Johnston <david.g.johnston@gmail.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.lucas@gmail.com <drum.lucas@gmail.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.​

CREATE OR REPLACE FUNCTION pg_schema_size(text) RETURNS BIGINT AS $$
SELECT SUM(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::BIGINT FROM pg_tables WHERE schemaname = $1