Following on the heels of this question does changing the length limit (type-modifier) of varchar()
result in a table rewrite or a lock that takes more time than changing the CHECK
constraint? From that question you can see that claim by Brandur,
If you ever want to change the length,
ALTER TABLE
requires an exclusive lock (see https://www.postgresql.org/docs/current/static/sql-altertable.html …). ChangingCHECK
is instant. When answering a question that called into question textCHECK (char_length(email) <= 255)
vsvarchar(255)
It seems this may have originated with a post by Depesz, in his post from 2010, "CHAR(X) VS. VARCHAR(X) VS. VARCHAR VS. TEXT – UPDATED 2010-03-03"
So, what happens with when you make the limit larger [with varchar]?
PostgreSQL has to rewrite the table. Which has 2 very important drawbacks:
1. Requires exclusive lock on the table for the time of operation
2. In case of non-trivial tables, will take considerable amount of time
You can see this again in a comment here (2017),
Still, I'd take a sub millisecond diff on every INSERT over a possible table lock when I'll want to convert my
VARCHAR(50)
toVARCHAR(250)
.
And again in here (2012),
As an aside: I never use varchar if I can avoid it – especially not with length modifier. It offers hardly anything that the type text couldn't do. If I need a length restriction, I use a column constraint which can be changed without rewriting the whole table.
And clearly there are others that doubt the claim.
It may be worth just addressing this single claim.
Best Answer
Essentially, this is out of date information. It hasn't been relevant since 9.2. Now, the only drawback, that I can see, is that the index gets rewritten if the length constraint gets more restrictive and it has to be rechecked.
In the release notes for 9.1, as discovered by Erwin it states
In the release notes in 9.2, as discovered by @a_horse_with_no_name found,
The docs on
ALTER
have this to sayFollowing with the test case provided by a_horse_with_no_name that I modified, let's see this in action.
So we have no slow down without an index. Then we add an index, and try it again,
I tried
VACUUM FULL ANALYZE
on the table and making the length onvarchar
again longer, and it still didn't take any more time. Not saying it's well debunked in all cases, but at least in the simple cases even when indexed this seems to be not-a-concern if you're going to make the constraint less restrictive. However, making the length constraint more restrictive seems to be doing something.Dropping the index and trying that again is substantially faster,
So it seems only if the length constraint is made more restrictive and has to be revalidated does the index get rewritten.