Re: database & table size - Mailing list pgsql-sql

From Frankie Lam
Subject Re: database & table size
Date
Msg-id 002001c33ae1$b13af6f0$6501a8c0@rnd.ucr.com.hk
Whole thread Raw
In response to database & table size  ("Anagha Joshi" <ajoshi@nulinkinc.com>)
List pgsql-sql
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 immediately remove the old tuple (version of the row). This approach is necessary to gain the benefits of multiversion concurrency control (see the PostgreSQL 7.3 User's Guide): the tuple must not be deleted while it is still potentially visible to other transactions. But eventually, an outdated or deleted 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 disk space requirements. This is done by running VACUUM.

------------->8-------------------
 
Frankie
----- Original Message -----
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=# 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)

test=# select oid from pg_class where relname = '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. 2MB

Table is like this:

test=# \d cdrrec;

                                     Table "cdrrec"

 Column  |         Type          |                      Modifiers

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

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

 timerec | bigint                |                                                      //8 bytes                                              

 ack     | boolean               | default 'f'::bool                                    //1 byte

 pos     | boolean               | default 'f'::bool                                    //1 byte       

 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

pgsql-sql by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: database & table size
Next
From: Dani Oderbolz
Date:
Subject: Re: Delete duplicates