Q:

How to design optimal and scalable unique page_view table in postgresql?

I am having trouble coming up with the most optimal solution in designining a scalable page_view log sql model for postgresql
I designed a model with
create table views(
uuid,
chapterid,
createdAt
)

with uuid and chapterid being indexed as primarykey
create table daily_views(
day,
chapterid,
view_count
)

pre-aggregated table with day,chapterid being indexed as primarykey
create table monthly_views(
monthyear,
chaperid,
view_count)

pre-aggregated table with monthyear,chapterid being indexed as primarykey
and similar table with year,
but the views table will bloat with billions of rows, if the site's traffic reached such number, but i cannot delete it due to it tracking unique views for each chapter(for book) page.
should I continue with this schema or use timeseries database(i cannot use timescaledb for postgresql because it is not supported in rds(aws service))for this only and host my own ec2 instance of said database?
What I need from this data is to be able to calculate a trend, and to be able to count the total views for each book, related to the chapter....

A:

Ideally, that's the perfect use case of the PipelineDB extension, as it allows real-time statistics with little overhead (but discards actual input data). For keeping the actual (raw) data, you should really consider the Timescale extension to have reasonable write latency over time and growing data set. Citus also has special support for time-series data.
You can also combine those two, although currently not supported first-class:

https://github.com/timescale/timescaledb/issues/14
https://github.com/pipelinedb/pipelinedb/issues/1876

If you really cannot make use of any of those, you have basically 2 options to decide where you'd want the additional performance penalty to apply:

performance penalty on writes: create a trigger that inserts/updates into a separate statistics table
performance penalty on reads: create a view or directly do an aggregation query

The option for materialized views also applies, but comes with an additional near-time requirement of todays' analytics applications.
Last but not least, don't forget that growing data sets by itself will become a serious issue on its own. So if you need something scalable in the long run, you absolutely need to account for that fact and even if starting without dynamic partitioning or other techniques, you should have a plan-B if things start to get slow.
Regarding the already foreseen issue of the data set size and always growing nature, you should also think about how precise the result have to be (not SHOULD, but MUST as in the business requirement). Please remember, that all of the bigger analytics providers will show you approximations, but very close to the real number.
Please have a read on different counting options for this (e.g. the HLL extension from Citus is supported at RDS, at least).

