Does changing the length limit (type-modifier) of varchar() result in a table or index rewrite?

Question

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 …). Changing CHECK is instant. When answering a question that called into question text CHECK (char_length(email) <= 255) vs varchar(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) to VARCHAR(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.

Answer 1

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

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.

In the release notes in 9.2, as discovered by @a_horse_with_no_name found,

Reduce need to rebuild tables and indexes for certain ALTER TABLE ... ALTER COLUMN TYPE operations (Noah Misch)

Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite. Similarly, increasing the allowable precision of a numeric column, or changing a column from constrained numeric to unconstrained numeric, no longer requires a table rewrite. Table rewrites are also avoided in similar cases involving the interval, timestamp, and timestamptz types.

The docs on ALTER have this to say

Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Adding or removing a system oid column also requires rewriting the entire table. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.

Following with the test case provided by a_horse_with_no_name that I modified, let's see this in action.

\timing 1

CREATE TABLE alter_test (id int primary key, some_data varchar(50));
INSERT INTO alter_test
  SELECT i, md5(i::text)
  FROM generate_series(1,1e7) AS gs(i);

ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(55);
Time: 5.671 ms

So we have no slow down without an index. Then we add an index, and try it again,

CREATE INDEX ON alter_test (some_data);
ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(55);
Time: 6.423 ms

I tried VACUUM FULL ANALYZE on the table and making the length on varchar 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.

ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(50);
ALTER TABLE
Time: 59690.885 ms

Dropping the index and trying that again is substantially faster,

DROP INDEX alter_test_some_data_idx ;
DROP INDEX
Time: 85.978 ms
test=# ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(49);
ALTER TABLE
Time: 9297.271 ms

So it seems only if the length constraint is made more restrictive and has to be revalidated does the index get rewritten.