3 ms·

PolyScale and ClickHouse Materialized Views

42 points by mvcalder 4y ago

data_ders 4y ago
Potentially ignorant question: who is the intended persona for this product? Is this for people that are serving "data products" to end users but don't want to own and manually configure their caching layer? And the product is magic provided by some automated rollup tables and materialized views based off of the data it sees being used to most? Maybe I'm overthinking..
- _ben_ 4y ago
  PolyScale founder here. Assuming you are referring to PolyScale (rather than ClickHouse), the product is aimed at devs who dont want to build data distribution and caching. You can connect your database and then have global low-latency reads, without writing code. Useful for multi-region deployments, serverless/microservices/FaaS as well as simply scaling your origin db. ClickHouse is used for computing Observability metrics within the UI. The automated caching algorithms do not use ClickHouse in any way. You can read more about the automation here: https://docs.polyscale.ai/how-does-it-work#caching-protocol https://docs.polyscale.ai/how-does-it-work#caching-protocol or try the live demo here: https://playground.polyscale.ai/ https://playground.polyscale.ai/
erikpukinskis 4y ago
I would love to see more databases support something like this. “Live queries” kind of thing. “Modern” app development today usually presumes some element of “live updates” or “multiplayer” functionality. It’s usually handled only partially, in the app layer, with a good amount of spaghetti. But the database to me seems like the place where this should happen. I should be able to SELECT * FROM projects WHERE owner_id=x ORDER BY name desc LIMIT 20 OFFSET 60; and then I should be able to subscribe to any changes to that query. I should be able to have thousands of these subscriptions open at a time. And ideally I should be able to get an efficient diff rather than a new result. Although a new result would be a great start. I’m not sure why this isn’t seen as table stakes for databases in 2022. It seems like such a fundamental challenge of distributed application development.
- hodgesrm 4y ago
  ClickHouse has a couple of features along this line: live views and window views. Here's a blog that compares them. https://altinity.com/blog/battle-of-the-views-clickhouse-window-view-vs-live-view https://altinity.com/blog/battle-of-the-views-clickhouse-win... Disclaimer: I work for Altinity. Polyscale is a customer.
  - fasteo 4y ago
    Related to this and Polyscale use case. Would this work ? CREATE WINDOW VIEW IF NOT EXISTS minute_metrics_wv minute_metrics_tt AS SELECT ... FROM raw_metrics WHERE ... GROUP BY ... That is, replacing the materialized view if favor of a WINDOW VIEW
  - benjaminwootton 4y ago
    I did a deep dive video on some of the options around Clickhouse materialised views too: https://m.youtube.com/watch?v=i7VYsf1e2qU https://m.youtube.com/watch?v=i7VYsf1e2qU
- fasteo 4y ago
  Check materialize[1] [1] https://materialize.com/ https://materialize.com/
  - TOMDM 4y ago
    Materialize looks like a perfect tool for the job should the need ever arise. I think my ideal would be for the functionality to be baked directly into PostgreSQL. Has anyone here used it? Any comments on speed/scalability/reliability? The self hosted license seems fairly permissive (Don't cluster, don't let your users manage the database views/queries [IANAL, read the license yourself]), but I'm hesitant given that I have never heard anyones stories about production use.
    - iany 4y ago
      > The self hosted license seems fairly permissive (Don't cluster, don't let your users manage the database views/queries [IANAL, read the license yourself]) The self-hosted version is being discontinued after the current version. The only offering will be the purely commercial cloud offering.

42 points by mvcalder 4y ago

Potentially ignorant question: who is the intended persona for this product? Is this for people that are serving "data products" to end users but don't want to own and manually configure their caching layer? And the product is magic provided by some automated rollup tables and materialized views based off of the data it sees being used to most? Maybe I'm overthinking..

_ben_ 4y ago
PolyScale founder here. Assuming you are referring to PolyScale (rather than ClickHouse), the product is aimed at devs who dont want to build data distribution and caching. You can connect your database and then have global low-latency reads, without writing code. Useful for multi-region deployments, serverless/microservices/FaaS as well as simply scaling your origin db. ClickHouse is used for computing Observability metrics within the UI. The automated caching algorithms do not use ClickHouse in any way. You can read more about the automation here: https://docs.polyscale.ai/how-does-it-work#caching-protocol https://docs.polyscale.ai/how-does-it-work#caching-protocol or try the live demo here: https://playground.polyscale.ai/ https://playground.polyscale.ai/

erikpukinskis 4y ago

I would love to see more databases support something like this. “Live queries” kind of thing. “Modern” app development today usually presumes some element of “live updates” or “multiplayer” functionality. It’s usually handled only partially, in the app layer, with a good amount of spaghetti. But the database to me seems like the place where this should happen. I should be able to SELECT * FROM projects WHERE owner_id=x ORDER BY name desc LIMIT 20 OFFSET 60; and then I should be able to subscribe to any changes to that query. I should be able to have thousands of these subscriptions open at a time. And ideally I should be able to get an efficient diff rather than a new result. Although a new result would be a great start. I’m not sure why this isn’t seen as table stakes for databases in 2022. It seems like such a fundamental challenge of distributed application development.

hodgesrm 4y ago
ClickHouse has a couple of features along this line: live views and window views. Here's a blog that compares them. https://altinity.com/blog/battle-of-the-views-clickhouse-window-view-vs-live-view https://altinity.com/blog/battle-of-the-views-clickhouse-win... Disclaimer: I work for Altinity. Polyscale is a customer.
- fasteo 4y ago
  Related to this and Polyscale use case. Would this work ? CREATE WINDOW VIEW IF NOT EXISTS minute_metrics_wv minute_metrics_tt AS SELECT ... FROM raw_metrics WHERE ... GROUP BY ... That is, replacing the materialized view if favor of a WINDOW VIEW
- benjaminwootton 4y ago
  I did a deep dive video on some of the options around Clickhouse materialised views too: https://m.youtube.com/watch?v=i7VYsf1e2qU https://m.youtube.com/watch?v=i7VYsf1e2qU
fasteo 4y ago
Check materialize[1] [1] https://materialize.com/ https://materialize.com/
- TOMDM 4y ago
  Materialize looks like a perfect tool for the job should the need ever arise. I think my ideal would be for the functionality to be baked directly into PostgreSQL. Has anyone here used it? Any comments on speed/scalability/reliability? The self hosted license seems fairly permissive (Don't cluster, don't let your users manage the database views/queries [IANAL, read the license yourself]), but I'm hesitant given that I have never heard anyones stories about production use.
  - iany 4y ago
    > The self hosted license seems fairly permissive (Don't cluster, don't let your users manage the database views/queries [IANAL, read the license yourself]) The self-hosted version is being discontinued after the current version. The only offering will be the purely commercial cloud offering.