Re: [PATCHES] dbsize contrib
Applied and new file attached. I cleaned it up a little. One problem
is that the sample query generates a syntax error.
---
Andreas Pflug wrote:
> Bruce Momjian wrote:
> > Patch applied. Thanks.
> >
> > Can I get some documentation in the README for all the new
> > functionality.
>
> Here it is.
>
> Regards,
> Andreas
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
This module contains several functions that report the size of a given
database object:
int8 database_size(name)
int8 relation_size(text)
int8 pg_database_size(oid)
int8 pg_tablespace_size(oid)
int8 pg_relation_size(oid)
text pg_size_pretty(int8)
The first two functions:
SELECT database_size('template1');
SELECT relation_size('pg_class');
take the name of the object, and support databases and tables. Please
note that relation_size() only reports table file usage and not the
space used by indexes and toast tables.
Functions using oids are:
SELECT pg_database_size(1); -- template1 database
SELECT pg_tablespace_size(1663);-- pg_default tablespace
SELECT pg_relation_size(1259); -- pg_class table size
pg_relation_size() will report the size of the table, index and toast
table OIDs, but they must be requested individually. To obtain the total
size of a table including all helper files you'd have to do something
like:
XXX This query does not work, syntax error XXX
SELECT pg_relation_size(cl.oid) AS tablesize,
CASE WHEN reltoastrelid=0 THEN 0
ELSE pg_relation_size(reltoastrelid) END AS toastsize,
SUM(pg_relation_size(indexrelid)) AS indexsize,
pg_size_pretty(pg_relation_size(cl.oid)
+ pg_relation_size(reltoastrelid)
+ SUM(pg_relation_size(indexrelid))::int8)
AS totalsize
FROM pg_class cl
JOIN pg_index ON cl.oid=indrelid
WHERE relname = 'pg_rewrite'
GROUP BY 1,2
This sample query utilizes the helper function pg_size_pretty(int8),
which formats the number of bytes into a convenient string using KB, MB,
GB. It is also contained in this module.
To install, just run make; make install. Finally, load the functions
into any database using dbsize.sql.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] dbsize contrib
Bruce Momjian wrote:
Patch applied. Thanks.
Can I get some documentation in the README for all the new
functionality.
Here it is.
Regards,
Andreas
Index: README.dbsize
===
RCS file: /projects/cvsroot/pgsql-server/contrib/dbsize/README.dbsize,v
retrieving revision 1.2
diff -u -r1.2 README.dbsize
--- README.dbsize 23 Jun 2002 20:09:23 - 1.2
+++ README.dbsize 2 Sep 2004 08:28:10 -
@@ -1,15 +1,51 @@
-This module contains two functions that report the size of a given
-database or relation. E.g.,
+This module contains several functions that report the size of a given
+database object.
+
+int8 database_size(name)
+int8 relation_size(text)
+int8 pg_database_size(oid)
+int8 pg_tablespace_size(oid)
+int8 pg_relation_size(oid)
+text pg_size_pretty(int8)
+
+These functions come in two flavours. The old style takes the name of the
+object, and supports databases and tables. These where the only functions
+supported for PostgreSQL up to 7.4.x.
SELECT database_size('template1');
SELECT relation_size('pg_class');
-These functions report the actual file system space. Thus, users can
-avoid digging through the details of the database directories.
+Please note that for relation_size() only the pure table file usage is
+computed, not the space used by indexes and toast tables.
+
+Starting with PostgreSQL 8.0, additional functions taking the oid of the
+object where added.
+
+SELECT pg_database_size(1); -- template1 database
+SELECT pg_tablespace_size(1663); -- pg_default tablespace
+SELECT pg_relation_size(1259); -- pg_class table size
+
+pg_relation_size will report the size of for table, index and toast table
+OIDs, but won't add them automatically. To obtain the total size of a table
+including all helper files you'd have to do something like
+
+SELECT pg_relation_size(cl.oid) AS tablesize,
+ CASE WHEN reltoastrelid=0 THEN 0
+ELSE pg_relation_size(reltoastrelid) END AS toastsize,
+ SUM(pg_relation_size(indexrelid)) AS indexsize,
+ pg_size_pretty(pg_relation_size(cl.oid)
++ pg_relation_size(reltoastrelid)
++ SUM(pg_relation_size(indexrelid))::int8) AS totalsize
+ FROM pg_class cl
+ JOIN pg_index ON cl.oid=indrelid
+ WHERE relname = 'pg_rewrite'
+ GROUP BY 1,2
+
+This sample query utilizes the helper function pg_size_pretty(int8), which
+formats the number of bytes into a convenient string using kB, MB, GB, TB.
+It is also contained in this module.
+
Copy this directory to contrib/dbsize in your PostgreSQL source tree.
Then just run make; make install. Finally, load the functions into any
database using dbsize.sql.
-
-When computing the size of a table, it does not include TOAST or index
-disk space.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] dbsize contrib
Patch applied. Thanks. Can I get some documentation in the README for all the new functionality. --- Andreas Pflug wrote: > The current implementation of dbsize doesn't handle tables in > tablespaces correctly, and is quite restricted on objects covered (only > tables and databases, but not tablespaces and indexes). > > The attached patch contributes: > > - database_size(name) > - relation_size(text) > These are the well-known functions, tablespace-aware. > > - pg_tablespace_size(oid) > - pg_database_size(oid) > - pg_relation_size(oid) > Tablespace-aware implementations, used by the upper functions. > pg_relation_size will report sizes of indexes as well. > > - pg_size_pretty(bigint) > Formatting of sizes, to display '146MB' instead of '152885668' > > Regards, > Andreas > > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PATCHES] dbsize contrib
On Mon, 30 Aug 2004, Andreas Pflug wrote: > Gavin Sherry wrote: > > >>The attached patch contributes: > >> > >>- database_size(name) > >>- relation_size(text) > > > > > > I sent in a dbsize patch to make these functions tablespace aware... > > AFAIR your patch was applied, but it misses tables in non-default > tablespaces. Ahh. There is a thoughto in my patch. The ifdef should have been HAVE_SYMLINK not SYMLINK. With HAVE_SYMLINK the current code works fine. That being said, your patch seems to clean up the code some whilst adding more functionality. Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] dbsize contrib
Gavin Sherry wrote: The attached patch contributes: - database_size(name) - relation_size(text) I sent in a dbsize patch to make these functions tablespace aware... AFAIR your patch was applied, but it misses tables in non-default tablespaces. Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] dbsize contrib
On Sun, 29 Aug 2004, Andreas Pflug wrote: > The current implementation of dbsize doesn't handle tables in > tablespaces correctly, and is quite restricted on objects covered (only > tables and databases, but not tablespaces and indexes). > > The attached patch contributes: > > - database_size(name) > - relation_size(text) I sent in a dbsize patch to make these functions tablespace aware... > These are the well-known functions, tablespace-aware. > > - pg_tablespace_size(oid) > - pg_database_size(oid) > - pg_relation_size(oid) > Tablespace-aware implementations, used by the upper functions. > pg_relation_size will report sizes of indexes as well. > > - pg_size_pretty(bigint) > Formatting of sizes, to display '146MB' instead of '152885668' Nice. Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
