Thread: Speeding up schema changes
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. Is there a better way to do this? S.
"Stefan Arentz" <stefan.arentz@gmail.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
On Sep 3, 2007, at 7:26 AM, Gregory Stark wrote: > 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). Hrm, do we actually pad before storing? ISTM we should really do that the other way around... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
"Decibel!" <decibel@decibel.org> writes: > On Sep 3, 2007, at 7:26 AM, Gregory Stark wrote: >> 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). > > Hrm, do we actually pad before storing? ISTM we should really do that the > other way around... Yes we do. And it isn't really fixable either. The problem is the familiar old problem that in Postgres the typmod is not really part of the type and not always available when we need it to interpret the datum. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com