Skip site navigation (1) Skip section navigation (2)

database & table size

From: "Anagha Joshi" <ajoshi(at)nulinkinc(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>, <pgsql-sql(at)postgresql(dot)org>
Subject: database & table size
Date: 2003-06-25 04:34:29
Message-ID: FF851C7EEB75954F9BCFB5CA117AB1EC61A457@delta.nulinkinc.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-sql
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


Responses

pgsql-admin by date

Next:From: Stephan SzaboDate: 2003-06-25 05:57:10
Subject: Re: [SQL] database & table size
Previous:From: Chris White (cjwhite)Date: 2003-06-24 17:01:54
Subject: Need help/suggestions with backup/restore of database tables.

pgsql-sql by date

Next:From: Stephan SzaboDate: 2003-06-25 05:57:10
Subject: Re: [SQL] database & table size
Previous:From: Michael A NachbaurDate: 2003-06-24 23:02:33
Subject: Re: Database design - AGAIN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group