Re: Index table locking

From: Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>
To: Mark Woodward <mark(dot)woodward(at)actifio(dot)com>, <pgadmin-hackers(at)postgresql(dot)org>
Subject: Re: Index table locking
Date: 2014-10-31 14:21:48
Message-ID: 54539AFC.9060009@vmware.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-hackers

On 10/31/2014 04:14 PM, Mark Woodward wrote:
> I have not kept up with PostgreSQL changes and have just been using it. A
> co-worker recently told me that you need to word "CONCURRENTLY" in "CREATE
> INDEX" to avoid table locking. I called BS on this because to my knowledge
> PostgreSQL does not lock tables. I referenced this page in the
> documentation:
>
> http://www.postgresql.org/docs/9.3/static/locking-indexes.html

Wrong list? This has nothing to do with pgadmin...

> However, I do see this sentece in the indexing page that was not in the
> docs prior to 8.0:
>
> "Creating an index can interfere with regular operation of a database.
> Normally PostgreSQL locks the table to be indexed against writes and
> performs the entire index build with a single scan of the table."
>
> Is this true? When/why the change?

Plain CREATE INDEX has always locked the table. You can query it while
the CREATE INDEX is running, but updates will block.

CREATE INDEX CONCURRENTLY was a new feature in 8.2 that avoids taking
that lock, allowing concurrent updates. It's slower than the
non-concurrent version, because it has to scan the table twice, and
there are a few other caveats. See
http://www.postgresql.org/docs/9.4/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY.

- Heikki

In response to

Browse pgadmin-hackers by date

  From Date Subject
Next Message Ashesh Vashi 2014-11-03 04:57:23 pgAdmin III commit: Allow to run the embed-xrc from the separate build
Previous Message Mark Woodward 2014-10-31 14:14:12 Index table locking