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).

[image] Venkat: Our version is Discourse 2.7.0 I won’t be able to help you with the specific issue you’re raising, but is there a reason you’re on such an old Discourse version? The more you wait, the more likely you may face issues when upgrading. Ideally, you should always be on the last version. Additionally, updates occasionally include security fixes.

I’m managing a site on ecs with rds and haven’t noticed any problems, but it’s an up to date version. It’s hard to guess just what the issue might be without more details of your setup. In general when someone has an issue with a very old version the first answer is to upgrade.

Discourse on RDS periodically results into many Lock:relation waits for create GIN index sessions

Product Support Self-hosting

venkytn (Venkat) July 7, 2023, 4:51pm 4

Fair enough. Thank you for your feedback.

Topic		Replies	Views
Upgrade failed: ERROR: relation "idx_regular_post_search_data" already exists Self-hosting	2	762	August 21, 2020
PostgreSQL 13 update Announcements	2	11454	September 13, 2023
Corrupt indexes in PG12, how do I fix? Self-hosting	43	12994	January 26, 2023
Restoring from backup failing: failure creating postgres index Self-hosting	7	1148	May 28, 2020
Long runtime moving posts / timeout errors Product Support	3	176	February 9, 2025

Discourse on RDS periodically results into many Lock:relation waits for create GIN index sessions

Related topics