pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddatabase & table size
3+ messages / 3 participants
[nested] [flat]
* database & table size
@ 2003-06-25 04:34 Anagha Joshi <ajoshi@nulinkinc.com>
0 siblings, 2 replies; 3+ messages in thread
From: Anagha Joshi @ 2003-06-25 04:34 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] database & table size
@ 2003-06-25 05:57 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Anagha Joshi <ajoshi@nulinkinc.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2003-06-25 05:57 UTC (permalink / raw)
To: Anagha Joshi <ajoshi@nulinkinc.com>; +Cc: pgsql-admin@postgresql.org; pgsql-sql
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).
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: database & table size
@ 2003-06-25 06:19 Frankie Lam <frankielam@ucr.com.hk>
parent: Anagha Joshi <ajoshi@nulinkinc.com>
1 sibling, 0 replies; 3+ messages in thread
From: Frankie Lam @ 2003-06-25 06:19 UTC (permalink / raw)
To: pgsql-sql; Anagha Joshi <ajoshi@nulinkinc.com>
database & table sizeHi,
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 -----
From: Anagha Joshi
To: pgsql-admin@postgresql.org ; pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-06-25 06:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-25 04:34 database & table size Anagha Joshi <ajoshi@nulinkinc.com>
2003-06-25 05:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-06-25 06:19 ` Frankie Lam <frankielam@ucr.com.hk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox