In this post, we explored four different ways to implement a Postgres-based solution for tracking how many people have seen a particular piece of content. We used the following approaches:
1. simple-counter: A simple counter that increments every time someone sees a post.
2. simple-hstore: Using an HStore field to store the number of times a post has been seen.
3. assoc-table: Storing each view as a row in a table, and querying for the count of rows.
4. hll: Using HyperLogLog to count distinct entries (views).
We found that while simple-counter was the fastest solution on average, it had the highest 99.999%ile latency. The HLL approach provided a good balance between performance and accuracy, with an average latency of 2.16ms and a 99.999%ile latency of 27ms.
We also discussed some other potential solutions, such as incremental view maintenance powered by pg_ivm and doing graph computations with AGE. While these solutions were not explored in depth, they could be worth considering for future implementations.