Just getting started with QuestDB and I don’t see any appealing ways to blend relational with time series. For this application I’m working on, the most interesting data is time series data (tracking product sales daily, maybe more frequently in future, and then trying to spot trends and predictive analytics, etc). However, the basic product details (product name, description, image url) don’t change often. (star schema)
I don’t really want to put this in a separate database, and I don’t really want to store it in a flat table because most of it would be repetitive. I can put it in a “legacy” table without a timestamp, but then I can’t enable WAL. I am also thinking that some of the product names might change over time, so maybe it’s okay to store it with a timestamp and just select or join by LATEST whenever I need the product details.
The other problem is a classic one, dealing with INSERT or UPDATE of this basic product detail information. When data is collected, I will have the entire record (product id, name, price, number sold) but most of it (the product details) will not change frequently. The goal would be to efficiently UPSERT the basic product details (insert or update only if something changed).
I was contemplating if maybe this just isn’t the right DB for this project, but the thing is, over time, when I scale it up, the sales data will add up to quite a bit of time series data (something like 100k-200k products sampled several times a day continually). When I start doing trend analysis, etc, over a year’s worth of data like that, I’ll need a DB that can handle that task efficiently.
p.s., I have done a bunch with relational and non-relational in the past, but it’s been a while, and I haven’t done much with time series. So any advice or thoughts are greatly appreciated