Altering table schema takes too much time

Question

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?

Answer 1

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.

Answer 2

You could always dump the database, edit the dumped schema, and import it back. A lot faster than altering the table. If you can't do it, here are some things you can do to make it faster.

1. Turn off access to the database for other users (e.g. web server).
2. Turn off fsync for the duration of the operation (may require restart of the database server).
3. Cluster the database before altering it.
4. Vacuum the database before altering it.

Note that clustering and vacuuming can take a long time. Vacuuming does not read-lock the database, but it may not affect the speed of altering the table.

By the way, you should define the attribute as plain VARCHAR, without size limit. Postgres handles the case just as effectively as VARCHAR(4000), but you don't have to resize it later.

Answer 3

There is really hardly any good reason to use the data type character varying (n) (let alone the even worse char(n) ). There is nothing it can do better than the data type text. varchar was invented at a time when computer systems still had trouble handling strings of variable length. Nowadays it's only around for compatibility with the standard and old applications. Use text instead - or varchar without length modifier (which is implemented identically).

If you need to enforce a maximum length, add a check constraint like this:

ALTER TABLE tbl ADD CONSTRAINT tbl_col_len CHECK (length(col) < 4000);

You can drop and recreate this constraint with a different maximum length without re-writing the table.

Start by converting to text after you have read the good news in @kgrittn's answer, that this is available for free in PostgreSQL 9.1.

Answer 4

In the case of increasing the limit , some people would certainly prefer using the unsupported hack mentioned here:

http://stackoverflow.com/questions/7729287/postgresql-change-the-size-of-a-varchar-column

Advantage: immediate, no rewriting of the table.

Drawback: not supported, so you use it at your own peril.

Answer 5

Generally speaking, no. The reason that it takes so long for millions of records is that it is an intense write operation on the disk. Each row has to take up more space, and with the way that databases actually write data to disk that means moving a lot of other data around. If you have indexes this can be even more intense.

As suggested in comments, if you do not have triggers or constraints on the table, you can copy to a new, properly configured table. Or you could add an additional column, and copy to that. However, for a table with millions of rows, you're talking about moving at a minimum 2 billion bytes, which is not a trivial amount of movement. It's going to take time. How long have you let it run?