Re: Table Size

Muhyiddin A.M Hayat wrote:

> How do I know table size?

Someone else suggested dumping the table to find out how large it is.
If you wanted to know how large it would be when dumped, this is the
only way to find out, but it's not a reliable measurement of how much
space a table is currently using.  Because dumps don't store indexes,
(and indexes can continue to grow), you will often find that a
compressed dump is many times smaller than the on-disk files which
belong to it. However, if you do a plain text dump, the dumped file may
very well be larger because everything is stored in ASCII.

The two methods below show on-disk sizes of tables, not including
indexes and TOASTED (stored out of line) columns.

METHOD 1:

You can learn approximately how much disk space it is using by doing:

SELECT relpages * 8192 AS size_in_bytes
FROM pg_class
WHERE
    relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'<schema_name>') AND
    relname = '<table_name>';

Notes:

(1) Use 'public' as the schema name unless you put your table in a
different schema.
(2) This value is only updated by the commands VACUUM, ANALYZE, and
CREATE INDEX.  Thus, if you have been changing your table, run ANALYZE
<table_name> before running this query.  See
http://www.postgresql.org/docs/7.4/static/catalog-pg-class.html

METHOD 2:

Go use ls -l to look at the file in which your data is stored.  Run this
command to find the file node number for a particular table:

SELECT relfilenode
FROM pg_class
WHERE
    relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'<schema_name>') AND
    relname = '<table_name>';

Then go do
ls -l <your postgres data directory>/base/<file node number>

Example:

ls -l /usr/local/pgsql/data/base/17384

Paul Tillotson