Hi, we are running Docker based Discourse 2.7.7 with a very large user base. After upgrading from 2.4.2 to 2.7.7, we are experiencing that some Postgres process are stuck at 100% CPU. Further investigation results that such queries seem to cause the problem: discourse_prod=# select pid, datid, query from pg_stat_activity WHERE pid = '244906'; pid | datid | query --------+---------+------------------------------------------------------- 244906 | 2068583 | DELETE FROM user_badges + | | WHERE id IN ( + | | SELECT ub.id + | | FROM user_badges ub + | | LEFT JOIN ( + | | SELECT id user_id, current_timestamp granted_at+ | | FROM users + | | WHERE id IN ( + | | SELECT p1.user_id + | | FROM post_custom_fields pc + | | JOIN badge_posts p1 ON p1.id = pc.post_id + | | JOIN topics t1 ON p1.topic_id = t1.id + | | WHERE p1.user_id <> t1.user_id AND + | | name = 'is_accepted_answer' AND + | | p1.user_id IN ( + | | SELECT user_id + | | FROM posts + | | WHERE TRUE OR p1.id IN (-1) + | | ) + | | GROUP BY p1.user_id + | | HAVING COUNT(*) > 9 + | | ) + | | ) q ON q.user_id = ub.user_id + | | + | | WHERE ub.badge_id = 103 AND q.user_id IS NULL + | | ) + | | (1 row) We found this query which, I assume, will cause a huge overhead, doesn’t it? | | SELECT user_id + | | FROM posts + | | WHERE TRUE OR p1.id IN (-1) + Any suggestions on this? Let me know if you need further info. Thanks, Daniel.

We’ve found the issue was caused by a custom SQL query in a user badge. So this is not an upstream issue. Thanks.

I suspect that you’ll also want to make sure that you vacuum and reindex as described in PostgreSQL 13 update.

Postgres עם 100% CPU עבור מסדי נתונים גדולים, Discourse 2.7.7

dmorlock (Daniel Morlock) 30 באוגוסט,‏ 2021,‏ 11:47am 2

This query seems to come from Jobs::BadgeGrant.

נושא		תגובות	צפיות
Postgres using 100% of CPU Self-hosting server-resources	16	20270	25 באוגוסט,‏ 2018
Unusually high CPU usage Self-hosting	31	1512	18 בפברואר,‏ 2026
Some queries that don't finish Product Support	16	1142	5 באפריל,‏ 2019
Long-Running Sidekiq Jobs Feature	21	2204	24 בדצמבר,‏ 2020
Primary Postgres database process (postmaster) eating all CPU Self-hosting server-resources	13	4542	28 במאי,‏ 2020