PostgreSQL CREATE INDEX CONCURRENTLY waiting column

Question

Im trying to create index on large table:

 datid  |       datname        |  pid  | usesysid |         usename         |       application_name       | client_addr  | client_hostname | client_port |          backend_start         |          xact_start           |          query_start           |         state_change          | waiting | state  | backend_xid | backend_xmin  |                                           query                                           
 -------+----------------------+-------+----------+-------------------------+---  --------------------------+--------------+-----------------+-------------+------ -------------------------+-------------------------------+---------------------- ---------+-------------------------------+---------+--------+-------------+----- ---------+---------------------------------------------------------------------- ---------------------
  25439 | messengerdb          | 30692 |    25438 | messengerdb_rw          |  pgAdmin III - Przegl??darka | 10.167.12.52 |                 |       50593 | 2016-08-11 05:27:12.101452+02  | 2016-08-11 05:28:01.535943+02 | 2016-08-11 05:28:01.535943+02 | 2016-08-11 05:28:01.535958+02 | t       | active  |             |   1173740991 | CREATE INDEX CONCURRENTLY user_time_idx                                                   +
        |                      |       |          |                         |                              |              |                 |             |                               |                                |                               |                               |         |         |             |              |    ON core.conversations (user_id ASC NULLS LAST,  last_message_timestamp ASC NULLS LAST);+

Is this query working? I'm worried about 'waiting' column === 't' does it mean that it is waiting for lock or sth?

Answer 1

Creating an index concurrently may take a long time since it does not lock the table from writes and it waits until other transactions are finished. However, it may wait forever if you have connections that stay idle in transactions (for example when a client or application keeps an open connection without rollback/commit).

Check if there are some idle connections in transactions (you should be able to see them in the processes list). You can also check PostgreSQL logs.

Section about creating index concurrently in PostgreSQL documentation can be helpful. There is also a nice article about concurrent indexes under this link.