PostgreSQL creates an index and avoids write data locking (concurrent indexing)

About concurrent Indexing: http://58.58.27.50:8079/doc/html/9.3.1_zh/sql-createindex.html

Write this blog from a handsome guy. The problem of table lock occurred in indexing. Let's introduce the index syntax for PostgreSQL:
version:9.1

CREATE [UNIQUE] INDEX [concurrently] [name] on table [USING method] ({column | (expression)} [COLLATE Collation] [Opclass] [ASC | DESC] [NULLS {First |    Last}] [, ...]    [With (Storage_parameter = value [, ...])]    [Tablespace tablespace] [WHERE predicate]

here does not explain the syntax of the many parameters used (sorting, use, filling factor, etc.), mainly about the use of concurrently scene.
  
Normally, PostgreSQL blocks DML (insert,update,delete) operations when a normal Btree index is established, until the index is complete and the read operation is not blocked. When there is only one user action this of course no problem, but in the production environment, concurrency is high, especially the large table index can not be so operation, or users to jump up dozens, point a button a day has not reacted.

--Using 
PostgreSQL provides a parameter that can be indexed online to avoid locking the table when writing data , which is called concurrently. It is easy to use the CREATE index concurrently instead of CREATE index.

--Side effects 
of course, the use of this parameter is a side effect, do not use this parameter to index when the DB scan only once the table, when using this parameter, the DB sweep two times the table, while waiting for all potential read to the index of the end of the transaction, a bit like the incremental index, so that the system's CPU and IO, Memory and so on will be affected, so comprehensive consideration, still let the user choose, rather than the default.

--Failure
when using the concurrently parameter to build an index, it is possible to encounter a failure, such as a unique index index discovery data duplication, or the user found that the index was built when the wrong segment, the index operation is canceled. At this point there will be an index on the table, because the index command with this parameter is issued, first in the system's log table first inserted an index record, and because the index eventually failed to build, so it will be labeled a invalid state, as follows:

postgres=# \d T_kenyon Table "Public.t_kenyon" Column | Type | Modifiers--------+---------+-----------Col | Integer | Indexes: "idx" Btree (COL) INVALID

--Reconstruction 
There are two ways to encounter the failed index rebuild, one is drop index index_name, and then the CREATE index concurrently is executed. Another is the Execute reindex index_name command, but the latter does not support the concurrently parameter.

--Summary 
It is best to take this parameter when performing the CREATE INDEX command on production, because it is relatively worthwhile to consume a little more system resources and time in exchange for a user's uninterrupted access update, but the urgent task or the oversized table should be considered. If it is an index rebuild, you can create an identical index on the original basis without the same name, and then cancel the old index.  

PostgreSQL creates an index and avoids write data locking (concurrent indexing)