Обсуждение: Index table locking
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
However, I do see this sentece in the indexing page that was not in the docs prior to 8.0:http://www.postgresql.org/docs/9.3/static/locking-indexes.html
"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."
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