Re: [GENERAL] Schema Size - PostgreSQL 9.2
On Thu, Mar 17, 2016 at 4:45 PM, Karsten Hilbert
wrote:
> On Fri, Mar 18, 2016 at 09:38:30AM +1300, drum.lu...@gmail.com wrote:
>
> > Can you please provide me a Query that tells me how much space is a
> Schema
> > in my DB?
>
> There's been a discussion on that recently (like last month)
> which can be found in the archive.
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
This should give you what you need:
SELECT n.nspname as schema,
pg_size_pretty(sum(pg_total_relation_size(quote_ident(n.nspname)||
'.' || quote_ident(c.relname as size,
sum(pg_total_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(c.relname))) as size_bytes
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind IN ('r')
GROUP BY 1
ORDER BY 3 DESC, 1, 2;
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Re: [GENERAL] Schema Size - PostgreSQL 9.2
On Fri, Mar 18, 2016 at 09:38:30AM +1300, drum.lu...@gmail.com wrote: > Can you please provide me a Query that tells me how much space is a Schema > in my DB? There's been a discussion on that recently (like last month) which can be found in the archive. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Schema Size - PostgreSQL 9.2
Hi all, Can you please provide me a Query that tells me how much space is a Schema in my DB? I'm using one but don't think it's right > > SELECT schema_name, >pg_size_pretty(sum(table_size)::bigint), >(sum(table_size) / pg_database_size(current_database())) * 100 as a > 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
Re: [GENERAL] Schema Size
On 2 March 2016 at 12:23, Scott Mead 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?
Re: [GENERAL] Schema Size
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 > 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.
>
>
Re: [GENERAL] 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.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)"
David J.
[GENERAL] 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
