Optimizing ClickHouse: Tactics that worked for us

We&#x27;ve been using highlight for our bootstrapped member management platform[1] and I gotta say I&#x27;m super impressed with the session replay feature, it&#x27;s really helpful for understanding user behavior at a fraction of the price of competitors.<p>I remember wanting to use Heap&#x27;s session replay only to release they want hundreds of dollars per _month_, my last bill with highlight was $2.38 I recall.<p>That&#x27;s all to say that I&#x27;m glad Highlight is figuring out how to scale while still offering their features to the small players of the world.<p>[1] <a href="https:&#x2F;&#x2F;embolt.app" rel="nofollow">https:&#x2F;&#x2F;embolt.app</a>

clickhouse-local is pretty slick as well. You can operate directly on text files as if they were tables. I made my own toy text file database thing and thought I was cool because I could outrun similar programs like q, textql, sqlite, etc. But clickhouse-local had me by a factor of 10 easy in every kind of query with every type of data. Those guys know stuff.

We found the &quot;lots of small inserts&quot; issue, and fixed it by just using the Buffered table engine[0]. Can create it as a replica of the destination table, and it stores inserts in memory until they cross a threshold and are written. Super simple and took 5 minutes.<p>[0] <a href="https:&#x2F;&#x2F;clickhouse.com&#x2F;docs&#x2F;en&#x2F;engines&#x2F;table-engines&#x2F;special&#x2F;buffer" rel="nofollow">https:&#x2F;&#x2F;clickhouse.com&#x2F;docs&#x2F;en&#x2F;engines&#x2F;table-engines&#x2F;special...</a>

&gt; We opted to use the ClickHouse Kafka Connect Sink that implements batched writes and exactly-once semantics achieved through ClickHouse Keeper.<p>Just a heads up, You&#x27;ve got repeated line there

Very interesting observations! Merge performance tuning seems often overlooked even though it&#x27;s a key aspect of sustained ClickHouse performance.<p>I also like that the blog is quite compact and gets the points across without getting too much into the weeds.<p>One thing I&#x27;ve noticed also that bloom filter index types can be quite costly to merge. In many cases that&#x27;s acceptable though due to the massive benefit they provide for text queries. One just has to be mindful of the overhead when adding them.

Thanks for sharing! I&#x27;m curious as to your approach to changing the ORDER BY key for such large tables without significant downtime, since AFAIK this can&#x27;t be done in place (see: <a href="https:&#x2F;&#x2F;kb.altinity.com&#x2F;altinity-kb-schema-design&#x2F;change-order-by&#x2F;" rel="nofollow">https:&#x2F;&#x2F;kb.altinity.com&#x2F;altinity-kb-schema-design&#x2F;change-ord...</a>). Are you able to share any details?

What size is the cluster? Just curious how much hardware is needed to handle such traffic :)