Thread: Dbsize backend integration
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. Regards, Dave.
Attachment
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
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