Does a varchar field's declared size have any impact in PostgreSQL?

Question

Is VARCHAR(100) any better than VARCHAR(500) from a performance point of view? What about disk usage?

Talking about PostgreSQL today, not some database some time in history.

Concertize

Answer 1

They are identical.

From the PostgreSQL documentation:

http://www.postgresql.org/docs/8.3/static/datatype-character.html

Tip: There are no performance differences between these three types, apart from increased storage size when using the blank-padded type, and a few extra cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, it has no such advantages in PostgreSQL. In most situations text or character varying should be used instead.

Here they are talking about the differences between char(n), varchar(n) and text (= varchar(1G)). The official story is that there is no difference between varchar(100) and text (very large varchar).

Mcroberts

I'm talking about VARCHAR(m) vs VARCHAR(n), not about VARCHAR vs CHAR, which is what you are pointing to. – Concertize

Check again. They refer to the THREE being identical. I.e. performance of varchar(n) is same as text, which is varchar(LOTS). The only difference between the THREE types is the padding and the length constraint. Neither apply between varchar(100) and varchar(500), so they are saying they are the same. – Mcroberts

Yes, they do mention all the three, but they only insist on two. I actually asked this question on SO exactly because I was not happy with that particular paragraph in the doc and I was looking for a more insightful explanation. And no, TEXT is not the same as VARCHAR(LOTS). TEXT is stored outside the row. At least AFAIK. – Concertize

As the referenced doc says, and as james2vegas re-iterates, any long field may be stored in a background table. – Mcroberts

OK, seems two other people agree with this, so I guess it is right after all. Sorry, but the documentation is indeed not very clear, that's why I didn't want to count the documentation as an answer in the beginning. – Concertize

Answer 2

There is no difference between varchar(m) and varchar(n)..

http://archives.postgresql.org/pgsql-admin/2008-07/msg00073.php

There is a difference between varchar(n) and text though, varchar(n) has a built in constraint which must be checked and is actually a little slower.

http://archives.postgresql.org/pgsql-general/2009-04/msg00945.php

Dexamethasone

Answer 3

TEXT /is/ the same as VARCHAR without an explicit length, the text

"The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB."

refers to both VARCHAR and TEXT (since VARCHAR(n) is just a limited version of TEXT). Limiting your VARCHARS artificially has no real storage or performance benefits (the overhead is based on the actual length of the string, not the length of the underlying varchar), except possibly for comparisons against wildcards and regexes (but at the level where that starts to matter, you should probably be looking at something like PostgreSQL's full-text indexing support).

Peloquin