According to PostgreSQL's docs, there's no performance difference between
Should I add an arbitrary length limit to a name or address column?
The answer is no.
Performance is almost the same -
If you actually need to enforce a maximum length, still use
You can modify or drop such a constraint at any time without having to mess with the table definition and all depending objects (views, functions, foreign keys, ...)
PostgreSQL 9.1 introduced a new feature to alleviate the pain somewhat. I quote the release notes here:
If you see the length limit as a kind of check constraint to make sure you validate the data, then yes add one. Actually you might want to not use a length definition but a real check constraint instead, to make changing the limit faster.
To change (increase) a length limit you need to run an
Changing (i.e. dropping and re-creating) a check constraint is a very brief operation and only requires reading the table's data, it will not change any rows. So that is going to be a lot quicker (which in turn means the exclusive table lock will be held for a much shorter amount of time).
During operation there is no difference whatsovever between a
It looks like there might be some performance difference if
However, depending on how you're using these strings outside your database, you might want to add a practical limit to prevent abuse of the system. For example, if you're displaying the name and address on a form somewhere, you might not be able to display a whole paragraph of text in the "name" field, so it would make sense to limit the name column to something like 500 characters.