title: Ran out of memory searching text in ClickHouse
body: <p>I'm investigating whether ClickHouse is a good option for OLAP purposes. To do so, I replicated some queries I have running on PostgreSQL, using ClickHouse's sintax.</p>
  
  <p>All the queries I have ran are <strong><em>much</em></strong> faster than Postgres', but the ones that perform text search <strong><em>run out of memory</em></strong>. Below is the error code and the stack trace.</p>
  
  <blockquote>
    <p>clickhouse_driver.errors.ServerException: Code: 241. DB::Exception:
    Memory limit (for query) exceeded: would use 9.31 GiB (attempt to
    allocate chunk of 524288 bytes), maximum: 9.31 GiB.</p>
  </blockquote>
  
  <p>The script for the query is:</p>
  
  <pre><code>SELECT COUNT(*)
  FROM ObserverNodeOccurrence as occ 
  LEFT JOIN
      ObserverNodeOccurrence_NodeElements as occ_ne
      ON occ._id = occ_ne.occurrenceId
  WHERE
      occ_ne.snippet LIKE '&lt;img&gt;'
  </code></pre>
  
  <p>The query above counts the number of entries of the column <code>snippet</code> which contain an HTML image (<code>&lt;img&gt;</code>). This column contains HTML snippets, hence searching text becomes quite expensive.  A close/mid term goal is to parse this column and convert it into a set of other columns (e.g. <code>contains_img</code>, <code>contains_script</code>, etc.). But, for now, I would like to be able to run such query <em>without running out of memory</em>.</p>
  
  <p>My question(s) is(are): </p>
  
  <ul>
  <li><em>how can I successfully execute text-search queries on such column without running out of memory?</em>
  
  <ul>
  <li>Is there a way to <em>force</em> the query planner to use disk as soon as it runs out of memory?</li>
  <li>I am using <code>MergeTree</code> engine. Is there another engine that's able to split the load between ram and disk?</li>
  </ul></li>
  </ul>
  
  <hr>
  
  <p>Full stack trace:</p>
  
  <pre><code>clickhouse_driver.errors.ServerException: Code: 241.
  DB::Exception: Memory limit (for query) exceeded: would use 9.31 GiB (attempt to allocate chunk of 524288 bytes), maximum: 9.31 GiB. Stack trace:
  
  0. /usr/bin/clickhouse-server(StackTrace::StackTrace()+0x22) [0x781c272]
  1. /usr/bin/clickhouse-server(MemoryTracker::alloc(long)+0x8ba) [0x71bbb4a]
  2. /usr/bin/clickhouse-server(MemoryTracker::alloc(long)+0xc5) [0x71bb355]
  3. /usr/bin/clickhouse-server() [0x67aeb4e]
  4. /usr/bin/clickhouse-server() [0x67af010]
  5. /usr/bin/clickhouse-server() [0x67e5af4]
  6. /usr/bin/clickhouse-server(void DB::Join::joinBlockImpl&lt;(DB::ASTTableJoin::Kind)1, (DB::ASTTableJoin::Strictness)2, DB::Join::MapsTemplate&lt;DB::JoinStuff::WithFlags&lt;DB::RowRefList, false&gt; &gt; &gt;(DB::Block&amp;, std::vector&lt;std::__cxx11::basic_string&lt;char, std::char_traits&lt;char&gt;, std::allocator&lt;char&gt; &gt;, std::allocator&lt;std::__cxx11::basic_string&lt;char, std::char_traits&lt;char&gt;, std::allocator&lt;char&gt; &gt; &gt; &gt; const&amp;, DB::NamesAndTypesList const&amp;, DB::Block const&amp;, DB::Join::MapsTemplate&lt;DB::JoinStuff::WithFlags&lt;DB::RowRefList, false&gt; &gt; const&amp;) const+0xe1c) [0x68020dc]
  7. /usr/bin/clickhouse-server(DB::Join::joinBlock(DB::Block&amp;, std::vector&lt;std::__cxx11::basic_string&lt;char, std::char_traits&lt;char&gt;, std::allocator&lt;char&gt; &gt;, std::allocator&lt;std::__cxx11::basic_string&lt;char, std::char_traits&lt;char&gt;, std::allocator&lt;char&gt; &gt; &gt; &gt; const&amp;, DB::NamesAndTypesList const&amp;) const+0x1a5) [0x67bc415]
  8. /usr/bin/clickhouse-server(DB::ExpressionAction::execute(DB::Block&amp;, bool) const+0xa5d) [0x6d961dd]
  9. /usr/bin/clickhouse-server(DB::ExpressionActions::execute(DB::Block&amp;, bool) const+0x45) [0x6d97545]
  10. /usr/bin/clickhouse-server(DB::ExpressionBlockInputStream::readImpl()+0x48) [0x6c52888]
  11. /usr/bin/clickhouse-server(DB::IBlockInputStream::read()+0x188) [0x6635628]
  12. /usr/bin/clickhouse-server(DB::FilterBlockInputStream::readImpl()+0xd9) [0x6c538b9]
  13. /usr/bin/clickhouse-server(DB::IBlockInputStream::read()+0x188) [0x6635628]
  14. /usr/bin/clickhouse-server(DB::ExpressionBlockInputStream::readImpl()+0x2d) [0x6c5286d]
  15. /usr/bin/clickhouse-server(DB::IBlockInputStream::read()+0x188) [0x6635628]
  16. /usr/bin/clickhouse-server(DB::ParallelInputsProcessor&lt;DB::ParallelAggregatingBlockInputStream::Handler&gt;::loop(unsigned long)+0x139) [0x6c7f409]
  17. /usr/bin/clickhouse-server(DB::ParallelInputsProcessor&lt;DB::ParallelAggregatingBlockInputStream::Handler&gt;::thread(std::shared_ptr&lt;DB::ThreadGroupStatus&gt;, unsigned long)+0x209) [0x6c7fc79]
  18. /usr/bin/clickhouse-server(ThreadFromGlobalPool::ThreadFromGlobalPool&lt;void (DB::ParallelInputsProcessor&lt;DB::ParallelAggregatingBlockInputStream::Handler&gt;::*)(std::shared_ptr&lt;DB::ThreadGroupStatus&gt;, unsigned long), DB::ParallelInputsProcessor&lt;DB::ParallelAggregatingBlockInputStream::Handler&gt;*, std::shared_ptr&lt;DB::ThreadGroupStatus&gt;, unsigned long&amp;&gt;(void (DB::ParallelInputsProcessor&lt;DB::ParallelAggregatingBlockInputStream::Handler&gt;::*&amp;&amp;)(std::shared_ptr&lt;DB::ThreadGroupStatus&gt;, unsigned long), DB::ParallelInputsProcessor&lt;DB::ParallelAggregatingBlockInputStream::Handler&gt;*&amp;&amp;, std::shared_ptr&lt;DB::ThreadGroupStatus&gt;&amp;&amp;, unsigned long&amp;)::{lambda()#1}::operator()() const+0x7f) [0x6c801cf]
  19. /usr/bin/clickhouse-server(ThreadPoolImpl&lt;std::thread&gt;::worker(std::_List_iterator&lt;std::thread&gt;)+0x1af) [0x71c778f]
  20. /usr/bin/clickhouse-server() [0xb2ac5bf]
  21. /lib/x86_64-linux-gnu/libpthread.so.0(+0x76db) [0x7fc5b50826db]
  22. /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7fc5b480988f]
  </code></pre>