from http://linuxboxadmin.com/articles/postgresql-for-mysql-users.php "PostgreSQL uses the vacuumdb utility program to reclaim space from deleted records and optimize indexes. Vacuum should be scheduled to run via cron at least once a week." Do you use that?
You might also like this older thread: http://archives.postgresql.org/pgsql-docs/2002-04/msg00001.php To answer your question, from http://developer.postgresql.org/pgdocs/postgres/functions-admin.html The functions shown in Table 9-48<http://developer.postgresql.org/pgdocs/postgres/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE>calculate the actual disk space usage of database objects. *Table 9-48. Database Object Size Functions* NameReturn TypeDescriptionpg_column_size(any)intNumber of bytes used to store a particular value (possibly compressed) pg_database_size(oid) bigintDisk space used by the database with the specified OID pg_database_size(name) bigintDisk space used by the database with the specified name pg_relation_size(oid) bigintDisk space used by the table or index with the specified OID pg_relation_size(text) bigint Disk space used by the table or index with the specified name. The table name can be qualified with a schema name pg_size_pretty(bigint) textConverts a size in bytes into a human-readable format with size units pg_tablespace_size(oid) bigintDisk space used by the tablespace with the specified OID pg_tablespace_size(name) bigintDisk space used by the tablespace with the specified name pg_total_relation_size(oid) bigint Total disk space used by the table with the specified OID, including indexes and toasted data pg_total_relation_size (text) bigint Total disk space used by the table with the specified name, including indexes and toasted data. The table name can be qualified with a schema name pg_column_size shows the space used to store any individual data value. pg_database_size and pg_tablespace_size accept the OID or name of a database or tablespace, and return the total disk space used therein. pg_relation_size accepts the OID or name of a table, index or toast table, and returns the size in bytes. pg_size_pretty can be used to format the result of one of the other functions in a human-readable way, using kB, MB, GB or TB as appropriate. pg_total_relation_size accepts the OID or name of a table or toast table, and returns the size in bytes of the data and all associated indexes and toast tables. What do I win? :) ben On 1/31/07, Rob Hudson <[EMAIL PROTECTED]> wrote:
Is there a way, from within Postgresql, to view the filesize usage of its databases and tables? That might be the best approach and sounds easier than looking at the raw data directory. On Wed Jan 31, 2007 at 02:35:16PM -0800, larry price wrote: > short answer: you can but it's ugly > > longer answer: > start looking at the pg_catalog.* relations (use \d from psql prompt) > as I understand it the files under $PG_DATA/base/ are named after > oid's for tables and relations, but there are a lot of subtleties, and > the whole point of a DBMS is to manage that bookkeeping for you. _______________________________________________ EUGLUG mailing list euglug@euglug.org http://www.euglug.org/mailman/listinfo/euglug
_______________________________________________ EUGLUG mailing list euglug@euglug.org http://www.euglug.org/mailman/listinfo/euglug
