83

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.

0

4 Answers 4

112

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:

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.

9
  • 3
    I think this answer would be a lot better if it was simply "no don't add arbitrary limits to a real database ever." I feel like a lot of this answer needs corrections and further information, but that it's totally off topic and would distract from your conclusion which I totally agree with. Mar 18, 2018 at 2:22
  • Yes, all based on Postgres versions before 9.1 - 6 years ago. A bit dusty by now, but the basic advice is still good. Mar 25, 2018 at 21:30
  • 1
    Is it a good or bad idea to add a check constraint for every text column for the purpose of a sanity check and ensuring a bug in the client doesn't use up all the database's disk space by inserting a very large text?
    – Code
    Jan 2, 2019 at 19:21
  • 3
    @Code: It's a viable option. If you have many columns with the same constraint, consider domains. Or varchar(n) after all, for simplicity - if the downsides don't typically affect you. (The limit is not arbitrary in your case if you want to enforce an actual maximum length.) Jan 2, 2019 at 19:51
  • Let's say you have only text field, how to u estimate the disk volume occupied by database with 1 million records for example ?
    – Tristan
    Mar 23, 2022 at 12:17
17

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.

2
  • out of plain curiosity, why do you think this length check cannot be done on a client application while capturing data?
    – PirateApp
    Mar 13, 2018 at 12:35
  • 5
    @PirateApp: because very often there will be more than one application or some external data source (think nightly batch imports). And almost always the database (and the data) live longer than one application.
    – user1822
    Mar 13, 2018 at 12:38
4

The question is specifically whether adding an arbitrary length limit to VARCHAR columns?

To that, the answer is simply "no". There is nothing that can justify adding an arbitrary limit like you would in inferior databases that support varchar(max) or use conventions like varchar(255). However, if the spec addresses a limit, I think the answer becomes much more complex especially on modern versions of PostgreSQL. And, for that, I would lean towards YES.

In my opinion, the limit is a wise-choice if the spec requires it. Especially for more reasonable workloads. If for no other reason then to preserve meta-data.

From my answer here, index performance for CHAR vs VARCHAR (Postgres), where I address the value of meta-data.

If I found a spec that had variable-length text-keys that were meaningful and that I trusted to have a constant max-length, I would use varchar too. However, I can't think of anything that fits that criteria.

1

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.

3
  • 1
    AFAIK there is no difference in TOASTing varchar and text fields.
    – dezso
    Jul 16, 2012 at 18:33
  • 1
    VARCHAR is purely syntactic sugar for TEXT in Postgres, there is zero difference in storage handling; the compression vs background table storage you mention is done based on the actual length of the data in the column and not on the column metadata. TEXT columns are stored internally as a varlena C struct (which is a variable length array with the first 4 bytes storing the length on create/update) and it is this struct that is optimized based on its length.
    – cowbert
    Aug 27, 2017 at 20:31
  • or the first byte storing the length for strings shorter than 128 bytes.
    – Jasen
    Feb 17, 2020 at 1:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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