Обсуждение: Creating an index on a live database

I have a table with a few million rows which has inserts performed on it
roughly 50 or so times a minute.  It contains a heavily-queried column
that I would like to add an index to, but I am concerned about a
deadlock occurring.  Should I wait until downtime to add the index, or
is the Postgres add index command "nice" enough to avoid deadlocks?

On Tuesday 06 February 2007 09:38, John McCawley <nospam@hardgeus.com>
wrote:
> I have a table with a few million rows which has inserts performed on it
> roughly 50 or so times a minute.  It contains a heavily-queried column
> that I would like to add an index to, but I am concerned about a
> deadlock occurring.  Should I wait until downtime to add the index, or
> is the Postgres add index command "nice" enough to avoid deadlocks?

It won't deadlock, but it will lock out writes for the duration of the index
creation (the transactions it locks out will just pause until the lock is
released).

If you're running 8.2, though, I believe you can CREATE INDEX CONCURRENTLY,
which will create the index without requiring the write lock on the table.

--
Opportunity is missed by most people because it is dressed in overalls and
looks like work. - Thomas Edison