Using ClickHouse to scale an events engine

&gt; Recently, the most interesting rift in the Postgres vs OLAP space is [Hydra](<a href="https:&#x2F;&#x2F;www.hydra.so">https:&#x2F;&#x2F;www.hydra.so</a>), an open-source, column-oriented distribution of Postgres that was very recently launched (after our migration to ClickHouse). Had Hydra been available during our decision-making time period, we might’ve made a different choice.<p>There will likely be a good OLAP solution (possibly implemented as an extension) in Postgres in the next year or so. There are a few companies are working on it (Hydra, Parade[0], tembo etc.).<p>0 - <a href="https:&#x2F;&#x2F;www.paradedb.com&#x2F;">https:&#x2F;&#x2F;www.paradedb.com&#x2F;</a>

I feel like with all the Clickhouse praise on HN that we &#x2F;must&#x2F; be doing something fundamentally wrong because I hate every interaction I have with Clickhouse.<p>* Timeouts (only 30s???) unless I used the cli client<p>* Cancelling rows - Just kill me, so many bugs and FINAL&#x2F;PREWHERE are massive foot-guns<p>* Cluster just feels annoying and fragile don&#x27;t forget &quot;ON CLUSTER&quot; or you&#x27;ll have a bad time<p>Again, I feel like we must be doing something wrong but we are paying an arm and a leg for that &quot;privilege&quot;.

Is ClickHouse a suitable engine for analyzing events? Absolutely, as long as you&#x27;re analyzing a large table, its speed is definitely fast enough. However, you might want to consider the cost of maintaining an OSS ClickHouse cluster, especially when you need to scale up, as the operational costs can be quite high.<p>If your analysis in Postgres was based on multiple tables and required a lot of JOIN operations, I don&#x27;t think ClickHouse is a good choice. In such cases, you often need to denormalize multiple data tables into one large table in advance, which means complex ETL and maintenance costs.<p>For these more common scenarios, I think StarRocks (www.StarRocks.io) is a better choice. It&#x27;s a Linux Foundation open-source project, with single-table query speeds comparable to ClickHouse (you can check Clickbench), and unmatched multi-table join query speeds, plus it can directly query open data lakes.

ClickHouse is awesome, but as the post shows, some code is involved in getting the data there.<p>I have been working on Scratchdata [1], which makes it easy to try out a column database to optimize aggregation queries (avg, sum, max). We have helped people [2] take their Postgres with 1 billion rows of information (1.5 TB) and significantly reduce their real-time data analysis query time. Because their data was stored more efficiently, they saved on their storage bill.<p>You can send data as a curl request and it will get batch-processed and flattened into ClickHouse:<p>curl -X POST &quot;<a href="http:&#x2F;&#x2F;app.scratchdata.com&#x2F;api&#x2F;data&#x2F;insert&#x2F;your_table?api_key=xxx">http:&#x2F;&#x2F;app.scratchdata.com&#x2F;api&#x2F;data&#x2F;insert&#x2F;your_table?api_ke...</a>&quot; --data &#x27;{&quot;user&quot;: &quot;alice&quot;, &quot;event&quot;: &quot;click&quot;}&#x27;<p>The founder, Jay, is super nice and just wants to help people save time and money. If you give us a ring, he or I will personally help you [3].<p>[1] <a href="https:&#x2F;&#x2F;www.scratchdb.com&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.scratchdb.com&#x2F;</a> [2] <a href="https:&#x2F;&#x2F;www.scratchdb.com&#x2F;blog&#x2F;embeddables&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.scratchdb.com&#x2F;blog&#x2F;embeddables&#x2F;</a> [3] <a href="https:&#x2F;&#x2F;q29ksuefpvm.typeform.com&#x2F;to&#x2F;baKR3j0p?typeform-source=www.scratchdb.com#source=hero" rel="nofollow">https:&#x2F;&#x2F;q29ksuefpvm.typeform.com&#x2F;to&#x2F;baKR3j0p?typeform-source...</a>

We use BigQuery a lot for internal analytics and we&#x27;ve been super happy. I don&#x27;t see a lot of love for BigQuery on HN and I wonder why. Tons of features, no hassle and easy to throw a bunch of TB at it.<p>I guess maybe the cost?

This change may make sense for Lago as a hosted multi-tenant service, as offered by Lago the company.<p>Simultaneously this change may <i>not</i> make sense for Lago as an open-source project self-hosted by a single tenant.<p>But that may also mean that it effectively makes sense for Lago as a business... to make it harder to self host.<p>I don&#x27;t at all fault Lago for making decisions to prioritize their multi-tenant cloud offering. That&#x27;s probably just the nature of running open-source SaaS these days.

How were they doing millions of events per minute with postgres.<p>I&#x27;m struggling with pg write performance ATM and want some tips.

And if you use MariaDB, just enable columnstore. Why not treat yourself to s3 backed storage while you are there?<p>It is extremely cost effective when you can scale a different workload without migrating.

I&#x27;m curious: how many rows Lago store in its CH cluster? Do they collect data for fighting fraud?<p>PG can handle a billion rows easily.

scale is becoming more and more important, not just for cost, but also as a key technology feature to help deal with unexpected traffic and reduce the cost of manual operations.

I have a tangentially related question since I don’t use an Olap db: is deleting data so hard to perform? Is it necessarily an immutable storage?<p>If so, is it a gdpr compliant storage solution? I am asking it since gdpr compliance may require data deletion (or at least anonimization)

deleting this comment because apparently jokes are not received well here