Old dead users cause large number of inserts to event_push_actions

[erikjohnston]

matrix.org inserts a lot of rows into event_push_actions (and _staging) for users that have long since disappeared. We should do something to reduce that.

(We insert a row per user per room for each event that notifies people, so messages in large rooms cause a lot of rows to be inserted)

[erikjohnston]

A temporary solution is to delete read receipts of all users that haven't been seen for more than six months, who have more than 1000 notifications in that room. This will stop new event push actions to generated, resulting in their counts no longer being updated (till they next look into the room). This is probably fine because I believe Riot caps notification counts to 99+ or something anyway.

Query to get those rooms/users:

SELECT user_id, room_id FROM event_push_summary
INNER JOIN user_ips USING (user_id)
WHERE notif_count > 1000
GROUP BY user_id, room_id
HAVING to_timestamp(max(last_seen)/1000) < '2019-01-01';

[uriesk]

i put this in a cron job

delete from event_push_actions u where room_id = '${room}' and not exists ( select from user_ips where user_id = u.user_id and to_timestamp(last_seen/1000) > now() - interval '10 weeks' )

to clean the event_push_actions

How would i delete read receipts?

My event_push_actions fills up very very fast, because i have lots of puppets from a bridge who never read notifications.