Implementing "seen by" functionality with Postgres

Summary

Tracking unique post views within Postgres presents trade-offs between counter accuracy, row bloat, and concurrent write performance. To evaluate options under an architecture constraint preventing external dependencies, a benchmark suite generated synthetic users, skewed post distributions, and replayed view actions. The benchmark tested four implementations: a naive counter column, an hstore key-value approach, an association table, and HyperLogLog (HLL). Benchmark results demonstrated that simple-hstore achieved the lowest average latency among deduplicating approaches at 2.15 milliseconds, closely followed by HLL at 2.16 milliseconds. Despite hstore's raw performance in prototypes, HLL is recommended for production because it circumvents row bloat as view counts grow while avoiding expensive row counts.

Context

Tracking content views across posts in Postgres requires deduplicating repeated views without overloading the database with concurrent updates, bloating table rows, or introducing unapproved external infrastructure.

Approach / What changed

The author built a benchmark suite using synthetic users and posts with skewed activity distributions to test four PostgreSQL-based view tracking strategies: a basic incrementing column, an hstore key-value set, an association table, and HyperLogLog (HLL).

Takeaways

• A naive counter column yielded the lowest average latency at 2.03 ms but causes incorrect counts on repeated views and increases concurrent database update contention.
• Although hstore achieved the fastest average latency among deduplication methods at 2.15 ms, HyperLogLog (2.16 ms average) is better suited for long-term production use by preventing row bloat.
• Ecosystem extensions like pg_ivm for incremental view maintenance and AGE for graph-based edge queries represent alternative architectural options for tracking view relations within Postgres.

Related reading