DBSize backend integration

Dave Page wrote:

The following functions are currently in contrib/dbsize. As Bruce has
suggested, we should discuss which functions should or shouldn't be
moved into the backend, and which should be renamed.

int8 pg_database_size(oid)
int8 database_size(name)

Both return the database size in bytes, the first by oid, the second by
name. Michael has indicated that he finds the second form useful, and we
already use the first form in pgAdmin. Either form can emulate the other
with a simple subselect. I would suggest that the second form be renamed
to match the first for consistency, if it is decided that they be kept.

Seems we should just name it one name and use function overloading to
support name and oid.

int8 pg_tablespace_size(oid)

This returns the size of the tablespace in bytes. If both forms of the
database_size function are included, then a 'by name' equivalent should
probably be added.

Yep.

int8 pg_relation_size(oid)
int8 relation_size(text)

As per *database_size(*), but per relation.

text pg_size_pretty(int8)

Converts a size in bytes to B/KB/MB/GB etc.

int8 indexes_size(text)

Returns the total size of the indexes on the named relation. A
convenience function with questionable usefulness (IMO). Currently
implemented as an SQL function.

int8 total_relation_size(text)

Returns relation_size(text) + indexes_size(text) +
relation_size(text->toast tables). As per indexes_size, currently
implemented as an SQL function.

setof record relation_size_components(text)

A 'view' returning the sizes of each component of the named relation
(relation, indexes, toast tables etc). Broken at present because it
isn't schema aware.

My personal view is that pg_database_size, pg_relation_size and
pg_tablespace_size, as well as pg_size_pretty should be included. If
others consider that the by name versions are also useful, then they
should be included, but renamed for consistency. The other three
functions should be dropped IMO.

So drop total_relation_size(), relation_size_components(), and what
else?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 24 June 2005 20:45
To: Dave Page
Cc: PostgreSQL-development
Subject: Re: [HACKERS] DBSize backend integration

My personal view is that pg_database_size, pg_relation_size and
pg_tablespace_size, as well as pg_size_pretty should be included. If
others consider that the by name versions are also useful, then they
should be included, but renamed for consistency. The other three
functions should be dropped IMO.

So drop total_relation_size(), relation_size_components(), and what
else?

indexes_size()

Regards, Dave.

Dave Page wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 24 June 2005 20:45
To: Dave Page
Cc: PostgreSQL-development
Subject: Re: [HACKERS] DBSize backend integration

My personal view is that pg_database_size, pg_relation_size and
pg_tablespace_size, as well as pg_size_pretty should be included. If
others consider that the by name versions are also useful, then they
should be included, but renamed for consistency. The other three
functions should be dropped IMO.

So drop total_relation_size(), relation_size_components(), and what
else?

indexes_size()

What is the logic for removing that? Because it is an aggregate of all
indexes?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

So drop total_relation_size(), relation_size_components(), and what
else?

But these answer easily the question I see most asked - how much space
in total does the relation occupy. I'd like to see at least one of
these, properly named and fixed w.r.t. schemas. Getting
total_relation_size() from relation_size_components() would be easy, so
if we only keep one then keep relation_size_components().

Just my $0.02 worth

cheers

andrew

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 24 June 2005 21:07
To: Dave Page
Cc: PostgreSQL-development
Subject: Re: [HACKERS] DBSize backend integration

So drop total_relation_size(),

relation_size_components(), and what

else?

indexes_size()

What is the logic for removing that? Because it is an
aggregate of all
indexes?

Yes, and is of limited use in my opinion. I can see a use for
pg_relation_size when used on an individual index, but the total of all
indexes on a relation seems of little real use to me (and is relatively
easily calculated if it really is required for a more specialised
purpose).

Regards, Dave.

-----Original Message-----
From: Andrew Dunstan [mailto:andrew@dunslane.net]
Sent: 24 June 2005 21:12
To: Bruce Momjian
Cc: Dave Page; PostgreSQL-development
Subject: Re: [HACKERS] DBSize backend integration

Bruce Momjian wrote:

So drop total_relation_size(), relation_size_components(), and what
else?

But these answer easily the question I see most asked - how
much space
in total does the relation occupy. I'd like to see at least one of
these, properly named and fixed w.r.t. schemas. Getting
total_relation_size() from relation_size_components() would
be easy, so
if we only keep one then keep relation_size_components().

relation_size_components() depends on total_relation_size() (which I
have to agree could be useful). I think relation_size_components() is
unecessary though - it looks like it was designed to show a summary
rather than as a view to be used by other clients (if that makes
sense!).

Regards, Dave.

Dave Page wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 24 June 2005 21:07
To: Dave Page
Cc: PostgreSQL-development
Subject: Re: [HACKERS] DBSize backend integration

So drop total_relation_size(),

relation_size_components(), and what

else?

indexes_size()

What is the logic for removing that? Because it is an
aggregate of all
indexes?

Yes, and is of limited use in my opinion. I can see a use for
pg_relation_size when used on an individual index, but the total of all
indexes on a relation seems of little real use to me (and is relatively
easily calculated if it really is required for a more specialised
purpose).

[from memory] the relation_components function adds components in a
questionable way, e.g. counting on index on the toast table as index. To
me, that's internal implementation detail, and should be counted as
toast table size too.

Regards,
Andreas

Andreas Pflug wrote:

[from memory] the relation_components function adds components in a
questionable way, e.g. counting on index on the toast table as index. To
me, that's internal implementation detail, and should be counted as
toast table size too.

Agreed. The user doesn't create the toast index like regular indexes.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Dave Page wrote:

-----Original Message-----
From: Andrew Dunstan [mailto:andrew@dunslane.net]
Sent: 24 June 2005 21:12
To: Bruce Momjian
Cc: Dave Page; PostgreSQL-development
Subject: Re: [HACKERS] DBSize backend integration

Bruce Momjian wrote:

So drop total_relation_size(), relation_size_components(), and what
else?

But these answer easily the question I see most asked - how
much space
in total does the relation occupy. I'd like to see at least one of
these, properly named and fixed w.r.t. schemas. Getting
total_relation_size() from relation_size_components() would
be easy, so
if we only keep one then keep relation_size_components().

relation_size_components() depends on total_relation_size() (which I
have to agree could be useful). I think relation_size_components() is
unecessary though - it looks like it was designed to show a summary
rather than as a view to be used by other clients (if that makes
sense!).

I agree that total_relation_size() is quite useful at least when used from
the command line. It should give you the correct answer to what space a
table including indexes and _toast_tables_ occupies.

I am not sure about relation_size_components.

Best Regards,
Michael Paesold

Michael Paesold wrote:

relation_size_components() depends on total_relation_size() (which I
have to agree could be useful). I think relation_size_components() is
unecessary though - it looks like it was designed to show a summary
rather than as a view to be used by other clients (if that makes
sense!).

I agree that total_relation_size() is quite useful at least when used from
the command line. It should give you the correct answer to what space a
table including indexes and _toast_tables_ occupies.

Can someone come up with a better name than total_relation_size(),
because we already have relation_size()? The problem is that in the
first case, relation means the relation/indexes/toast, and in the second
it is just the heap. Should we call relation_size() pg_heap_size(). I
prefer that.

I think we are considering adding pg_* too. Anyway, this is the time to
add consistency.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Can someone come up with a better name than total_relation_size(),
because we already have relation_size()? The problem is that in the
first case, relation means the relation/indexes/toast, and in the second
it is just the heap. Should we call relation_size() pg_heap_size(). I
prefer that.

Both "relation" and "heap" are PG-isms I think. Seems to me we should
be using "pg_table_size" for the "most natural" unit, which is either
heap+toast+toast_index or heap+toast+toast_index+table_indexes depending
on whether you agree with the SQL committee that indexes are an
implementation detail ...

regards, tom lane