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

Question

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?

Answer 1

let's say we have 100 users querying the database per second on average, and each one fetches a result set consisting of 200 thumbnails. Then the total writes per second needs to scale to 100 * 200 / sec

Yes... it could get out of hand.

Since your thumbnails are static files, the fastest option is to serve them with a standard static web server and not with dynamic web pages (like php scripts...) unless you absolutely need scripting for permissions.

These web servers output log files, which conveniently implements the task of gathering all views in a single place for analysis, so that's one less thing to do.

If you use dynamic scripts to serve your thumbnails, you can use a similar format for data gathering, appending into a log file or an insert-only table in a database, whichever is more convenient and faster.

The source of these logs could also be the script or API that generates the web page, search results, etc, to bypass browser caching.

I don't like the Redis solution because it relies on a central server which will have to process all requests, and redis uses TCP which means most of the overhead is going to be in TCP not actually incrementing counts, which means the redis stuff will use pretty much the same amount of resources as the web servers would use to serve the small static files. Also it is not clear how you will dump updates from Redis to postgres.

So, we either have log files which contain streams of thumbnail views, in an easy to parse format, for example: date and url... or tables containing the same thing. I will use the name "log" for both, no matter how they are stored.

This is inherently scalable, as all the files/tables are independent: you can have one per webserver, for example, all with pretty much zero effort, especially with log files. This runs on the same box as the webserver, so there is no network and no latency.

Now we have to parse, coalesce, and process these logs to accumulate the view counts. This is the same thing as any other web analytics. It can be done by a deferred process that fetches logs and does not need to run in real time. If this process is overwhelmed by a temporary spike in views, it doesn't matter, it will catch up later, as long as each webserver keeps accumulating logs.

Now say we have a minute's worth of server logs with all the thumbnails that were viewed. What's the statistical distribution of this?

Assuming the usual, which is: not all users are online at the same time, and some popular users make most of the views, I'd guess we can expect something like a power law. Thus, in this time interval, we'd get a small number of most popular thumbnails viewed many times, and a large number of unpopular thumbnails viewed few times.

Thus, if we look at the real time stream of thumbnail views, there is probably a way to substantially reduce the number of operations to do by pre-processing it in chunks and grouping view with the same id before inserting in a database.

Because this is a lazy answer, let's get existing software to do all the work, and fire up clickhouse:

CREATE TABLE IF NOT EXISTS views(
    dt          Date            NOT NULL CODEC(Delta,ZSTD(9)),
    user_id     Int64           NOT NULL CODEC(Delta,ZSTD(9)),
    view_count  Int64           NOT NULL CODEC(Delta,ZSTD(9)),
)   ENGINE = SummingMergeTree
    PARTITION BY (toYYYYMM(dt))
    ORDER BY (dt,user_id)
    PRIMARY KEY (dt,user_id);

This is a SummingMergeTree table. This means every time there is an INSERT, if there is no row with the same (dt,user_id) primary key then the row will be simply inserted. But if there is already a row then the view_count of the existing row will be incremented by the view_count in the INSERT.

I added a date column and partitioning, for convenience. So this table contains the number of views per thumbnail per day, which should massively reduce the data volume. It can later be aggregated into views per week, per month, or all time.

The way this is implemented is simple to describe. First, INSERTs should be batched, with few INSERTs per second, maybe once per second. So each INSERT has a bunch of rows (say, 10-100 million). Clickhouse stuffs that into buffers and sorts them according to the table key, then it eliminates duplicates and coalesces them, summing the view_count column. Once in a while, this is lazily written into the actual table.

You can expect about 10 million rows/second per CPU core, which is about 100x faster than anything else off the shelf. It needs a lot of RAM.

---

If you're using an app, another method would be to have the app maintain aggregated view counts locally, and periodically send updates to a server. Of course, this is slightly less secure, but potentially more accurate as it bypasses local caching.