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.
+3
A:
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).
the.jxc
2009-07-01 03:06:51
I'm talking about VARCHAR(m) vs VARCHAR(n), not about VARCHAR vs CHAR, which is what you are pointing to.
ionut bizau
2009-07-01 03:08:44
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.
the.jxc
2009-07-01 03:10:13
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.
ionut bizau
2009-07-01 03:17:30
As the referenced doc says, and as james2vegas re-iterates, any long field may be stored in a background table.
the.jxc
2009-07-03 01:54:11
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.
ionut bizau
2009-07-03 10:17:29
+3
A:
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 to real storage (the overhead is based on the actual length of the string, not the length of the underlying varchar) or performance benefit, 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).
MkV
2009-07-01 10:12:34
+3
A:
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
rfusca
2009-07-02 05:17:10