Thread

Re: [HACKERS] database size

Darren King <darrenk@insightdist.com> — 1998-01-07T18:03:07Z

> > 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

The above is for the current release of 6.2.1.  For 6.3, a couple of things
have been removed from the header that gives a 13% size savings for the above.
That percentage will go down of course as you add fields to the table.

A little more accurate by including the tuple rounding before storage.  For
me the above would still be true if there is one or two int4s since the four
bytes I would save would be taken back by the double-word tuple alignment.

With the current src tree...again, all with aix alignment...

 40 bytes + each row header
  8 bytes + two int fields @ 4 bytes each
--------- =
 48 bytes per tuple (round up to next highest mulitple of 8)
  4 bytes + pointer on page to tuple
--------- =
 52 bytes per tuple
 
8192 bytes - page size
   8 bytes - page header
   0 bytes - "special" Opaque space at page end...currently unused.
---------- =
8184 bytes

8184 / 52 gives 157 tuples per page.

300000 / 157 ... round up ... need 1911 pages which gives us ...

1911 * 8192 = 15,654,912 ... 13% smaller than 6.2 file size!

space = pg_sz * ceil(num_tuples / floor((pg_sz - pg_hdr - pg_opaque) / tup_sz))

where tup_sz is figured out from above.  You can figure out what your
platform is using by creating the table, inserting one record and then
examining the table file with a binary editor such as bpatch or beav.

Using the above and knowing the size of the fields, you should be able
to accurately calculate the amount a space any table will require before
you create it.

darrenk