Thread: DBSize backend integration
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. 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. 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. Thoughts? Regards, Dave.
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, Pennsylvania19073
> -----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, Pennsylvania19073
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, Pennsylvania19073
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, Pennsylvania19073
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