From: | 高健 <luckyjackgao(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | How to evaluate disk space needed by a table |
Date: | 2013-05-28 04:18:58 |
Message-ID: | CAL454F3P4PnYGmDkzGMd4AdKhWnf7gOThPPfDC2Ga47vkdKWMg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello:
I created a table, and found the file created for that table is about 10
times of that I estimated!
The following is what I did:
postgres=# create table tst01(id integer);
CREATE TABLE
postgres=#
postgres=# select oid from pg_class where relname='tst01';
oid
-------
16384
(1 row)
Then I can see the file now:
[root(at)lex base]# ls ./12788/16384
./12788/16384
I heard that one integer type will use 4 bytes.
so I think that 2048 records with only one column of integer data type,
will use a little more than 8K(2048 records * 4 bytes/per integer data
type + headers).
But in fact they use so much more:
After I run this:
postgres=# insert into tst01 values(generate_series(1,2048));
INSERT 0 2048
postgres=#
I can find the file 16384 is now 80KB!
[root(at)lex base]# ls -lrt ./12788/16384
-rw------- 1 postgres postgres 81920 May 28 11:54 ./12788/16384
[root(at)lex base]# ls -lrt -kb ./12788/16384
-rw------- 1 postgres postgres 80 May 28 11:54 ./12788/16384
[root(at)lex base]#
Then I tried again , I put another 2048 records:
postgres=# insert into tst01 values(generate_series(2049,4096));
INSERT 0 2048
postgres=#
And found that the file is now 152KB!
[root(at)lex base]# ls -lrt -kb ./12788/16384
-rw------- 1 postgres postgres 152 May 28 11:56 ./12788/16384
[root(at)lex base]#
Before this, I have thought that headers and other structure will just
use a little space.
But what I found is about 10 times the space I evaluated.
So , Is there any method to correctly evaluate disk space one table will
need,
given the table's column data types and , estimated record numbers ?
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2013-05-28 04:42:04 | Re: How to evaluate disk space needed by a table |
Previous Message | Edson Richter | 2013-05-28 03:13:16 | Re: Most efficient way to initialize a standby server |