Mysql – Does varchar(n) affect the number of records which can be stored per page even if you store less than n

First questions -

• Yes they are stored on page normally (for non-(MAX) values)
• They are not padded, but there is a 2 byte per row overhead to store the length of the field

1 - IT DEPENDS on if the page is full or not. Very likely it will fit on the current page. The header record for the row needs to updated as well to update the length of the field.

2 - Data will only be rearranged at the page level for an insert or update, so nothing else gets shifted. If it fits on your current page it will, if not there will be a page split.

3 - Never as far as I know for varchar - if you want this behavior you can make it a CHAR instead.

4 - Only if the value exceeds the space on the page.

As to the design itself...

You will definitely get better performance by using the id instead of a varchar.

• An int or smallint is orders of magnitude smaller than your strings.
• Index lookups are a lot more efficient on integers than on strings
• Data stored on the page will be consistent and much smaller
• SQL likes keys! A lot of optimizations can take place when you use PK/FK relationships because the engine can infer some metadata about duplicates/distinct values, etc.

The physical storage for rows is described in the docs in Database Page Layout. The column contents for the same row are all stored in the same disk page, with the notable exception of TOAST'ed contents (too large to fit in a page). Contents are extracted sequentially within each row, as explained:

To read the data you need to examine each attribute in turn. First check whether the field is NULL according to the null bitmap. If it is, go to the next. Then make sure you have the right alignment. If the field is a fixed width field, then all the bytes are simply placed.

In the simplest case (no TOAST'ed columns), postgres will fetch the entire row even if few columns are needed. So in this case, the answer is yes, having more columns may have a clear adverse impact on waster buffer cache, particularly if the column contents are large while still under the TOAST threshold.

Now the TOAST case: when an individual field exceeds ~2kB , the engine stores the field contents into a separate physical table. It also comes into play when the entire row doesn't fit into a page (8kB by default): some of the fields are moved to TOAST storage. Doc says:

If it's a variable length field (attlen = -1) then it's a bit more complicated. All variable-length data types share the common header structure struct varlena, which includes the total length of the stored value and some flag bits. Depending on the flags, the data can be either inline or in a TOAST table; it might be compressed, too

TOAST'ed contents are not fetched when they're not explicitly needed, so their effect on the total number of pages to fetch is small (a few bytes per column). This explains the results in @dezso's answer.

As for writes, each row with all its columns is entirely rewritten on each UPDATE, no matter what columns are changed. So having more columns is obviously more costly for writes.