Thread: Table Size

Muhyiddin A.M Hayat typed this on 02/02/2004 09:26 PM:
> How do I know table size?

You can always dumb the table and check the file size.

-Robby

--
#-------------------------------------------------------
# Robby Russell,  |  Sr. Administrator / Lead Programmer
# Command Prompt, Inc.   |  http://www.commandprompt.com
# rrussell@commandprompt.com | Telephone: (503) 667.4564
#-------------------------------------------------------

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

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

can be replaced with

WHERE oid = 'schemaname.tablename'::regclass::oid

which will look up the oid for the table taking into account the current
schema_path settings.
(the cast to oid is unneccesary, but included here for clarity only)

--
Tom Hebbron
www.hebbron.com

"Paul Tillotson" <pntil@shentel.net> wrote in message
news:40203D13.4000906@shentel.net...
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>