PostgreSQL: increasing a column's length in a very large table

Question

Aurora PostgreSQL, version 10.4.

I have a table with several million rows. One of the columns is defined as character varying(255). Once upon a time, 255 was plenty of room, but now it's not, so I have to make more.

I found this in the PG 9.1 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.

This suggests that changing to a longer varchar is not practical (since rewriting a table of that size would lock it for an ungodly amount of time), but changing to text would work. Is this correct?

Any other things I should know about when making such a change? I want to avoid data loss, obviously, and I can't afford to make this table inaccessible for more than a short period.

Answer 1

You should have read all release notes.

Because just one version later

Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite.

You can test that easily for yourself:

postgres=# select version();
                          version
------------------------------------------------------------
 PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit
(1 row)

postgres=# \timing on
Timing is on.
postgres=# create table alter_test (id serial, some_col varchar(255));
CREATE TABLE
Time: 22.331 ms
postgres=# insert into alter_test (some_col) select md5(random()::text) from generate_series(1,10e6);
INSERT 0 10000000
Time: 40894.275 ms (00:40.894)
postgres=# alter table alter_test alter column some_col type varchar(500);
ALTER TABLE
Time: 5.297 ms
postgres=#