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.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.

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

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.​

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?