<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Kanishga Subramani</title>
    <description>The latest articles on DEV Community by Kanishga Subramani (@kanishga_subramani_49ad73).</description>
    <link>https://dev.to/kanishga_subramani_49ad73</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F3951880%2F08e2b1d3-1c3e-4280-91fc-99fd18e39198.jpg</url>
      <title>DEV Community: Kanishga Subramani</title>
      <link>https://dev.to/kanishga_subramani_49ad73</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/kanishga_subramani_49ad73"/>
    <language>en</language>
    <item>
      <title>Fix OOM Kills and MEMORY_LIMIT_EXCEEDED on ClickHouse® Servers</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Sat, 14 Nov 2026 07:04:19 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/fix-oom-kills-and-memorylimitexceeded-on-clickhouser-servers-5i4</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/fix-oom-kills-and-memorylimitexceeded-on-clickhouser-servers-5i4</guid>
      <description>&lt;h2&gt;
  
  
  Introduction
&lt;/h2&gt;

&lt;p&gt;ClickHouse® servers can experience two types of memory-related failures.&lt;/p&gt;

&lt;p&gt;In the first, the Linux Out-Of-Memory (OOM) killer terminates &lt;code&gt;clickhouse-server&lt;/code&gt;. The service restarts, and all running queries fail.&lt;/p&gt;

&lt;p&gt;In the second, multiple queries fail with &lt;code&gt;MEMORY_LIMIT_EXCEEDED&lt;/code&gt; (code 241). The error message contains &lt;code&gt;(total)&lt;/code&gt;, indicating that the entire server has reached its memory limit rather than an individual query exceeding its own limit.&lt;/p&gt;

&lt;p&gt;Both failures have the same underlying cause: &lt;strong&gt;the server is attempting to use more memory than the host can provide.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;In this guide, we'll explain how ClickHouse manages server memory, how to identify the source of memory failures, and how to configure appropriate limits to protect your server.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Why Memory Failures Happen
&lt;/h2&gt;

&lt;p&gt;ClickHouse maintains a hard memory limit for the entire server. When a query attempts an allocation that would exceed this limit, the query is stopped.&lt;/p&gt;

&lt;p&gt;Two steps determine the server's memory limit.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 1: Establishing the Memory Ceiling
&lt;/h3&gt;

&lt;p&gt;At startup, ClickHouse calculates its memory ceiling using the available memory and the setting &lt;code&gt;max_server_memory_usage_to_ram_ratio&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;The default value is &lt;code&gt;0.9&lt;/code&gt;, meaning ClickHouse can use up to 90% of the available memory for its server memory limit.&lt;/p&gt;

&lt;p&gt;The setting &lt;code&gt;max_server_memory_usage&lt;/code&gt; defaults to &lt;code&gt;0&lt;/code&gt;, which means no fixed limit is configured. If you specify a fixed value, ClickHouse uses the lower of that value and the calculated ceiling.&lt;/p&gt;

&lt;p&gt;For example, on a host with 13.55 GiB of RAM, setting a fixed limit of 15.63 GiB resulted in the following log message:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Lowered setting 'max_server_memory_usage' to 12.20 GiB because the system has too little memory. The new value was calculated as 13.55 GiB available memory * 0.90 max_server_memory_usage_to_ram_ratio
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;ClickHouse automatically lowered the configured limit because the host could not support the requested amount.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 2: Understanding the Runtime Memory Limit
&lt;/h3&gt;

&lt;p&gt;ClickHouse also uses a background thread called the memory worker to recalculate the hard memory limit while the server is running.&lt;/p&gt;

&lt;p&gt;The runtime limit is calculated using:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;(resident memory + available system memory) * ratio
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The result cannot exceed the previously established ceiling.&lt;/p&gt;

&lt;p&gt;When other processes consume memory, the available system memory decreases, and the runtime limit can fall accordingly.&lt;/p&gt;

&lt;p&gt;For example:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;MemoryWorker: Adjusting total memory hard limit from 5.62 GiB to 5.69 GiB (resident: 364.99 MiB, available: 5.97 GiB, ceiling: 12.20 GiB, ratio: 0.9)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The calculation is approximately:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;(0.36 GiB + 5.97 GiB) × 0.9 = 5.69 GiB
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Although the ceiling remained at 12.20 GiB, the live limit stayed around 6 GiB because other processes were consuming some of the host's memory.&lt;/p&gt;

&lt;h3&gt;
  
  
  Why the Safety Margin Matters
&lt;/h3&gt;

&lt;p&gt;The ratio provides a safety margin because ClickHouse's memory tracker does not account for every memory allocation.&lt;/p&gt;

&lt;p&gt;In one test, &lt;code&gt;MemoryResident&lt;/code&gt; was 940 MiB while &lt;code&gt;TrackedMemory&lt;/code&gt; was only 365 MiB. The remaining memory was consumed by the kernel, other services, and allocator overhead.&lt;/p&gt;

&lt;p&gt;A sudden memory spike can consume the available margin between memory-worker checks, potentially triggering the Linux OOM killer.&lt;/p&gt;

&lt;h3&gt;
  
  
  What Happens in Containers?
&lt;/h3&gt;

&lt;p&gt;ClickHouse can read memory limits imposed through cgroups.&lt;/p&gt;

&lt;p&gt;For example, after setting &lt;code&gt;MemoryMax=8G&lt;/code&gt; with systemd and restarting the server, the log showed:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Changed setting 'max_server_memory_usage' to 7.20 GiB (8.00 GiB available memory * 0.90 max_server_memory_usage_to_ram_ratio)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Without a container memory limit, ClickHouse may see the memory available on the entire host rather than the amount intended for the service.&lt;/p&gt;

&lt;p&gt;The setting &lt;code&gt;memory_worker_use_cgroup&lt;/code&gt; controls the use of cgroup memory information for runtime memory-limit calculations. However, a test on a host with similar free memory inside and outside the limit does not, by itself, prove the behavior.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Version note:&lt;/strong&gt; In ClickHouse 26.3.9, &lt;code&gt;memory_worker_dynamic_hard_limit&lt;/code&gt; does not exist. The limit remains at the ceiling, making the memory ratio an important protection mechanism.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. How to Confirm the Problem
&lt;/h2&gt;

&lt;p&gt;Before changing settings, determine whether the operating system killed the process or ClickHouse stopped a query because of its own memory limit.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 1: Check for OOM Kills
&lt;/h3&gt;

&lt;p&gt;Run:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;sudo dmesg -T &lt;span class="o"&gt;|&lt;/span&gt; grep -i -E &lt;span class="s2"&gt;"out of memory|killed process"&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;You can also inspect kernel logs from the last 24 hours:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;sudo journalctl -k --since &lt;span class="s2"&gt;"24 hours ago"&lt;/span&gt; &lt;span class="o"&gt;|&lt;/span&gt; grep -i -E &lt;span class="s2"&gt;"out of memory|killed process"&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Check when the ClickHouse service last started:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;systemctl show clickhouse-server -p ActiveEnterTimestamp
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If the service started recently and the kernel logs show that &lt;code&gt;clickhouse-serv&lt;/code&gt; was killed, the operating system likely terminated the process due to memory pressure.&lt;/p&gt;

&lt;p&gt;The kernel truncates process names to 15 characters, which explains why the process name may appear as &lt;code&gt;clickhouse-serv&lt;/code&gt;.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 2: Find Server-Level Memory Errors
&lt;/h3&gt;

&lt;p&gt;A ClickHouse server-level memory failure typically appears as &lt;code&gt;MEMORY_LIMIT_EXCEEDED&lt;/code&gt; with error code 241 and &lt;code&gt;(total)&lt;/code&gt; in the message.&lt;/p&gt;

&lt;p&gt;For example, this error was observed on ClickHouse 26.8.36:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Code: 241. DB::Exception: (total) memory limit exceeded: would use 6.28 GiB (attempt to allocate chunk of 4.00 GiB), current RSS: 2.23 GiB, maximum: 6.08 GiB. OvercommitTracker decision: Query was selected to stop by OvercommitTracker. Untracked memory across all threads: -6.82 MiB: While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The &lt;code&gt;maximum&lt;/code&gt; value represents the live server memory limit. It may be lower than the configured ceiling.&lt;/p&gt;

&lt;p&gt;To find recent server-level memory errors, query &lt;code&gt;system.query_log&lt;/code&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt;
    &lt;span class="n"&gt;event_time&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;query_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="nb"&gt;substring&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;exception&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;250&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;exception_start&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;system&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_log&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;type&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'ExceptionWhileProcessing'&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;exception_code&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;241&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;exception&lt;/span&gt; &lt;span class="k"&gt;LIKE&lt;/span&gt; &lt;span class="s1"&gt;'%(total)%'&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;event_time&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="nb"&gt;now&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="kt"&gt;INTERVAL&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="no"&gt;DAY&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;event_time&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;
&lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="mi"&gt;20&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The &lt;code&gt;system.errors&lt;/code&gt; table contains error counts accumulated since the server started and is reset on restart. The query log provides historical records of individual failures.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 3: Compare Process Memory with Host Memory
&lt;/h3&gt;

&lt;p&gt;Run the following query:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt;
    &lt;span class="n"&gt;metric&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="nf"&gt;formatReadableSize&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;value&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;size&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;system&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;asynchronous_metrics&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;metric&lt;/span&gt; &lt;span class="k"&gt;IN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="s1"&gt;'MemoryResident'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="s1"&gt;'TrackedMemory'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="s1"&gt;'OSMemoryTotal'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="s1"&gt;'OSMemoryAvailable'&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;metric&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;These metrics help you compare the memory used by ClickHouse with the total and available memory on the host.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 4: Read the Live Memory Limit
&lt;/h3&gt;

&lt;p&gt;Use &lt;code&gt;system.server_settings&lt;/code&gt; to inspect the current server memory limit:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt;
    &lt;span class="nf"&gt;formatReadableSize&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;toUInt64&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;value&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;live_limit&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;system&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;server_settings&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'max_server_memory_usage'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This shows the effective limit, which may differ from the value in your configuration file.&lt;/p&gt;

&lt;p&gt;For more information about runtime adjustments, inspect the server logs:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;sudo grep -a &lt;span class="s2"&gt;"Adjusting total memory hard limit"&lt;/span&gt; /var/log/clickhouse-server/clickhouse-server.log &lt;span class="o"&gt;|&lt;/span&gt; tail -5
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;These messages are available at Trace log level.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Understanding the Relevant Settings
&lt;/h2&gt;

&lt;p&gt;The following defaults are based on ClickHouse 26.8.36. Verify them on your own server before making changes.&lt;/p&gt;

&lt;p&gt;Run:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt;
    &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;value&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;default&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;changed&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;changeable_without_restart&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;system&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;server_settings&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="k"&gt;LIKE&lt;/span&gt; &lt;span class="s1"&gt;'max_server_memory%'&lt;/span&gt;
   &lt;span class="k"&gt;OR&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="k"&gt;LIKE&lt;/span&gt; &lt;span class="s1"&gt;'memory_worker%'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The important settings are:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Setting&lt;/th&gt;
&lt;th&gt;Default&lt;/th&gt;
&lt;th&gt;Purpose&lt;/th&gt;
&lt;th&gt;Restart required&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;max_server_memory_usage&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;0&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Defines a fixed server memory limit in bytes. A higher value is lowered to the calculated ceiling.&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;max_server_memory_usage_to_ram_ratio&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;0.9&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Controls the share of memory available to the server and determines the memory ceiling. A value of &lt;code&gt;0&lt;/code&gt; disables the limits established by this ratio.&lt;/td&gt;
&lt;td&gt;No in the test described&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;memory_worker_dynamic_hard_limit&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;true&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Allows the memory worker to recalculate the hard limit at runtime.&lt;/td&gt;
&lt;td&gt;Yes&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;memory_worker_use_cgroup&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;true&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Uses cgroup memory information when calculating the runtime limit.&lt;/td&gt;
&lt;td&gt;Yes&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The &lt;code&gt;changeable_without_restart&lt;/code&gt; column may show &lt;code&gt;No&lt;/code&gt; for the ratio setting. In the test described here, reloading the configuration still applied the change, and the logs showed a new &lt;code&gt;Changed setting&lt;/code&gt; message.&lt;/p&gt;

&lt;p&gt;If the expected log message does not appear after reloading the configuration, restart the server.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. How to Fix Memory Failures
&lt;/h2&gt;

&lt;p&gt;The recommended approach is to reserve memory for the operating system and configure explicit memory limits for containers.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 1: Set a Lower Memory Ratio
&lt;/h3&gt;

&lt;p&gt;Create a configuration file at:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;/etc/clickhouse-server/config.d/memory.xml
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Add the following configuration:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight xml"&gt;&lt;code&gt;&lt;span class="nt"&gt;&amp;lt;clickhouse&amp;gt;&lt;/span&gt;
    &lt;span class="nt"&gt;&amp;lt;max_server_memory_usage_to_ram_ratio&amp;gt;&lt;/span&gt;0.8&lt;span class="nt"&gt;&amp;lt;/max_server_memory_usage_to_ram_ratio&amp;gt;&lt;/span&gt;
&lt;span class="nt"&gt;&amp;lt;/clickhouse&amp;gt;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;A ratio of &lt;code&gt;0.8&lt;/code&gt; allows ClickHouse to use up to 80% of the available memory for its server memory limit.&lt;/p&gt;

&lt;p&gt;For example, on a host with 64 GiB of memory, the ceiling would be 51.2 GiB, leaving 12.8 GiB for the operating system and other processes.&lt;/p&gt;

&lt;p&gt;Consider these starting points:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;0.8:&lt;/strong&gt; A reasonable starting point for many hosts.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;0.7:&lt;/strong&gt; Consider this when the host runs other services.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;0.85:&lt;/strong&gt; Consider this only on a dedicated host with carefully monitored memory usage.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;These values are starting points, not universal recommendations. The appropriate ratio depends on the host, workload, and memory consumed by other processes.&lt;/p&gt;

&lt;p&gt;Reload the configuration:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="no"&gt;SYSTEM&lt;/span&gt; &lt;span class="no"&gt;RELOAD&lt;/span&gt; &lt;span class="no"&gt;CONFIG&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Then verify the updated setting in the logs:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;sudo grep -a &lt;span class="s2"&gt;"max_server_memory_usage"&lt;/span&gt; /var/log/clickhouse-server/clickhouse-server.log &lt;span class="o"&gt;|&lt;/span&gt; tail -3
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;You should see a &lt;code&gt;Changed setting&lt;/code&gt; message confirming the new value.&lt;/p&gt;

&lt;p&gt;For example, a test using a ratio of &lt;code&gt;0.15&lt;/code&gt; produced:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Changed setting 'max_server_memory_usage' to 2.03 GiB (13.55 GiB available memory * 0.15 max_server_memory_usage_to_ram_ratio)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Configuration tip:&lt;/strong&gt; Write the new file in a separate directory first, then copy it into &lt;code&gt;config.d/&lt;/code&gt;. This reduces the chance of ClickHouse loading a partially written configuration file.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 2: Configure a Fixed Memory Limit
&lt;/h3&gt;

&lt;p&gt;If you know how much memory ClickHouse should use on a shared host, you can set an explicit limit alongside the ratio.&lt;/p&gt;

&lt;p&gt;Update &lt;code&gt;memory.xml&lt;/code&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight xml"&gt;&lt;code&gt;&lt;span class="nt"&gt;&amp;lt;clickhouse&amp;gt;&lt;/span&gt;
    &lt;span class="nt"&gt;&amp;lt;max_server_memory_usage_to_ram_ratio&amp;gt;&lt;/span&gt;0.8&lt;span class="nt"&gt;&amp;lt;/max_server_memory_usage_to_ram_ratio&amp;gt;&lt;/span&gt;
    &lt;span class="nt"&gt;&amp;lt;max_server_memory_usage&amp;gt;&lt;/span&gt;53687091200&lt;span class="nt"&gt;&amp;lt;/max_server_memory_usage&amp;gt;&lt;/span&gt;
&lt;span class="nt"&gt;&amp;lt;/clickhouse&amp;gt;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The value &lt;code&gt;53687091200&lt;/code&gt; represents 50 GiB in bytes.&lt;/p&gt;

&lt;p&gt;A fixed limit makes the memory budget easier to understand and manage. ClickHouse uses the lowest applicable value among the fixed limit, the calculated ceiling, and the runtime limit.&lt;/p&gt;

&lt;p&gt;Reload the configuration:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="no"&gt;SYSTEM&lt;/span&gt; &lt;span class="no"&gt;RELOAD&lt;/span&gt; &lt;span class="no"&gt;CONFIG&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;In the test described here, changing the configured value from 3 GiB to 2 GiB through a configuration reload caused the next memory error to report:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;maximum: 2.00 GiB
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Verify the effective limit in the logs and through &lt;code&gt;system.server_settings&lt;/code&gt;.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 3: Set a Container Memory Limit
&lt;/h3&gt;

&lt;p&gt;A container memory limit prevents ClickHouse from consuming the entire host's memory.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Docker example:&lt;/strong&gt;&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;docker run -d --name clickhouse --memory=32g clickhouse/clickhouse-server
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Kubernetes example:&lt;/strong&gt;&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="na"&gt;resources&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
  &lt;span class="na"&gt;requests&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="na"&gt;memory&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;32Gi&lt;/span&gt;
  &lt;span class="na"&gt;limits&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="na"&gt;memory&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;32Gi&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Setting the Kubernetes memory request equal to the limit reserves the same memory amount for scheduling purposes and helps avoid unexpected memory-pressure evictions caused by overcommitting the requested amount.&lt;/p&gt;

&lt;p&gt;With a 32 GiB container limit and a ratio of &lt;code&gt;0.8&lt;/code&gt;, the calculated ceiling is 25.6 GiB.&lt;/p&gt;

&lt;p&gt;Systemd's &lt;code&gt;MemoryMax&lt;/code&gt; provides a similar cgroup memory limit.&lt;/p&gt;

&lt;p&gt;Remember that the container limit and ClickHouse's internal memory limit serve different purposes: the container constrains the process at the operating-system level, while ClickHouse's settings help it stop queries before reaching that boundary.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. How to Verify the Fix
&lt;/h2&gt;

&lt;p&gt;After changing the configuration, check the effective settings and confirm that the logs show the expected memory limit.&lt;/p&gt;

&lt;p&gt;On a test server, you can trigger a memory-limit error using:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="nf"&gt;groupArray&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;number&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="nf"&gt;numbers&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;100000000000&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="no"&gt;SETTINGS&lt;/span&gt; &lt;span class="n"&gt;max_memory_usage&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
&lt;span class="no"&gt;FORMAT&lt;/span&gt; &lt;span class="kc"&gt;Null&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Warning:&lt;/strong&gt; Do not run this test on a production server. It is intended for controlled testing only.&lt;/p&gt;

&lt;p&gt;The query should fail with code 241. The &lt;code&gt;maximum&lt;/code&gt; value in the error should match the configured effective limit or be lower if the host has insufficient available memory.&lt;/p&gt;

&lt;p&gt;For production, monitor the server for seven days after applying the changes.&lt;/p&gt;

&lt;p&gt;The fix is working as intended when:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Kernel logs show no new OOM kills.&lt;/li&gt;
&lt;li&gt;The query log contains no new unexpected &lt;code&gt;(total)&lt;/code&gt; memory-limit errors.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;MemoryResident&lt;/code&gt; remains below &lt;code&gt;OSMemoryTotal&lt;/code&gt; with a safe memory margin.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Continue monitoring workloads that generate large aggregations, memory-intensive queries, merges, and mutations.&lt;/p&gt;

&lt;h2&gt;
  
  
  6. Risks and Trade-Offs
&lt;/h2&gt;

&lt;h3&gt;
  
  
  Lower Limits Can Cause Queries to Fail Earlier
&lt;/h3&gt;

&lt;p&gt;Reducing the memory limit means ClickHouse may reject queries that previously succeeded.&lt;/p&gt;

&lt;p&gt;This is often the correct trade-off: a failed query is preferable to a server process being killed and disrupting all running queries.&lt;/p&gt;

&lt;p&gt;Identify and tune the heaviest queries rather than simply increasing the server limit.&lt;/p&gt;

&lt;h3&gt;
  
  
  The Live Limit Can Change
&lt;/h3&gt;

&lt;p&gt;On a shared host, the effective memory limit can decrease when other processes consume more memory.&lt;/p&gt;

&lt;p&gt;As a result, queries may fail even when the configured settings have not changed. Investigate other processes and host-level memory usage before increasing ClickHouse's limits.&lt;/p&gt;

&lt;h3&gt;
  
  
  Merges and Mutations Have Their Own Memory Controls
&lt;/h3&gt;

&lt;p&gt;Merges and mutations have a separate setting:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;merges_mutations_memory_usage_to_ram_ratio
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Its default is &lt;code&gt;0.5&lt;/code&gt;. Review it when diagnosing memory pressure related to background operations.&lt;/p&gt;

&lt;h3&gt;
  
  
  Caches Also Consume Memory
&lt;/h3&gt;

&lt;p&gt;Caches contribute to memory consumption. Avoid increasing cache sizes when lowering the server's memory limit unless you have measured the impact and confirmed that sufficient memory remains available.&lt;/p&gt;

&lt;h3&gt;
  
  
  Be Careful When Using Swap
&lt;/h3&gt;

&lt;p&gt;The setting description notes that low-RAM hosts with swap may require a ratio above &lt;code&gt;1&lt;/code&gt; in certain situations.&lt;/p&gt;

&lt;p&gt;This should only be done deliberately. Allowing ClickHouse to use swap before reaching its configured limit may delay an OOM event, but heavy swapping can make queries extremely slow and postpone rather than prevent a more severe failure.&lt;/p&gt;

&lt;p&gt;For ClickHouse hosts, consider disabling swap or configuring a low &lt;code&gt;vm.swappiness&lt;/code&gt; value according to your operational requirements.&lt;/p&gt;

&lt;h2&gt;
  
  
  7. Summary
&lt;/h2&gt;

&lt;p&gt;When troubleshooting ClickHouse memory failures:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Check &lt;code&gt;dmesg&lt;/code&gt; and the service start time to determine whether the operating system killed the process.&lt;/li&gt;
&lt;li&gt;Look for &lt;code&gt;MEMORY_LIMIT_EXCEEDED&lt;/code&gt; code 241 errors containing &lt;code&gt;(total)&lt;/code&gt; to identify server-level memory failures.&lt;/li&gt;
&lt;li&gt;Read the &lt;code&gt;maximum&lt;/code&gt; value in the error message to understand the effective live limit.&lt;/li&gt;
&lt;li&gt;Remember that the live limit is constrained by the fixed limit, the ratio-based ceiling, and the runtime limit calculated by the memory worker.&lt;/li&gt;
&lt;li&gt;Configure a suitable &lt;code&gt;max_server_memory_usage_to_ram_ratio&lt;/code&gt; in &lt;code&gt;config.d/&lt;/code&gt; and reload the configuration.&lt;/li&gt;
&lt;li&gt;Set an explicit container memory limit when running ClickHouse in Docker, Kubernetes, or another cgroup-managed environment.&lt;/li&gt;
&lt;li&gt;Monitor kernel logs, query errors, and memory metrics after applying the fix.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The goal is not to let ClickHouse consume every available byte of memory. It is to establish a predictable memory budget that protects the server while allowing normal workloads to run reliably.&lt;/p&gt;

&lt;h2&gt;
  
  
  References
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;ClickHouse documentation: &lt;code&gt;max_server_memory_usage&lt;/code&gt; and &lt;code&gt;max_server_memory_usage_to_ram_ratio&lt;/code&gt;.&lt;/li&gt;
&lt;/ul&gt;

</description>
      <category>clickhouse</category>
      <category>devops</category>
      <category>analytics</category>
    </item>
    <item>
      <title>CH-Ops Pro Scheduled Archival: Move ClickHouse® Data to S3</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Sat, 31 Oct 2026 06:01:23 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/ch-ops-pro-scheduled-archival-move-clickhouser-data-to-s3-3aco</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/ch-ops-pro-scheduled-archival-move-clickhouser-data-to-s3-3aco</guid>
      <description>&lt;h2&gt;
  
  
  Table of Contents
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;What is CH-Ops?&lt;/li&gt;
&lt;li&gt;Introducing the CH-Ops Scheduled Archival&lt;/li&gt;
&lt;li&gt;Creation of an Archival Job&lt;/li&gt;
&lt;li&gt;Choose How Data Should Be Archived&lt;/li&gt;
&lt;li&gt;Configure the Watermark Column&lt;/li&gt;
&lt;li&gt;Starting Point and File Partitioning&lt;/li&gt;
&lt;li&gt;Choose the Storage Format&lt;/li&gt;
&lt;li&gt;Where the Job Runs, and Where It Writes To&lt;/li&gt;
&lt;li&gt;Scheduling and Getting Notified&lt;/li&gt;
&lt;li&gt;Defining the Restore Shape Upfront&lt;/li&gt;
&lt;li&gt;Managing a Job After It's Created&lt;/li&gt;
&lt;li&gt;A Complete Archival Example&lt;/li&gt;
&lt;li&gt;Restore Archived Data&lt;/li&gt;
&lt;li&gt;Explore Archived Files&lt;/li&gt;
&lt;li&gt;A Simple End-to-End Flow&lt;/li&gt;
&lt;li&gt;Why This Is Worth Setting Up&lt;/li&gt;
&lt;li&gt;Further Reading&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  What is CH-Ops?
&lt;/h2&gt;

&lt;p&gt;CH-Ops is a browser-based operations platform built for managing ClickHouse® deployments. Instead of relying entirely on the command line or HTTP APIs, it provides a unified interface for executing SQL queries, monitoring clusters, managing users, backups, alerts, dashboards, and much more — all from a single web application.&lt;/p&gt;

&lt;p&gt;One of the features available in the CH-Ops Pro version is Scheduled Archival, which moves aging data to S3-compatible storage on a schedule instead of letting it grow indefinitely inside the cluster.&lt;/p&gt;

&lt;p&gt;Let's explore how Scheduled Archival works and how you can use it to automate ClickHouse data archival.&lt;/p&gt;

&lt;h2&gt;
  
  
  Introducing the CH-Ops Scheduled Archival
&lt;/h2&gt;

&lt;p&gt;A ClickHouse® cluster tends to grow in one direction: up. Old rows rarely get deleted, and most of them are barely queried once they age past a certain point.&lt;/p&gt;

&lt;p&gt;Scheduled Archival, under the Backups section in CH-Ops, gives you a way to move that cold data out to S3-compatible storage on a schedule, in a columnar format, without writing your own export scripts.&lt;/p&gt;

&lt;p&gt;It allows you to create jobs that periodically archive data from ClickHouse to S3-compatible storage. A job runs a plain SELECT query against your cluster on a schedule, writes the result to S3-compatible storage, and — depending on the mode you choose — keeps track of what it has already archived so the next run only picks up new rows.&lt;/p&gt;

&lt;p&gt;You write the query once. CH-Ops handles the window, the file format, the compression, and the schedule around it.&lt;/p&gt;

&lt;p&gt;Three tabs organize the feature:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Tab&lt;/th&gt;
&lt;th&gt;What it's for&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Jobs&lt;/td&gt;
&lt;td&gt;Create, run, pause, and monitor archival jobs&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Restore&lt;/td&gt;
&lt;td&gt;Bring an archived snapshot back into a ClickHouse table&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Archive Explorer&lt;/td&gt;
&lt;td&gt;Browse the folder structure of what has actually been written to storage&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Creation of an Archival Job
&lt;/h2&gt;

&lt;p&gt;Start by creating a new job from &lt;strong&gt;Scheduled Archival → New Job&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;Give the job a name and provide a normal SELECT query describing the data you want to archive.&lt;/p&gt;

&lt;p&gt;For example:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;uk&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;uk_price_paid&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The archival window is handled automatically by the engine, so the query itself does not need to contain a time filter for an incremental job.&lt;/p&gt;

&lt;p&gt;Before creating the job, you can use &lt;strong&gt;Test (describe)&lt;/strong&gt; to inspect the query and its columns.&lt;/p&gt;

&lt;h2&gt;
  
  
  Choose How Data Should Be Archived
&lt;/h2&gt;

&lt;p&gt;The Mode determines how each archival run handles the data.&lt;/p&gt;

&lt;p&gt;The Mode dropdown gives you two options:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Incremental (windowed):&lt;/strong&gt; Archives only new rows each run, tracked with a watermark column.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Full snapshot:&lt;/strong&gt; Exports the entire result of the query every run.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Incremental is the one you will use for anything that grows over time, like an events or transactions table. Full snapshot suits smaller, slowly changing tables where re-exporting everything each time is cheap enough not to matter.&lt;/p&gt;

&lt;h2&gt;
  
  
  Configure the Watermark Column
&lt;/h2&gt;

&lt;p&gt;For incremental archival, CH-Ops uses a watermark column to determine what data should be included in each window.&lt;/p&gt;

&lt;p&gt;The interface allows you to pick a column that increases on both INSERT and UPDATE, something like &lt;code&gt;updated_at&lt;/code&gt;. The archival engine uses this value to track progress between runs.&lt;/p&gt;

&lt;p&gt;If your only candidate is a String column, CH-Ops warns you first, because ClickHouse compares strings byte-wise, which only matches logical order under specific conditions.&lt;/p&gt;

&lt;h2&gt;
  
  
  Starting Point and File Partitioning
&lt;/h2&gt;

&lt;p&gt;The &lt;strong&gt;Start from&lt;/strong&gt; value determines where the first archival window begins. The initial run can catch up on the historical data from the selected starting point, while subsequent runs continue from where the previous run stopped.&lt;/p&gt;

&lt;p&gt;This is particularly useful when setting up archival for an existing dataset rather than starting from newly inserted records.&lt;/p&gt;

&lt;p&gt;An optional partition expression, like &lt;code&gt;toDate(created)&lt;/code&gt;, drives a third folder level in storage, so archived files land in date-based (or otherwise expression-based) folders instead of one flat run.&lt;/p&gt;

&lt;p&gt;Leave it blank and you get a single folder per run instead.&lt;/p&gt;

&lt;p&gt;A &lt;strong&gt;Check&lt;/strong&gt; button lets you validate the expression before saving the job.&lt;/p&gt;

&lt;h2&gt;
  
  
  Choose the Storage Format
&lt;/h2&gt;

&lt;p&gt;CH-Ops lets you select the format used for archived files.&lt;/p&gt;

&lt;p&gt;The available formats shown in the interface include:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Setting&lt;/th&gt;
&lt;th&gt;Options&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Format&lt;/td&gt;
&lt;td&gt;Parquet, ORC, Avro&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Codec&lt;/td&gt;
&lt;td&gt;zstd, snappy, gzip, lz4, brotli, none&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Max rows per file&lt;/td&gt;
&lt;td&gt;Specify the maximum number of rows that should be written to each archive file, for example, 5,000,000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Insert threads&lt;/td&gt;
&lt;td&gt;Configure the number of threads used during the archival operation. Use 1 for an exact split; more threads provide faster but approximate splitting&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;This gives you control over the balance between storage size and processing requirements.&lt;/p&gt;

&lt;p&gt;Parquet with zstd is a reasonable default: columnar, well compressed, and widely readable by downstream tools.&lt;/p&gt;

&lt;h2&gt;
  
  
  Where the Job Runs, and Where It Writes To
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Run on node&lt;/strong&gt; decides which cluster node executes the job every time, defaulting to whichever node is selected in the navbar.&lt;/p&gt;

&lt;p&gt;The Test and partition check also run on this node, so the columns you see while configuring the job match what the scheduled run will actually use.&lt;/p&gt;

&lt;p&gt;The &lt;strong&gt;Storage Profile&lt;/strong&gt; determines where the archived files are stored. In the example configuration, the job uses an S3 storage &lt;code&gt;mvp-poc&lt;/code&gt; profile.&lt;/p&gt;

&lt;p&gt;Once the storage profile is selected, CH-Ops can write the generated archival files to the configured object storage location.&lt;/p&gt;

&lt;p&gt;This separates long-term archived data from the primary ClickHouse storage while keeping the data available for later exploration or restoration.&lt;/p&gt;

&lt;h2&gt;
  
  
  Scheduling and Getting Notified
&lt;/h2&gt;

&lt;p&gt;The schedule is entered as a standard cron expression, evaluated in UTC, not server local time, so it lines up with the archival window, which itself seals at the start of day UTC.&lt;/p&gt;

&lt;p&gt;Something like &lt;code&gt;0 * * * *&lt;/code&gt; runs hourly.&lt;/p&gt;

&lt;p&gt;Notification channels reuse whatever alert channels you have already configured, like Google Chat or email, with an option to notify on failure only, so you are not paged for every successful run.&lt;/p&gt;

&lt;h2&gt;
  
  
  Defining the Restore Shape Upfront
&lt;/h2&gt;

&lt;p&gt;Before you save the job, you also define the shape a restored table will take: an engine (MergeTree is the default), the ORDER BY columns, and an optional PARTITION BY.&lt;/p&gt;

&lt;p&gt;This is used to build the CREATE TABLE statement automatically when you restore later, and the column list comes from the Test step, so it is worth running Test with your final query before finishing this section.&lt;/p&gt;

&lt;p&gt;Once everything is filled in, &lt;strong&gt;Create&lt;/strong&gt; saves and enables the job.&lt;/p&gt;

&lt;h2&gt;
  
  
  Managing a Job After It's Created
&lt;/h2&gt;

&lt;p&gt;Each job appears as a card showing its name, mode (windowed or full snapshot), the underlying query, schedule, format and codec, target node, watermark column, and status.&lt;/p&gt;

&lt;p&gt;From there, you can:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Run&lt;/strong&gt; it immediately, outside its schedule.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Pause&lt;/strong&gt; it without deleting it.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Resume&lt;/strong&gt; it if needed again.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;History&lt;/strong&gt; to see past runs.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Backfill&lt;/strong&gt; to catch up a range that was missed or never covered.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Edit&lt;/strong&gt; or &lt;strong&gt;Delete&lt;/strong&gt; the job entirely.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  A Complete Archival Example
&lt;/h2&gt;

&lt;p&gt;Here is an example configuration for a scheduled archival job:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Setting&lt;/th&gt;
&lt;th&gt;Value&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Name&lt;/td&gt;
&lt;td&gt;testing1&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Node&lt;/td&gt;
&lt;td&gt;replica2 (10.X.X.X.X)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Query&lt;/td&gt;
&lt;td&gt;&lt;code&gt;SELECT * FROM uk.uk_price_paid&lt;/code&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Mode&lt;/td&gt;
&lt;td&gt;Incremental&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Watermark&lt;/td&gt;
&lt;td&gt;created_at (DateTime)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Start from&lt;/td&gt;
&lt;td&gt;01/01/2026 12:42&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Storage profile&lt;/td&gt;
&lt;td&gt;s3&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Format / Codec&lt;/td&gt;
&lt;td&gt;Parquet / zstd&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Max rows per file&lt;/td&gt;
&lt;td&gt;5,000,000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Schedule&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;0 * * * *&lt;/code&gt; (02:00 UTC daily)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Notifications&lt;/td&gt;
&lt;td&gt;#Testing-channel, on failure only&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The first run archives everything from January 2026 up to the start of the current day, which might be a few hundred million rows in one large snapshot.&lt;/p&gt;

&lt;p&gt;Every run after that picks up one day.&lt;/p&gt;

&lt;h2&gt;
  
  
  Restore Archived Data
&lt;/h2&gt;

&lt;p&gt;Archiving is only useful if the data can be retrieved when needed.&lt;/p&gt;

&lt;p&gt;The &lt;strong&gt;Restore&lt;/strong&gt; tab allows you to select an archival job and choose a snapshot to restore.&lt;/p&gt;

&lt;p&gt;You can specify:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Destination database&lt;/li&gt;
&lt;li&gt;Destination table&lt;/li&gt;
&lt;li&gt;Optional ON CLUSTER clause&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;CH-Ops can also build the required DDL for the restored table using &lt;strong&gt;Build DDL&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;This provides a straightforward path from archived files back into ClickHouse.&lt;/p&gt;

&lt;h2&gt;
  
  
  Explore Archived Files
&lt;/h2&gt;

&lt;p&gt;The &lt;strong&gt;Archive Explorer&lt;/strong&gt; provides a visual view of the archived data stored in the configured storage location.&lt;/p&gt;

&lt;p&gt;Instead of navigating through object-storage paths manually, you can browse the available archive folders directly from CH-Ops.&lt;/p&gt;

&lt;p&gt;The interface identifies archives based on the configured job and storage format, making it easier to locate archived datasets.&lt;/p&gt;

&lt;h2&gt;
  
  
  A Simple End-to-End Flow
&lt;/h2&gt;

&lt;p&gt;The complete workflow brings together the key steps:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Create an archival job and define the SELECT query.&lt;/li&gt;
&lt;li&gt;Choose incremental archival or full snapshot mode.&lt;/li&gt;
&lt;li&gt;Configure the watermark column and starting point when using incremental mode.&lt;/li&gt;
&lt;li&gt;Select the storage format, compression codec, and file partitioning options.&lt;/li&gt;
&lt;li&gt;Choose the execution node and S3-compatible storage profile.&lt;/li&gt;
&lt;li&gt;Configure the schedule and notification channels.&lt;/li&gt;
&lt;li&gt;Define the restored table's engine, sorting key, and optional partitioning.&lt;/li&gt;
&lt;li&gt;Create the job and monitor its runs.&lt;/li&gt;
&lt;li&gt;Explore archived files or restore a snapshot when needed.&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  Why This Is Worth Setting Up
&lt;/h2&gt;

&lt;p&gt;Manually exporting cold data to S3 usually means a cron job somewhere, a script that tracks its own watermark, and a separate process for restoring anything back.&lt;/p&gt;

&lt;p&gt;CH-Ops Pro's Scheduled Archival brings these steps into a single workflow.&lt;/p&gt;

&lt;p&gt;With incremental and full-snapshot modes, configurable storage formats and compression, scheduled execution, notifications, restoration, and archive exploration, it provides a practical way to manage long-term ClickHouse data in S3-compatible storage.&lt;/p&gt;

&lt;h2&gt;
  
  
  Further Reading
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;CH-Ops Installation Guide&lt;/li&gt;
&lt;li&gt;CH-Ops Official GitHub Repository&lt;/li&gt;
&lt;li&gt;CH-Ops Demo Video&lt;/li&gt;
&lt;/ul&gt;

</description>
      <category>database</category>
      <category>devops</category>
      <category>analytics</category>
    </item>
    <item>
      <title>Simplifying Authentication with Google, Keycloak, and Okta</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Tue, 27 Oct 2026 19:03:08 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/simplifying-authentication-with-google-keycloak-and-okta-2e2n</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/simplifying-authentication-with-google-keycloak-and-okta-2e2n</guid>
      <description>&lt;p&gt;Modern organizations rely on centralized identity providers to manage authentication securely. Instead of creating and maintaining separate usernames and passwords for every application, users expect to access applications using their existing organizational accounts.&lt;/p&gt;

&lt;p&gt;To provide a seamless and secure authentication experience, CH-OPS supports Single Sign-On (SSO) with multiple identity providers:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Google&lt;/li&gt;
&lt;li&gt;Keycloak&lt;/li&gt;
&lt;li&gt;Okta&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;With SSO, CH-OPS delegates user authentication to trusted identity providers while maintaining control over which users are authorized to access the application.&lt;/p&gt;

&lt;h2&gt;
  
  
  What Is Single Sign-On (SSO)?
&lt;/h2&gt;

&lt;p&gt;Single Sign-On (SSO) is an authentication mechanism that allows users to authenticate through an external identity provider and access an application without creating or managing a separate password for that application.&lt;/p&gt;

&lt;p&gt;Instead of CH-OPS directly handling user credentials, authentication is delegated to an identity provider such as Google, Keycloak, or Okta.&lt;/p&gt;

&lt;h3&gt;
  
  
  Benefits
&lt;/h3&gt;

&lt;ul&gt;
&lt;li&gt;Stronger security&lt;/li&gt;
&lt;li&gt;Reduced password fatigue&lt;/li&gt;
&lt;li&gt;Centralized identity management&lt;/li&gt;
&lt;li&gt;Faster login experience&lt;/li&gt;
&lt;li&gt;Easier onboarding and offboarding&lt;/li&gt;
&lt;li&gt;Support for enterprise security policies such as Multi-Factor Authentication (MFA)&lt;/li&gt;
&lt;li&gt;Integration with existing organizational identity infrastructure&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  SSO Providers Supported by CH-OPS
&lt;/h2&gt;

&lt;p&gt;CH-OPS currently supports three identity providers through OpenID Connect (OIDC).&lt;/p&gt;

&lt;h3&gt;
  
  
  Google
&lt;/h3&gt;

&lt;p&gt;Google authentication allows users to authenticate using their Google or Google Workspace accounts.&lt;/p&gt;

&lt;p&gt;Google SSO provides:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Familiar login experience&lt;/li&gt;
&lt;li&gt;OAuth 2.0 and OpenID Connect support&lt;/li&gt;
&lt;li&gt;Secure identity verification&lt;/li&gt;
&lt;li&gt;Google Workspace integration&lt;/li&gt;
&lt;li&gt;Provider-managed authentication and security policies&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;It is particularly useful for organizations already using Google Workspace.&lt;/p&gt;

&lt;h3&gt;
  
  
  Keycloak
&lt;/h3&gt;

&lt;p&gt;Keycloak is an open-source Identity and Access Management (IAM) platform commonly used in enterprise and self-hosted environments.&lt;/p&gt;

&lt;p&gt;CH-OPS integrates with Keycloak to support organizations that already manage their users through their own identity infrastructure.&lt;/p&gt;

&lt;p&gt;Keycloak provides capabilities such as:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Centralized user management&lt;/li&gt;
&lt;li&gt;Role-based access control&lt;/li&gt;
&lt;li&gt;Multi-factor authentication&lt;/li&gt;
&lt;li&gt;LDAP and Active Directory integration&lt;/li&gt;
&lt;li&gt;Custom identity providers&lt;/li&gt;
&lt;li&gt;Enterprise authentication policies&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Organizations using Keycloak can integrate CH-OPS without introducing another authentication system.&lt;/p&gt;

&lt;h3&gt;
  
  
  Okta
&lt;/h3&gt;

&lt;p&gt;Okta is an enterprise identity platform that provides centralized authentication and identity management.&lt;/p&gt;

&lt;p&gt;CH-OPS can use an organization's existing Okta environment to authenticate users.&lt;/p&gt;

&lt;p&gt;Okta provides features such as:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Enterprise-grade authentication&lt;/li&gt;
&lt;li&gt;Single Sign-On across applications&lt;/li&gt;
&lt;li&gt;Multi-factor authentication&lt;/li&gt;
&lt;li&gt;Centralized user administration&lt;/li&gt;
&lt;li&gt;Identity lifecycle management&lt;/li&gt;
&lt;li&gt;Enterprise security policies&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;This makes Okta a suitable choice for organizations that already use Okta as their identity provider.&lt;/p&gt;

&lt;h2&gt;
  
  
  How SSO Authentication Works in CH-OPS
&lt;/h2&gt;

&lt;p&gt;The CH-OPS SSO implementation uses OpenID Connect (OIDC) to communicate with external identity providers.&lt;/p&gt;

&lt;p&gt;An important part of the implementation is that authentication and authorization are treated as separate concerns.&lt;/p&gt;

&lt;p&gt;The identity provider verifies who the user is, while CH-OPS verifies whether that user is allowed to access the application.&lt;/p&gt;

&lt;h3&gt;
  
  
  Authentication Flow
&lt;/h3&gt;

&lt;p&gt;The complete authentication flow follows these steps:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;The user opens the CH-OPS login page.&lt;/li&gt;
&lt;li&gt;The user selects an SSO provider.&lt;/li&gt;
&lt;li&gt;CH-OPS initiates the authentication process.&lt;/li&gt;
&lt;li&gt;The user is redirected to Google, Keycloak, or Okta.&lt;/li&gt;
&lt;li&gt;The identity provider authenticates the user.&lt;/li&gt;
&lt;li&gt;The identity provider returns the authentication response to CH-OPS.&lt;/li&gt;
&lt;li&gt;CH-OPS validates the identity information.&lt;/li&gt;
&lt;li&gt;CH-OPS checks whether the user exists in its database.&lt;/li&gt;
&lt;li&gt;If the user is authorized, CH-OPS creates a session and redirects the user to the dashboard.&lt;/li&gt;
&lt;li&gt;If the user does not exist, access is denied.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Let's examine each step in detail.&lt;/p&gt;

&lt;h3&gt;
  
  
  1. User Selects an SSO Provider
&lt;/h3&gt;

&lt;p&gt;The CH-OPS login page provides SSO options for the supported identity providers.&lt;/p&gt;

&lt;p&gt;The user can choose:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Continue with Google&lt;/li&gt;
&lt;li&gt;Continue with Keycloak&lt;/li&gt;
&lt;li&gt;Continue with Okta&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The user selects the identity provider they want to use.&lt;/p&gt;

&lt;h3&gt;
  
  
  2. CH-OPS Initiates Authentication
&lt;/h3&gt;

&lt;p&gt;When the user clicks an SSO button, CH-OPS initiates the authentication process for the selected provider.&lt;/p&gt;

&lt;p&gt;The application determines which identity provider was selected and starts the corresponding OpenID Connect authentication flow.&lt;/p&gt;

&lt;p&gt;Conceptually, CH-OPS uses a provider-specific login route:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;/:provider/login&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;/google/login&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;&lt;code&gt;/keycloak/login&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;&lt;code&gt;/okta/login&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;This allows CH-OPS to maintain a common authentication process while supporting multiple identity providers.&lt;/p&gt;

&lt;h3&gt;
  
  
  3. Redirect to the Identity Provider
&lt;/h3&gt;

&lt;p&gt;After receiving the login request, CH-OPS initiates the OpenID Connect authentication flow.&lt;/p&gt;

&lt;p&gt;The user is redirected from CH-OPS to the selected identity provider through an authorization request.&lt;/p&gt;

&lt;p&gt;The identity provider is responsible for authenticating the user.&lt;/p&gt;

&lt;p&gt;CH-OPS does not ask the user for their identity-provider password. Instead, the user completes authentication using the provider's existing login mechanism.&lt;/p&gt;

&lt;p&gt;If the organization has additional security policies, such as Multi-Factor Authentication (MFA), those policies can also be enforced by the identity provider.&lt;/p&gt;

&lt;h3&gt;
  
  
  4. Identity Provider Returns the User Information
&lt;/h3&gt;

&lt;p&gt;After successful authentication, the identity provider sends the authorization response back to CH-OPS.&lt;/p&gt;

&lt;p&gt;Using OpenID Connect, CH-OPS can obtain the authenticated user's identity information.&lt;/p&gt;

&lt;p&gt;The identity information can contain details such as:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Subject or user ID&lt;/li&gt;
&lt;li&gt;Email address&lt;/li&gt;
&lt;li&gt;Name&lt;/li&gt;
&lt;li&gt;Other identity claims&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;CH-OPS then validates the identity information received from the provider.&lt;/p&gt;

&lt;h3&gt;
  
  
  5. CH-OPS Checks the User in the Database
&lt;/h3&gt;

&lt;p&gt;This is an important part of the CH-OPS architecture.&lt;/p&gt;

&lt;p&gt;Authentication through the identity provider does not automatically mean that the user is authorized to use CH-OPS.&lt;/p&gt;

&lt;p&gt;After receiving the user's identity, CH-OPS checks its application database using the user's email address.&lt;/p&gt;

&lt;p&gt;The result determines whether the user is allowed to access CH-OPS.&lt;/p&gt;

&lt;h4&gt;
  
  
  User Exists
&lt;/h4&gt;

&lt;p&gt;If the email address exists in the CH-OPS database, authentication continues and the user can access CH-OPS.&lt;/p&gt;

&lt;p&gt;The flow is:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;The identity provider verifies the user's identity.&lt;/li&gt;
&lt;li&gt;CH-OPS receives and validates the identity information.&lt;/li&gt;
&lt;li&gt;CH-OPS searches its database for the user's email address.&lt;/li&gt;
&lt;li&gt;The user is found.&lt;/li&gt;
&lt;li&gt;Authentication continues.&lt;/li&gt;
&lt;/ol&gt;

&lt;h4&gt;
  
  
  User Does Not Exist
&lt;/h4&gt;

&lt;p&gt;If the email address does not exist in the CH-OPS database, CH-OPS returns a &lt;strong&gt;User Not Found&lt;/strong&gt; response.&lt;/p&gt;

&lt;p&gt;The application does not automatically create a new user account.&lt;/p&gt;

&lt;p&gt;This provides an additional authorization layer and prevents anyone with a valid Google, Keycloak, or Okta account from automatically gaining access to CH-OPS.&lt;/p&gt;

&lt;h3&gt;
  
  
  6. Session Creation and Callback
&lt;/h3&gt;

&lt;p&gt;When the user is successfully found in the CH-OPS database, the authentication process continues.&lt;/p&gt;

&lt;p&gt;CH-OPS establishes the authenticated session and redirects the user to the configured callback URL.&lt;/p&gt;

&lt;p&gt;The final flow is:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;The SSO provider returns the OIDC response.&lt;/li&gt;
&lt;li&gt;CH-OPS validates the identity.&lt;/li&gt;
&lt;li&gt;CH-OPS checks the user's email address against its database.&lt;/li&gt;
&lt;li&gt;If the user is not found, access is denied.&lt;/li&gt;
&lt;li&gt;If the user is found, CH-OPS creates a session.&lt;/li&gt;
&lt;li&gt;The user is redirected to the callback URL.&lt;/li&gt;
&lt;li&gt;The CH-OPS dashboard becomes accessible.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The user can now access CH-OPS without having a separate CH-OPS password.&lt;/p&gt;

&lt;h2&gt;
  
  
  Authentication Flow Summary
&lt;/h2&gt;

&lt;p&gt;The complete CH-OPS authentication process can be summarized as follows:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Successful authentication:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;User → SSO Button → CH-OPS Authentication → Identity Provider → OpenID Connect → Identity Validation → Email Lookup → CH-OPS Database → Create Session → Callback URL → CH-OPS Dashboard&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Unsuccessful authorization:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;User → SSO Button → CH-OPS Authentication → Identity Provider → OpenID Connect → Identity Validation → Email Lookup → User Not Found → Access Denied&lt;/p&gt;

&lt;p&gt;This design gives CH-OPS both external identity verification and application-level access control.&lt;/p&gt;

&lt;h2&gt;
  
  
  Authentication vs. Authorization
&lt;/h2&gt;

&lt;p&gt;One of the important concepts in the CH-OPS SSO implementation is the separation between authentication and authorization.&lt;/p&gt;

&lt;h3&gt;
  
  
  Authentication
&lt;/h3&gt;

&lt;p&gt;Authentication answers the question:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Who is this user?&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Google, Keycloak, or Okta authenticates the user and provides the user's identity information through OpenID Connect.&lt;/p&gt;

&lt;h3&gt;
  
  
  Authorization
&lt;/h3&gt;

&lt;p&gt;Authorization answers the question:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Is this user allowed to access CH-OPS?&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;CH-OPS checks the user's email address against its own database.&lt;/p&gt;

&lt;p&gt;Therefore, having a valid Google, Keycloak, or Okta account does not automatically grant access to CH-OPS.&lt;/p&gt;

&lt;p&gt;The user must also exist in the CH-OPS database.&lt;/p&gt;

&lt;h2&gt;
  
  
  Security Features
&lt;/h2&gt;

&lt;p&gt;Security is a primary focus of the CH-OPS authentication architecture.&lt;/p&gt;

&lt;h3&gt;
  
  
  OpenID Connect
&lt;/h3&gt;

&lt;p&gt;CH-OPS uses OpenID Connect (OIDC) for standardized identity authentication.&lt;/p&gt;

&lt;p&gt;OIDC provides an identity layer on top of OAuth 2.0, allowing applications to verify users through supported identity providers.&lt;/p&gt;

&lt;h3&gt;
  
  
  OAuth 2.0
&lt;/h3&gt;

&lt;p&gt;The authentication flow uses OAuth 2.0 authorization mechanisms, with OpenID Connect providing the authentication and identity layer.&lt;/p&gt;

&lt;h3&gt;
  
  
  Provider-Side Password Management
&lt;/h3&gt;

&lt;p&gt;CH-OPS does not directly handle or store passwords for Google, Keycloak, or Okta users.&lt;/p&gt;

&lt;p&gt;Instead, users authenticate through their chosen identity provider.&lt;/p&gt;

&lt;h3&gt;
  
  
  Token Validation
&lt;/h3&gt;

&lt;p&gt;Identity information received from the provider is validated before the application continues authentication.&lt;/p&gt;

&lt;p&gt;This helps ensure that the application relies on a valid authentication response.&lt;/p&gt;

&lt;h3&gt;
  
  
  HTTPS
&lt;/h3&gt;

&lt;p&gt;Communication between CH-OPS and identity providers should use HTTPS to protect authentication data during transit.&lt;/p&gt;

&lt;h3&gt;
  
  
  Application-Level Authorization
&lt;/h3&gt;

&lt;p&gt;Even after successful authentication, CH-OPS checks whether the user exists in its database before granting access.&lt;/p&gt;

&lt;p&gt;This ensures that authentication by an external identity provider alone is not sufficient to access the application.&lt;/p&gt;

&lt;h3&gt;
  
  
  Multi-Factor Authentication
&lt;/h3&gt;

&lt;p&gt;Organizations can use MFA configured at their identity provider, allowing CH-OPS to benefit from the provider's authentication security policies.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why Support Multiple SSO Providers?
&lt;/h2&gt;

&lt;p&gt;Different organizations use different identity platforms.&lt;/p&gt;

&lt;p&gt;By supporting multiple providers, CH-OPS can integrate into existing authentication environments without forcing organizations to change their identity infrastructure.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Organization Type&lt;/th&gt;
&lt;th&gt;Recommended Provider&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Google Workspace users&lt;/td&gt;
&lt;td&gt;Google&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Open-source or self-hosted infrastructure&lt;/td&gt;
&lt;td&gt;Keycloak&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Enterprise identity management&lt;/td&gt;
&lt;td&gt;Okta&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;This flexibility allows CH-OPS to support different organizational environments while maintaining a consistent application-level authentication flow.&lt;/p&gt;

&lt;h2&gt;
  
  
  The User Experience
&lt;/h2&gt;

&lt;p&gt;From the user's perspective, the authentication process is simple.&lt;/p&gt;

&lt;p&gt;The user opens the CH-OPS login page, selects their organization's preferred SSO provider, completes authentication with that provider, and is redirected back to CH-OPS.&lt;/p&gt;

&lt;p&gt;If the user is already registered in CH-OPS, they can immediately access the application.&lt;/p&gt;

&lt;p&gt;There is no need to create or remember another password.&lt;/p&gt;

&lt;p&gt;Behind this simple experience, CH-OPS performs several security checks:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Choose an identity provider.&lt;/li&gt;
&lt;li&gt;Authenticate with the provider.&lt;/li&gt;
&lt;li&gt;Validate the identity.&lt;/li&gt;
&lt;li&gt;Check the CH-OPS user database.&lt;/li&gt;
&lt;li&gt;Create an authenticated session.&lt;/li&gt;
&lt;li&gt;Access the application.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The result is a login experience that is both simple for users and controlled by the application.&lt;/p&gt;

&lt;h2&gt;
  
  
  Conclusion
&lt;/h2&gt;

&lt;p&gt;Authentication should be secure, reliable, and effortless.&lt;/p&gt;

&lt;p&gt;CH-OPS achieves this by integrating with Google, Keycloak, and Okta using OpenID Connect.&lt;/p&gt;

&lt;p&gt;The architecture delegates user authentication to trusted identity providers while keeping application-level authorization under the control of CH-OPS.&lt;/p&gt;

&lt;p&gt;The key principle is simple:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;The identity provider verifies the user's identity; CH-OPS verifies whether that user is authorized to access the application.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;When a user clicks an SSO button, CH-OPS initiates the authentication process, communicates with the selected identity provider through OpenID Connect, retrieves the user's identity, and checks the user's email address against the CH-OPS database.&lt;/p&gt;

&lt;p&gt;If the user exists, CH-OPS establishes the session and redirects the user to the callback URL. If the user does not exist, access is denied with a User Not Found response.&lt;/p&gt;

&lt;p&gt;This approach provides CH-OPS with a flexible, secure, and centralized authentication mechanism while allowing organizations to continue using the identity infrastructure they already trust.&lt;/p&gt;

&lt;h2&gt;
  
  
  References
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;CH-Ops Installation Guide&lt;/li&gt;
&lt;li&gt;CH-Ops Official Repository&lt;/li&gt;
&lt;li&gt;CH-Ops Demo Video&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Next in the CHOps Series
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;CH-Ops Pro Scheduled Archival: Move ClickHouse® Data to S3&lt;/strong&gt;&lt;/p&gt;

</description>
      <category>devops</category>
      <category>clickhouse</category>
      <category>database</category>
    </item>
    <item>
      <title>Audit Logs in CHOps Pro: Who Did That?</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Fri, 23 Oct 2026 17:56:13 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/audit-logs-in-chops-pro-who-did-that-3ojj</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/audit-logs-in-chops-pro-who-did-that-3ojj</guid>
      <description>&lt;p&gt;Audit logs answer one of the most important questions in a shared ClickHouse® cluster: who did that?&lt;/p&gt;

&lt;p&gt;When a setting changes, a table disappears, or a node is restarted, CHOps Pro records who performed the action, what they changed, when and where it happened, and whether it succeeded.&lt;/p&gt;

&lt;p&gt;If nobody wrote it down, the answer is guesswork. That is the gap an audit log closes.&lt;/p&gt;

&lt;h2&gt;
  
  
  What an Audit Log Actually Is
&lt;/h2&gt;

&lt;p&gt;An audit log is a durable record of actions taken in an application: who did something, what they did, when they did it, where they connected from, and whether it worked.&lt;/p&gt;

&lt;p&gt;It works alongside the logs you already have, and each one answers a different question.&lt;/p&gt;

&lt;p&gt;Your application logs explain how the software behaved. ClickHouse®'s &lt;code&gt;system.query_log&lt;/code&gt; and &lt;code&gt;system.session_log&lt;/code&gt; show what reached each server.&lt;/p&gt;

&lt;p&gt;The CHOps audit log adds the human layer on top: what your team did through CHOps, tied to a person and the role they held, across every cluster you manage, in one place and kept for as long as your retention policy says.&lt;/p&gt;

&lt;p&gt;Run all three and you have the complete picture: the software's view, the database's view, and the people behind both.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why It Matters
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Accountability:&lt;/strong&gt; In an environment where several engineers share admin access, a name and a timestamp on every change turns a shrug into a fact.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Forensics:&lt;/strong&gt; When something breaks, the first useful question is "what changed just before this?" A filtered view of the last hour usually answers it faster than reading server logs.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Compliance evidence:&lt;/strong&gt; SOC 2, ISO 27001 and internal audits all ask you to demonstrate access control over production data. A searchable, exportable trail is the evidence.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Deterrence:&lt;/strong&gt; People are more careful when they know actions are recorded. That effect is free.&lt;/p&gt;

&lt;h2&gt;
  
  
  What CHOps Pro Records
&lt;/h2&gt;

&lt;p&gt;Every audited event captures a consistent set of fields:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Timestamp:&lt;/strong&gt; When the action happened.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;User and role:&lt;/strong&gt; Who performed it, and the role they held at the time.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Source IP:&lt;/strong&gt; Where the request came from.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Category and action:&lt;/strong&gt; What kind of change it was.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Target:&lt;/strong&gt; The type, ID and name of the thing that was changed.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;SQL text:&lt;/strong&gt; The statement, where the action involved one.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Cluster and node:&lt;/strong&gt; Which cluster and host the action was aimed at.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Status:&lt;/strong&gt; Success or error, with the error message on failure.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Failures are recorded alongside successes. A rejected attempt to change a setting is often more interesting than a successful one.&lt;/p&gt;

&lt;p&gt;Events are grouped into categories covering the operational surface of CHOps: clusters, users, settings, dashboards and charts, alert rules and channels, backups, application backups, API keys, remote admin, scheduled archival, and email reports.&lt;/p&gt;

&lt;p&gt;Within those categories, actions range from create, update, delete and alter through to password changes and resets, service start, stop and restart, manual and scheduled backups, key generation, and purges.&lt;/p&gt;

&lt;h2&gt;
  
  
  Key Features
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Two views&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;An Overview tab charts daily activity, events by category, the success-to-error split, and your ten busiest users. A Logs tab gives you the raw table, with each row expandable to its full detail.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Filter and search&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Narrow by user, category, action, status or date range, and run a free-text search across SQL text, target names and details.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Tamper-evident exports&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Export a filtered report to PDF and CHOps records a SHA-256 hash of that exact file. Send the PDF back to the verification endpoint later and CHOps confirms whether it is a genuine, unmodified export from your instance. This is useful when a report leaves your hands and goes to an auditor. Exports are themselves audited.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Admin-only access&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Every audit endpoint is restricted to administrators.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Isolated storage&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The audit trail lives in its own database, separate from application data, so its history stands on its own.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Never in the way&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Audit writes are fire-and-forget. A full or unavailable audit database cannot slow down or break the main application.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Built for volume&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Composite indexes and cursor-based pagination keep queries fast however deep the history runs.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Retention you control&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Set a retention window, which is 90 days by default. A nightly job trims older entries in batches and reclaims the disk space.&lt;/p&gt;

&lt;h2&gt;
  
  
  A Five-Minute Investigation
&lt;/h2&gt;

&lt;p&gt;Here is what using it actually looks like.&lt;/p&gt;

&lt;p&gt;Queries started failing on your staging cluster around 14:00, and nobody owns up to a change.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 1: Open the Overview tab.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The daily activity chart shows a bump in events yesterday afternoon, and the success-versus-error chart shows errors where there normally are none. Something happened, and some of it did not go cleanly.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 2: Narrow down the Logs tab.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Set the date range to cover the afternoon. Leave everything else open at first. You are looking for volume and shape, not a specific row.&lt;/p&gt;

&lt;p&gt;Twenty events. Three users.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 3: Filter by category.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Most of the twenty are routine dashboard edits. Two are settings changes. Filter to the settings category, and you are down to the two rows that could plausibly matter.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 4: Expand the row.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Each entry opens to its full detail: the user and the role they held, the source IP, the target cluster and node, the statement where one was involved, and the status.&lt;/p&gt;

&lt;p&gt;One of the two changes came back as an error, with the message attached.&lt;/p&gt;

&lt;p&gt;One expanded entry answers who, what, when, from where, against which cluster, and whether it worked.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 5: Export the report.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;If the answer needs to go to someone outside the team, export the filtered view to PDF.&lt;/p&gt;

&lt;p&gt;The report carries its own summary, a generated-by line, and page footers. CHOps keeps a hash of the file so its authenticity can be confirmed later.&lt;/p&gt;

&lt;p&gt;Five minutes, no guesswork, and a document at the end of it.&lt;/p&gt;

&lt;h2&gt;
  
  
  What to Audit in Your Own Application
&lt;/h2&gt;

&lt;p&gt;The specifics above are CHOps, but the design decisions behind them apply to any application adding an audit trail.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;1. Record failures, not just successes.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;A denied action is a security signal. An audit log that only contains things that worked will not show you someone probing at the edges of their permissions.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;2. Capture the actor's role at the time, not just their name.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Roles change. A record saying a user was an administrator when they made a change is worth much more six months later than a username you have to go and look up.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;3. Never let auditing break the request.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Audit writes should be fire-and-forget and wrapped so they cannot throw. If the audit store is full, unreachable or corrupt, the application must keep serving. A logging feature that can take production down is a liability.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;4. Keep it separate from application data.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;A distinct store means the trail is not caught up in migrations, resets or restores of your main database.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;5. Cap what you store per event.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Free-text fields and SQL statements need a length limit, or one pathological entry bloats the store. Strip control characters from anything user-supplied while you are at it. Log injection is a real technique.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;6. Plan retention on day one.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Decide how long you keep entries, delete in batches so cleanup never holds a long lock, and reclaim the space afterwards.&lt;/p&gt;

&lt;p&gt;Retention is also a compliance question: too short and you fail the audit; indefinite and you are storing personal data you cannot justify.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;7. Index for the queries you will actually run.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Audit tables are written constantly and read in narrow slices: this user, this window, this category.&lt;/p&gt;

&lt;p&gt;Composite indexes on those combinations, and cursor-based pagination instead of deep offsets, are the difference between a trail you use and one everyone avoids.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;8. Restrict who can read it.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;An audit log contains a map of your operational activity. Treat it as sensitive and put it behind your strongest role check.&lt;/p&gt;

&lt;h2&gt;
  
  
  Common Questions
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Does it slow the application down?&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;No. Writes are fire-and-forget and go to a local store that never sits in the request path.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;How much space does it need?&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;That depends on your team's activity, but retention keeps it bounded. Entries older than the window are removed nightly and the space is reclaimed.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Who can see the audit log?&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Administrators only.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Why not just use the database's own logs?&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;ClickHouse®'s system tables tell you what reached the server, per node, for as long as their own retention allows.&lt;/p&gt;

&lt;p&gt;They will not tell you who clicked what in your admin tool, tie an action to an application user and role, or span the clusters you manage.&lt;/p&gt;

&lt;p&gt;The two are complementary. Use both.&lt;/p&gt;

&lt;h2&gt;
  
  
  Conclusion
&lt;/h2&gt;

&lt;p&gt;When something changes in your ClickHouse® environment, you shouldn't have to guess who did it.&lt;/p&gt;

&lt;p&gt;CHOps Pro's audit log gives your team a clear, searchable record of every important action, from successful changes to failed attempts.&lt;/p&gt;

&lt;p&gt;With the right audit trail, investigations take minutes instead of hours, accountability is clear, and compliance evidence is always within reach.&lt;/p&gt;

&lt;p&gt;When the question is "Who did that?", CHOps Pro gives you the answer.&lt;/p&gt;

&lt;h2&gt;
  
  
  Getting It
&lt;/h2&gt;

&lt;p&gt;Audit logging ships in CHOps Pro, the commercial edition, alongside SSO features, scheduled archival, scheduled email reports, extended alerting and remote cluster management.&lt;/p&gt;

&lt;p&gt;The Community edition records who can log in and what role they hold; Pro records what they actually did.&lt;/p&gt;

&lt;p&gt;To enable it, visit &lt;a href="https://ch-ops.io" rel="noopener noreferrer"&gt;https://ch-ops.io&lt;/a&gt; or contact Quantrail™ Data.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Installation Guide:&lt;/strong&gt; CH-Ops Installation Guide&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Documentation:&lt;/strong&gt; &lt;a href="https://ch-ops.io/docs" rel="noopener noreferrer"&gt;https://ch-ops.io/docs&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;GitHub:&lt;/strong&gt; &lt;a href="https://github.com/Quantrail-Data/CH-Ops" rel="noopener noreferrer"&gt;https://github.com/Quantrail-Data/CH-Ops&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Next in the CHOps series:&lt;/strong&gt; Secure SSO in CH-OPS: Google, Keycloak &amp;amp; Okta&lt;/p&gt;

</description>
      <category>clickhouse</category>
      <category>analytics</category>
      <category>database</category>
    </item>
    <item>
      <title>Scheduled Email Reports in CH-Ops Pro for Your ClickHouse® Database</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Sat, 17 Oct 2026 07:24:36 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/scheduled-email-reports-in-ch-ops-pro-for-your-clickhouser-database-4p2f</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/scheduled-email-reports-in-ch-ops-pro-for-your-clickhouser-database-4p2f</guid>
      <description>&lt;h2&gt;
  
  
  Introduction
&lt;/h2&gt;

&lt;p&gt;Many ClickHouse® workflows involve generating the same report at regular intervals, reviewing the results, and sharing them with the team. When this process is handled manually, it can take time and require repeated effort, especially for reports that need to be delivered consistently.&lt;/p&gt;

&lt;p&gt;CH-Ops Pro simplifies this process with Scheduled Email Reports, allowing you to automate report generation and delivery. Define the SQL query once, set when it should be executed, choose the email channel and recipients, and let CH-Ops deliver the results automatically.&lt;/p&gt;

&lt;p&gt;In this article, we'll walk through how to set up and use Scheduled Email Reports in CH-Ops, from configuring the email channel to receiving the generated report.&lt;/p&gt;

&lt;h2&gt;
  
  
  What Is CH-Ops?
&lt;/h2&gt;

&lt;p&gt;CH-Ops is a browser-based operations platform for ClickHouse® that provides a visual interface for managing and monitoring ClickHouse® deployments.&lt;/p&gt;

&lt;p&gt;Instead of relying entirely on the command line or HTTP API, CH-Ops brings common operational tasks into a single web application. It supports capabilities such as running SQL queries, monitoring cluster health, managing users, configuring backups and alerts, and working with dashboards and reports.&lt;/p&gt;

&lt;p&gt;Scheduled Email Reports is a CH-Ops Pro feature that extends these capabilities to recurring reporting workflows, allowing query results to be delivered automatically to specified email recipients.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why Use Scheduled Email Reports in CH-Ops Pro?
&lt;/h2&gt;

&lt;p&gt;Reports generated at regular intervals often require the same query to be executed repeatedly. Manually running the query, reviewing the results, and sending them to the appropriate people adds repetitive work to an otherwise straightforward process.&lt;/p&gt;

&lt;p&gt;Scheduled Email Reports automate these steps.&lt;/p&gt;

&lt;p&gt;With CH-Ops Pro, you can:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Define the SQL query once.&lt;/li&gt;
&lt;li&gt;Validate the query before scheduling it.&lt;/li&gt;
&lt;li&gt;Specify when the query should run.&lt;/li&gt;
&lt;li&gt;Select an email notification channel.&lt;/li&gt;
&lt;li&gt;Configure recipients and the email subject.&lt;/li&gt;
&lt;li&gt;Execute the report against a selected ClickHouse® cluster.&lt;/li&gt;
&lt;li&gt;Receive the query results automatically by email.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;This is useful for recurring operational reports such as query-performance summaries, usage reports, or other ClickHouse® metrics that teams need to review regularly.&lt;/p&gt;

&lt;h2&gt;
  
  
  How Scheduled Email Reports Work
&lt;/h2&gt;

&lt;p&gt;The Scheduled Email Reports workflow in CH-Ops Pro follows a simple process.&lt;/p&gt;

&lt;p&gt;First, configure an email notification channel with the required SMTP settings. Next, create and configure the report with its SQL query, schedule, email details, and ClickHouse® cluster. CH-Ops then lets you validate the query, test the configuration, and receive the results by email.&lt;/p&gt;

&lt;p&gt;Let's walk through the process.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 1: Configure an Email Notification Channel
&lt;/h2&gt;

&lt;p&gt;Before creating a Scheduled Email Report, configure an email notification channel. CH-Ops uses this channel to connect to your SMTP server and deliver the generated reports by email.&lt;/p&gt;

&lt;p&gt;First, open:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Custom Alerts → Scheduled Reports&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;If no email channel has been configured, CH-Ops displays a message prompting you to create one.&lt;/p&gt;

&lt;p&gt;To configure the channel, go to:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Control Panel → Notification Channels&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Create a new notification channel and select &lt;strong&gt;Email (SMTP)&lt;/strong&gt; as the channel type.&lt;/p&gt;

&lt;p&gt;Enter the SMTP details required by your email provider:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Name:&lt;/strong&gt; A name used to identify the email channel.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;SMTP Host:&lt;/strong&gt; The hostname of the SMTP server.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;SMTP Port:&lt;/strong&gt; The port used by the SMTP server.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;SMTP User:&lt;/strong&gt; The SMTP account used for authentication.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;SMTP Password:&lt;/strong&gt; The password for the SMTP account.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;From:&lt;/strong&gt; The email address used as the sender.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;To:&lt;/strong&gt; The recipient address or addresses for the channel.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;For this walkthrough, the email channel is named &lt;code&gt;Testing-channel&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;After entering the required details, click &lt;strong&gt;Create&lt;/strong&gt;. Once the channel is created successfully, it becomes available for selection when you configure a Scheduled Email Report.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Note:&lt;/strong&gt; Use the SMTP settings provided by your email service or organization, and keep SMTP credentials secure.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 2: Create and Configure a Scheduled Email Report
&lt;/h2&gt;

&lt;p&gt;After configuring the email channel, navigate to:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Custom Alerts → Scheduled Reports&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;If no reports have been created yet, the Scheduled Email Reports page displays an empty state with the &lt;strong&gt;New Report&lt;/strong&gt; button.&lt;/p&gt;

&lt;p&gt;Click &lt;strong&gt;New Report&lt;/strong&gt; to open the report configuration form.&lt;/p&gt;

&lt;p&gt;The New Report form brings the complete report configuration into one place. It includes the report details, SQL query, schedule, email delivery settings, ClickHouse® cluster, and enabled status.&lt;/p&gt;

&lt;h3&gt;
  
  
  Configure the Report Details
&lt;/h3&gt;

&lt;p&gt;In the New Report form, configure the following fields:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Report Name:&lt;/strong&gt; Enter a descriptive name for the report, such as &lt;code&gt;ClickHouse® Slow Query Report&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Description:&lt;/strong&gt; Add a brief description of the report. This description is included in the generated email. For example: &lt;code&gt;Top 15 queries by average execution time over the last 24 hours.&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;SQL Query:&lt;/strong&gt; Enter the ClickHouse® query to execute when the report runs. In this example, the query uses &lt;code&gt;system.query_log&lt;/code&gt; to summarize query performance, including average execution time and number of executions. Aggregated results are generally preferable for scheduled reports.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Cron Schedule:&lt;/strong&gt; Define when the report should run using a cron expression. For example, &lt;code&gt;0 8 * * *&lt;/code&gt; runs the report every day at 8:00 AM.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Email Channel (SMTP):&lt;/strong&gt; Select the email channel configured in Step 1, such as &lt;code&gt;Testing-channel&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Recipient Email(s):&lt;/strong&gt; Enter the email address or addresses that should receive the report.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Custom Subject:&lt;/strong&gt; Customize the email subject using placeholders such as &lt;code&gt;{name}&lt;/code&gt; and &lt;code&gt;{date}&lt;/code&gt;. For example, &lt;code&gt;{name} - {date}&lt;/code&gt; includes the report name and execution date in the subject.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Cluster:&lt;/strong&gt; Select the ClickHouse® cluster where the report query should execute, such as &lt;code&gt;Default cluster&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Enabled:&lt;/strong&gt; Keep the option enabled to allow CH-Ops to execute and send the report according to the configured schedule.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Step 3: Validate the SQL Query
&lt;/h2&gt;

&lt;p&gt;Before creating the report, click &lt;strong&gt;Validate SQL&lt;/strong&gt; to check the query and preview its results.&lt;/p&gt;

&lt;p&gt;CH-Ops displays a validation message along with a preview of the returned data, including the number of rows and columns and sample fields such as &lt;code&gt;query&lt;/code&gt;, &lt;code&gt;avg_ms&lt;/code&gt;, and &lt;code&gt;runs&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;In this example, the interface shows:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;Query valid. Preview: 5 rows, 3 columns.&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;Review the preview to confirm that the query returns the expected data. Then close the preview and click &lt;strong&gt;Create Report&lt;/strong&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 4: View and Test the Scheduled Report
&lt;/h2&gt;

&lt;p&gt;After the report is created, it appears on the Scheduled Email Reports page.&lt;/p&gt;

&lt;p&gt;The report card provides a quick overview of the configured report, including its:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Report name&lt;/li&gt;
&lt;li&gt;Description&lt;/li&gt;
&lt;li&gt;Schedule&lt;/li&gt;
&lt;li&gt;Email channel&lt;/li&gt;
&lt;li&gt;Recipient&lt;/li&gt;
&lt;li&gt;Enabled status&lt;/li&gt;
&lt;li&gt;Run status&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The report card provides actions such as &lt;strong&gt;Edit&lt;/strong&gt;, &lt;strong&gt;Send Test&lt;/strong&gt;, &lt;strong&gt;Enable/Disable&lt;/strong&gt;, and &lt;strong&gt;Delete&lt;/strong&gt;.&lt;/p&gt;

&lt;h3&gt;
  
  
  Test the Report
&lt;/h3&gt;

&lt;p&gt;Before waiting for the scheduled execution, use &lt;strong&gt;Send Test&lt;/strong&gt; to verify the report configuration and email delivery.&lt;/p&gt;

&lt;p&gt;A test run helps confirm that:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;The SQL query executes successfully.&lt;/li&gt;
&lt;li&gt;The expected results are generated.&lt;/li&gt;
&lt;li&gt;The configured email channel is working.&lt;/li&gt;
&lt;li&gt;The report is delivered to the intended recipient.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;This provides a quick way to verify the complete configuration before relying on the scheduled execution.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 5: Receive the Report by Email
&lt;/h2&gt;

&lt;p&gt;Once the report runs, CH-Ops sends the generated results to the configured recipients.&lt;/p&gt;

&lt;p&gt;The generated email contains the report information along with the results returned by the SQL query.&lt;/p&gt;

&lt;p&gt;The email includes several useful sections.&lt;/p&gt;

&lt;h3&gt;
  
  
  Report Information
&lt;/h3&gt;

&lt;p&gt;The email header displays the report name and execution timestamp.&lt;/p&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;ClickHouse® Slow Query Report&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;The email also includes the time at which the report was generated.&lt;/p&gt;

&lt;h3&gt;
  
  
  Report Details
&lt;/h3&gt;

&lt;p&gt;The generated email presents the report information and query results in a structured format.&lt;/p&gt;

&lt;h3&gt;
  
  
  Query Results
&lt;/h3&gt;

&lt;p&gt;The SQL query results are presented in a structured table.&lt;/p&gt;

&lt;p&gt;For the example slow-query report, the results include:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;query:&lt;/strong&gt; The executed query text.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;avg_ms:&lt;/strong&gt; Average query execution time.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;runs:&lt;/strong&gt; Number of executions.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;This allows recipients to review the recurring report directly from their inbox without manually opening CH-Ops and running the query each time.&lt;/p&gt;

&lt;h2&gt;
  
  
  Managing Scheduled Email Reports
&lt;/h2&gt;

&lt;p&gt;After creation, reports can be managed from the Scheduled Email Reports page.&lt;/p&gt;

&lt;p&gt;CH-Ops allows you to:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Edit an existing report.&lt;/li&gt;
&lt;li&gt;Enable or disable a report.&lt;/li&gt;
&lt;li&gt;Send a test report.&lt;/li&gt;
&lt;li&gt;Delete a report.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;These options make it easier to maintain scheduled reports as reporting requirements change.&lt;/p&gt;

&lt;h2&gt;
  
  
  Use Cases for Scheduled Email Reports
&lt;/h2&gt;

&lt;p&gt;Scheduled Email Reports can simplify a variety of recurring ClickHouse® reporting tasks, including:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Query Performance Reports&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Monitor slow, frequently executed, or resource-intensive queries.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Daily Operational Reports&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Receive daily summaries of ClickHouse® activity, system metrics, or other operational data.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Usage Reports&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Track recurring usage trends and other operational information.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Team Reports&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Automatically share recurring query results with relevant teams and stakeholders.&lt;/p&gt;

&lt;p&gt;With customizable SQL queries, schedules, and email recipients, CH-Ops makes it easy to automate recurring reports for different operational needs.&lt;/p&gt;

&lt;h2&gt;
  
  
  Conclusion
&lt;/h2&gt;

&lt;p&gt;Scheduled Email Reports in CH-Ops Pro provide a simple way to automate recurring ClickHouse® reporting. Instead of manually running the same queries and sharing their results, you can configure a report once and let CH-Ops Pro handle its scheduled execution and email delivery.&lt;/p&gt;

&lt;p&gt;The workflow brings everything together in a single process:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Configure Email → Configure Report → Validate SQL → Create and Test → Receive Report&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;With customizable queries, schedules, email recipients, and ClickHouse® clusters, CH-Ops helps teams reduce repetitive reporting work and receive timely ClickHouse® insights directly in their inbox.&lt;/p&gt;

&lt;h2&gt;
  
  
  Explore CH-Ops
&lt;/h2&gt;

&lt;p&gt;Want to learn more about CH-Ops and explore its features? Visit the official website and explore these resources:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;CH-Ops Official Website&lt;/li&gt;
&lt;li&gt;Getting Started with CH-Ops&lt;/li&gt;
&lt;li&gt;CH-Ops Official GitHub Repository&lt;/li&gt;
&lt;li&gt;CH-Ops Demo Video&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Next in the CH-Ops series:&lt;/strong&gt; ClickHouse® Alerts in Slack, Teams, Google Chat, and PagerDuty.&lt;/p&gt;

</description>
      <category>clickhouse</category>
      <category>devops</category>
      <category>analytics</category>
    </item>
    <item>
      <title>Manage ClickHouse® Without SSH with CH-Ops Pro</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Wed, 14 Oct 2026 10:26:37 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/manage-clickhouser-without-ssh-with-ch-ops-pro-ml9</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/manage-clickhouser-without-ssh-with-ch-ops-pro-ml9</guid>
      <description>&lt;p&gt;You need to restart ClickHouse®.&lt;/p&gt;

&lt;p&gt;So you open a terminal.&lt;/p&gt;

&lt;p&gt;SSH into the server.&lt;/p&gt;

&lt;p&gt;Check whether the service is running.&lt;/p&gt;

&lt;p&gt;Look through the logs.&lt;/p&gt;

&lt;p&gt;Maybe edit a configuration file.&lt;/p&gt;

&lt;p&gt;Restart ClickHouse® again.&lt;/p&gt;

&lt;p&gt;Then check the logs one more time.&lt;/p&gt;

&lt;p&gt;None of these steps are particularly difficult.&lt;/p&gt;

&lt;p&gt;But there is a simple question worth asking:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Do you really need SSH for all of them?&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;If you're already managing your ClickHouse® deployment from a browser, having to leave that interface every time you need to inspect a log, change a server setting, or restart the service creates an unnecessary gap between database administration and server administration.&lt;/p&gt;

&lt;p&gt;That's what Remote Admin in CH-Ops Pro is designed to solve.&lt;/p&gt;

&lt;p&gt;With Remote Admin, you can manage the ClickHouse® service, search ClickHouse® and journalctl logs, and update ClickHouse® server configuration directly from CH-Ops.&lt;/p&gt;

&lt;p&gt;No SSH session is required for those routine operations.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Part of ClickHouse® Administration That Usually Lives in SSH
&lt;/h2&gt;

&lt;p&gt;CH-Ops gives you a browser-based interface for managing ClickHouse®.&lt;/p&gt;

&lt;p&gt;You can work with your databases, queries, monitoring, and other ClickHouse® operations.&lt;/p&gt;

&lt;p&gt;But eventually, you run into questions that SQL alone can't answer:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Is the ClickHouse® service running?&lt;/li&gt;
&lt;li&gt;Why did the service stop?&lt;/li&gt;
&lt;li&gt;What did ClickHouse® log before it stopped?&lt;/li&gt;
&lt;li&gt;What does the system journal say?&lt;/li&gt;
&lt;li&gt;What configuration is currently applied?&lt;/li&gt;
&lt;li&gt;Can I change that configuration and restart the service?&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Traditionally, those answers live on the server itself.&lt;/p&gt;

&lt;p&gt;So the workflow becomes:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;CH-Ops
   |
   | Database operations
   v
ClickHouse®

SSH
   |
   +-- systemctl
   +-- journalctl
   +-- log files
   +-- configuration files
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;You're using two different interfaces to manage one ClickHouse® deployment.&lt;/p&gt;

&lt;p&gt;Remote Admin brings those routine server-side operations into CH-Ops Pro.&lt;/p&gt;

&lt;h2&gt;
  
  
  What Remote Admin Gives You
&lt;/h2&gt;

&lt;p&gt;Remote Admin focuses on three areas:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Service Management&lt;/li&gt;
&lt;li&gt;Logs&lt;/li&gt;
&lt;li&gt;Server Settings&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Together, they cover many of the routine tasks you would otherwise perform through SSH.&lt;/p&gt;

&lt;p&gt;You can check the ClickHouse® service state, start or stop the service, restart ClickHouse®, search server logs, inspect journalctl logs, and modify ClickHouse® configuration files.&lt;/p&gt;

&lt;p&gt;The idea is simple:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Manage more of your ClickHouse® deployment from one place.&lt;/strong&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Manage the ClickHouse® Service
&lt;/h2&gt;

&lt;p&gt;Start with the most basic question:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Is ClickHouse® actually running?&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The Service Management page gives you the current state of the ClickHouse® service.&lt;/p&gt;

&lt;p&gt;You can see information such as:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Load state&lt;/li&gt;
&lt;li&gt;Active state&lt;/li&gt;
&lt;li&gt;Sub-state&lt;/li&gt;
&lt;li&gt;Unit state&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;So instead of connecting to the server and running:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;systemctl status clickhouse-server
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;you can check the service state directly from CH-Ops Pro.&lt;/p&gt;

&lt;p&gt;For example:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Load State     loaded
Active State   active
Sub State      running
Unit State     enabled
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This gives you an immediate view of whether the service is loaded, active, and running.&lt;/p&gt;

&lt;h3&gt;
  
  
  Restart ClickHouse® Without SSH
&lt;/h3&gt;

&lt;p&gt;Configuration changes are one common reason you might need to restart ClickHouse®.&lt;/p&gt;

&lt;p&gt;Normally, that means running:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="nb"&gt;sudo &lt;/span&gt;systemctl restart clickhouse-server
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;With Remote Admin, you can perform the same operational task through the interface.&lt;/p&gt;

&lt;p&gt;Select &lt;strong&gt;Restart ClickHouse®&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;The service controls also take the current state of the service into account.&lt;/p&gt;

&lt;p&gt;So you aren't simply presented with a collection of terminal commands disguised as buttons.&lt;/p&gt;

&lt;p&gt;You can check the state, perform the required action, and verify the result from the same page.&lt;/p&gt;

&lt;h3&gt;
  
  
  Stop ClickHouse® When You Need To
&lt;/h3&gt;

&lt;p&gt;Remote Admin also provides a &lt;strong&gt;Stop ClickHouse®&lt;/strong&gt; action when the service is running.&lt;/p&gt;

&lt;p&gt;This can be useful during maintenance or when you need to intentionally take a ClickHouse® node offline.&lt;/p&gt;

&lt;p&gt;The workflow is straightforward:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Check service
     |
     v
Choose action
     |
     +---- Start
     |
     +---- Stop
     |
     +---- Restart
     |
     v
Verify state
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;No terminal required.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Search ClickHouse® Logs Without Opening a Terminal
&lt;/h2&gt;

&lt;p&gt;Service status tells you what is happening.&lt;/p&gt;

&lt;p&gt;Logs often tell you why.&lt;/p&gt;

&lt;p&gt;When ClickHouse® starts behaving unexpectedly, the usual response is to SSH into the server and start searching through log files.&lt;/p&gt;

&lt;p&gt;You might use:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="nb"&gt;grep
&lt;/span&gt;less
&lt;span class="nb"&gt;tail
&lt;/span&gt;journalctl
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Then narrow things down by timestamp, severity, component, or keyword.&lt;/p&gt;

&lt;p&gt;The Logs section in CH-Ops Pro puts that investigation into the same interface.&lt;/p&gt;

&lt;p&gt;The Search interface lets you filter logs using:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;From&lt;/li&gt;
&lt;li&gt;To&lt;/li&gt;
&lt;li&gt;Level&lt;/li&gt;
&lt;li&gt;Limit&lt;/li&gt;
&lt;li&gt;Offset&lt;/li&gt;
&lt;li&gt;Component&lt;/li&gt;
&lt;li&gt;Keyword&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Available log levels include:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Trace&lt;/li&gt;
&lt;li&gt;Debug&lt;/li&gt;
&lt;li&gt;Information&lt;/li&gt;
&lt;li&gt;Warning&lt;/li&gt;
&lt;li&gt;Error&lt;/li&gt;
&lt;li&gt;Fatal&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Instead of manually searching through a large log file, you can narrow the results to exactly what you're looking for.&lt;/p&gt;

&lt;h3&gt;
  
  
  Search the Time Window That Actually Matters
&lt;/h3&gt;

&lt;p&gt;Suppose ClickHouse® started producing errors around 11:20.&lt;/p&gt;

&lt;p&gt;You don't necessarily need to inspect the entire day's logs.&lt;/p&gt;

&lt;p&gt;Start with the incident window:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;From:  11:20
To:    11:30
Level: Error
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Then add a component or keyword if you need to narrow the results further.&lt;/p&gt;

&lt;p&gt;The question changes from:&lt;/p&gt;

&lt;p&gt;"What's in the ClickHouse® log?"&lt;/p&gt;

&lt;p&gt;to:&lt;/p&gt;

&lt;p&gt;"What errors did ClickHouse® produce during the period when the incident started?"&lt;/p&gt;

&lt;p&gt;That's a much easier question to answer.&lt;/p&gt;

&lt;p&gt;And during an incident, reducing the amount of information you have to scan can make troubleshooting considerably faster.&lt;/p&gt;

&lt;h3&gt;
  
  
  ClickHouse® Logs Aren't the Only Source of Information
&lt;/h3&gt;

&lt;p&gt;Sometimes the ClickHouse® server log isn't enough.&lt;/p&gt;

&lt;p&gt;The system journal can provide additional information about what happened to the ClickHouse® service.&lt;/p&gt;

&lt;p&gt;That's where &lt;code&gt;journalctl&lt;/code&gt; becomes useful.&lt;/p&gt;

&lt;p&gt;Instead of leaving CH-Ops and running:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;journalctl &lt;span class="nt"&gt;-u&lt;/span&gt; clickhouse-server
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;you can work with journal logs through the Remote Admin Logs section.&lt;/p&gt;

&lt;p&gt;This gives you two useful perspectives:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;ClickHouse® logs:&lt;/strong&gt; Database-level events.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;journalctl:&lt;/strong&gt; Service-level events.&lt;/p&gt;

&lt;p&gt;Together, they provide better troubleshooting context.&lt;/p&gt;

&lt;p&gt;You can investigate both without switching to a terminal.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Change ClickHouse® Configuration Without SSH
&lt;/h2&gt;

&lt;p&gt;Now we get to one of the most useful parts of Remote Admin.&lt;/p&gt;

&lt;p&gt;ClickHouse® configuration is file-based.&lt;/p&gt;

&lt;p&gt;Custom configuration is commonly placed under:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;/etc/clickhouse-server/config.d/
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;For example:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;config.d/custom.xml
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Normally, changing that file means connecting to the server first:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;ssh server
&lt;span class="nb"&gt;cd&lt;/span&gt; /etc/clickhouse-server/config.d
vim custom.xml
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Then you need to validate the configuration.&lt;/p&gt;

&lt;p&gt;Save it.&lt;/p&gt;

&lt;p&gt;Restart ClickHouse®.&lt;/p&gt;

&lt;p&gt;And finally, check whether ClickHouse® came back successfully.&lt;/p&gt;

&lt;p&gt;With CH-Ops Pro, you can edit the configuration directly from Server Settings.&lt;/p&gt;

&lt;p&gt;The interface provides access to:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;config.d/custom.xml
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Along with actions for:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Reload&lt;/li&gt;
&lt;li&gt;Validate XML&lt;/li&gt;
&lt;li&gt;Save Configuration&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;So a configuration change doesn't require you to open an SSH session just to find and edit the file.&lt;/p&gt;

&lt;h3&gt;
  
  
  Validate Before Saving
&lt;/h3&gt;

&lt;p&gt;This is an important part of the configuration workflow.&lt;/p&gt;

&lt;p&gt;ClickHouse® configuration uses XML, and a malformed configuration can cause problems when ClickHouse® attempts to load it.&lt;/p&gt;

&lt;p&gt;A missing closing tag or incorrect XML structure is enough to cause trouble.&lt;/p&gt;

&lt;p&gt;That's why Server Settings provides a dedicated &lt;strong&gt;Validate XML&lt;/strong&gt; action.&lt;/p&gt;

&lt;p&gt;The interface also warns:&lt;/p&gt;

&lt;p&gt;"Invalid XML may cause ClickHouse® to fail starting."&lt;/p&gt;

&lt;p&gt;The workflow becomes:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Create/Edit configuration
       |
       v
  Validate XML
       |
       +---- Invalid
       |       |
       |       v
       |      Fix
       |
       v
Save Configuration
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;You get an opportunity to catch an XML problem before saving the configuration.&lt;/p&gt;

&lt;h3&gt;
  
  
  Configuration Changes Still Require a Restart
&lt;/h3&gt;

&lt;p&gt;There is one important thing to keep in mind.&lt;/p&gt;

&lt;p&gt;Remote Admin doesn't make every ClickHouse® configuration change dynamic.&lt;/p&gt;

&lt;p&gt;For the configuration managed through Server Settings, changes require a service restart to take effect.&lt;/p&gt;

&lt;p&gt;So the complete workflow is:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Create/Edit
  |
  v
Validate XML
  |
  v
Save Configuration
  |
  v
Restart ClickHouse®
  |
  v
Verify service
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;And because Service Management is part of the same Remote Admin experience, you can move directly from changing the configuration to restarting the service.&lt;/p&gt;

&lt;h2&gt;
  
  
  Put the Three Together
&lt;/h2&gt;

&lt;p&gt;This is where Remote Admin becomes more than three separate features.&lt;/p&gt;

&lt;p&gt;Imagine a ClickHouse® node suddenly starts failing.&lt;/p&gt;

&lt;p&gt;You don't know whether the problem is:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;The ClickHouse® service&lt;/li&gt;
&lt;li&gt;A configuration change&lt;/li&gt;
&lt;li&gt;An application error&lt;/li&gt;
&lt;li&gt;A service-level failure&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;You can work through the investigation from one place.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 1: Check the Service
&lt;/h3&gt;

&lt;p&gt;Open:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Remote Admin → Service Management&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Check whether ClickHouse® is active and running.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 2: Investigate the Logs
&lt;/h3&gt;

&lt;p&gt;Open:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Remote Admin → Logs&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Search around the time the problem started.&lt;/p&gt;

&lt;p&gt;Start with:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Level: Error
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Then narrow the results using a component or keyword.&lt;/p&gt;

&lt;p&gt;If necessary, inspect the journalctl logs as well.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 3: Check the Configuration
&lt;/h3&gt;

&lt;p&gt;If the logs point towards a configuration problem, open:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Remote Admin → Server Settings&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Inspect the relevant configuration file.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 4: Make the Change
&lt;/h3&gt;

&lt;p&gt;Update the configuration.&lt;/p&gt;

&lt;p&gt;Run:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Validate XML&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;If the configuration is valid, save it.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 5: Restart ClickHouse®
&lt;/h3&gt;

&lt;p&gt;Return to Service Management.&lt;/p&gt;

&lt;p&gt;Select:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Restart ClickHouse®&lt;/strong&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 6: Verify
&lt;/h3&gt;

&lt;p&gt;Check the service state again.&lt;/p&gt;

&lt;p&gt;If everything is healthy, you're done.&lt;/p&gt;

&lt;p&gt;If it isn't, go back to the Logs section and continue investigating.&lt;/p&gt;

&lt;p&gt;The workflow becomes:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Service State
     |
     v
   Logs
     |
     v
Configuration
     |
     v
  Restart
     |
     v
  Verify
     |
     +--------&amp;gt; Logs
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That's the real value of Remote Admin.&lt;/p&gt;

&lt;p&gt;It's not simply about replacing &lt;code&gt;systemctl&lt;/code&gt; with a button.&lt;/p&gt;

&lt;p&gt;It's about keeping the &lt;strong&gt;investigate → change → restart → verify&lt;/strong&gt; workflow together.&lt;/p&gt;

&lt;h2&gt;
  
  
  You Still Have SSH When You Need It
&lt;/h2&gt;

&lt;p&gt;Remote Admin isn't intended to replace SSH.&lt;/p&gt;

&lt;p&gt;There will always be situations where direct server access is necessary.&lt;/p&gt;

&lt;p&gt;Maybe you're debugging networking.&lt;/p&gt;

&lt;p&gt;Maybe you need to inspect the filesystem.&lt;/p&gt;

&lt;p&gt;Maybe you're investigating an operating-system issue.&lt;/p&gt;

&lt;p&gt;Maybe you need to perform an operation that isn't exposed through Remote Admin.&lt;/p&gt;

&lt;p&gt;SSH remains the right tool for those situations.&lt;/p&gt;

&lt;p&gt;The idea is much simpler:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Don't use SSH when you don't need SSH.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;If the task is checking ClickHouse® status, inspecting logs, changing a supported configuration file, or restarting the service, you shouldn't necessarily have to leave your ClickHouse® administration interface.&lt;/p&gt;

&lt;h2&gt;
  
  
  Less Switching. More ClickHouse® Administration.
&lt;/h2&gt;

&lt;p&gt;Running ClickHouse® isn't only about writing SQL.&lt;/p&gt;

&lt;p&gt;At some point, you have to deal with the service underneath the database.&lt;/p&gt;

&lt;p&gt;You need to know:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Is the service running?&lt;/li&gt;
&lt;li&gt;Why did it stop?&lt;/li&gt;
&lt;li&gt;What does the log say?&lt;/li&gt;
&lt;li&gt;Did the configuration change?&lt;/li&gt;
&lt;li&gt;Can I safely restart it?&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Traditionally, those questions live outside the database interface.&lt;/p&gt;

&lt;p&gt;Service state lives in systemd.&lt;/p&gt;

&lt;p&gt;Logs live in log files and the system journal.&lt;/p&gt;

&lt;p&gt;Configuration lives in XML files.&lt;/p&gt;

&lt;p&gt;And the administrator moves between all of them through SSH.&lt;/p&gt;

&lt;p&gt;CH-Ops Pro Remote Admin brings those routine operational tasks into the ClickHouse® administration workflow.&lt;/p&gt;

&lt;p&gt;You still have the server.&lt;/p&gt;

&lt;p&gt;You still have the underlying system tools.&lt;/p&gt;

&lt;p&gt;But for common ClickHouse® administration tasks, you don't necessarily need to open another terminal.&lt;/p&gt;

&lt;h2&gt;
  
  
  Manage ClickHouse® Without Leaving CH-Ops Pro
&lt;/h2&gt;

&lt;p&gt;With CH-Ops Pro Remote Admin, you can:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Check ClickHouse® service state&lt;/li&gt;
&lt;li&gt;Start and stop ClickHouse®&lt;/li&gt;
&lt;li&gt;Restart ClickHouse®&lt;/li&gt;
&lt;li&gt;Search ClickHouse® server logs&lt;/li&gt;
&lt;li&gt;Search journalctl logs&lt;/li&gt;
&lt;li&gt;Filter logs by time, level, component, and keyword&lt;/li&gt;
&lt;li&gt;Edit ClickHouse® configuration&lt;/li&gt;
&lt;li&gt;Validate XML before saving&lt;/li&gt;
&lt;li&gt;Apply configuration changes&lt;/li&gt;
&lt;li&gt;Restart and verify the service&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The important part isn't that CH-Ops Pro gives you graphical versions of &lt;code&gt;systemctl&lt;/code&gt; or &lt;code&gt;journalctl&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;It's that database administration and server administration no longer have to feel like two completely separate workflows.&lt;/p&gt;

&lt;p&gt;Less context switching.&lt;/p&gt;

&lt;p&gt;Fewer SSH sessions for routine tasks.&lt;/p&gt;

&lt;p&gt;One interface for managing your ClickHouse® deployment.&lt;/p&gt;

&lt;p&gt;That's Remote Admin in CH-Ops Pro.&lt;/p&gt;

&lt;h2&gt;
  
  
  Next in CH-Ops Series
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;CH-Ops: A ClickHouse® Admin Tool Built for Simplicity&lt;/strong&gt;&lt;/p&gt;

</description>
      <category>clickhouse</category>
      <category>devops</category>
      <category>database</category>
    </item>
    <item>
      <title>CH-Ops: A ClickHouse® Admin Tool Built for Simplicity</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Sat, 10 Oct 2026 05:58:45 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/ch-ops-a-clickhouser-admin-tool-built-for-simplicity-3l1k</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/ch-ops-a-clickhouser-admin-tool-built-for-simplicity-3l1k</guid>
      <description>&lt;h2&gt;
  
  
  Introduction
&lt;/h2&gt;

&lt;p&gt;CH-Ops is a lightweight ClickHouse® admin tool designed to make ClickHouse® administration easier without making the underlying deployment more complicated.&lt;/p&gt;

&lt;p&gt;When we started building CH-Ops, we wanted to create a management layer that could sit beside ClickHouse® rather than become another component inside it. That principle led to two important architectural decisions: CH-Ops communicates with ClickHouse® through its existing HTTP interface, and the application can be compiled and shipped as a single executable.&lt;/p&gt;

&lt;p&gt;Those two decisions—HTTP and a single binary—are more connected than they might initially appear. Together, they define how lightweight CH-Ops can be to deploy and operate.&lt;/p&gt;

&lt;p&gt;They give CH-Ops a small operational footprint while still allowing it to provide a substantial administration surface.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Architecture
&lt;/h2&gt;

&lt;p&gt;At a high level, CH-Ops looks like this:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;                         Browser
                            |
                            | HTTP
                            v
                    +----------------+
                    |     CH-Ops     |
                    |                |
                    | React frontend |
                    | Express API    |
                    | SQLite state   |
                    +-------+--------+
                            |
                            | HTTP / HTTPS
                            v
                  +---------------------+
                  |     ClickHouse®     |
                  |                     |
                  |  Node 1   Node 2    |
                  |  Node 3   ...      |
                  +---------------------+
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;CH-Ops is a separate management application.&lt;/p&gt;

&lt;p&gt;The ClickHouse® cluster remains the database.&lt;/p&gt;

&lt;p&gt;That separation is intentional.&lt;/p&gt;

&lt;p&gt;We wanted the cost of adopting CH-Ops to look roughly like this:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Install CH-Ops somewhere
        |
        v
Give it persistent storage
        |
        v
Give it network access
        |
        v
Point it at ClickHouse®
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Not this:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Install CH-Ops
      |
      +---- Install agent on Node 1
      |
      +---- Install agent on Node 2
      |
      +---- Install agent on Node 3
      |
      +---- Configure every agent
      |
      +---- Upgrade every agent
      |
      +---- Monitor every agent
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That distinction is what we mean by lightweight.&lt;/p&gt;

&lt;p&gt;It is not primarily about how many features CH-Ops has.&lt;/p&gt;

&lt;p&gt;It is about how many things you have to install, coordinate, and operate.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why HTTP?
&lt;/h2&gt;

&lt;p&gt;ClickHouse® already exposes an HTTP interface.&lt;/p&gt;

&lt;p&gt;The standard HTTP interface commonly listens on port &lt;code&gt;8123&lt;/code&gt;, while the native protocol commonly uses port &lt;code&gt;9000&lt;/code&gt;. SQL can be sent directly over HTTP:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;curl &lt;span class="s2"&gt;"http://localhost:8123/"&lt;/span&gt; &lt;span class="se"&gt;\&lt;/span&gt;
  &lt;span class="nt"&gt;--data-binary&lt;/span&gt; &lt;span class="s2"&gt;"SELECT version()"&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That gives an application a straightforward way to communicate with ClickHouse® without implementing the native TCP protocol.&lt;/p&gt;

&lt;p&gt;For CH-Ops, that is a useful boundary.&lt;/p&gt;

&lt;p&gt;We are building an administration application. Our job is to inspect system tables, execute SQL, monitor queries, inspect replication and cluster state, manage users and grants, and expose operational information through a browser.&lt;/p&gt;

&lt;p&gt;We don't need CH-Ops to become another component inside the ClickHouse® server.&lt;/p&gt;

&lt;p&gt;We need a reliable way to communicate with it.&lt;/p&gt;

&lt;p&gt;HTTP provides that interface.&lt;/p&gt;

&lt;h3&gt;
  
  
  HTTP Is Not a Shortcut
&lt;/h3&gt;

&lt;p&gt;It is worth being precise here.&lt;/p&gt;

&lt;p&gt;Choosing HTTP does not mean we think the ClickHouse® native protocol is inferior.&lt;/p&gt;

&lt;p&gt;It isn't.&lt;/p&gt;

&lt;p&gt;There are workloads where the native protocol is exactly what an application should use.&lt;/p&gt;

&lt;p&gt;Our decision was based on the requirements of an administration application.&lt;/p&gt;

&lt;p&gt;HTTP gives us a standard network boundary that fits naturally into infrastructure people already operate:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Reverse proxies&lt;/li&gt;
&lt;li&gt;Load balancers&lt;/li&gt;
&lt;li&gt;Ingress controllers&lt;/li&gt;
&lt;li&gt;TLS termination&lt;/li&gt;
&lt;li&gt;Kubernetes networking&lt;/li&gt;
&lt;li&gt;Firewalls&lt;/li&gt;
&lt;li&gt;Monitoring systems&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;That doesn't automatically make HTTP faster.&lt;/p&gt;

&lt;p&gt;It makes it operationally convenient.&lt;/p&gt;

&lt;p&gt;For an administration UI, that trade-off is valuable.&lt;/p&gt;

&lt;h3&gt;
  
  
  The Biggest Benefit: No Agent on ClickHouse®
&lt;/h3&gt;

&lt;p&gt;This is probably the most important architectural decision in CH-Ops.&lt;/p&gt;

&lt;p&gt;There is no CH-Ops process that needs to run on every ClickHouse® node.&lt;/p&gt;

&lt;p&gt;Consider an agent-based design:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;CH-Ops
  |
  +---- Agent on Node 1
  |
  +---- Agent on Node 2
  |
  +---- Agent on Node 3
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That immediately creates another operational system.&lt;/p&gt;

&lt;p&gt;Every ClickHouse® node now has to maintain a CH-Ops agent.&lt;/p&gt;

&lt;p&gt;The agent needs its own:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Binary&lt;/li&gt;
&lt;li&gt;Configuration&lt;/li&gt;
&lt;li&gt;Permissions&lt;/li&gt;
&lt;li&gt;Upgrade process&lt;/li&gt;
&lt;li&gt;Logs&lt;/li&gt;
&lt;li&gt;Health checks&lt;/li&gt;
&lt;li&gt;Network rules&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;And if the cluster grows from three nodes to thirty, the operational footprint of that architecture grows with it.&lt;/p&gt;

&lt;p&gt;CH-Ops takes a different approach:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;                 CH-Ops
                   |
              HTTP / HTTPS
                   |
        +----------+----------+
        |          |          |
     Node 1     Node 2     Node 3
  ClickHouse® ClickHouse® ClickHouse®
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The ClickHouse® nodes don't need to know that CH-Ops exists.&lt;/p&gt;

&lt;p&gt;They simply expose the interfaces ClickHouse® already provides.&lt;/p&gt;

&lt;p&gt;This keeps the management plane separate from the database itself.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why Not Use the Native Protocol?
&lt;/h2&gt;

&lt;p&gt;The native ClickHouse® protocol has advantages, and there are good reasons for applications to use it.&lt;/p&gt;

&lt;p&gt;But CH-Ops isn't a high-throughput analytical client.&lt;/p&gt;

&lt;p&gt;It is an administration application.&lt;/p&gt;

&lt;p&gt;Its workload looks more like:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Inspect system tables&lt;/li&gt;
&lt;li&gt;Execute administrative SQL&lt;/li&gt;
&lt;li&gt;Monitor queries&lt;/li&gt;
&lt;li&gt;Inspect merges and mutations&lt;/li&gt;
&lt;li&gt;Check replication&lt;/li&gt;
&lt;li&gt;Inspect distributed DDL&lt;/li&gt;
&lt;li&gt;Manage users&lt;/li&gt;
&lt;li&gt;Run &lt;code&gt;EXPLAIN&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Build dashboards&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;For these operations, the HTTP interface gives us an appropriate transport without requiring CH-Ops to introduce another protocol dependency into the ClickHouse® deployment.&lt;/p&gt;

&lt;p&gt;There's also a broader infrastructure advantage.&lt;/p&gt;

&lt;p&gt;HTTP is something almost every modern infrastructure stack already understands.&lt;/p&gt;

&lt;p&gt;That means CH-Ops can fit behind existing network boundaries rather than requiring administrators to introduce a special connectivity path just for the management application.&lt;/p&gt;

&lt;h2&gt;
  
  
  HTTP Still Has Protocol Semantics
&lt;/h2&gt;

&lt;p&gt;Using HTTP doesn't eliminate ClickHouse®-specific behavior.&lt;/p&gt;

&lt;p&gt;For example, an HTTP &lt;code&gt;200 OK&lt;/code&gt; does not necessarily mean that a ClickHouse® query ultimately succeeded. Headers can be sent before the complete result is available, meaning an error can appear later in the response body.&lt;/p&gt;

&lt;p&gt;ClickHouse® documents mechanisms such as &lt;code&gt;wait_end_of_query=1&lt;/code&gt; for handling this behavior.&lt;/p&gt;

&lt;p&gt;This is an important distinction.&lt;/p&gt;

&lt;p&gt;We aren't treating HTTP as “simple because it is HTTP.”&lt;/p&gt;

&lt;p&gt;A serious ClickHouse® client still needs to correctly handle:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Authentication&lt;/li&gt;
&lt;li&gt;Query parameters&lt;/li&gt;
&lt;li&gt;Response formats&lt;/li&gt;
&lt;li&gt;Streaming&lt;/li&gt;
&lt;li&gt;Compression&lt;/li&gt;
&lt;li&gt;Timeouts&lt;/li&gt;
&lt;li&gt;Large result sets&lt;/li&gt;
&lt;li&gt;HTTPS&lt;/li&gt;
&lt;li&gt;ClickHouse®-specific settings&lt;/li&gt;
&lt;li&gt;Query errors&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The advantage is that all of those concerns remain at the network boundary.&lt;/p&gt;

&lt;p&gt;CH-Ops doesn't need to be installed inside the ClickHouse® servers to deal with them.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Other Half: One Binary
&lt;/h2&gt;

&lt;p&gt;The second major design decision is on the CH-Ops side.&lt;/p&gt;

&lt;p&gt;CH-Ops has a React frontend and an Express backend, running on Bun.&lt;/p&gt;

&lt;p&gt;In development, those are naturally separate pieces.&lt;/p&gt;

&lt;p&gt;For production, we can compile the application into a single executable.&lt;/p&gt;

&lt;p&gt;Conceptually:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;React source
     |
     | vite build
     v
   dist/
     |
     +-------------------+
                         |
Express backend ---------+
                         |
                         | bun build --compile
                         v
                  +--------------+
                  |    CH-Ops    |
                  |    binary    |
                  +--------------+
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The frontend is built into &lt;code&gt;dist/&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;That output, together with the backend and its dependencies, is bundled into the compiled executable.&lt;/p&gt;

&lt;p&gt;The result is a much simpler deployment artifact.&lt;/p&gt;

&lt;p&gt;Instead of needing to separately install a JavaScript runtime, application dependencies, backend source, and frontend assets, the production application can be represented by one executable.&lt;/p&gt;

&lt;p&gt;The deployment can look like this:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Server
├── chops
├── .env
└── data/
    └── chops.db
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That is a meaningful operational simplification.&lt;/p&gt;

&lt;h3&gt;
  
  
  Single Binary Does Not Mean Zero State
&lt;/h3&gt;

&lt;p&gt;There is an important nuance here.&lt;/p&gt;

&lt;p&gt;A single executable does not mean CH-Ops has no external requirements.&lt;/p&gt;

&lt;p&gt;CH-Ops still needs:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;An operating system&lt;/li&gt;
&lt;li&gt;Network connectivity&lt;/li&gt;
&lt;li&gt;Persistent storage&lt;/li&gt;
&lt;li&gt;Configuration&lt;/li&gt;
&lt;li&gt;Secrets&lt;/li&gt;
&lt;li&gt;Access to ClickHouse®&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;CH-Ops also maintains its own application state in SQLite.&lt;/p&gt;

&lt;p&gt;That state includes things such as cluster definitions, users, dashboards, and alerts.&lt;/p&gt;

&lt;p&gt;So the more accurate model is:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;CH-Ops binary
     +
configuration
     +
persistent application state
     +
network access
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The benefit of the compiled binary is that the application runtime and application code don't need to be assembled on the target machine.&lt;/p&gt;

&lt;p&gt;That is the useful property.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why SQLite Belongs in the Picture
&lt;/h2&gt;

&lt;p&gt;We deliberately keep CH-Ops' own state separate from ClickHouse®.&lt;/p&gt;

&lt;p&gt;Conceptually:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;              CH-Ops
                |
       +--------+--------+
       |                 |
       v                 v
    SQLite           ClickHouse®
       |                 |
 CH-Ops state        User's data
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;SQLite stores state belonging to the management application.&lt;/p&gt;

&lt;p&gt;ClickHouse® remains the database being managed.&lt;/p&gt;

&lt;p&gt;This separation has a few useful consequences.&lt;/p&gt;

&lt;p&gt;CH-Ops doesn't need to create a special database inside ClickHouse® just to remember its own configuration.&lt;/p&gt;

&lt;p&gt;It doesn't need to modify the ClickHouse® data model to maintain application state.&lt;/p&gt;

&lt;p&gt;And the CH-Ops state can move with the CH-Ops deployment.&lt;/p&gt;

&lt;p&gt;The application remains self-contained.&lt;/p&gt;

&lt;h2&gt;
  
  
  One Binary Does Not Mean One Deployment Model
&lt;/h2&gt;

&lt;p&gt;Some environments prefer to run applications directly.&lt;/p&gt;

&lt;p&gt;Others standardize on containers.&lt;/p&gt;

&lt;p&gt;We don't want the architecture to force one answer.&lt;/p&gt;

&lt;p&gt;The single executable works in both models:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;              CH-Ops binary
                   |
          +--------+--------+
          |                 |
          v                 v
     Run directly       Container
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;A container can still provide isolation, resource limits, orchestration, and deployment consistency.&lt;/p&gt;

&lt;p&gt;The difference is that the container isn't needed simply to provide Node.js, install npm dependencies, or assemble the frontend and backend.&lt;/p&gt;

&lt;p&gt;It becomes a packaging and isolation mechanism rather than a prerequisite for running the application.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why Bundle the Frontend?
&lt;/h2&gt;

&lt;p&gt;CH-Ops is a browser application, so there are naturally two sides:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Frontend&lt;/li&gt;
&lt;li&gt;Backend&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Those can be deployed independently.&lt;/p&gt;

&lt;p&gt;But they don't have to be.&lt;/p&gt;

&lt;p&gt;We build the React application and package the resulting assets into the compiled application.&lt;/p&gt;

&lt;p&gt;That gives us a production architecture like this:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Browser
   |
   v
+----------------------+
|       CH-Ops         |
|                      |
| React static assets  |
| Express API          |
| Bun runtime          |
+----------+-----------+
           |
           | HTTP / HTTPS
           v
      ClickHouse®
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;One application serves the UI and API.&lt;/p&gt;

&lt;p&gt;One port exposes it.&lt;/p&gt;

&lt;p&gt;One artifact represents the application version.&lt;/p&gt;

&lt;p&gt;That makes upgrades and deployments considerably simpler.&lt;/p&gt;

&lt;h2&gt;
  
  
  Kubernetes Reinforces the Same Architecture
&lt;/h2&gt;

&lt;p&gt;The separation becomes particularly interesting when CH-Ops is used with Kubernetes-managed ClickHouse® clusters.&lt;/p&gt;

&lt;p&gt;In that environment, CH-Ops can interact with the Kubernetes API to discover information about the cluster while separately communicating with ClickHouse® to execute queries and retrieve database information.&lt;/p&gt;

&lt;p&gt;The architecture becomes:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;                         +----------+
                         |  Browser |
                         +----+-----+
                              |
                              v
                         +---------+
                         | CH-Ops  |
                         +----+----+
                              |
                 +------------+------------+
                 |                         |
                 v                         v
          Kubernetes API             ClickHouse® HTTP
                 |                         |
                 v                         v
          Cluster metadata            Database nodes
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Again, CH-Ops doesn't need to live inside the ClickHouse® cluster to manage it.&lt;/p&gt;

&lt;p&gt;It can interact with the control plane and the database through their existing interfaces.&lt;/p&gt;

&lt;p&gt;That is exactly the separation we wanted.&lt;/p&gt;

&lt;h2&gt;
  
  
  Lightweight Doesn't Mean Limited
&lt;/h2&gt;

&lt;p&gt;This distinction is important.&lt;/p&gt;

&lt;p&gt;CH-Ops can provide functionality such as:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Live query monitoring&lt;/li&gt;
&lt;li&gt;Query history&lt;/li&gt;
&lt;li&gt;Merges and mutations&lt;/li&gt;
&lt;li&gt;Replication information&lt;/li&gt;
&lt;li&gt;Distributed DDL&lt;/li&gt;
&lt;li&gt;SQL editing&lt;/li&gt;
&lt;li&gt;&lt;code&gt;EXPLAIN&lt;/code&gt;&lt;/li&gt;
&lt;li&gt;Query profiling&lt;/li&gt;
&lt;li&gt;Dashboards&lt;/li&gt;
&lt;li&gt;Exports&lt;/li&gt;
&lt;li&gt;User and role management&lt;/li&gt;
&lt;li&gt;Backups and restores&lt;/li&gt;
&lt;li&gt;Kubernetes insights&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;None of that requires installing a CH-Ops agent across the ClickHouse® cluster.&lt;/p&gt;

&lt;p&gt;So “lightweight” isn't a statement about functionality.&lt;/p&gt;

&lt;p&gt;It is a statement about operational coupling.&lt;/p&gt;

&lt;p&gt;A management application can have a substantial feature set while still being easy to deploy if it doesn't require itself to be distributed throughout the infrastructure it manages.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Design Principle
&lt;/h2&gt;

&lt;p&gt;This is ultimately why we made these choices.&lt;/p&gt;

&lt;p&gt;HTTP keeps the ClickHouse® integration at the network boundary.&lt;/p&gt;

&lt;p&gt;The single executable keeps the CH-Ops application deployment at the executable boundary.&lt;/p&gt;

&lt;p&gt;SQLite keeps CH-Ops' own state separate from ClickHouse®.&lt;/p&gt;

&lt;p&gt;Together, they produce a clean separation:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;             Management plane
          +---------------------+
          |       CH-Ops        |
          | UI + API + state    |
          +----------+----------+
                     |
                   HTTP/S
                     |
          +----------v----------+
          |     ClickHouse®     |
          |       cluster       |
          +---------------------+
                  Data plane
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;CH-Ops can be upgraded independently.&lt;/p&gt;

&lt;p&gt;ClickHouse® can be upgraded independently.&lt;/p&gt;

&lt;p&gt;The ClickHouse® nodes don't need a CH-Ops installation.&lt;/p&gt;

&lt;p&gt;The CH-Ops server doesn't need a ClickHouse® installation.&lt;/p&gt;

&lt;p&gt;And the management application doesn't need to become part of the database itself.&lt;/p&gt;

&lt;p&gt;That's the architecture we wanted.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Trade-offs Are Real
&lt;/h2&gt;

&lt;p&gt;This design isn't universally better.&lt;/p&gt;

&lt;p&gt;The native ClickHouse® protocol remains valuable for applications that need its specific capabilities and performance characteristics.&lt;/p&gt;

&lt;p&gt;A single binary doesn't eliminate configuration, persistent state, networking, or secrets.&lt;/p&gt;

&lt;p&gt;SQLite isn't intended to replace a full production database for every possible application workload.&lt;/p&gt;

&lt;p&gt;And when Kubernetes integration is involved, CH-Ops still needs appropriate network access to the Kubernetes API and ClickHouse®.&lt;/p&gt;

&lt;p&gt;Lightweight doesn't mean infrastructure-free.&lt;/p&gt;

&lt;p&gt;It means the infrastructure boundary is deliberately small.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Real Meaning of Lightweight
&lt;/h2&gt;

&lt;p&gt;When we started building CH-Ops, we didn't want to create another system that administrators had to distribute across every ClickHouse® node.&lt;/p&gt;

&lt;p&gt;We wanted to build a management layer that could sit beside ClickHouse® rather than inside it.&lt;/p&gt;

&lt;p&gt;That led naturally to two decisions:&lt;/p&gt;

&lt;p&gt;Use the interface ClickHouse® already provides.&lt;/p&gt;

&lt;p&gt;Package the management application into the smallest practical deployment artifact.&lt;/p&gt;

&lt;p&gt;HTTP gives CH-Ops a clean network boundary.&lt;/p&gt;

&lt;p&gt;The compiled executable gives CH-Ops a clean deployment boundary.&lt;/p&gt;

&lt;p&gt;SQLite keeps the application's state independent.&lt;/p&gt;

&lt;p&gt;And the absence of agents keeps the ClickHouse® cluster untouched.&lt;/p&gt;

&lt;p&gt;So the real architecture is surprisingly simple:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Install CH-Ops somewhere
        |
        v
Give it persistent storage
        |
        v
Give it network access
        |
        v
Point it at ClickHouse®
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;No CH-Ops agent to distribute across the cluster.&lt;/p&gt;

&lt;p&gt;No separate frontend service required for the production build.&lt;/p&gt;

&lt;p&gt;No Node.js installation required on the target machine for the compiled binary.&lt;/p&gt;

&lt;p&gt;No requirement for CH-Ops to become part of the ClickHouse® server.&lt;/p&gt;

&lt;p&gt;That is what lightweight by design means to us.&lt;/p&gt;

&lt;p&gt;Not fewer features&lt;/p&gt;

</description>
      <category>clickhouse</category>
      <category>devops</category>
      <category>analytics</category>
      <category>database</category>
    </item>
    <item>
      <title>Native ClickHouse® Backups in CH-Ops: A Practical Guide</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Wed, 07 Oct 2026 15:00:40 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/native-clickhouser-backups-in-ch-ops-a-practical-guide-40g3</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/native-clickhouser-backups-in-ch-ops-a-practical-guide-40g3</guid>
      <description>&lt;h1&gt;
  
  
  Native ClickHouse® Backups in CH-Ops: A Practical Guide
&lt;/h1&gt;

&lt;p&gt;There's a feature called App Data Backup in CH-Ops, and it's easy to assume this article is about the same thing. It isn't.&lt;/p&gt;

&lt;p&gt;App Data Backup protects CH-Ops's own configuration — dashboards, alert rules, users. This is about the data actually sitting in your ClickHouse® tables, and it's a completely separate system under &lt;strong&gt;Backups → Data Lifecycle&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;If you only remember one thing before reading on: these two features share a destination (Storage Profiles) and nothing else.&lt;/p&gt;

&lt;p&gt;With that out of the way — this page assumes you've never taken a ClickHouse® backup before, and it's worth reading in that spirit even if you have, because a few things about how ClickHouse® backups work explain almost every "why is it doing that" moment later.&lt;/p&gt;

&lt;h2&gt;
  
  
  How this actually works under the hood
&lt;/h2&gt;

&lt;p&gt;ClickHouse® has a &lt;code&gt;BACKUP&lt;/code&gt; statement built in. You run it against the server, point it at a destination, and the server writes the data there.&lt;/p&gt;

&lt;p&gt;CH-Ops doesn't copy anything itself — it composes that statement, sends it, and reports back what happened.&lt;/p&gt;

&lt;p&gt;That one fact explains three things that otherwise look odd:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;The server needs access to the destination, not CH-Ops.&lt;/strong&gt; If your ClickHouse® nodes can't reach the S3 bucket, the backup fails — even if CH-Ops itself can reach it fine. This is the single most common first-time failure, and it's worth internalizing now rather than discovering it mid-incident.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;A backup is a directory of files, not one file.&lt;/strong&gt; ClickHouse® writes a structured layout to the bucket. Don't rearrange it by hand — the structure is how ClickHouse® finds its own pieces again on restore.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Consistency is per-table, not necessarily across tables at one instant.&lt;/strong&gt; Fine for most purposes, but worth knowing before you lean on cross-table consistency for something that actually needs it.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Before you can back anything up
&lt;/h2&gt;

&lt;p&gt;CH-Ops needs somewhere to send backups — a bucket and credentials to reach it.&lt;/p&gt;

&lt;p&gt;That's a &lt;strong&gt;storage profile&lt;/strong&gt;, configured once under &lt;strong&gt;Control Panel → Storage Profiles&lt;/strong&gt;, and it's the same profile system used by App Data Backup.&lt;/p&gt;

&lt;p&gt;Set the destination up once, and both features can use it.&lt;/p&gt;

&lt;p&gt;Worth testing the profile before trusting it — but be precise about what a passing test actually proves.&lt;/p&gt;

&lt;p&gt;It confirms &lt;strong&gt;CH-Ops&lt;/strong&gt; can reach the bucket. It says nothing about whether your ClickHouse® servers can, and since the server is what actually writes the backup, that's the connection that matters.&lt;/p&gt;

&lt;p&gt;A profile that validates here can still fail at backup time for exactly that reason.&lt;/p&gt;

&lt;h2&gt;
  
  
  Taking a manual backup
&lt;/h2&gt;

&lt;p&gt;Go to:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Backups → Data Lifecycle → Manual Backup&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The flow:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Choose a scope — whole cluster, one database, or one table.&lt;/li&gt;
&lt;li&gt;Choose a storage profile.&lt;/li&gt;
&lt;li&gt;Set any options. Defaults are reasonable for a first backup.&lt;/li&gt;
&lt;li&gt;Read the preview — the exact statement, credentials masked, updating live as you change choices.&lt;/li&gt;
&lt;li&gt;Run it.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;That preview step isn't decoration — it's the last chance to notice the scope is wrong or the destination is still pointed at a test bucket before anything actually runs.&lt;/p&gt;

&lt;p&gt;It's also, incidentally, the fastest way to learn &lt;code&gt;BACKUP&lt;/code&gt; syntax: change one option and watch which clause appears or disappears.&lt;/p&gt;

&lt;h3&gt;
  
  
  Choosing a scope
&lt;/h3&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Scope&lt;/th&gt;
&lt;th&gt;Statement&lt;/th&gt;
&lt;th&gt;Use when&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Cluster&lt;/td&gt;
&lt;td&gt;&lt;code&gt;BACKUP ALL&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;You want everything&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Database&lt;/td&gt;
&lt;td&gt;&lt;code&gt;BACKUP DATABASE x&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;One application's data&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Table&lt;/td&gt;
&lt;td&gt;&lt;code&gt;BACKUP TABLE x.y&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Before a risky change to one table&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Exclusions let you take a broad scope and carve out what you don't want, which is usually the better direction than listing every database you do want.&lt;/p&gt;

&lt;p&gt;Skip anything rebuildable, like staging copies or materialized data you could recompute instead.&lt;/p&gt;

&lt;p&gt;And keep an eye on time: a cluster backup on a large deployment reads a lot of data and takes a while.&lt;/p&gt;

&lt;p&gt;Save that scope for when you actually need it; use narrower scopes for routine work.&lt;/p&gt;

&lt;h3&gt;
  
  
  The two options worth knowing
&lt;/h3&gt;

&lt;p&gt;&lt;strong&gt;Run in the background&lt;/strong&gt; adds &lt;code&gt;ASYNC&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;The statement returns immediately and the server keeps going after that.&lt;/p&gt;

&lt;p&gt;Use this for anything large — without it, the request stays open until the backup finishes, and a browser timeout partway through a long backup leaves you genuinely unsure whether it's still running or not.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Apply across a cluster&lt;/strong&gt; adds &lt;code&gt;ON CLUSTER&lt;/code&gt;, running the statement on every node instead of just the one you're connected to.&lt;/p&gt;

&lt;p&gt;Necessary for a distributed setup; changes nothing on a single server.&lt;/p&gt;

&lt;p&gt;There's also a free-text &lt;strong&gt;extra settings&lt;/strong&gt; field for anything ClickHouse® backup settings support directly.&lt;/p&gt;

&lt;p&gt;Three you'll likely reach for:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Setting&lt;/th&gt;
&lt;th&gt;Does&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;base_backup&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Makes this backup incremental against an earlier one&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;compression_method&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;e.g. &lt;code&gt;'lz4'&lt;/code&gt; — trades CPU for size&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;s3_storage_class&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Which S3 storage class to write to, for cost&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Written exactly as you'd write it in SQL:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;base_backup = S3('https://bucket.s3.amazonaws.com/full-2026-07-01'), compression_method = 'lz4'&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;These settings are passed straight through to ClickHouse®, so its own documentation is the authority on anything not covered here.&lt;/p&gt;

&lt;h2&gt;
  
  
  Restoring
&lt;/h2&gt;

&lt;p&gt;On the same &lt;strong&gt;Manual Backup&lt;/strong&gt; tab, choose &lt;strong&gt;List Available Backups&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;CH-Ops scans your S3 storage and lists what's there, newest first.&lt;/p&gt;

&lt;p&gt;Pick one, restore it.&lt;/p&gt;

&lt;p&gt;A few things worth holding onto before clicking restore:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Restoring is not undo.&lt;/strong&gt; Read what it will actually do to what already exists — restoring over a table that's still there isn't automatically safe.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Restore somewhere else first when you can.&lt;/strong&gt; A different database name, or a staging cluster, proves the backup is good without risking the thing you were trying to protect in the first place.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;A backup you've never restored is a hope, not a backup.&lt;/strong&gt; The first restore shouldn't happen during an actual incident. Try one on something small while nothing's on fire.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Browsing what you have
&lt;/h2&gt;

&lt;p&gt;The &lt;strong&gt;Available Backups&lt;/strong&gt; tab is the inventory.&lt;/p&gt;

&lt;p&gt;Choose a storage profile, click &lt;strong&gt;Scan S3&lt;/strong&gt;, and see everything that's actually out there.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Column&lt;/th&gt;
&lt;th&gt;Meaning&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;ID&lt;/td&gt;
&lt;td&gt;The backup's identifier&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Scope&lt;/td&gt;
&lt;td&gt;Cluster, database, or table&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Created&lt;/td&gt;
&lt;td&gt;When it was taken&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Type&lt;/td&gt;
&lt;td&gt;Full or incremental&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Retention&lt;/td&gt;
&lt;td&gt;How long it's set to be kept&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Worth scanning this occasionally even when nothing's wrong.&lt;/p&gt;

&lt;p&gt;It's how you find out a schedule quietly stopped running three weeks ago, instead of finding out at the worst possible moment.&lt;/p&gt;

&lt;h2&gt;
  
  
  Full vs. incremental
&lt;/h2&gt;

&lt;p&gt;A &lt;strong&gt;full&lt;/strong&gt; backup contains everything in its scope.&lt;/p&gt;

&lt;p&gt;An &lt;strong&gt;incremental&lt;/strong&gt; contains only what changed since an earlier one, referenced through &lt;code&gt;base_backup&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;Incrementals are smaller and faster to take.&lt;/p&gt;

&lt;p&gt;The trade: restoring one needs its base — and the base of that base, all the way back to a full.&lt;/p&gt;

&lt;p&gt;Break any link in that chain and everything after it stops being usable.&lt;/p&gt;

&lt;p&gt;A practical pattern:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;A full backup weekly&lt;/li&gt;
&lt;li&gt;Incrementals daily against it&lt;/li&gt;
&lt;li&gt;Each week starts a fresh chain&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;This keeps no single chain long enough to become fragile.&lt;/p&gt;

&lt;p&gt;And keep at least one complete chain past your retention window.&lt;/p&gt;

&lt;p&gt;Retention that deletes a base while its incrementals survive leaves you holding files that can't restore anything.&lt;/p&gt;

&lt;h2&gt;
  
  
  When something doesn't work
&lt;/h2&gt;

&lt;h3&gt;
  
  
  Fails immediately with an access error
&lt;/h3&gt;

&lt;p&gt;The server can't reach the bucket.&lt;/p&gt;

&lt;p&gt;Remember, the server writes the backup, not CH-Ops, so a profile that validated fine can still fail here.&lt;/p&gt;

&lt;p&gt;Check that your ClickHouse® nodes have real network access to S3, and that the credentials are ones the server can use.&lt;/p&gt;

&lt;p&gt;On a cloud deployment, that often means an attached instance role rather than static keys.&lt;/p&gt;

&lt;h3&gt;
  
  
  Runs forever and the page seems stuck
&lt;/h3&gt;

&lt;p&gt;&lt;code&gt;ASYNC&lt;/code&gt; wasn't enabled.&lt;/p&gt;

&lt;p&gt;The request stays open until the server finishes.&lt;/p&gt;

&lt;p&gt;Turn it on for anything large — the backup itself isn't affected by what the browser does.&lt;/p&gt;

&lt;h3&gt;
  
  
  Scan S3 returns nothing
&lt;/h3&gt;

&lt;p&gt;Three possibilities, in order of likelihood:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;The profile points at a different bucket or prefix than what was actually written to.&lt;/li&gt;
&lt;li&gt;Nothing's been backed up with this profile yet.&lt;/li&gt;
&lt;li&gt;Everything's aged past retention and been cleaned up.&lt;/li&gt;
&lt;/ol&gt;

&lt;h3&gt;
  
  
  Restore fails, missing base backup
&lt;/h3&gt;

&lt;p&gt;This usually means a broken incremental chain.&lt;/p&gt;

&lt;p&gt;Its base was deleted, probably by retention.&lt;/p&gt;

&lt;p&gt;Find an earlier full and restore that instead, then fix retention so it stops deleting bases ahead of their incrementals.&lt;/p&gt;

&lt;h3&gt;
  
  
  Restore fails, table already exists
&lt;/h3&gt;

&lt;p&gt;Expected.&lt;/p&gt;

&lt;p&gt;ClickHouse® won't silently overwrite.&lt;/p&gt;

&lt;p&gt;Restore under a different name, or deliberately remove the existing table first if you're certain.&lt;/p&gt;

&lt;h3&gt;
  
  
  Backup succeeded but looks smaller than expected
&lt;/h3&gt;

&lt;p&gt;Usually exclusions.&lt;/p&gt;

&lt;p&gt;Check what got left out in the preview.&lt;/p&gt;

&lt;p&gt;It can also just be normal: ClickHouse® compresses well, and a backup being much smaller than the raw data on disk isn't automatically alarming.&lt;/p&gt;

&lt;h2&gt;
  
  
  References
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Backup and Restore: CH-Ops Documentation&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Next in the CHOps series
&lt;/h2&gt;

&lt;p&gt;CH-Ops: A ClickHouse® Admin Tool Built for Simplicity&lt;/p&gt;

</description>
      <category>clickhouse</category>
      <category>analytics</category>
      <category>devops</category>
    </item>
    <item>
      <title>CH-Ops Access Control and App-Data Backup</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Tue, 06 Oct 2026 15:54:41 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/ch-ops-access-control-and-app-data-backup-3d91</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/ch-ops-access-control-and-app-data-backup-3d91</guid>
      <description>&lt;p&gt;&lt;strong&gt;CH-Ops access control&lt;/strong&gt; provides a dedicated permission layer for managing who can use the CH-Ops application and what they can do inside it.&lt;/p&gt;

&lt;p&gt;Managing a ClickHouse® cluster is only one part of running a database platform. Once a team starts using a shared administration tool, another question becomes important: who is allowed to use the tool, and what happens to the tool's own configuration if the application is lost?&lt;/p&gt;

&lt;p&gt;CH-Ops addresses both concerns with application-level access control and app-data backup capabilities.&lt;/p&gt;

&lt;p&gt;The application maintains its own configuration separately from the ClickHouse® data it manages. This includes information such as users, dashboards, alerts, and cluster definitions.&lt;/p&gt;

&lt;p&gt;That creates an important distinction:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;A ClickHouse® backup protects ClickHouse® data. An app-data backup protects the state of CH-Ops itself.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;In this article, we'll look at both capabilities and how they fit into a self-hosted ClickHouse® environment.&lt;/p&gt;

&lt;h2&gt;
  
  
  Application-Level Access Control
&lt;/h2&gt;

&lt;p&gt;There are two permission systems to consider when operating ClickHouse® through CH-Ops.&lt;/p&gt;

&lt;p&gt;The first is &lt;strong&gt;ClickHouse® access control&lt;/strong&gt;, which determines what a ClickHouse® user can do inside the database.&lt;/p&gt;

&lt;p&gt;The second is &lt;strong&gt;CH-Ops application access control&lt;/strong&gt;, which determines what someone can do inside the CH-Ops application.&lt;/p&gt;

&lt;p&gt;These systems serve different purposes.&lt;/p&gt;

&lt;p&gt;For example, several engineers might need access to the same production cluster to monitor queries and investigate issues. That doesn't necessarily mean everyone should be able to modify application configuration or manage other CH-Ops users.&lt;/p&gt;

&lt;p&gt;CH-Ops provides four application-level roles so access can be assigned according to the responsibilities of each user.&lt;/p&gt;

&lt;p&gt;The important part is that these roles apply to &lt;strong&gt;CH-Ops itself&lt;/strong&gt;, rather than replacing ClickHouse®'s own RBAC system.&lt;/p&gt;

&lt;h2&gt;
  
  
  CH-Ops Users vs. ClickHouse® Users
&lt;/h2&gt;

&lt;p&gt;It is easy to confuse these two concepts.&lt;/p&gt;

&lt;p&gt;A ClickHouse® deployment may contain database users such as:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;ClickHouse®
├── analyst
├── data_engineer
├── reporting_user
└── admin
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;These accounts are managed by ClickHouse® and control access to databases, tables, queries, and other database operations.&lt;/p&gt;

&lt;p&gt;CH-Ops has a separate set of application users:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;CH-Ops
├── User A
├── User B
├── User C
└── User D
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;These accounts determine who can access the CH-Ops application and what application-level operations they can perform.&lt;/p&gt;

&lt;p&gt;CH-Ops can separately manage ClickHouse® users, roles, and grants through its Access Control functionality. Keeping these two permission layers separate is useful because administering the management application and administering the database are different responsibilities.&lt;/p&gt;

&lt;h2&gt;
  
  
  Managing CH-Ops Users
&lt;/h2&gt;

&lt;p&gt;A single administrator account may be sufficient for a personal installation, but a team operating multiple ClickHouse® clusters needs a way to separate administrative responsibilities from day-to-day operational access.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why Back Up CH-Ops Itself?
&lt;/h2&gt;

&lt;p&gt;Now consider a different failure scenario.&lt;/p&gt;

&lt;p&gt;Your ClickHouse® data is backed up correctly, but the server running CH-Ops is lost.&lt;/p&gt;

&lt;p&gt;What happens to the configuration you created inside CH-Ops?&lt;/p&gt;

&lt;p&gt;CH-Ops stores its own application state in a SQLite database rather than storing that configuration inside ClickHouse®. This state can include users, dashboards, alerts, cluster definitions, and other application settings.&lt;/p&gt;

&lt;p&gt;Reinstalling CH-Ops does not automatically recreate all of that configuration.&lt;/p&gt;

&lt;p&gt;This is where app-data backup becomes useful.&lt;/p&gt;

&lt;p&gt;The two backups protect different layers of the infrastructure.&lt;/p&gt;

&lt;h2&gt;
  
  
  One-Click App Data Backup
&lt;/h2&gt;

&lt;p&gt;CH-Ops provides an application-data backup workflow through its Administration functionality.&lt;/p&gt;

&lt;p&gt;Instead of manually locating the application's SQLite database and building a separate copy process, administrators can use the application's backup functionality to protect its configuration.&lt;/p&gt;

&lt;p&gt;The workflow is intentionally straightforward.&lt;/p&gt;

&lt;p&gt;This is particularly useful for self-hosted deployments where CH-Ops is running on a VM, server, or container.&lt;/p&gt;

&lt;h2&gt;
  
  
  App Backup vs. ClickHouse® Backup
&lt;/h2&gt;

&lt;p&gt;The word "backup" can make these two operations sound interchangeable, but they protect different things.&lt;/p&gt;

&lt;p&gt;A &lt;strong&gt;ClickHouse® backup&lt;/strong&gt; is concerned with ClickHouse® data and database objects.&lt;/p&gt;

&lt;p&gt;An &lt;strong&gt;app-data backup&lt;/strong&gt; is concerned with CH-Ops configuration and application state.&lt;/p&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Backup&lt;/th&gt;
&lt;th&gt;Protects&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;ClickHouse® backup&lt;/td&gt;
&lt;td&gt;ClickHouse® database data&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;CH-Ops app-data backup&lt;/td&gt;
&lt;td&gt;Users, dashboards, alerts, cluster definitions, and application configuration&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;CH-Ops does not use its SQLite database to store the actual ClickHouse® table data.&lt;/p&gt;

&lt;p&gt;That means backing up CH-Ops does not replace a ClickHouse® backup strategy, and backing up ClickHouse® does not automatically protect the configuration of the CH-Ops installation.&lt;/p&gt;

&lt;p&gt;A production environment should treat these as two separate backup concerns.&lt;/p&gt;

&lt;h2&gt;
  
  
  Keeping the Backup Separate
&lt;/h2&gt;

&lt;p&gt;A backup stored on the same machine as the application does not provide much protection against a machine-level failure.&lt;/p&gt;

&lt;p&gt;CH-Ops supports app-data backups to external storage destinations such as S3, GCS, and Azure.&lt;/p&gt;

&lt;p&gt;That makes it possible to separate the application from the location where its backup is stored.&lt;/p&gt;

&lt;p&gt;If the CH-Ops host is lost, the backup remains available independently of that host.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Backup Workflow
&lt;/h2&gt;

&lt;p&gt;The practical workflow is simple:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Open &lt;strong&gt;CONTROL PANEL&lt;/strong&gt;.&lt;/li&gt;
&lt;li&gt;Open the app-data backup functionality.&lt;/li&gt;
&lt;li&gt;Configure or select the backup destination.&lt;/li&gt;
&lt;li&gt;Start the backup.&lt;/li&gt;
&lt;li&gt;Verify that the operation completed successfully.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The important distinction is that this operation backs up &lt;strong&gt;CH-Ops application data&lt;/strong&gt;, not the data stored in your ClickHouse® tables.&lt;/p&gt;

&lt;p&gt;That makes it complementary to an existing ClickHouse® backup strategy rather than a replacement for it.&lt;/p&gt;

&lt;h2&gt;
  
  
  Restoring CH-Ops App Data
&lt;/h2&gt;

&lt;p&gt;Backing up application data is only half of the recovery story. CH-Ops provides a way to restore the backed-up SQLite database, but there is one important requirement: &lt;strong&gt;the CH-Ops server must be stopped during the restore&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;Restoring replaces the existing application data, so this operation should be performed carefully.&lt;/p&gt;

&lt;h3&gt;
  
  
  1. Download the Backup
&lt;/h3&gt;

&lt;p&gt;First, download the backup file from the configured S3-compatible storage.&lt;/p&gt;

&lt;p&gt;For example, using the AWS CLI:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;aws s3 &lt;span class="nb"&gt;cp &lt;/span&gt;s3://YOUR_BUCKET/chops-app-backups/BACKUP_ID.db ./chops-restore.db &lt;span class="se"&gt;\&lt;/span&gt;
  &lt;span class="nt"&gt;--endpoint-url&lt;/span&gt; YOUR_ENDPOINT
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The same approach can be used with S3-compatible storage by providing the appropriate endpoint.&lt;/p&gt;

&lt;h3&gt;
  
  
  2. Stop the CH-Ops Server
&lt;/h3&gt;

&lt;p&gt;The server must not be running while the database is replaced.&lt;/p&gt;

&lt;p&gt;For development, stop the running process:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Ctrl+C
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;For a production installation managed by systemd:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="nb"&gt;sudo &lt;/span&gt;systemctl stop chops
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If CH-Ops is running as a foreground binary, stop the process with &lt;code&gt;Ctrl+C&lt;/code&gt; or terminate the process normally.&lt;/p&gt;

&lt;h3&gt;
  
  
  3. Replace the Application Database
&lt;/h3&gt;

&lt;p&gt;Before replacing the database, remove the existing SQLite WAL and SHM files:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="nb"&gt;rm&lt;/span&gt; &lt;span class="nt"&gt;-f&lt;/span&gt; data/chops.db-wal data/chops.db-shm
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;These files belong to the existing SQLite database and can contain unflushed state from the old database.&lt;/p&gt;

&lt;p&gt;The backup itself is a self-contained SQLite database created using &lt;code&gt;VACUUM INTO&lt;/code&gt;. The backup is stored as base64 text, so decode it first:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="nb"&gt;base64&lt;/span&gt; &lt;span class="nt"&gt;-d&lt;/span&gt; chops-restore.db &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; chops_decoded.db
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Then replace the current application database:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="nb"&gt;cp &lt;/span&gt;chops_decoded.db data/chops.db
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;At this point, the CH-Ops application database has been replaced with the backed-up state.&lt;/p&gt;

&lt;h3&gt;
  
  
  4. Start CH-Ops Again
&lt;/h3&gt;

&lt;p&gt;For development:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;bun run dev
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;For a systemd-managed production installation:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="nb"&gt;sudo &lt;/span&gt;systemctl start chops
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;For a foreground binary:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;./chops
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Once the server starts, CH-Ops will use the restored SQLite database.&lt;/p&gt;

&lt;h3&gt;
  
  
  What Gets Restored?
&lt;/h3&gt;

&lt;p&gt;The app-data backup contains CH-Ops's internal application state, including:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Alert rules&lt;/li&gt;
&lt;li&gt;Alert channels&lt;/li&gt;
&lt;li&gt;Dashboards&lt;/li&gt;
&lt;li&gt;Charts&lt;/li&gt;
&lt;li&gt;Backup schedules&lt;/li&gt;
&lt;li&gt;User accounts&lt;/li&gt;
&lt;li&gt;Application settings&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The restoration affects &lt;strong&gt;CH-Ops application state only&lt;/strong&gt;. It does not restore, modify, or overwrite the underlying ClickHouse® data.&lt;/p&gt;

&lt;p&gt;In other words:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;CH-Ops backup
      │
      ▼
SQLite application state
      │
      ├── Users
      ├── Dashboards
      ├── Alerts
      ├── Charts
      ├── Backup schedules
      └── Settings

          X ClickHouse® data

      └── Not affected
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Because restoration replaces the existing application database, this should be treated as a recovery operation rather than a routine configuration change.&lt;/p&gt;

&lt;h2&gt;
  
  
  A Practical Team Scenario
&lt;/h2&gt;

&lt;p&gt;Consider a data platform team running several ClickHouse® clusters through CH-Ops.&lt;/p&gt;

&lt;p&gt;A platform administrator may need to manage application users and cluster configuration, while other engineers primarily need the ability to monitor clusters and investigate problems.&lt;/p&gt;

&lt;p&gt;Application-level roles provide the separation between these responsibilities.&lt;/p&gt;

&lt;p&gt;At the same time, the team may have accumulated dashboards, alerts, cluster definitions, and other configuration inside CH-Ops.&lt;/p&gt;

&lt;p&gt;That configuration is now part of the team's operational environment.&lt;/p&gt;

&lt;p&gt;The two features address these concerns together:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Access control protects who can change the management layer.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;App-data backup protects the management layer itself.&lt;/strong&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  A Simple Operational Checklist
&lt;/h2&gt;

&lt;p&gt;For a self-hosted CH-Ops deployment:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Create individual CH-Ops accounts rather than sharing credentials.&lt;/li&gt;
&lt;li&gt;Assign users the appropriate application role.&lt;/li&gt;
&lt;li&gt;Keep CH-Ops credentials and ClickHouse® credentials protected.&lt;/li&gt;
&lt;li&gt;Back up CH-Ops application data separately from ClickHouse® data.&lt;/li&gt;
&lt;li&gt;Store backups on external or independent storage.&lt;/li&gt;
&lt;li&gt;Verify that backups complete successfully.&lt;/li&gt;
&lt;li&gt;Test the recovery process rather than assuming the backup works.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Final Thoughts
&lt;/h2&gt;

&lt;p&gt;A ClickHouse® management platform becomes part of the infrastructure around your database.&lt;/p&gt;

&lt;p&gt;That means it needs its own access controls and its own recovery strategy.&lt;/p&gt;

&lt;p&gt;CH-Ops addresses these two areas through &lt;strong&gt;application-level access control&lt;/strong&gt; and &lt;strong&gt;app-data backup&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;Application-level roles provide a way to control who can use and administer CH-Ops without confusing those permissions with ClickHouse®'s own RBAC system.&lt;/p&gt;

&lt;p&gt;App-data backups protect the configuration stored by CH-Ops, including the application state needed to recreate a working management environment.&lt;/p&gt;

&lt;p&gt;Together, they answer two simple operational questions:&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;&lt;em&gt;Who is allowed to manage the management layer?&lt;/em&gt;&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;and:&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;&lt;em&gt;Can we recover that management layer if the host is lost?&lt;/em&gt;&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;For teams using CH-Ops to manage their ClickHouse® infrastructure, those are small but important pieces of building a more resilient operational setup.&lt;/p&gt;

&lt;h2&gt;
  
  
  References
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;CH-Ops - User Management Documentation&lt;/li&gt;
&lt;li&gt;CH-Ops - App Data Backups Documentation&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Next in the CHOps series
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Native ClickHouse® Backups in CH-Ops: A Practical Guide&lt;/strong&gt;&lt;/p&gt;

</description>
      <category>clickhouse</category>
      <category>devops</category>
      <category>analytics</category>
    </item>
    <item>
      <title>CH-Ops Scheduled Alerts: Know Before Things Break</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Sat, 03 Oct 2026 16:42:02 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/ch-ops-scheduled-alerts-know-before-things-break-3pdk</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/ch-ops-scheduled-alerts-know-before-things-break-3pdk</guid>
      <description>&lt;h2&gt;
  
  
  What is CH-Ops
&lt;/h2&gt;

&lt;p&gt;CH-Ops is a browser-based operations platform built for managing ClickHouse® deployments. Instead of relying entirely on the command line or HTTP APIs, it provides a unified interface for executing SQL queries, monitoring clusters, managing users, backups, alerts, dashboards, and much more, all from a single web application.&lt;/p&gt;

&lt;p&gt;One of its useful features is &lt;strong&gt;Alert Rules&lt;/strong&gt;, which lets you turn a SQL query into a scheduled check and notify you when a defined condition is met.&lt;/p&gt;

&lt;p&gt;In this walkthrough, we'll use Email (SMTP) as the notification channel and create a simple scheduled alert from the CH-Ops interface. Let's see how you can create, test, and manage alerts without building a separate monitoring script.&lt;/p&gt;

&lt;h2&gt;
  
  
  Introducing CH-Ops Alert Rules
&lt;/h2&gt;

&lt;p&gt;Monitoring a ClickHouse® cluster is not only about looking at dashboards. Some conditions are easier to detect with a simple query.&lt;/p&gt;

&lt;p&gt;For example, you may want to know when:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;The number of failed operations goes above a threshold.&lt;/li&gt;
&lt;li&gt;A particular metric exceeds an expected value.&lt;/li&gt;
&lt;li&gt;A query returns an unexpected result.&lt;/li&gt;
&lt;li&gt;A cluster condition needs attention.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Instead of manually running the same query again and again, Alert Rules allows you to schedule the query and define what should trigger an alert. Once the rule is enabled, CH-Ops periodically executes the query and checks its result against the configured condition.&lt;/p&gt;

&lt;h2&gt;
  
  
  Setting up an email notification channel
&lt;/h2&gt;

&lt;p&gt;Before creating an alert, you need a notification channel that CH-Ops can use to deliver the alert.&lt;/p&gt;

&lt;p&gt;You can find this under:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Control Panel → Notification Channels&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Click &lt;strong&gt;New&lt;/strong&gt; to create a notification channel.&lt;/p&gt;

&lt;p&gt;For this walkthrough, select &lt;strong&gt;Email (SMTP)&lt;/strong&gt; as the notification type. The form provides fields for configuring the email connection:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;strong&gt;Field&lt;/strong&gt;&lt;/th&gt;
&lt;th&gt;&lt;strong&gt;What it's for&lt;/strong&gt;&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Name&lt;/td&gt;
&lt;td&gt;A name to identify the notification channel&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;SMTP Host&lt;/td&gt;
&lt;td&gt;The mail server address used to send the alert&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;SMTP Port&lt;/td&gt;
&lt;td&gt;The port that server listens on&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;SMTP User&lt;/td&gt;
&lt;td&gt;The account CH-Ops authenticates as&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;SMTP Password&lt;/td&gt;
&lt;td&gt;That account's password&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;From&lt;/td&gt;
&lt;td&gt;The sender address&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;To&lt;/td&gt;
&lt;td&gt;The recipient address&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Fill in the SMTP details provided by your email service and click &lt;strong&gt;Create&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;Once configured, the channel can be reused by alert rules instead of configuring email details every time you create a new alert.&lt;/p&gt;

&lt;p&gt;After creating an Email (SMTP) notification channel, use its &lt;strong&gt;Test&lt;/strong&gt; button to verify that the configured SMTP details can successfully send a test notification. A successful test confirms that the notification channel is ready to be used by alert rules.&lt;/p&gt;

&lt;h2&gt;
  
  
  Creating an Alert Rule
&lt;/h2&gt;

&lt;p&gt;After configuring the notification channel, go to:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Custom Alerts → Alert Rules&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;If you haven't created any rules yet, CH-Ops displays an empty state with a &lt;strong&gt;New Rule&lt;/strong&gt; button. Click &lt;strong&gt;New Rule&lt;/strong&gt; to start creating an alert.&lt;/p&gt;

&lt;p&gt;The alert rule form brings the main configuration into one place.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;strong&gt;Field&lt;/strong&gt;&lt;/th&gt;
&lt;th&gt;&lt;strong&gt;What it does&lt;/strong&gt;&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Name&lt;/td&gt;
&lt;td&gt;Identifies the rule&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Severity&lt;/td&gt;
&lt;td&gt;How serious a firing alert is, for example warning&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Schedule (cron)&lt;/td&gt;
&lt;td&gt;How often the rule runs, for example &lt;code&gt;*/5 * * * *&lt;/code&gt; for every five minutes&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Operator&lt;/td&gt;
&lt;td&gt;How the result is compared to the threshold, for example greater than&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Threshold&lt;/td&gt;
&lt;td&gt;The value the operator compares against&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;SQL (single value)&lt;/td&gt;
&lt;td&gt;A query that returns one number to check&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Description&lt;/td&gt;
&lt;td&gt;Context shown when the alert fires (optional), but it is useful for documenting what the alert is checking&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Cluster&lt;/td&gt;
&lt;td&gt;Which cluster the rule runs against, defaulting to all clusters&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Target nodes&lt;/td&gt;
&lt;td&gt;Which nodes to run on, left empty to mean all nodes&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Before configuring, the Alert Rule also has its own &lt;strong&gt;Test&lt;/strong&gt; button. It executes the configured SQL query and checks the result against the defined condition and threshold.&lt;/p&gt;

&lt;p&gt;This lets you verify the alert logic before relying on the scheduled rule.&lt;/p&gt;

&lt;p&gt;Once created, the channel shows up as a card with its status, when it was last tested, and Edit, Enable, and Delete actions.&lt;/p&gt;

&lt;h2&gt;
  
  
  From Query to Notification
&lt;/h2&gt;

&lt;p&gt;Once the alert is created, the workflow becomes straightforward:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;1. Run the SQL query&lt;/strong&gt;&lt;br&gt;
CH-Ops executes the configured query according to the schedule.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;2. Check the result&lt;/strong&gt;&lt;br&gt;
The returned value is evaluated against the configured operator and threshold.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;3. Determine whether the condition is met&lt;/strong&gt;&lt;br&gt;
If the result satisfies the alert condition, the rule is triggered.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;4. Send the notification&lt;/strong&gt;&lt;br&gt;
The configured notification channel is used to deliver the alert. This turns a SQL query into a simple scheduled monitoring mechanism.&lt;/p&gt;

&lt;h2&gt;
  
  
  A Simple End-to-End Flow
&lt;/h2&gt;

&lt;p&gt;The complete workflow can be summarized as:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;SQL Query → Schedule → Condition → Notification&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The important idea is that you don't need a separate script or external scheduler just to repeatedly check a SQL-based condition.&lt;/p&gt;

&lt;p&gt;CH-Ops brings the query, condition, schedule, and notification configuration together in one interface.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why This Is Worth Setting Up
&lt;/h2&gt;

&lt;p&gt;The value of an alert is how early it catches a problem. A five-minute schedule can flag an issue long before someone notices it during a manual dashboard check. Keeping the alert rule and notification channel separate also means you can change how you receive alerts without rewriting the alert logic.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Email (SMTP)&lt;/strong&gt; is available in the open source version and is enough for many setups. CH-Ops Pro adds additional notification channels (&lt;strong&gt;Slack, Microsoft Teams, Google Chat, and PagerDuty&lt;/strong&gt;) for teams that need more immediate delivery.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Explore CH-Ops Pro features:&lt;/strong&gt;&lt;br&gt;
&lt;a href="https://www.ch-ops.io/blog/clickhouse-alerts-in-slack-teams-google-chat-pagerduty" rel="noopener noreferrer"&gt;https://www.ch-ops.io/blog/clickhouse-alerts-in-slack-teams-google-chat-pagerduty&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Further Reading
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;CH-Ops Installation Guide:&lt;/strong&gt;&lt;br&gt;
&lt;a href="https://www.ch-ops.io/blog/install-ch-ops-in-10-minutes-docker-binary-or-source" rel="noopener noreferrer"&gt;https://www.ch-ops.io/blog/install-ch-ops-in-10-minutes-docker-binary-or-source&lt;/a&gt;&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;CH-Ops Official GitHub Repository:&lt;/strong&gt;&lt;br&gt;
&lt;a href="https://github.com/Quantrail-Data/CH-Ops/tree/main" rel="noopener noreferrer"&gt;https://github.com/Quantrail-Data/CH-Ops/tree/main&lt;/a&gt;&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;CH-Ops Demo Video:&lt;/strong&gt;&lt;br&gt;
&lt;a href="https://www.linkedin.com/posts/quantrail-data_clickhouse-opensource-dataengineering-activity-7489909665477091328-QOIY/" rel="noopener noreferrer"&gt;https://www.linkedin.com/posts/quantrail-data_clickhouse-opensource-dataengineering-activity-7489909665477091328-QOIY/&lt;/a&gt;&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Next in the CHOps series
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;CH-Ops Access Control and App Data Backups&lt;/strong&gt;&lt;/p&gt;

</description>
      <category>devops</category>
      <category>clickhouse</category>
      <category>database</category>
    </item>
    <item>
      <title>Search ClickHouse® Logs Without Writing SQL</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Tue, 29 Sep 2026 16:16:04 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/search-clickhouser-logs-without-writing-sql-1785</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/search-clickhouser-logs-without-writing-sql-1785</guid>
      <description>&lt;p&gt;When something goes wrong in a ClickHouse® deployment, logs are often the first place to look. But investigating them traditionally means knowing which system table to query, writing SQL, choosing the right time range, and filtering through the results.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;CH-Ops&lt;/strong&gt; provides a visual way to explore these logs without writing SQL.&lt;/p&gt;

&lt;p&gt;From the &lt;strong&gt;Logs&lt;/strong&gt; section, you can choose a log type, select a time range, view an overview of the activity, and switch to &lt;strong&gt;Search&lt;/strong&gt; when you need to investigate individual log records.&lt;/p&gt;

&lt;h2&gt;
  
  
  What Is CH-Ops?
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;CH-Ops&lt;/strong&gt; is a browser-based operations platform for ClickHouse® that provides a visual interface for managing and monitoring ClickHouse® deployments.&lt;/p&gt;

&lt;p&gt;Instead of relying entirely on the command line or HTTP API, CH-Ops brings common operational tasks into a single web application. It supports capabilities such as SQL querying, cluster monitoring, user management, backups, alerts, dashboards, and log exploration.&lt;/p&gt;

&lt;p&gt;In this article, we'll focus on the &lt;strong&gt;Logs&lt;/strong&gt; section and how CH-Ops makes ClickHouse® log investigation easier without requiring SQL for every search.&lt;/p&gt;

&lt;h2&gt;
  
  
  Exploring ClickHouse® Logs in CH-Ops
&lt;/h2&gt;

&lt;p&gt;The &lt;strong&gt;Logs&lt;/strong&gt; section provides dedicated views for different ClickHouse® system logs:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Crash Log&lt;/strong&gt; - investigate server crashes&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Error Log&lt;/strong&gt; - understand recurring error types&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Text Log&lt;/strong&gt; - explore server messages across different log levels&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Session Log&lt;/strong&gt; - review login and logout activity&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Each log provides two views:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Overview&lt;/strong&gt; - understand the overall activity for a selected time range.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Search&lt;/strong&gt; - find specific records using filters relevant to that log.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Quick time ranges such as &lt;strong&gt;1h, 6h, 24h, 48h, 7d, and 30d&lt;/strong&gt; make it easy to focus on a specific period.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Crash Log: Investigating Server Crashes
&lt;/h2&gt;

&lt;p&gt;The &lt;strong&gt;Crash Log&lt;/strong&gt; is based on &lt;code&gt;system.crash_log&lt;/code&gt; and is useful when a ClickHouse® process unexpectedly stops or a node restarts.&lt;/p&gt;

&lt;p&gt;The Overview provides a quick summary of crash activity, including:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Total crashes&lt;/li&gt;
&lt;li&gt;Distinct signals&lt;/li&gt;
&lt;li&gt;Affected ClickHouse® versions&lt;/li&gt;
&lt;li&gt;Recent crash activity&lt;/li&gt;
&lt;li&gt;Crash distribution by signal and version&lt;/li&gt;
&lt;li&gt;Crash incidents&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Crash Log Overview&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Once a crash is identified, the &lt;strong&gt;Search&lt;/strong&gt; view can be used to inspect individual crash records using details such as the event time, signal, query information, and exception trace.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Crash Log Search&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;This makes it easier to move from &lt;strong&gt;“Did a crash happen?”&lt;/strong&gt; to &lt;strong&gt;“What exactly was recorded when it happened?”&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;An empty Crash Log can also be a good result. If no crashes were recorded during the selected period, there may simply be nothing to investigate.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Error Log: Finding Recurring Errors
&lt;/h2&gt;

&lt;p&gt;The &lt;strong&gt;Error Log&lt;/strong&gt; is based on &lt;code&gt;system.error_log&lt;/code&gt; and helps identify the types of errors occurring on the server.&lt;/p&gt;

&lt;p&gt;The Overview summarizes the selected period through metrics and visualizations such as:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Total errors&lt;/li&gt;
&lt;li&gt;Number of error types&lt;/li&gt;
&lt;li&gt;Local versus remote errors&lt;/li&gt;
&lt;li&gt;Top error types&lt;/li&gt;
&lt;li&gt;Latest error&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Error Log Overview&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The &lt;strong&gt;Top Error Types&lt;/strong&gt; chart provides a quick starting point. If one type accounts for a large portion of the errors, you can investigate that category further.&lt;/p&gt;

&lt;h3&gt;
  
  
  Searching Error Logs
&lt;/h3&gt;

&lt;p&gt;The &lt;strong&gt;Search&lt;/strong&gt; view lets you investigate error records without writing SQL. You can select a &lt;strong&gt;time range&lt;/strong&gt;, filter by &lt;strong&gt;Error Type&lt;/strong&gt;, search by &lt;strong&gt;Error Message&lt;/strong&gt;, and set the &lt;strong&gt;row limit&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Error Log Search&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The search results show the matching records along with details such as the &lt;strong&gt;event time, error type, error message, and query ID&lt;/strong&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Text Log: Understanding Server Activity
&lt;/h2&gt;

&lt;p&gt;The &lt;strong&gt;Text Log&lt;/strong&gt; is based on &lt;code&gt;system.text_log&lt;/code&gt; and provides a broader view of messages generated by the ClickHouse® server.&lt;/p&gt;

&lt;p&gt;Unlike the Error Log, it isn't limited to errors. It includes messages across different log levels, making it useful when something appears unusual but hasn't necessarily resulted in an error.&lt;/p&gt;

&lt;p&gt;The Overview provides information such as:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Total log lines&lt;/li&gt;
&lt;li&gt;Errors&lt;/li&gt;
&lt;li&gt;Warnings&lt;/li&gt;
&lt;li&gt;Number of loggers&lt;/li&gt;
&lt;li&gt;Recent activity&lt;/li&gt;
&lt;li&gt;Log volume by level&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Text Log Overview&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The &lt;strong&gt;Log Volume by Level&lt;/strong&gt; chart provides a quick picture of server activity across the selected period.&lt;/p&gt;

&lt;h3&gt;
  
  
  Searching Text Logs
&lt;/h3&gt;

&lt;p&gt;When you need to investigate a particular event, switch to the &lt;strong&gt;Search&lt;/strong&gt; tab.&lt;/p&gt;

&lt;p&gt;You can filter logs by &lt;strong&gt;time range&lt;/strong&gt;, &lt;strong&gt;log level&lt;/strong&gt;, and &lt;strong&gt;message&lt;/strong&gt;, and set the number of results to display.&lt;/p&gt;

&lt;p&gt;The Search view presents the individual log records, including information such as:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Event time&lt;/li&gt;
&lt;li&gt;Log level&lt;/li&gt;
&lt;li&gt;Query ID&lt;/li&gt;
&lt;li&gt;Logger name&lt;/li&gt;
&lt;li&gt;Message&lt;/li&gt;
&lt;li&gt;Source file and line&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Text Log Search&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;This lets you quickly narrow down relevant server activity and inspect the underlying records &lt;strong&gt;without writing SQL against &lt;code&gt;system.text_log&lt;/code&gt;&lt;/strong&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Session Log: Tracking Login Activity
&lt;/h2&gt;

&lt;p&gt;The &lt;strong&gt;Session Log&lt;/strong&gt; is based on &lt;code&gt;system.session_log&lt;/code&gt; and provides visibility into login and logout activity.&lt;/p&gt;

&lt;p&gt;The Overview summarizes session activity through:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Total events&lt;/li&gt;
&lt;li&gt;Successful logins&lt;/li&gt;
&lt;li&gt;Failed logins&lt;/li&gt;
&lt;li&gt;Logouts&lt;/li&gt;
&lt;li&gt;Distinct users&lt;/li&gt;
&lt;li&gt;Recent activity&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Session Log Overview&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The &lt;strong&gt;Login Outcomes&lt;/strong&gt; chart provides a quick view of successful logins and logouts, while &lt;strong&gt;Top Users&lt;/strong&gt; highlights the accounts generating session activity.&lt;/p&gt;

&lt;p&gt;The &lt;strong&gt;Search&lt;/strong&gt; view can be used when you need to investigate specific session activity. You can filter events by &lt;strong&gt;event type, user, or failure reason&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;This makes it easier to investigate authentication activity directly from CH-Ops without manually querying &lt;code&gt;system.session_log&lt;/code&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  From Overview to Search
&lt;/h2&gt;

&lt;p&gt;Across the different log types, CH-Ops follows a simple investigation pattern:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Choose a log → Select a time range → Load the Overview → Identify something interesting → Switch to Search → Investigate the records&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The &lt;strong&gt;Overview&lt;/strong&gt; helps you understand the bigger picture, while &lt;strong&gt;Search&lt;/strong&gt; helps you narrow down specific events and investigate the underlying records.&lt;/p&gt;

&lt;h2&gt;
  
  
  When Should You Use Each Log?
&lt;/h2&gt;

&lt;p&gt;Each log answers a different operational question:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;strong&gt;Log&lt;/strong&gt;&lt;/th&gt;
&lt;th&gt;&lt;strong&gt;Useful when you want to know&lt;/strong&gt;&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Crash Log&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;Did the ClickHouse® process crash, and what was recorded?&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Error Log&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;What types of errors are occurring, and how frequently?&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Text Log&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;What has the ClickHouse® server been reporting about its activity?&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Session Log&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;Who has been connecting, and are login attempts succeeding or failing?&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The Overview helps identify the problem, while Search helps investigate it.&lt;/p&gt;

&lt;h2&gt;
  
  
  From Investigation to Alerts
&lt;/h2&gt;

&lt;p&gt;Logs help you investigate what has already happened. For conditions that require proactive attention, &lt;strong&gt;CH-Ops Alert Rules&lt;/strong&gt; can notify you when defined conditions occur.&lt;/p&gt;

&lt;p&gt;The workflow becomes:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Monitor → Detect → Alert → Investigate&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Use &lt;strong&gt;Alerts&lt;/strong&gt; to be notified about important conditions, and &lt;strong&gt;Logs&lt;/strong&gt; to investigate the details behind them.&lt;/p&gt;

&lt;h2&gt;
  
  
  Bringing ClickHouse® Log Investigation Into One Interface
&lt;/h2&gt;

&lt;p&gt;ClickHouse® system logs contain valuable information for monitoring and troubleshooting, but accessing them shouldn't always require writing SQL.&lt;/p&gt;

&lt;p&gt;With &lt;strong&gt;CH-Ops&lt;/strong&gt;, you can explore &lt;strong&gt;Crash Log, Error Log, Text Log, and Session Log&lt;/strong&gt; from a single &lt;strong&gt;Logs&lt;/strong&gt; section. The &lt;strong&gt;Overview&lt;/strong&gt; gives you the big picture, while the &lt;strong&gt;Search&lt;/strong&gt; view lets you inspect the underlying records.&lt;/p&gt;

&lt;p&gt;Instead of starting every investigation with a SQL query, you can follow a simple visual workflow:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Select the log → Choose the time range → Load the data → Understand the overview → Search the records&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;This makes ClickHouse® log investigation more accessible and helps you move from identifying an issue to finding the relevant details - &lt;strong&gt;without writing SQL for every search.&lt;/strong&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Explore CH-Ops
&lt;/h2&gt;

&lt;p&gt;Want to learn more about CH-Ops and explore its features? Visit the official website and explore the resources below:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;CH-Ops Official Website&lt;/li&gt;
&lt;li&gt;Getting Started with CH-Ops&lt;/li&gt;
&lt;li&gt;CH-Ops Official GitHub Repository&lt;/li&gt;
&lt;li&gt;CH-Ops Demo Video&lt;/li&gt;
&lt;/ul&gt;




&lt;h1&gt;
  
  
  Next in the CHOps series
&lt;/h1&gt;

&lt;p&gt;&lt;strong&gt;CH-Ops Scheduled Alerts: Know Before Things Break&lt;/strong&gt;&lt;/p&gt;

</description>
      <category>clickhouse</category>
      <category>analytics</category>
      <category>devops</category>
      <category>database</category>
    </item>
    <item>
      <title>Watch your clusters live on CHOps</title>
      <dc:creator>Kanishga Subramani</dc:creator>
      <pubDate>Fri, 25 Sep 2026 06:20:52 +0000</pubDate>
      <link>https://dev.to/kanishga_subramani_49ad73/watch-your-clusters-live-on-chops-pb6</link>
      <guid>https://dev.to/kanishga_subramani_49ad73/watch-your-clusters-live-on-chops-pb6</guid>
      <description>&lt;h2&gt;
  
  
  Complete Visibility into Your ClickHouse® Infrastructure
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Monitor cluster health, analyze performance, track system resources, and gain real-time insights into every ClickHouse® node — all from a single, intuitive interface.&lt;/strong&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Introduction
&lt;/h2&gt;

&lt;p&gt;ClickHouse® has established itself as a high-performance, real-time analytical database. However, sustaining sub-second query performance at scale requires continuous monitoring of database internals — including background pools, thread scheduling, storage parts, and I/O amplification.&lt;/p&gt;

&lt;p&gt;Our custom management platform, CHOps, provides real-time telemetry across every layer of your ClickHouse® nodes, bridging hardware metrics with native database engine states.&lt;/p&gt;

&lt;h2&gt;
  
  
  Key Features &amp;amp; Dashboard Overview
&lt;/h2&gt;

&lt;h3&gt;
  
  
  1. Machine &amp;amp; Node Telemetry
&lt;/h3&gt;

&lt;p&gt;CHOps presents real-time gauge metrics updated dynamically, for example every 5 seconds.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Resource Allocation:&lt;/strong&gt; CPU utilization, OS Memory, ClickHouse®-specific Memory, and Thread Pool saturation.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Node Summary:&lt;/strong&gt; Instant visibility into active database versions, total databases, table counts, active queries, running merges, and mutations.&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  2. Disk &amp;amp; Storage Health Check
&lt;/h3&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Disk Partitioning:&lt;/strong&gt; Tracks raw space utilization, with the default storage shown at 457.00 GiB and 53.6% used.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Part Formats:&lt;/strong&gt; Monitors the breakdown between Compact (76.46%) and Wide (23.54%) storage parts to help understand write behavior.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;System Health Checks:&lt;/strong&gt; Automated monitoring across 15+ critical health states, including Delayed inserts, Spilling to disk, Keeper expired, Readonly replicas, and Broken disks.&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  3. Background Pools &amp;amp; Execution Shaping
&lt;/h3&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Pool Capacity vs. Usage:&lt;/strong&gt; Real-time histograms for background tasks including Merges, Fetches, Moves, Schedule, Buffer Flush, Distributed operations, and Message Brokers.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Efficiency &amp;amp; Compression Ratios:&lt;/strong&gt; Live tracking of Read Amplification, Write Amplification, and Read Compression.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Read Amplification: &lt;strong&gt;566.1 rows/row&lt;/strong&gt;
&lt;/li&gt;
&lt;li&gt;Read Compression: &lt;strong&gt;16.0x&lt;/strong&gt;
&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  4. Query &amp;amp; Time Execution Profiling
&lt;/h3&gt;

&lt;p&gt;&lt;strong&gt;"Where the Time Goes":&lt;/strong&gt; Granular thread-level decomposition showing exact thread usage spent on:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Disk Read&lt;/li&gt;
&lt;li&gt;CPU User&lt;/li&gt;
&lt;li&gt;Merge Exec&lt;/li&gt;
&lt;li&gt;CPU Kernel&lt;/li&gt;
&lt;li&gt;Disk Write&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;In-Flight Metrics:&lt;/strong&gt; Tracking active query locks, I/O in flight, open read/write operations, memory consumption by mapped files versus server runtime, and thread distribution.&lt;/p&gt;

&lt;h3&gt;
  
  
  Dashboard Snapshot
&lt;/h3&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Metric&lt;/th&gt;
&lt;th&gt;Value&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Storage Usage&lt;/td&gt;
&lt;td&gt;457 GiB (53.6% Used)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Part Breakdown&lt;/td&gt;
&lt;td&gt;Compact: 76.5% / Wide: 23.5%&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Read Compression&lt;/td&gt;
&lt;td&gt;16.0x&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Read Amplification&lt;/td&gt;
&lt;td&gt;566.1 rows/row&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Advantages of Using CHOps
&lt;/h2&gt;

&lt;h3&gt;
  
  
  Early Detection of Bottlenecks
&lt;/h3&gt;

&lt;p&gt;Instantly spot write-amplification spikes or thread pool exhaustion before queries degrade.&lt;/p&gt;

&lt;h3&gt;
  
  
  Data Compression Optimization
&lt;/h3&gt;

&lt;p&gt;Track part churn and row compression ratios to optimize storage policies and partition schemes.&lt;/p&gt;

&lt;h3&gt;
  
  
  Simplified Cluster Management
&lt;/h3&gt;

&lt;p&gt;Monitor cluster topology, replica delays, and Keeper/ZooKeeper connections in a single unified dashboard.&lt;/p&gt;




&lt;h1&gt;
  
  
  ClickHouse® Architecture: Clusters &amp;amp; Nodes Explained
&lt;/h1&gt;

&lt;h3&gt;
  
  
  1. What is a ClickHouse® Node?
&lt;/h3&gt;

&lt;p&gt;A Node is a single running instance of the ClickHouse® server process (&lt;code&gt;clickhouse-server&lt;/code&gt;) on an isolated physical machine, virtual server, or container.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Role:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;A node stores local table parts using engines such as MergeTree, receives SQL queries, parses and compiles them, and processes data using vectorized multi-threaded execution.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;In Your Dashboard:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The dashboard highlights individual nodes, such as &lt;code&gt;node-1&lt;/code&gt; (&lt;code&gt;localhost ::1:9000&lt;/code&gt;), showing specific runtime metrics such as uptime, active queries, CPU, and RAM allocation.&lt;/p&gt;

&lt;h3&gt;
  
  
  2. What is a ClickHouse® Cluster?
&lt;/h3&gt;

&lt;p&gt;A Cluster is a logical grouping of multiple ClickHouse® nodes working together to handle large analytical datasets across distributed infrastructure.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Sharding &amp;amp; Replication:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Clusters can split dataset subsets across different nodes through sharding or duplicate data for high availability through replication, using Distributed table engines and ClickHouse® Keeper/ZooKeeper.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Horizontal Scaling:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;When data volume or query load grows beyond a single machine's capacity, adding nodes to a cluster expands storage capacity and query execution power in parallel.&lt;/p&gt;

&lt;h2&gt;
  
  
  Conclusion
&lt;/h2&gt;

&lt;p&gt;As analytical workloads grow in volume and complexity, maintaining deep visibility into database internals becomes a necessity — not a luxury.&lt;/p&gt;

&lt;p&gt;CHOps bridges the gap between high-level database administration and low-level engine telemetry, helping keep ClickHouse® clusters resilient, performant, and cost-efficient.&lt;/p&gt;

&lt;p&gt;By transforming complex metrics — such as memory footprints, thread pool allocation, write amplification, and part formats — into clear, real-time visual insights, CHOps helps data engineers and DevOps teams identify potential bottlenecks before they impact production.&lt;/p&gt;

&lt;h3&gt;
  
  
  Why CHOps telemetry is different
&lt;/h3&gt;

&lt;p&gt;Don't confuse general system-level host monitoring, such as standard Prometheus or Grafana OS exporters, with the engine-native telemetry provided by CHOps.&lt;/p&gt;

&lt;p&gt;Standard infrastructure tools primarily track raw host metrics.&lt;/p&gt;

&lt;p&gt;CHOps explicitly surfaces ClickHouse®-specific engine internals, including:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Background merge queues&lt;/li&gt;
&lt;li&gt;Part formats such as Compact vs. Wide&lt;/li&gt;
&lt;li&gt;Read/write amplification&lt;/li&gt;
&lt;li&gt;Thread pool scheduling&lt;/li&gt;
&lt;li&gt;ClickHouse®-specific memory and execution metrics&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;This blog focuses specifically on deep ClickHouse® database performance optimization using CHOps.&lt;/p&gt;

&lt;h2&gt;
  
  
  References
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;CH-OPS Open Source (OSS) Version – GitHub:&lt;/strong&gt; &lt;a href="https://github.com/Quantrail-Data/CH-Ops" rel="noopener noreferrer"&gt;https://github.com/Quantrail-Data/CH-Ops&lt;/a&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;CH-OPS Website:&lt;/strong&gt; &lt;a href="https://www.ch-ops.io/" rel="noopener noreferrer"&gt;https://www.ch-ops.io/&lt;/a&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;CH-OPS Installation Guide:&lt;/strong&gt; &lt;a href="https://www.ch-ops.io/blog/install-ch-ops-in-10-minutes-docker-binary-or-source" rel="noopener noreferrer"&gt;https://www.ch-ops.io/blog/install-ch-ops-in-10-minutes-docker-binary-or-source&lt;/a&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;CH-Ops Demo Video:&lt;/strong&gt; &lt;a href="https://www.linkedin.com/posts/quantrail-data_ClickHouse-opensource-dataengineering-activity-7489909665477091328-QOIY/" rel="noopener noreferrer"&gt;https://www.linkedin.com/posts/quantrail-data_ClickHouse-opensource-dataengineering-activity-7489909665477091328-QOIY/&lt;/a&gt;
&lt;/li&gt;
&lt;/ul&gt;




&lt;h1&gt;
  
  
  Next in the CHOps series
&lt;/h1&gt;

&lt;p&gt;&lt;strong&gt;Search ClickHouse® Logs Without Writing SQL&lt;/strong&gt;&lt;br&gt;
&lt;a href="https://www.ch-ops.io/blog/search-clickhouse-logs-without-writing-sql" rel="noopener noreferrer"&gt;https://www.ch-ops.io/blog/search-clickhouse-logs-without-writing-sql&lt;/a&gt;&lt;/p&gt;

</description>
      <category>clickhouse</category>
      <category>devops</category>
      <category>analytics</category>
      <category>database</category>
    </item>
  </channel>
</rss>
