Re: Speeding up schema changes

"Stefan Arentz" <stefan(dot)arentz(at)gmail(dot)com> writes:

> Is there a way to speed up simple schema changes like ...
>
> ALTER TABLE foo ADD COLUMN bar CHAR(64);
>
> ... where foo already contains millions of records?
>
> On a live database changes like this can take hours. Even when the
> database is idle.

Are you sure that's exactly like the change you're making? I think that should
be instantaneous because it will add a new column which is null everywhere. It
doesn't have to actually modify the table contents at all to do that, just the
schema. It could be slow if the table is extremely busy and it has trouble
getting the lock but I don't think it's easy to create that situation, at
least not such that it will last more than a few seconds.

If, on the other hand, you added a column with a default value then it would
be an entirely different scenario. In that case it has to rewrite the whole
table with the new values in every record. It also has to reindex every index
for the new table contents and so on.

So if you didn't have to initialise the contents you would avoid the wait.

Also, incidentally do you have a good reason to use CHAR instead of varchar or
text? char(64) will take 64 bytes (actually 68 bytes in 8.2) even if you don't
store anything more in it. text or varchar will take only as many bytes as the
data you're storing (plus 4 bytes).

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com