Will table size differ depending on attributes string length?

By default the string length of an attribute is 200. If you increase this to 400, will the actual table-size increase? Or will the MBS only start demanding more space when it has to actually use more than 200 characters for some object’s attribute? Same question for decreasing the string length of an attribute: will this decrease the table size, or was it shrunk to only-what-was-needed anyway?
1 answers

Hi Tim,

  I believe the amount of space used is database dependent. In the Mendix Cloud, a Postgres database is used. Varchar fields do not reserve the entire length per row, meaning increasing the size limit won’t actually increase the DB size unless you start to add data. Of course, like all things database, it is not quite so straightforward. Here is what the Postgres documentation has to say about it:

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. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB. (The maximum value that will be allowed for n in the data type declaration is less than that. It wouldn't be useful to change this because with multibyte character encodings the number of characters and bytes can be quite different. If you desire to store long strings with no specific upper limit, use text or character varying without a length specifier, rather than making up an arbitrary length limit.)

So using a longer string might actually take up less space due to compression, depending on what is in there. In any case, there is a few byte difference in overhead that might be offset by additional compression.