> I created a table with two columns of type int, and loaded about 300 K records
> in it. So, the total size of the table is approx. that of 600 K integers,
> roughly 2.4 MB.
> But, the file corresponding to the table in pgsql/data/base directory
> has a size of 19 MB. I was wondering if I have done something wrong in
> the installation or usage, or is it the normal behavior ?

48 bytes + each row header (on my aix box..._your_ mileage may vary)
 8 bytes + two int fields @ 4 bytes each
 4 bytes + pointer on page to tuple
-------- =
60 bytes per tuple

8192 / 60 give 136 tuples per page.

300000 / 136 ... round up ... need 2206 pages which gives us ...

2206 * 8192 = 18,071,532

So 19 MB is about right.  And this is the best to be done, unless
you can make do with int2s which would optimally shrink the table
size to 16,834,560 bytes.  Any nulls in there might add a few bytes
per offending row too, but other than that, this should be considered
normal postgresql behavior.

> ...
> One massive sort file...
> ...

This one I don't know if is "normal"...


Darren  aka  darrenk@insightdist.com

Tue, 6 Jan 1998 18:26:31 -0500
darrenk@insightdist.com (Darren King)
Re: [HACKERS] database size
On Tue, 6 Jan 1998, Darren King wrote:

> 48 bytes + each row header (on my aix box..._your_ mileage may vary)
>  8 bytes + two int fields @ 4 bytes each
>  4 bytes + pointer on page to tuple
> -------- =
> 60 bytes per tuple
> 
> 8192 / 60 give 136 tuples per page.
> 
> 300000 / 136 ... round up ... need 2206 pages which gives us ...
> 
> 2206 * 8192 = 18,071,532
> 
> So 19 MB is about right.  And this is the best to be done, unless
> you can make do with int2s which would optimally shrink the table
> size to 16,834,560 bytes.  Any nulls in there might add a few bytes
> per offending row too, but other than that, this should be considered
> normal postgresql behavior.

	Bruce...this would be *great* to have in the FAQ!!  What we do need is
a section of the User Manual dealing with computing resources required for
a table, similar to this :)

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Tue, 6 Jan 1998 20:32:39 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] database size
> 
> > I created a table with two columns of type int, and loaded about 300 K records
> > in it. So, the total size of the table is approx. that of 600 K integers,
> > roughly 2.4 MB.
> > But, the file corresponding to the table in pgsql/data/base directory
> > has a size of 19 MB. I was wondering if I have done something wrong in
> > the installation or usage, or is it the normal behavior ?
> 
> 48 bytes + each row header (on my aix box..._your_ mileage may vary)
>  8 bytes + two int fields @ 4 bytes each
>  4 bytes + pointer on page to tuple
> -------- =
> 60 bytes per tuple
> 
> 8192 / 60 give 136 tuples per page.
> 
> 300000 / 136 ... round up ... need 2206 pages which gives us ...
> 
> 2206 * 8192 = 18,071,532
> 
> So 19 MB is about right.  And this is the best to be done, unless
> you can make do with int2s which would optimally shrink the table
> size to 16,834,560 bytes.  Any nulls in there might add a few bytes
> per offending row too, but other than that, this should be considered
> normal postgresql behavior.


Nice math exercise.

Does anyone want to tell me the row overhead on commercial databases?

-- 
Bruce Momjian
maillist@candle.pha.pa.us

Tue, 6 Jan 1998 20:18:26 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] database size
> 
> On Tue, 6 Jan 1998, Darren King wrote:
> 
> > 48 bytes + each row header (on my aix box..._your_ mileage may vary)
> >  8 bytes + two int fields @ 4 bytes each
> >  4 bytes + pointer on page to tuple
> > -------- =
> > 60 bytes per tuple
> > 
> > 8192 / 60 give 136 tuples per page.
> > 
> > 300000 / 136 ... round up ... need 2206 pages which gives us ...
> > 
> > 2206 * 8192 = 18,071,532
> > 
> > So 19 MB is about right.  And this is the best to be done, unless
> > you can make do with int2s which would optimally shrink the table
> > size to 16,834,560 bytes.  Any nulls in there might add a few bytes
> > per offending row too, but other than that, this should be considered
> > normal postgresql behavior.
> 
> 	Bruce...this would be *great* to have in the FAQ!!  What we do need is
> a section of the User Manual dealing with computing resources required for
> a table, similar to this :)

Added to FAQ.

-- 
Bruce Momjian
maillist@candle.pha.pa.us

Tue, 6 Jan 1998 22:02:42 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] database size