Search Postgresql Archives

Re: Schema Size

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On 2 March 2016 at 12:23, Scott Mead <scottm@xxxxxxxxxxx> wrote:

On Tue, Mar 1, 2016 at 6:07 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
You should read the definitions for the functions you are using to retrieve the sizes.


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

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?

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux