Re: How to evaluate disk space needed by a table

On Tue, May 28, 2013 at 9:48 AM, 高健 <luckyjackgao(at)gmail(dot)com> wrote:

> 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).
>
>
You heard right, as other said there are various hidden fileds added to
every tuple like (ctid,xmin,xmax,cmin,cmax). All these occupy some bytes in
the page. Take your example.

As per integer column, every column data occupies 4 bytes.

postgres=# select pg_column_size(id) from tst01 limit 1;
pg_column_size
----------------
4
(1 row)

When you calculate the row size...

postgres=# select pg_column_size(t) from tst01 t limit 1;
pg_column_size
----------------
28
(1 row)

Here 24 bytes as row header and 4 bytes of integer data.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/