# Postgres has 100% CPU for large databases, Discourse 2.7.7
**URL:** https://meta.discourse.org/t/postgres-has-100-cpu-for-large-databases-discourse-2-7-7/202106
**Category:** Self-hosting
**Tags:** server-resources
**Created:** [August 30, 2021, 11:36am UTC](https://meta.discourse.org/t/postgres-has-100-cpu-for-large-databases-discourse-2-7-7/202106 "2021-08-30T11:36:37Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@dmorlock](https://meta.discourse.org/u/dmorlock)
#### Post date: [August 30, 2021, 11:36am UTC](https://meta.discourse.org/t/postgres-has-100-cpu-for-large-databases-discourse-2-7-7/202106/1 "2021-08-30T11:36:37Z")
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:
```plaintext
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?
```plaintext
| | 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.
---
### Author:  [@dmorlock](https://meta.discourse.org/u/dmorlock)
#### Post date: [August 30, 2021, 11:47am UTC](https://meta.discourse.org/t/postgres-has-100-cpu-for-large-databases-discourse-2-7-7/202106/2 "2021-08-30T11:47:30Z")
This query seems to come from `Jobs::BadgeGrant`.
---
### Author:  [@dmorlock](https://meta.discourse.org/u/dmorlock)
#### Post date: [August 30, 2021, 12:13pm UTC](https://meta.discourse.org/t/postgres-has-100-cpu-for-large-databases-discourse-2-7-7/202106/3 "2021-08-30T12:13:47Z")
We’ve found the issue was caused by a custom SQL query in a user badge. So this is not an upstream issue. Thanks.
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [August 30, 2021, 1:11pm UTC](https://meta.discourse.org/t/postgres-has-100-cpu-for-large-databases-discourse-2-7-7/202106/5 "2021-08-30T13:11:28Z")
I suspect that you’ll also want to make sure that you vacuum and reindex as described in [PostgreSQL 13 update](https://meta.discourse.org/t/postgresql-13-update/172563).
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [September 29, 2021, 1:12pm UTC](https://meta.discourse.org/t/postgres-has-100-cpu-for-large-databases-discourse-2-7-7/202106/6 "2021-09-29T13:12:10Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.