Re: dbsize contrib - Mailing list pgsql-patches

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 -0000    1.2
+++ README.dbsize    2 Sep 2004 08:28:10 -0000
@@ -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.