Summary
Sometimes the load on the database increases multiple times. I found a strange query, but I can’t understand why at a random moment in time it starts to be executed very many times.
SELECT COUNT(ThreadMemberships.PostId) FROM ThreadMemberships LEFT JOIN Threads ON Threads.PostId = ThreadMemberships.PostId WHERE ThreadMemberships.Following = $1 AND ThreadMemberships.UserId = $2 AND (Threads.ThreadTeamId = $3 OR Threads.ThreadTeamId = $4) AND COALESCE(Threads.ThreadDeleteAt, $6) = $5 AND ThreadMemberships.LastViewed < Threads.LastReplyAt
Steps to reproduce
I dont know
Expected behavior
linear load on database
Observed behavior
I took a closer look at the database load spikes you mentioned, and it seems tied to that query you found (the one counting thread memberships for unread updates). It runs fine most of the time, but occasionally gets called a ton, spiking CPU and slowing things down.
Follow these simple ways to check and fix it gently. Nothing too complex, and you can test as you go:
-
Spot the pattern: Let’s add some quick logging in PostgreSQL (like enabling pg_stat_statements if it’s not on) to see exactly when and why this query surges. It might link to user actions, like logins or notifications—could be a small app trigger you can tweak.
-
Check query speed: Run a simple “EXPLAIN ANALYZE” on the query in psql to see if it’s scanning too much data. If so, adding an index (e.g., on UserId, Following, or LastViewed columns) could make it way faster without changing code.
-
Tune the database: Bump up shared_buffers or work_mem in postgres.conf slightly if memory allows—this often smooths out spikes. Or, if it’s app-side, limit how often the query runs (e.g., cache results for a few seconds).
-
Test safely: You can try these on a staging setup first to ensure no disruptions, then monitor with tools like pgBadger for improvements.
Hope it helps!
Helped for me:
CREATE INDEX idx_threads_team_delete ON Threads (
ThreadTeamId,
ThreadDeleteAt,
PostId
);
Maybe it good idea to add index “out of box“?
1 Like
