PostgreSQL 12 High Availability Cookbook - Third Edition

Dangers of OLTP use

Concurrent indexes are not a panacea; they still follow rules for lock acquisition. Specifically, PostgreSQL cannot acquire a lock to attach the index as long as any earlier transactions are still running. While it waits for the lock, any new transactions that need to modify the table contents will also wait. This feedback loop of waits can quickly consume all available client connections on an already busy OLTP system.

It's best to avoid this situation by following the normal index-creation protocol on OLTP systems: that is, only create indexes when the volume is low. We can also massively reduce the risk by avoiding long-running transactions that could potentially block the final lock request. OLTP systems should only ...