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
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. |
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 |