Table size with page layout

Question

I'm using PostgreSQL 9.2 on Oracle Linux Server release 6.3.

According to the storage layout documentation, a page layout holds:

• PageHeaderData(24 byte)
• n number of points to item(index item / table item) AKA ItemIdData(4 byte)
• free space
• n number of items
• special space

I tested it to make some formula to estimate table size anticipated...(TOAST concept might be ignored.)

postgres=# \d t1;

                      Table "public.t1"
    Column    ','         Type         ','         Modifiers
---------------+------------------------+------------------------------
 code          |character varying(8)    |not null
 name          |character varying(100)  |not null
 act_yn        |character(1)            |not null default 'N'::bpchar
 desc          |character varying(100)  |not null
 org_code1     |character varying(3)    |
 org_cole2     |character varying(10)   |

 postgres=# insert into t1 values(
'11111111', -- 8
'1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111', <-- 100
'Y',
'1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111', <-- 100
'111',
'1111111111');

postgres=# select * from pgstattuple('t1');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
      8192 |           1 |       252 |          3.08 |                1 |            252 |               3.08 |       7644 |        93.31
(1 row)

Why is tuple_len 252 instead of 249? ("222 byte of all column's maximum length" PLUS "27 byte of tuple header followed by an optional null bitmap, an optional object ID field, and the user data")

Where do the 3 bytes come from?

Is there something wrong with my formula?

Answer 1

Your calculation is off at several points.

• Storage size of varchar or text is (quoting the manual here):

The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less.

Bold emphasis mine to address question in comment.

• The HeapTupeHeader occupies 23 bytes, not 27 bytes.

• 1 byte of padding due to data alignment (multiple of 8), which is used for the NULL bitmask in this case.

• No padding for type varchar.

So, the actual calculation is:

        23    -- heaptupleheader
     +   1    -- NULL bit mask (or padding if all columns are NOT NULL)
     +   8    -- columns
     + 100 
     +   1 
     + 100 
     +   3 
     +  10 
     +   6    -- 1 byte overhead per varchar column, 6 columns

-> 252 bytes.

I wrote a couple of related answers, you can find most of them linked to this one (look at the link list to the right).