From: Paul Tillotson <pntil@shentel.net>
To: pgsql-php@postgresql.org
Subject: Re: Table Size
Date: Tue, 03 Feb 2004 19:30:11 -0500
Message-ID: <40203D13.4000906@shentel.net> (raw)
In-Reply-To: <002c01c3ea17$1acd10a0$4f00a8c0@MIDDINKS>
References: <002c01c3ea17$1acd10a0$4f00a8c0@MIDDINKS>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: pntil@shentel.net
Subject: Re: Table Size
In-Reply-To: <40203D13.4000906@shentel.net>
References: <002c01c3ea17$1acd10a0$4f00a8c0@MIDDINKS> <40203D13.4000906@shentel.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox