Ask HN: How to transform event/clickstream data into personalized user metadata

I would like to capture every time a user views a brand, seller, or listing on our app. Then I would like to have a method that returns a list of their brands, sellers, or listings based on the ones they visit the most.

I see a lot of software like mixpanel allows you to capture click stream data, but that seems more like "business analyst wants to track x stat against y stat in the last 30 days."

Requirements:

- relatively low cost (we're small-medium scale)

- can't use postgres extensions like pipelinedb (on gcloud managed sql)

Current idea:

Create an event record in postgres (e.g. user_id, "viewed", entity_id, entity_type)

then write an sql query to fetch their favorite brands, sellers, etc.. based on the aggregation/sum of their events in real time.

Concerns:

a) Should raw event data be stored in postgresql? Won't it just cause an unnecessary amount of writes, and requires involvement to maintain performance. We would probably have 10M events every few months.

b) Moving to something like big query, red shift, or whatever "analytics" db seems like the wrong tool / overkill. We would like to rapidly query the data for user facing apps, gather personalized stats for users on the fly (e.g. "my favorite brands"), these tools seem like they're more for generating reports, big data analysis, etc...

c) this leads me to believe that raw event data should be stored somewhere like s3, and aggregated offline, and the results be stored in postgresql for rapid querying

Am I over complicating this?