[HN Gopher] Scaling product analytics built on ClickHouse
       ___________________________________________________________________
        
       Scaling product analytics built on ClickHouse
        
       Author : macobo
       Score  : 55 points
       Date   : 2022-03-22 14:33 UTC (8 hours ago)
        
 (HTM) web link (posthog.com)
 (TXT) w3m dump (posthog.com)
        
       | tiffanyh wrote:
       | I like Posthog (and ClickHouse).
       | 
       | If the author of this post is reading, just a recommendation -
       | when you write posts talking about performance increases - the
       | reader kind of expects to see some type of before/after graph
       | that pictorially shows the improvements.
        
         | SheinhardtWigCo wrote:
         | Not this reader, FWIW. The headings "10x faster", "speeding up
         | by 25x", and "23% win" painted a clear picture for me.
        
       | barrkel wrote:
       | ClickHouse is awesome, but most of the benefits come from
       | columnar storage and you need to design around that. Be aware of
       | how the thing works and how computer architecture works, because
       | sympathy with the machine is what reaps rewards.
       | 
       | You want to minimize the number and size of columns touched when
       | filtering and aggregating. If you need source data, store it
       | relationally or in a document store and only select the key from
       | CH. Don't put JSON in CH, fat columns don't make sense. And CH
       | can be just as slow as MySQL if you select a whole wide row but
       | only apply predicates to a handful of columns. Only touch the
       | columns you need.
       | 
       | Joins are super expensive because it costs a whole lot of
       | instructions to look up a row on a per value basis. CH can use
       | vectorized operations to eliminate or aggregate multiple "rows"
       | with instruction level parallelism because the column data is
       | contiguous. Joins are going to be an order of magnitude slower,
       | just with memory latency randomly hopping around a hash table.
       | Insert data prejoined; use the low cardinality string column,
       | substitute (i.e. precalculate) conditions on low cardinality
       | relations with integer IN tests, denormalize high cardinality
       | relations.
       | 
       | Partitions and various other storage level tricks are a way to
       | eek out better perf for mutations when they're needed. Rebuild a
       | subset of the data and swap it in and out. This is common on
       | Hadoop based columnar stores like Parquet and last I looked at CH
       | it was getting better ways to shuffle partitions around, "attach
       | partition from" and so on.
        
       ___________________________________________________________________
       (page generated 2022-03-22 23:01 UTC)