We see periodically that a create GIN index on post_search_data results into plenty of Lock:relations on performance insight. Killing the session creating this index releases all blocked sessions. What’s interesting is that the index uses, “concurrently” which is not supposed to be blocking operation. Also interesting why it prefixes it with temp.
CREATE INDEX CONCURRENTLY temp_idx_recent_regular_post_search_data ON post_search_data USING GIN(search_data) WHERE NOT private_message AND post_id >= 12431619
I have tried also to reduce idle_in_transaction_session_timeout to 10 mins (initially was set to 1d). but it didn’t make any difference. Anyone else had such an issue? If yes, how did you handle this?
Our version is Discourse 2.7.0 and Postgres version is 13.8. App and DB are not on docker but individually hosted (ec2, rds).