On 2 March 2016 at 12:23, Scott Mead <sco...@openscg.com> wrote: > > On Tue, Mar 1, 2016 at 6:07 PM, David G. Johnston < > david.g.johns...@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.lu...@gmail.com < >> drum.lu...@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. >> >> >
So.. I'm doing this way: 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 $$ LANGUAGE SQL; Link: https://wiki.postgresql.org/wiki/Schema_Size That's working - But I'd like to test it.. to compare the results with another one trustfull - Do you have some?