database & table size
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
Re: [SQL] database & table size
On Wed, 25 Jun 2003, Anagha Joshi wrote:
> 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?
The two varyings won't necessarily take up the full size if the actual
data is smaller IIRC and large values of cdrs will be compressed and/or
stuck into a toast table. You should look for the associated toast table
and factor its space in as well (I think reltoastrelid in the pg_class
row for cdrrec will give you the appropriate oid to look for).