Dbsize backend integration

Dave Page wrote:

The attached patch integrates dbsize functions into the backend, as per
discussion on -hackers. The following functions are included:

pg_relation_size(text) - Get relation size by name/schema.name
pg_relation_size(oid) - Get relation size by OID
pg_tablespace_size(name) - Get tablespace size by name
pg_tablespace_size(oid) - Get tablespace size by OID
pg_database_size(name) - Get database size by name
pg_database_size(oid) - Get database size by OID
pg_table_size(text) - Get table size (including all indexes and
toast tables) by name/schema.name
pg_table_size(oid) - Get table size (including all indexes and
toast tables) by OID
pg_size_pretty(int8) - Pretty print (and round) the byte size
specified (eg, 123456 = 121KB)

This is based on the dbsize contrib module, and previous patches from
Andreas Pflug and Ed L.

The dbsize module should be removed once this is applied, and the
catalog version incremented as I haven't included that in the patch.

OK, so you went with relation as heap/index/toast only, and table as the
total of them. I am not sure that makes sense because we usually equate
relation with table, and an index isn't a relation, really.

Do we have to use pg_object_size? Is there a better name? Are
indexes/toasts even objects?

Of course, these issues are all minor, but we might as well get them
resolved.

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

Dave Page wrote:

pg_relation_size(text) - Get relation size by name/schema.name
pg_relation_size(oid) - Get relation size by OID
pg_tablespace_size(name) - Get tablespace size by name
pg_tablespace_size(oid) - Get tablespace size by OID
pg_database_size(name) - Get database size by name
pg_database_size(oid) - Get database size by OID
pg_table_size(text) - Get table size (including all indexes and
toast tables) by name/schema.name
pg_table_size(oid) - Get table size (including all indexes and
toast tables) by OID
pg_size_pretty(int8) - Pretty print (and round) the byte size
specified (eg, 123456 = 121KB)

OK, so you went with relation as heap/index/toast only, and table as the
total of them. I am not sure that makes sense because we usually equate
relation with table, and an index isn't a relation, really.

Do we have to use pg_object_size? Is there a better name? Are
indexes/toasts even objects?

Relation is not an ideal names, but I heard people talk about heap relation
and index relation. Indexes and tables (and sequences) are treated in a
similar way quite often. Think of ALTER TABLE example_index RENAME TO
another_index. This is even less obvious. Of course in relational theory,
an index would not be a relation, because an index is just implementation
detail.

I don't like object_size any better, since that makes me rather think of
large objects or rows as objects (object id...).

Perhaps pg_table_size should be split into pg_table_size and
pg_indexes_size, where pg_indexes_size is the aggregate of all indexes on a
table und pg_table_size is just table+toast+toast-index.

If noone has a better idea for pg_relation_size, I would rather keep it for
consistency with the contrib module, and because it's not too far off.

Best Regards,
Michael Paesold

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Wed 6/29/2005 2:16 AM
To: Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

OK, so you went with relation as heap/index/toast only, and table as the
total of them. I am not sure that makes sense because we usually equate
relation with table, and an index isn't a relation, really.

Err, yes - posted that before I got your reply!

Do we have to use pg_object_size? Is there a better name? Are
indexes/toasts even objects?

Yeah, I think perhaps pg_object_size is better in some ways than pg_relation_size, however I stuck with relation because (certainly in pgAdmin world) we tend to think of pretty much anything as an object. I could go either way on that though, however Michael doesn't seem so keen.

So, one for pg_object_size, one on the fench and one against :-). Anyone else got a preference?

Regards, Dave.

Dave Page wrote:

-----Original Message----- From: Bruce Momjian
[mailto:pgman@candle.pha.pa.us] Sent: Wed 6/29/2005 2:16 AM To: Dave
Page Cc: PostgreSQL-patches; PostgreSQL-development Subject: Re:
[PATCHES] Dbsize backend integration

OK, so you went with relation as heap/index/toast only, and table as the
total of them. I am not sure that makes sense because we usually equate
relation with table, and an index isn't a relation, really.

Err, yes - posted that before I got your reply!

Do we have to use pg_object_size? Is there a better name? Are
indexes/toasts even objects?

Yeah, I think perhaps pg_object_size is better in some ways than
pg_relation_size, however I stuck with relation because (certainly in
pgAdmin world) we tend to think of pretty much anything as an object.
I could go either way on that though, however Michael doesn't seem so
keen.

So, one for pg_object_size, one on the fench and one against :-). Anyone
else got a preference?

I have a new idea --- pg_storage_size(). That would do just the
toast/index/heap, and pg_relation_size() gets a total of them all, and
only works on heap, no index or toast.

How is that?

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

Michael Paesold wrote:

Do we have to use pg_object_size? Is there a better name? Are
indexes/toasts even objects?

Relation is not an ideal names, but I heard people talk about heap relation
and index relation. Indexes and tables (and sequences) are treated in a
similar way quite often. Think of ALTER TABLE example_index RENAME TO
another_index. This is even less obvious. Of course in relational theory,
an index would not be a relation, because an index is just implementation
detail.

I don't like object_size any better, since that makes me rather think of
large objects or rows as objects (object id...).

Perhaps pg_table_size should be split into pg_table_size and
pg_indexes_size, where pg_indexes_size is the aggregate of all indexes on a
table und pg_table_size is just table+toast+toast-index.

If noone has a better idea for pg_relation_size, I would rather keep it for
consistency with the contrib module, and because it's not too far off.

Yea, but then we have toast and we would need another name. I suggested
pg_storage_size() because it relates to a storage unit (index, toast,
etc), and not a real object or relation.

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

Yea, but then we have toast and we would need another name. I suggested
pg_storage_size() because it relates to a storage unit (index, toast,
etc), and not a real object or relation.

I'm not really happy that all functions change their names (more
versioning handling in pgadmin), but pg_storage_size is certainly the
most precise name.

Regards,
Andreas

Andreas Pflug <pgadmin@pse-consulting.de> writes:

I'm not really happy that all functions change their names (more
versioning handling in pgadmin), but pg_storage_size is certainly the
most precise name.

Actually, it seems excessively imprecise to me: the name conveys nothing
at all to help you remember what the definition is. "storage" could
mean any of the different definitions that have been kicked around in
this thread.

regards, tom lane

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 29 June 2005 12:46
To: Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

That would do just the
toast/index/heap, and pg_relation_size() gets a total of them all, and
only works on heap, no index or toast.

The totalling version (whatever it ends up being called) should
definitely work on toast tables, as it is a legitimate use case to want
to see the size of such a table and it's indexes, independent of the
owner table. There is no need for it to work on an index though,
however, it will return the right answer if it is used that way, so I
think that trying to prevent it will be unecessary code that simply
slows down the majority of invocations of the function for no benefit.

Regards, Dave.

On Jun 30, 2005, at 5:48 PM, Dave Page wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 29 June 2005 12:46

<snip />

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe
pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

I'm still unclear as to what exactly is trying to be captured by the
names, so I'll just throw some out and see if they're intuitive to
anyone.

pg_table_extensions_size()
pg_table_support_size()
pg_relation_extensions_size()
pg_relation_support_size()

pg_relation_extended_size()

My two yen... if that :)

Michael Glaesemann
grzm myrealbox com

-----Original Message-----
From: Michael Glaesemann [mailto:grzm@myrealbox.com]
Sent: 30 June 2005 10:01
To: Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

I'm still unclear as to what exactly is trying to be captured by the
names, so I'll just throw some out and see if they're intuitive to
anyone.

Thanks Michael. We have 2 functions - 1 returns the on disk size of a
table or index without any additional parts such as indexes or toast
tables. The other function returns the total on disk size of a table and
all associated indexes and toast tables (and any indexes they might
have). The current names are pg_relation_size() for the first function,
and pg_table_size() for the second.

pg_table_extensions_size()
pg_table_support_size()
pg_relation_extensions_size()
pg_relation_support_size()

pg_relation_extended_size()

Hmm, none of those really stand out - but thanks anyway. More are
welcome :-)

Regards, Dave

-----Original Message-----
From: viy@mits.lv [mailto:viy@mits.lv]
Sent: 30 June 2005 10:29
To: Bruce Momjian; Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

Maybe pg_trait_size() or pg_property_size() will do?

I don't think property is right. What's your thinking for trait though?

Regards, Dave

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

Maybe pg_trait_size() or pg_property_size() will do?

--

Victor

---- Msg sent via @Mail ISP MiTS - http://www.mits.lv/

"Dave Page" <dpage@vale-housing.co.uk> writes:

Thanks Michael. We have 2 functions - 1 returns the on disk size of a
table or index without any additional parts such as indexes or toast
tables. The other function returns the total on disk size of a table and
all associated indexes and toast tables (and any indexes they might
have). The current names are pg_relation_size() for the first function,
and pg_table_size() for the second.

That seems to me to work perfectly fine. "Relation" is being used here
in its PG-jargon sense, that is an object described by one row of
pg_class, and "table" is being used from the user's point of view.

Or at least sort of --- I think most users know enough to distinguish
tables and indexes. We can figure that the toast table and its index
ought to be considered part of the "base" table, though, since the
user doesn't have a choice about those.

I've not been following the thread closely, so maybe this was already
proposed and rejected, but what about:

pg_relation_size: size of exactly the relation you point it at
(table, index, toast table, whatever)

pg_table_size: point it at heap, get size of heap+toast+toast_index

pg_index_size: point it at heap, get size of all indexes for heap
(excludes toast index)

pg_total_size: point it at heap, get table_size + index_size

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 30 June 2005 14:41
To: Dave Page
Cc: Michael Glaesemann; PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

"Dave Page" <dpage@vale-housing.co.uk> writes:

Thanks Michael. We have 2 functions - 1 returns the on disk

size of a

table or index without any additional parts such as indexes or toast
tables. The other function returns the total on disk size

of a table and

all associated indexes and toast tables (and any indexes they might
have). The current names are pg_relation_size() for the

first function,

and pg_table_size() for the second.

That seems to me to work perfectly fine. "Relation" is being
used here
in its PG-jargon sense, that is an object described by one row of
pg_class, and "table" is being used from the user's point of view.

I'm beginning to think that this is the best we'll get. Still, it is
documented, so it's not like the name has to be a perfect description of
the function's purpose.

Or at least sort of --- I think most users know enough to distinguish
tables and indexes. We can figure that the toast table and its index
ought to be considered part of the "base" table, though, since the
user doesn't have a choice about those.

I've not been following the thread closely, so maybe this was already
proposed and rejected, but what about:

pg_relation_size: size of exactly the relation you point it at
(table, index, toast table, whatever)

pg_table_size: point it at heap, get size of
heap+toast+toast_index

pg_index_size: point it at heap, get size of all
indexes for heap
(excludes toast index)

pg_total_size: point it at heap, get table_size + index_size

That moves the goal posts somewhat. We had settled on just the 2
functions - other combinations could easily be returned using
pg_relation_size and a little SQL for those people with more unusual
requirements. Unless you're particularly set on including these extra
two, I'm inclined to leave it as it is.

Regards, Dave.

"Dave Page" <dpage@vale-housing.co.uk> writes:

I've not been following the thread closely, so maybe this was already
proposed and rejected, but what about:
[4 functions]

That moves the goal posts somewhat.

Fair enough. The two you described are OK by me.

regards, tom lane

On 6/30/05, Dave Page <dpage@vale-housing.co.uk> wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 29 June 2005 12:46
To: Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

pg_diskspace_size()
pg_diskusage_size()
pg_media_used_size()
pg_allocated_size()
pg_diskspace_used()

Regards,
Dawid

PS: Yep, they aren't good...

Dawid Kuroczko wrote:

On 6/30/05, Dave Page <dpage@vale-housing.co.uk> wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 29 June 2005 12:46
To: Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

pg_df(text,text)

where the $1 would be the relation name and $2 would
be the type of output (human readable, bytes etc...)

J

pg_diskspace_size()
pg_diskusage_size()
pg_media_used_size()
pg_allocated_size()
pg_diskspace_used()

Regards,
Dawid

PS: Yep, they aren't good...

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Dave Page wrote:

That would do just the
toast/index/heap, and pg_relation_size() gets a total of them all, and
only works on heap, no index or toast.

The totalling version (whatever it ends up being called) should
definitely work on toast tables, as it is a legitimate use case to want
to see the size of such a table and it's indexes, independent of the
owner table. There is no need for it to work on an index though,
however, it will return the right answer if it is used that way, so I
think that trying to prevent it will be unecessary code that simply
slows down the majority of invocations of the function for no benefit.

Agreed.

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

viy@mits.lv wrote:

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

Maybe pg_trait_size() or pg_property_size() will do?

I don't think so. I think trait and property suggests an aspect of the
object, so saying trait/property size is saying I am talking about an
aspect of the object, while for a heap, its size is really its size, it
isn't an aspect of its size.

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