Re: Postgres table size

Erik Jones <erik(at)myemma(dot)com> writes:
> On Nov 21, 2007, at 9:17 AM, SHARMILA JOTHIRAJAH wrote:
>> Calculation
>> varchar = (overhead) 4 + (actual length of string) 3 = 7 bytes
>> *for 5 varchar cols =
>> 5*7 = 35 bytes
>>
>> numeric (according to manual--- The actual storage requirement is
>> two bytes for each group of four decimal digits, plus eight bytes
>> overhead )
>> numeric = ( 10/4)*2 +8 = 13 bytes
>> *for 5 numeric cols = 13 *5 = 65

> Not that it will make much difference, but you need to round up in
> the 10/4 part so you get 14 bytes, not 13.

Also, this calculation is ignoring the fact that (pre-8.3) varlena
values have to be int-aligned, so there's wasted pad space too.
The varchar values really need 8 bytes each, and the numeric values
16, so the actual data payload in each row is 120 bytes. Then add
HeapTupleHeader (28 bytes), and then MAXALIGN the whole row size.
So the rowsize would be either 148 or 152 bytes depending on if you
were on a machine with 8-byte MAXALIGN. Then add the per-row item
pointer, giving total per-row space of 152 or 156 bytes. That
means you can fit either 53 or 52 rows per page, giving either 188
or 192 pages as the minimum possible file size. Evidently, Sharmila
is using a MAXALIGN=4 machine and has a few dead rows in there.

regards, tom lane