Comment by mason55 | Hacker News Reader

Comment by mason55 | Hacker News Reader

Parent Full thread

Really depends what you're trying to do with your OLAP database.

If you're using it purely for reporting purposes then you really don't want to interconnect it with your OLTP database to support real-time queries. Reason number one is that you don't want some unexpected analytics workload to suddenly impact your production Postgres. Reason number two is that your analytics data model and your OLTP data model are frequently different. Usually your OLAP model needs to know what the value of a given dimension was at the time the event occurred, but if you're linking directly to Postgres then you can only see what the values are right now.

You can also go the other direction if you need the current values of OLTP data joined with your Clickhouse data: schedule an ETL out of Clickhouse and back into your OLTP database and aggregate the data to a reasonable level such that Posgres can handle it without a problem.

What's your actual use case? I'd normally consider a requirement to join OLAP & OLTP data in real time to be a "design smell". I don't mean that there's no value in things like fdw or easier ways to move data around, but you should consider using it to help with the ETL process and not as a real-time interconnect. Keep OLTP & OLAP workloads separate and both of your DBs will be happier.

Alright, here is an example. My data is a stream of events, each row has the values (event_id, person_id, date). This is the table with 40M rows, with inserts between 2/s and 10/s.

person_id is a foreign ID for a "person table" that fits well in the relational model. A "person" has various attributes (name, DOB, email) as well as a one-to-many relation to "groups". Groups are a collection of users with additional attributes (e.g. group name).

Now, what if I want to answer questions like:

   - how many total events for all persons in group X
   - who are the top 10 users in number of events in group X
   - which are the top 10 groups in number of events

In this case, the person/group tables are part of the core business logic, they aren't specific to the events table. It doesn't make sense to store it in Clickhouse. Also, this person/group data gets updated sparsely, but "freshness" should be kept at a minimum (< 30secs).

The simple approach to the first question would be

   - Get all the user IDs in group X
   - Filter events by those user IDs

But what if there are tens of thousands of users in group X? And hundreds of groups? Are megabyte-long queries supported in Clickhouse?

Some things to think about

1. Are the questions you're asking completely ad hoc? Or can you mostly define them ahead of time? If it's the former then you should be looking at getting your OLTP data into Clickhouse. If it's the latter then you should be looking to aggregate the data to various levels and get it out of Clickhouse.

All three of your sample questions lend themselves quite nicely to pre-aggregation. I'm sure your actual questions are more complex, but what I'd do to address all three of your examples is every night I'd roll up the raw events into (person_id, date, event_count) and send it back over to Postgres. Then every week you roll up the previous seven days into (person_id, week, event_count). Each month you roll the weeks up and each year you roll the months up. If you need the data more frequently than daily then you can go down to hourly or whatever it is you need.

Now you've got your data back into Postgres but at a reasonable granularity. Depending on the cardinality of the user-*group relationship you might have to do some magic to pre-aggregate that if the join is big as well, which could turn into a challenge as group membership changes (you'd need to re-aggregate all your group metrics any time group membership changed) but it's still better than trying to join across the Clickhouse/Postgres boundary.

If you really do need to support totally ad hoc questions all the time then you should figure out how to get your Postgres data into Clickhouse. If the data really gets update infrequently then it shouldn't be a problem to get changes in user/group membership into Clickhouse quickly, then you can do all your joins and analysis completely in Clickhouse.

2. Do you really want the current group memberships? Or do you want the group memberships at the time the event occurred? It's a subtle difference and there's not usually one right answer (or the answer is "I need both").

If you have something like Kafka before you insert your events into CH, you could consider enriching the event data by connecting with your Postgres data. That way, you still retain a flat CH table which should work just fine.

Reply on news.ycombinator.com