Postgresql – Should I add an arbitrary length limit to VARCHAR columns

datatypesperformancepostgresqlvarchar

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:

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.

Best 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. Related:

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.

Related Question