6

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?

3
  • 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. May 10 '12 at 18:25
12

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.

8
  • 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. 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 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. May 13 '12 at 8:29
7

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.

1
  • I agree Ervin on the use of text data type for string. In some cases varchar(100) may still be useful when you know your input values should not be longer than 100. It serves as a constraint to some extent.
    – Kemin Zhou
    Feb 14 '19 at 18:28
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.

0
1

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

https://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.

0
0

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?

5
  • 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. 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! 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. May 13 '12 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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