<story><title>ClickHouse as an alternative to Elasticsearch for log storage and analysis</title><url>https://pixeljets.com/blog/clickhouse-vs-elasticsearch/</url></story><parent_chain><item><author>the-alchemist</author><text>Also wanted to share my overall positive experience with Clickhouse.&lt;p&gt;UPSIDES&lt;p&gt;* started a 3-node cluster using the official Docker images super quickly&lt;p&gt;* ingested billions of rows super fast&lt;p&gt;* great compression (of course, depends on your data&amp;#x27;s characteristics)&lt;p&gt;* features like &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;clickhouse.tech&amp;#x2F;docs&amp;#x2F;en&amp;#x2F;engines&amp;#x2F;table-engines&amp;#x2F;mergetree-family&amp;#x2F;aggregatingmergetree&amp;#x2F;&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;clickhouse.tech&amp;#x2F;docs&amp;#x2F;en&amp;#x2F;engines&amp;#x2F;table-engines&amp;#x2F;merget...&lt;/a&gt; are amazing to see&lt;p&gt;* ODBC support.  I initially said &amp;quot;Who uses that??&amp;quot;, but we used it to connect PostgreSQL and so we can keep the non-timeseries data in PostgreSQL but still access PostgreSQL tables in Clickhouse (!)&lt;p&gt;* you can go the other way too: read Clickhouse from PostgreSQL (see &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;Percona-Lab&amp;#x2F;clickhousedb_fdw&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;Percona-Lab&amp;#x2F;clickhousedb_fdw&lt;/a&gt;, although we didn&amp;#x27;t try this)&lt;p&gt;* PRs welcome, and quickly reviewed. (We improved the ODBC UUID support)&lt;p&gt;* code quality is pretty high.&lt;p&gt;DOWNSIDES&lt;p&gt;* limited JOIN capabilities, which is expected from a timeseries-oriented database like Clickhouse.  It&amp;#x27;s almost impossible to implement JOINs at this kind of scale.  The philosophy is &amp;quot;If it won&amp;#x27;t be fast as scale, we don&amp;#x27;t support it&amp;quot;&lt;p&gt;* not-quite-standard SQL syntax, but they&amp;#x27;ve been improving it&lt;p&gt;* limited DELETE support, which is also expected from this kind of database, but rarely used in the kinds of environments that CH usually runs in (how often do people delete data from ElasticSearch?)&lt;p&gt;It&amp;#x27;s really an impressive piece of engineering.  Hats off to the Yandex crew.</text></item></parent_chain><comment><author>IanCal</author><text>I&amp;#x27;d like to add an upside which is:&lt;p&gt;Totally great and simple on a single node.&lt;p&gt;I looked at a bunch of analytical databases and had a lot that started with &amp;quot;so here&amp;#x27;s a basic 10 node cluster&amp;quot;. Clickhouse installed and worked instantly with decent but not &amp;quot;big&amp;quot; data with no hassle. A hundred million rows with lots of heavy text blobs and a lot of columns, that kind of thing. Happily dealt with triple nested joins over that, and with billions of entries in arrays on those columns didn&amp;#x27;t bat an eye.&lt;p&gt;I&amp;#x27;m sure I could do some great magic in postgres but naive work didn&amp;#x27;t give anywhere near the same results as clickhouse (obvious caveat for my workload).&lt;p&gt;Pretty good with JSON data, my only issue there at the time (may have improved) was you had to format the JSON quite strictly.</text></comment>
<story><title>ClickHouse as an alternative to Elasticsearch for log storage and analysis</title><url>https://pixeljets.com/blog/clickhouse-vs-elasticsearch/</url></story><parent_chain><item><author>the-alchemist</author><text>Also wanted to share my overall positive experience with Clickhouse.&lt;p&gt;UPSIDES&lt;p&gt;* started a 3-node cluster using the official Docker images super quickly&lt;p&gt;* ingested billions of rows super fast&lt;p&gt;* great compression (of course, depends on your data&amp;#x27;s characteristics)&lt;p&gt;* features like &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;clickhouse.tech&amp;#x2F;docs&amp;#x2F;en&amp;#x2F;engines&amp;#x2F;table-engines&amp;#x2F;mergetree-family&amp;#x2F;aggregatingmergetree&amp;#x2F;&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;clickhouse.tech&amp;#x2F;docs&amp;#x2F;en&amp;#x2F;engines&amp;#x2F;table-engines&amp;#x2F;merget...&lt;/a&gt; are amazing to see&lt;p&gt;* ODBC support.  I initially said &amp;quot;Who uses that??&amp;quot;, but we used it to connect PostgreSQL and so we can keep the non-timeseries data in PostgreSQL but still access PostgreSQL tables in Clickhouse (!)&lt;p&gt;* you can go the other way too: read Clickhouse from PostgreSQL (see &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;Percona-Lab&amp;#x2F;clickhousedb_fdw&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;Percona-Lab&amp;#x2F;clickhousedb_fdw&lt;/a&gt;, although we didn&amp;#x27;t try this)&lt;p&gt;* PRs welcome, and quickly reviewed. (We improved the ODBC UUID support)&lt;p&gt;* code quality is pretty high.&lt;p&gt;DOWNSIDES&lt;p&gt;* limited JOIN capabilities, which is expected from a timeseries-oriented database like Clickhouse.  It&amp;#x27;s almost impossible to implement JOINs at this kind of scale.  The philosophy is &amp;quot;If it won&amp;#x27;t be fast as scale, we don&amp;#x27;t support it&amp;quot;&lt;p&gt;* not-quite-standard SQL syntax, but they&amp;#x27;ve been improving it&lt;p&gt;* limited DELETE support, which is also expected from this kind of database, but rarely used in the kinds of environments that CH usually runs in (how often do people delete data from ElasticSearch?)&lt;p&gt;It&amp;#x27;s really an impressive piece of engineering.  Hats off to the Yandex crew.</text></item></parent_chain><comment><author>hodgesrm</author><text>&amp;gt; It&amp;#x27;s really an impressive piece of engineering. Hats off to the Yandex crew.&lt;p&gt;And thousands of contributors! Toward the end of 2020 over 680 unique users had submitted PRs and close to 2000 had opened issues. It&amp;#x27;s becoming a very large community.</text></comment>