Postgres with upserts (and triggers if your de-duplication logic can't be handled on the backend)?
OLAP works here, but is not great depending on how fast you need info to be available. If you're generating reports, Postgres is fine as long as your queries are properly optimized, and you can get them within minutes for massive workloads. If you need near-instant (sub 1s) results, I would recommend you sync your RDBMS to a columnar database like ClickHouse, and let the better data layout work in your favor, rather than trying to constrain a row-based DB to act like it's not.
Otherwise, both are rock solid and simple to use. I've dealt with more intensive workloads than you mentioned, with the same use-case and Postgres worked very well. ClickHouse never had a problem.
Awesome, thank you. That's kind of what I was thinking, I'm glad you confirmed it. How exactly did you sync or data from PG -> Clickhouse?
I was considering using something like Airbyte, but then I thought this may actually be complex if PG rows are updating/deleting it means I also need to sync single rows (or groups of rows) to clickhouse, and I wasn't sure how the support was for that.
What I did in my case was setup stream replication to send over the Postgres WAL to another service that would update a ClickHouse cluster. Essentially, every time the WAL file is closed, a batch of all the SQL commands that were committed are sent over the wire.
It might be easier to find some "change data capture" product that will do that for you though (like Airbyte). I can't give any recommendations here, however.
OLAP works here, but is not great depending on how fast you need info to be available. If you're generating reports, Postgres is fine as long as your queries are properly optimized, and you can get them within minutes for massive workloads. If you need near-instant (sub 1s) results, I would recommend you sync your RDBMS to a columnar database like ClickHouse, and let the better data layout work in your favor, rather than trying to constrain a row-based DB to act like it's not.
Otherwise, both are rock solid and simple to use. I've dealt with more intensive workloads than you mentioned, with the same use-case and Postgres worked very well. ClickHouse never had a problem.