X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5E260D1C9AB for ; Wed, 4 Feb 2004 00:30:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 15683-08 for ; Tue, 3 Feb 2004 20:30:12 -0400 (AST) Received: from seahorse.shentel.net (seahorse.shentel.net [204.111.11.44]) by svr1.postgresql.org (Postfix) with ESMTP id E053CD1C517 for ; Tue, 3 Feb 2004 20:30:07 -0400 (AST) Received: from shentel.net (ha96s006.d.shentel.net [204.111.96.6]) by seahorse.shentel.net (8.11.7/8.11.7) with ESMTP id i140U5425256 for ; Tue, 3 Feb 2004 19:30:06 -0500 Message-ID: <40203D13.4000906@shentel.net> Date: Tue, 03 Feb 2004 19:30:11 -0500 From: Paul Tillotson User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-php@postgresql.org Subject: Re: Table Size References: <002c01c3ea17$1acd10a0$4f00a8c0@MIDDINKS> In-Reply-To: <002c01c3ea17$1acd10a0$4f00a8c0@MIDDINKS> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/17 X-Sequence-Number: 2565 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 = '') AND relname = ''; 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 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 = '') AND relname = ''; Then go do ls -l /base/ Example: ls -l /usr/local/pgsql/data/base/17384 Paul Tillotson