Thread: 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. > ... > One massive sort file... > ... This one I don't know if is "normal"... Darren aka darrenk@insightdist.com
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
> > > 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
> > 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