Re: CREATE INDEX CONCURRENTLY?

On 10/31/2014 10:28 AM, 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

That page refers to using the indexes, not creating them.

>
> However, I do see this sentence 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?
>
> When we use "concurrently," it seems to hang. I am looking into it.
>
>

Creating indexes always did lock tables. See for example
http://www.postgresql.org/docs/7.4/static/explicit-locking.html#LOCKING-TABLES
there CREATE INDEX is documented to take a SHARE lock on the table.

CONCURRENTLY was an additional feature to allow you to get around this,
at the possible cost of some extra processing.

So we haven't made things harder, we've made them easier, and your
understanding of old releases is incorrect.

cheers

andrew