Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com May 10 '12 at 21:27

This question came from our site for professional and enthusiast programmers.

3  
Create a new table with the schema that you want, insert into the new table, drop the previous table, and rename the new table to the old name. This does not work so well if you have foreign key constraints, triggers, and other dependencies on the table. –  Gordon Linoff May 10 '12 at 18:00
    
I am crawling websites, and there are two columns -- url and text , the database size is 400 GB.The text size is huge for some urls.is this better approach than alter table? will it takes lesser time? –  Srinivas Reddy Thatiparthy May 10 '12 at 18:21
3  
As an aside, you should skip varchar(4000) and go straight to text, varchar is a bit of an anachronism in PostgreSQL. –  mu is too short May 10 '12 at 18:25
add comment

5 Answers 5

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.

share|improve this answer
    
Odd. I had a longer version of this, but I lost connection every time I tried to enter it. I took it down to a tiny entry and expanded until I hit the error; so if you saw the entry growing, that is why. I think I got the most critical bits of the answer in here. –  kgrittn May 10 '12 at 19:46
    
Either way, text is the way to go, and your quote is the key piece of good news for the OP. –  Erwin Brandstetter May 10 '12 at 21:23
    
@kgrittn if you can figure out a way of reproducing the problem please let us know and we'll raise it as a bug on mSO –  Jack Douglas May 12 '12 at 14:24
    
@JackDouglas: I have had this problem before, and have generally just abandoned the attempt to answer. I just pasted something to this answer that I know failed yesterday when I tried to add it, but it works now. I have no theories. :-( –  kgrittn May 12 '12 at 14:47
    
@Kgrittn: What browser (exact version) and OS you are using, may also be relevant to the issue. If you write a question on meta, include that, too. –  ypercube May 13 '12 at 8:29
show 2 more comments

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
+1 for "use it at your own peril" –  Jack Douglas May 12 '12 at 14:25
add comment

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?

share|improve this answer
    
30 minutes...this table size is 400 GB. please see the comment i posted in response to @Gordon Linoff. –  Srinivas Reddy Thatiparthy May 10 '12 at 18:23
    
2 billion bytes is 2GB. That said, 30 minutes is probably on the too-short side for this sort of operation. I'd let it run at least an hour or so, or overnight if that was convenient. Also, be sure to take jmz's advice re:not putting in a size limit. –  Nathaniel Ford May 10 '12 at 18:32
    
-1 for "Each row has to take up more space" - this is a metadata change. The issue is the (unfortunate) table rewrite not that there is any more data than before. –  Jack Douglas May 12 '12 at 15:28
2  
@JackDouglas Thanks for the clarification! I'm glad this post moved to dba; much more useful information than on so! –  Nathaniel Ford May 13 '12 at 1:26
1  
Well that is a most gracious response to a down vote! I am glad you have signed up to the site and do hope you stick around. If you want to change your answer I'll reverse my vote - or you can leave it as a signpost for others. –  Jack Douglas May 13 '12 at 7:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.