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?

Really dont know how to do it better :( sorry for the inconvenience — Rafał Kot
– Rafał Kot, Commented Aug 11, 2016 at 7:54

Maciej Małecki · Accepted Answer · 2021-05-12 11:10:38Z

14

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.

edited May 12, 2021 at 11:10

answered Aug 11, 2016 at 7:21

Maciej Małecki

2,74521 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rafał Kot Over a year ago

Thanks a lot. Is there a way to find out what is the current state (how big it is) of this index creating? I do not see any 'idle connections in transaction' ;/

Maciej Małecki Over a year ago

@DreamCatch afaik there is no possibility to check the current state of the whole process.

Collectives™ on Stack Overflow

PostgreSQL CREATE INDEX CONCURRENTLY waiting column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related