Re: [HACKERS] database size
От | darrenk@insightdist.com (Darren King) |
---|---|
Тема | Re: [HACKERS] database size |
Дата | |
Msg-id | 9801071803.AA62954@ceodev обсуждение исходный текст |
Ответы |
Re: [HACKERS] database size
|
Список | pgsql-hackers |
> > 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
В списке pgsql-hackers по дате отправления: