X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 8556730EEA7 for ; Wed, 25 Jun 2003 06:17:34 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 53095-09 for ; Wed, 25 Jun 2003 03:17:23 -0300 (ADT) Received: from mail01.ucr.com.hk (unknown [210.177.214.194]) by svr1.postgresql.org (Postfix) with ESMTP id A67E030E5AC for ; Wed, 25 Jun 2003 03:17:20 -0300 (ADT) Received: from frankie (machine1-101 [192.168.1.101]) by mail01.ucr.com.hk (8.11.6/8.11.6) with SMTP id h5P6HrG08894; Wed, 25 Jun 2003 14:17:55 +0800 Message-ID: <002001c33ae1$b13af6f0$6501a8c0@rnd.ucr.com.hk> From: "Frankie Lam" To: , "Anagha Joshi" References: Subject: Re: database & table size Date: Wed, 25 Jun 2003 14:19:09 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001D_01C33B24.BE345CF0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=HTML_50_60, HTML_FONT_FACE_BAD, HTML_FONT_FACE_ODD, HTML_MESSAGE, REFERENCES X-Spam-Level: * X-Archive-Number: 200306/288 X-Sequence-Number: 14046 This is a multi-part message in MIME format. ------=_NextPart_000_001D_01C33B24.BE345CF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable database & table sizeHi, Have you tried to run 'VACUUM FULL' command? According to PostgreSQL documentation -------------8<------------------- 8.2.1. Recovering disk space In normal PostgreSQL operation, an UPDATE or DELETE of a row does not immed= iately remove the old tuple (version of the row). This approach is necessar= y to gain the benefits of multiversion concurrency control (see the Postgre= SQL 7.3 User's Guide): the tuple must not be deleted while it is still pote= ntially visible to other transactions. But eventually, an outdated or delet= ed tuple is no longer of interest to any transaction. The space it occupies= must be reclaimed for reuse by new tuples, to avoid infinite growth of dis= k space requirements. This is done by running VACUUM.=20 ------------->8------------------- Frankie ----- Original Message -----=20 From: Anagha Joshi=20 To: pgsql-admin@postgresql.org ; pgsql-sql@postgresql.org=20 Sent: Wednesday, June 25, 2003 12:34 PM Subject: [SQL] database & table size Hello , Here is something I've found related to database & table size My databse name is 'test' and table name is 'cdrrec'. 1. Following will give oids & database test=3D# select datname, oid from pg_database; datname | oid -------------+--------- fcsconfigdb | 16562 template1 | 1 template0 | 16555 fcsauditdb | 16565 fcsbsdb | 16566 fcsmibdb | 32386 fcstrapdb | 52561 test | 1580177 (8 rows)=20 test=3D# select oid from pg_class where relname =3D 'cdrrec'; oid --------- 2078989 (1 row) 2. Here are actual disk sizes of tables & database $ cd /export/home/uxapps/postgresql-7.2.4/data/base/ $ ls -l total 32 drwx------ 2 postgres postgres 1536 Jun 6 17:13 1 drwx------ 2 postgres postgres 1536 Jun 24 15:02 1580177 drwx------ 2 postgres postgres 1536 Jun 6 17:02 16555 drwx------ 2 postgres postgres 2048 Jun 6 18:04 16562 drwx------ 2 postgres postgres 1536 Jun 10 16:18 16565 drwx------ 2 postgres postgres 2048 Jun 6 18:03 16566 drwx------ 2 postgres postgres 1536 Jun 6 17:41 32386 drwx------ 3 postgres postgres 1536 Jun 19 11:08 52561 3. the directory '1580177' corresponds to 'test' database. $ cd 1580177 4. the file '2078989' corresponds to 'cdrrec' file (table) $ ls -l 2078989 -rw------- 1 postgres postgres 2359296 Jun 24 17:18 2078989 This means table 'cdrrec' takes approx. 2030.2626953125 KB i.e. approx. 2= MB Table is like this: test=3D# \d cdrrec; Table "cdrrec" Column | Type | Modifiers ---------+-----------------------+---------------------------------------= --------------- seqno | integer | not null default nextval('"cdrrec_seqn= o_seq"'::text) //4 bytes timerec | bigint | = //8 bytes=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20 ack | boolean | default 'f'::bool = //1 byte pos | boolean | default 'f'::bool = //1 byte=20=20=20=20=20=20=20=20 ipaddr | character varying(16) | = // 4 + 16 cdrs | bit varying(524288) | = //64K Primary key: cdrrec_pkey *Sizes are as per documentation. Therefore, 1 record is of 65570 bytes i.e. 64.033203125 KB. And table contains 1000 records. But results are noway closer to '2MB". Any idea? Thanks, Anagha ------=_NextPart_000_001D_01C33B24.BE345CF0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable database & table size
Hi,
 
Have you tried to run 'VACUUM FULL' command?
 
According to PostgreSQL documentation
 
-------------8<-------------------
 
8.2.1. Recovering disk space

In normal PostgreSQL operation, an UPDATE or DELETE of a row does = not=20 immediately remove the old tuple (version of the row). This approach is=20 necessary to gain the benefits of multiversion concurrency control (see the= =20 PostgreSQL 7.3 User's Guide): the tuple must not be deleted while it is sti= ll=20 potentially visible to other transactions. But eventually, an outdated or= =20 deleted tuple is no longer of interest to any transaction. The space it occ= upies=20 must be reclaimed for reuse by new tuples, to avoid infinite growth of disk= =20 space requirements. This is done by running VACUUM.

------------->8-------------------
 
Frankie
----- Original=20 Message -----
To: pgsql-admin@postgresql.org ; pgsql-sql@postgresql.org
Sent:=20 Wednesday, June 25, 2003 12:34 PM
Subject:=20 [SQL] database & table size

Hello ,

Here is something I=92v= e found=20 related to database & table size

My databse name=20 is <= FONT=20 size=3D2>=92test=92 and table name is =91cdrrec=92.

    1.   &= nbsp; =20 Following will give oids & database

test=3D# select datname= , oid from=20 pg_database;

   datname&nb= sp; =20 |   oid

-------------+---------

 fcsconfigdb |&nbs= p; =20 16562

 template1 &n= bsp;=20 |       1

 template0 &n= bsp;=20 |   16555

 fcsauditdb = =20 |   16565

 fcsbsdb     |  =20 16566

 fcsmibdb &nb= sp; =20 |   32386

 fcstrapdb &n= bsp;=20 |   52561

 test        |=20 1580177

(8 rows) =

test=3D# select oid fro= m pg_class=20 where relname =3D 'cdrrec';

   oid=

---------=

 2078989

(1 row)

    2.   &= nbsp; =20 Here are actual disk sizes of tables & database

    $ cd=20 /export/home/uxapps/postgresql-7.2.4/data/base/

    $ ls -l

    total 32<= /P>

    drwx------   = 2 postgres=20 postgres    1536 Jun  6 17:13 1

    drwx------   = 2 postgres=20 postgres    1536 Jun 24 15:02 1580177

    drwx------   = 2 postgres=20 postgres    1536 Jun  6 17:02 16555

    drwx------   = 2 postgres=20 postgres    2048 Jun  6 18:04 16562

    drwx------   = 2 postgres=20 postgres    1536 Jun 10 16:18 16565

    drwx------   = 2 postgres=20 postgres    2048 Jun  6 18:03 16566

    drwx------   = 2 postgres=20 postgres    1536 Jun  6 17:41 32386

    drwx------   = 3 postgres=20 postgres    1536 Jun 19 11:08 52561


      3.   &= nbsp; =20 the directory =911580177=92 corresponds=20 to= =91test=92 database.

    $ cd 1580177

      4.   &= nbsp; =20 the file =912078989=92 corresponds to =91cdrrec=92=20 file (table)

    $ ls -l 2078989<= /SPAN>

    -rw-------   = 1 postgres=20 postgres 2359296 Jun 24 17:18 2078989

    This means=20 table =92cdrrec=92=20 takes approx. 2030.2626953125 KB i.e.=20 approx.= 2MB

    Table is like=20 this:

    test=3D# \d=20 cdrrec;

              &nbs= p;            &= nbsp;           &nbs= p;=20 Table "cdrrec"

     Column =20 |        =20 Type         =20 |            =          =20 Modifiers

    ---------+-----------------------+------------------------------= ------------------------

     seqno  = |=20 integer           =    =20 | not null default nextval('"cdrrec_seqno_seq"'::text)=20         //4=20 bytes

     timerec |=20 bigint           &= nbsp;   =20 |             = ;=20        =20        =20        =20        =20         //8=20 bytes      =20        =20        =20        =20        =20        

     ack  &n= bsp; =20 |=20 boolean           =    =20 | default 'f'::bool   =20        =20        =20        =20         //1=20 byte

     pos  &n= bsp; =20 |=20 boolean           =    =20 | default 'f'::bool   =20        =20        =20        =20         //1=20 byte       

     ipaddr  | ch= aracter=20 varying(16) |     =20        =20        =20        =20        =20        =20         // 4 += =20 16<= /P>

     cdrs  &= nbsp; | bit=20 varying(524288)   |     =20        =20        =20        =20        =20        =20         //64K

    Primary key:=20 cdrrec_pkey

    *Sizes are as per=20 documentation.

    Therefore, 1 record is = of 65570=20 bytes i.e. 64.033203125 KB.

    And table contains 1000= =20 records.

    But results are noway c= loser to=20 =91<= FONT=20 size=3D2>2MB=94.

    Any idea?=

    Thanks,

    Anagha


    ------=_NextPart_000_001D_01C33B24.BE345CF0--