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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-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

Browse pgsql-admin by date

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

Browse pgsql-sql by date

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