2 ms·

Yes (via Clickhouse Cloud, which is pretty reasonably priced). It’s important to structure your tables and queries in a way that aligns with the ordering keys,

Yes (via Clickhouse Cloud, which is pretty reasonably priced). It’s important to structure your tables and queries in a way that aligns with the ordering keys, in order to optimize how much data needs to be loaded into RAM. You absolutely CANNOT just replicate your existing postgres DB and its primary keys or whatever over to CH. There are tricks like projections and incremental materialized views that can help to get the appropriate “lenses” for your queries. We use incremental MVs to, for example, continuously aggregate all-time stats about tens of billions of records. In general, for CH, space is cheap and RAM is expensive, so it’s better to duplicate a table’s data with a different ordering key than to make an inefficient query. As long as the queries align with the ordering keys, it is insanely fast and able to enable analytics queries for truly massive amounts of data. We’ve been very impressed.

Boxxed 1y ago
Well that's exactly my complaint. The bug I filed above was pretty much the optimal case (one huge table, one very small table, both ordered by the join key) and it still OoMs.
- mplanchard 1y ago
  Yeah it sucks at joins! If you can restructure your query to use an IN, or first limit the large table in a CTE and then JOIN, you may see better results. So far we haven’t found any cases where it couldn’t manage the task, but we have often had to be clever about join strategies. Depending on your use case, an incremental materialized view can also be really effective: when new rows for one table come in, query for related rows in a secondary table and populate the combination into a MV for efficient querying. You can also specify specific join strategies for queries, but we haven’t had as much luck with that so far. The JOIN thing is definitely the biggest pain point, though, I’ll not debate that at all.

Boxxed 1y ago

Well that's exactly my complaint. The bug I filed above was pretty much the optimal case (one huge table, one very small table, both ordered by the join key) and it still OoMs.

mplanchard 1y ago
Yeah it sucks at joins! If you can restructure your query to use an IN, or first limit the large table in a CTE and then JOIN, you may see better results. So far we haven’t found any cases where it couldn’t manage the task, but we have often had to be clever about join strategies. Depending on your use case, an incremental materialized view can also be really effective: when new rows for one table come in, query for related rows in a secondary table and populate the combination into a MV for efficient querying. You can also specify specific join strategies for queries, but we haven’t had as much luck with that so far. The JOIN thing is definitely the biggest pain point, though, I’ll not debate that at all.