PostgreSQL Lock Conflicts

Database engineering course | @hnasr | ☕

CREATE INDEX CONCURRENTLY

The CREATE INDEX CONCURRENTLY command acquires ShareUpdateExclusiveLock table lock . Following are the locks CREATE INDEX CONCURRENTLY acquires, the commands that are allowed to run concurrently with its lock ShareUpdateExclusiveLock and the commands that conflict with it. The list also includes the conflicting row locks if applicable

Locks acquired by CREATE INDEX CONCURRENTLY

  1. ShareUpdateExclusiveLock (table)

Commands concurrently allowed on the table with CREATE INDEX CONCURRENTLY

e.g. If tx1 does a CREATE INDEX CONCURRENTLY on the table then tx2 is allowed to do any of the following commands concurrently on the same table without being blocked. Some DMLs executed on the same rows may block, read more below.
  1. SELECT
  2. SELECT FOR UPDATE
  3. SELECT FOR SHARE
  4. SELECT FOR NO KEY UPDATE
  5. SELECT FOR KEY SHARE
  6. COPY TO
  7. INSERT
  8. UPDATE (NO KEYS)
  9. UPDATE (KEYS)
  10. DELETE
  11. COPY FROM
  12. MERGE
  13. ALTER TABLE ADD FOREIGN KEY (PARENT)

      Commands conflicting with CREATE INDEX CONCURRENTLY on the table

      e.g. If tx1 does a CREATE INDEX CONCURRENTLY on the table then concurrently tx2 tries to do any of the following commands on the same table, tx2 will be blocked. Conversely, if tx1 executes any of the following commands and then tx2 concurrently tries to execute CREATE INDEX CONCURRENTLY, tx2 will block.
      1. VACUUM
      2. VACUUM FULL
      3. TRUNCATE
      4. REINDEX
      5. REINDEX CONCURRENTLY
      6. REFRESH MATERIALIZED VIEW
      7. REFRESH MATERIALIZED VIEW CONCURRENTLY
      8. DROP TABLE
      9. DROP INDEX
      10. DROP INDEX CONCURRENTLY
      11. CREATE TRIGGER
      12. CREATE STATISTICS
      13. CREATE INDEX
      14. CREATE INDEX CONCURRENTLY
      15. COMMENT ON
      16. CLUSTER
      17. ANALYZE
      18. ALTER TABLE VALIDATE CONSTRAINT
      19. ALTER TABLE SET/DROP DEFAULT
      20. ALTER TABLE SET WITHOUT CLUSTER
      21. ALTER TABLE SET TOAST
      22. ALTER TABLE SET TABLESPACE
      23. ALTER TABLE SET STORAGE
      24. ALTER TABLE SET STATISTICS
      25. ALTER TABLE SET SEQUENCE
      26. ALTER TABLE SET N_DISTINCT
      27. ALTER TABLE SET FILLFACTOR
      28. ALTER TABLE SET DATA TYPE
      29. ALTER TABLE SET COMPRESSION
      30. ALTER TABLE SET AUTOVACUUUM
      31. ALTER TABLE RESET STORAGE
      32. ALTER TABLE RENAME
      33. ALTER TABLE INHERIT PARENT
      34. ALTER TABLE ENABLE/DISABLE TRIGGER
      35. ALTER TABLE ENABLE/DISABLE RULE
      36. ALTER TABLE ENABLE/DISABLE ROW LEVEL SECURITY
      37. ALTER TABLE DROP EXPRESSION
      38. ALTER TABLE DROP CONSTRAINT
      39. ALTER TABLE DROP COLUMN
      40. ALTER TABLE DETACH PARTITION (PARENT)
      41. ALTER TABLE DETACH PARTITION (TARGET/DEFAULT)
      42. ALTER TABLE DETACH PARTITION CONCURRENTLY (PARENT)
      43. ALTER TABLE DETACH PARTITION CONCURRENTLY (TARGET/DEFAULT)
      44. ALTER TABLE CLUSTER ON
      45. ALTER TABLE ATTACH PARTITION (PARENT)
      46. ALTER TABLE ATTACH PARTITION (TARGET/DEFAULT)
      47. ALTER TABLE ALTER CONSTRAINT
      48. ALTER TABLE ADD FOREIGN KEY NOT VALID (PARENT)
      49. ALTER TABLE ADD FOREIGN KEY NOT VALID (CHILD)
      50. ALTER TABLE ADD FOREIGN KEY (CHILD)
      51. ALTER TABLE ADD COLUMN
      52. ALTER TABLE ADD CONSTRAINT
      53. ALTER INDEX SET TABLESPACE
      54. ALTER INDEX SET FILLFACTOR
      55. ALTER INDEX ATTACH PARTITION
      56. ALTER INDEX (RENAME)