Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT.

Should I add an arbitrary length limit to a name or address column?

share|improve this question
    
Only if you want to limit their length explicitly. You have found the best answer in the docs. –  dezso Jul 16 '12 at 17:49
4  
You may be interested in this. –  Bruno Jul 16 '12 at 19:15
1  
FYI, In SQL Server, Varchar means Varchar(1) and so you one has to specify a length if more than 1 character is required. –  Emmad Kareem Jul 16 '12 at 20:23

3 Answers 3

The answer is no.
Don't add a length modifier to varchar if you can avoid it. Most of the time, you don't actually need a length restriction anyway. Just use text for all character data. Make that varchar (no length modifier) if you need to stay compatible with RDBMS which don't have text.

Performance is almost the same - text is a bit faster in rare situations, and you save the cycles for the check on the length.

If you actually need to enforce a maximum length, still use text and add a check constraint for that:

ALTER TABLE tbl ADD CONSTRAINT tbl_col_len CHECK (length(col) < 51);

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, ...)

With length modifiers you just run into problems like this or this or this ...

PostgreSQL 9.1 introduced a new feature to alleviate the pain somewhat. I quote the release notes here:

Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in appropriate cases (Noah Misch, Robert Haas)

For example, converting a varchar column to text no longer requires a rewrite of the table. However, increasing the length constraint on a varchar column still requires a table rewrite.

share|improve this answer
    
+1 that's what I'd do too –  Jack Douglas Jul 25 '12 at 17:21

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 ALTER TABLE which might take a long time to finish (due to a possible re-write of the table) during which an exclusive table lock is necessary.

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 text, a varchar or a varchar(5000) column.

share|improve this answer
    
What's your opinion about the solution in @Bruno's link involving DOMAINs? –  dezso Jul 16 '12 at 19:30
    
@dezso: looks even better! I assumed changing a domain would require the same work as changing the column type - but it doesn't seem to be that case. –  a_horse_with_no_name Jul 16 '12 at 19:33

It looks like there might be some performance difference if VARCHAR is regularly used to store very large strings, since "long strings are compressed by the system automatically" and "very long values are also stored in background tables." Theoretically this would mean that a high volume of requests for a very long string field would be slower than for a short string field. You'll probably never run into this problem, since names and addresses aren't going to be very long.

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.

share|improve this answer
    
AFAIK there is no difference in TOASTing varchar and text fields. –  dezso Jul 16 '12 at 18:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.