Sample Header Ad - 728x90

Strategy for Postgres updates with very high write load, but low consistency requirements

0 votes
1 answer
437 views
I need to collect statistics on profile thumbnail views for a social media app, backed by Postgres. Every time the thumbnail of a user appears anywhere on a page (in a list of profiles, or next to comments on a post, etc.), the view count for the userId corresponding to the thumbnail should be incremented. Obviously it would be horrendously taxing on the server to issue a count update for every userId that is ever returned from a query. So I am considering a couple of different ways of handling this. Feedback would be greatly appreciated: 1. I could update the counts in an in-memory cache, e.g. Redis, and use this as a write-through cache, which slowly in the background dumps count updates to Postgres, in batches of some fixed size. I'm pretty sure this system could never keep up with the update rate, but it would alleviate a lot of load from Postgres. Maybe the largest count changes could be prioritized to be written through to Postgres first. One downside is that if the server had to be restarted, unwritten counts would be lost (which is not great but not terrible in this case, since it's only a view statistic.) 2. I could update the view count only some small percentage of the time, e.g. for 1% of views, in other words, sample the views. This would give me a reasonable estimate over time of the actual view rate for large view counts, but would underrepresent small view counts. Any other ideas?
Asked by Luke Hutchison (141 rep)
Feb 8, 2024, 07:19 PM
Last activity: Feb 17, 2024, 10:45 AM