Should I add an arbitrary length limit to VARCHAR columns?

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?

Edit: Not a dupe of:

• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I know the CHAR type is a relic of the past and I'm interested not only in performance but other pros and cons like Erwin stated in his amazing answer.

The answer is no.

Related advice in the Postgres Wiki.

Don't add a length modifier to varchar if you don't need it. (Most of the time, you don't.) Just use text for all character data. Make that varchar (standard SQL type) without length modifier if you need to stay compatible with RDBMS which don't have text as generic character string type.

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

A particularly common misconception is varchar(255), which hardly ever makes sense in Postgres. Often carried over from other (outdated) RDBMS, where the particular limit has performance benefits. That's not true for Postgres. See:

• Any downsides of using data type “text” for storing strings?

If you actually need to enforce a maximum length, varchar(n) is a valid choice. But I would still consider text with a CHECK constraint like:

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 depending objects (views, functions, foreign keys, ...). And you can enforce other requirements in the (same) constraint.

Length modifiers used to cause problems like this or this or this ...

PostgreSQL 9.1 introduced a new feature to alleviate the pain somewhat. The release notes:

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.

More issues with varchar(n) have been fixed in later releases.

StackExchange Database Administrators Q#20974, answer score: 118