varchar(n) - how big should n be?

I am mostly worried about whether I made it too small, and whether I can resize it.

For some reasons, the ORM requires specifying size, even if the underlying JDBC driver doesn't need to be strict about size (H2), or doesn't read size at all (SQLite). I am also planning to use PostgreSQL as well.

According to StackOverflow, maybe even VARCHAR(2) poses no restriction in size?

Also, there seems to be no meaning in using 2^n - 1?

Comments

[Dian Fay]

I wrote about this recently! There's no hard and fast rule -- the problem with erring on the small side is obvious, but if it's too big you waste space which has further effects on how hard the database server has to work. And of course actual values for 'too big' and 'too small' depend entirely on the column itself.

[Pacharapol Withayasakpunt]

So, there is TEXT type in Postgres (and obviously SQLite), with not only no size limit, but also better performance as well!

[Alex Janke]

To be honest, just limit it to your personal upper limits. A varchar(2) uses virtually the same amount of space as a varchar(200) if your input string is the same. The number in the brackets pretty much acts as a restriction.

Check out this page from the postgre docs for a way more detailed explanation:
postgresql.org/docs/9.3/datatype-c...

[cubiclesocial]

VARCHAR is short for "variable CHAR." CHAR(n) is always stored as exactly 'n' characters. VARCHAR(n) uses a size byte preceding the field data so that the stored data doesn't end up with extra whitespace added to it. For n < 256, VARCHAR(n) uses a single byte prefix. Starting at n >= 256, there is a two byte prefix. Other than that, 'n' just tells the database to restrict the maximum number of characters for a field and there is no functional difference other than storage requirements. Depends on the database product as to whether or not it enforces the limit. MySQL/MariaDB will.

If you don't know the exact size of the data, go with VARCHAR(255) or something somewhat larger than your projected field size. I generally use 50, 100, and 255. It won't matter to anyone except pedantic database administrators. Anything larger than VARCHAR(255) should probably be a MEDIUMTEXT field. Anything that's larger than MEDIUMTEXT probably shouldn't be stored in a database unless you enjoy slow database queries that consume lots of disk I/O and CPU usage.