Altering table schema takes too much time

I am using PostgreSQL database.

I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much time.

Is there any quicker way to do it?

Yes, there is a quicker way to do it if you are using version 9.1. The PostgreSQL version 9.1 release notes include this:


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.

Further improvements in this area are coming in version 9.2, which hits beta release next week. But for now it appears the faster way is to alter the type to text, in which case it is a quick update to system tables with no change to the heap.

StackExchange Database Administrators Q#17706, answer score: 14