From: Gregory Stark <stark@enterprisedb.com>
To: Stefan Arentz <stefan.arentz@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Speeding up schema changes
Date: Mon, 03 Sep 2007 13:26:29 +0100
Message-ID: <874pic0way.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <adf34c300709030422p7a5bf9ban915c01547c3cb008@mail.gmail.com>
References: <adf34c300709030422p7a5bf9ban915c01547c3cb008@mail.gmail.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stark@enterprisedb.com, stefan.arentz@gmail.com
Subject: Re: Speeding up schema changes
In-Reply-To: <874pic0way.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox