DEV Community: Kanishga Subramani

Audit Logs in CHOps Pro: Who Did That?

Kanishga Subramani — Fri, 23 Oct 2026 17:56:13 +0000

Audit logs answer one of the most important questions in a shared ClickHouse® cluster: who did that?

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.

If nobody wrote it down, the answer is guesswork. That is the gap an audit log closes.

What an Audit Log Actually Is

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.

It works alongside the logs you already have, and each one answers a different question.

Your application logs explain how the software behaved. ClickHouse®'s system.query_log and system.session_log show what reached each server.

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.

Run all three and you have the complete picture: the software's view, the database's view, and the people behind both.

Why It Matters

Accountability: In an environment where several engineers share admin access, a name and a timestamp on every change turns a shrug into a fact.

Forensics: 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.

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

Deterrence: People are more careful when they know actions are recorded. That effect is free.

What CHOps Pro Records

Every audited event captures a consistent set of fields:

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

Failures are recorded alongside successes. A rejected attempt to change a setting is often more interesting than a successful one.

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.

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.

Key Features

Two views

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.

Filter and search

Narrow by user, category, action, status or date range, and run a free-text search across SQL text, target names and details.

Tamper-evident exports

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.

Admin-only access

Every audit endpoint is restricted to administrators.

Isolated storage

The audit trail lives in its own database, separate from application data, so its history stands on its own.

Never in the way

Audit writes are fire-and-forget. A full or unavailable audit database cannot slow down or break the main application.

Built for volume

Composite indexes and cursor-based pagination keep queries fast however deep the history runs.

Retention you control

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

A Five-Minute Investigation

Here is what using it actually looks like.

Queries started failing on your staging cluster around 14:00, and nobody owns up to a change.

Step 1: Open the Overview tab.

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.

Step 2: Narrow down the Logs tab.

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.

Twenty events. Three users.

Step 3: Filter by category.

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.

Step 4: Expand the row.

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.

One of the two changes came back as an error, with the message attached.

One expanded entry answers who, what, when, from where, against which cluster, and whether it worked.

Step 5: Export the report.

If the answer needs to go to someone outside the team, export the filtered view to PDF.

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.

Five minutes, no guesswork, and a document at the end of it.

What to Audit in Your Own Application

The specifics above are CHOps, but the design decisions behind them apply to any application adding an audit trail.

1. Record failures, not just successes.

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.

2. Capture the actor's role at the time, not just their name.

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.

3. Never let auditing break the request.

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.

4. Keep it separate from application data.

A distinct store means the trail is not caught up in migrations, resets or restores of your main database.

5. Cap what you store per event.

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.

6. Plan retention on day one.

Decide how long you keep entries, delete in batches so cleanup never holds a long lock, and reclaim the space afterwards.

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

7. Index for the queries you will actually run.

Audit tables are written constantly and read in narrow slices: this user, this window, this category.

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.

8. Restrict who can read it.

An audit log contains a map of your operational activity. Treat it as sensitive and put it behind your strongest role check.

Common Questions

Does it slow the application down?

No. Writes are fire-and-forget and go to a local store that never sits in the request path.

How much space does it need?

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.

Who can see the audit log?

Administrators only.

Why not just use the database's own logs?

ClickHouse®'s system tables tell you what reached the server, per node, for as long as their own retention allows.

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.

The two are complementary. Use both.

Conclusion

When something changes in your ClickHouse® environment, you shouldn't have to guess who did it.

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

With the right audit trail, investigations take minutes instead of hours, accountability is clear, and compliance evidence is always within reach.

When the question is "Who did that?", CHOps Pro gives you the answer.

Getting It

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

The Community edition records who can log in and what role they hold; Pro records what they actually did.

To enable it, visit https://ch-ops.io or contact Quantrail™ Data.

Installation Guide: CH-Ops Installation Guide

Documentation: https://ch-ops.io/docs

GitHub: https://github.com/Quantrail-Data/CH-Ops

Next in the CHOps series: Secure SSO in CH-OPS: Google, Keycloak & Okta

Scheduled Email Reports in CH-Ops Pro for Your ClickHouse® Database

Kanishga Subramani — Sat, 17 Oct 2026 07:24:36 +0000

Introduction

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.

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.

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.

What Is CH-Ops?

CH-Ops is a browser-based operations platform for ClickHouse® that provides a visual interface for managing and monitoring ClickHouse® deployments.

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.

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.

Why Use Scheduled Email Reports in CH-Ops Pro?

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.

Scheduled Email Reports automate these steps.

With CH-Ops Pro, you can:

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

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

How Scheduled Email Reports Work

The Scheduled Email Reports workflow in CH-Ops Pro follows a simple process.

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.

Let's walk through the process.

Step 1: Configure an Email Notification Channel

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.

First, open:

Custom Alerts → Scheduled Reports

If no email channel has been configured, CH-Ops displays a message prompting you to create one.

To configure the channel, go to:

Control Panel → Notification Channels

Create a new notification channel and select Email (SMTP) as the channel type.

Enter the SMTP details required by your email provider:

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

For this walkthrough, the email channel is named Testing-channel.

After entering the required details, click Create. Once the channel is created successfully, it becomes available for selection when you configure a Scheduled Email Report.

Note: Use the SMTP settings provided by your email service or organization, and keep SMTP credentials secure.

Step 2: Create and Configure a Scheduled Email Report

After configuring the email channel, navigate to:

Custom Alerts → Scheduled Reports

If no reports have been created yet, the Scheduled Email Reports page displays an empty state with the New Report button.

Click New Report to open the report configuration form.

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.

Configure the Report Details

In the New Report form, configure the following fields:

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

Step 3: Validate the SQL Query

Before creating the report, click Validate SQL to check the query and preview its results.

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 query, avg_ms, and runs.

In this example, the interface shows:

Query valid. Preview: 5 rows, 3 columns.

Review the preview to confirm that the query returns the expected data. Then close the preview and click Create Report.

Step 4: View and Test the Scheduled Report

After the report is created, it appears on the Scheduled Email Reports page.

The report card provides a quick overview of the configured report, including its:

Report name
Description
Schedule
Email channel
Recipient
Enabled status
Run status

The report card provides actions such as Edit, Send Test, Enable/Disable, and Delete.

Test the Report

Before waiting for the scheduled execution, use Send Test to verify the report configuration and email delivery.

A test run helps confirm that:

The SQL query executes successfully.
The expected results are generated.
The configured email channel is working.
The report is delivered to the intended recipient.

This provides a quick way to verify the complete configuration before relying on the scheduled execution.

Step 5: Receive the Report by Email

Once the report runs, CH-Ops sends the generated results to the configured recipients.

The generated email contains the report information along with the results returned by the SQL query.

The email includes several useful sections.

Report Information

The email header displays the report name and execution timestamp.

For example:

ClickHouse® Slow Query Report

The email also includes the time at which the report was generated.

Report Details

The generated email presents the report information and query results in a structured format.

Query Results

The SQL query results are presented in a structured table.

For the example slow-query report, the results include:

query: The executed query text.
avg_ms: Average query execution time.
runs: Number of executions.

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

Managing Scheduled Email Reports

After creation, reports can be managed from the Scheduled Email Reports page.

CH-Ops allows you to:

Edit an existing report.
Enable or disable a report.
Send a test report.
Delete a report.

These options make it easier to maintain scheduled reports as reporting requirements change.

Use Cases for Scheduled Email Reports

Scheduled Email Reports can simplify a variety of recurring ClickHouse® reporting tasks, including:

Query Performance Reports

Monitor slow, frequently executed, or resource-intensive queries.

Daily Operational Reports

Receive daily summaries of ClickHouse® activity, system metrics, or other operational data.

Usage Reports

Track recurring usage trends and other operational information.

Team Reports

Automatically share recurring query results with relevant teams and stakeholders.

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

Conclusion

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.

The workflow brings everything together in a single process:

Configure Email → Configure Report → Validate SQL → Create and Test → Receive Report

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.

Explore CH-Ops

Want to learn more about CH-Ops and explore its features? Visit the official website and explore these resources:

CH-Ops Official Website
Getting Started with CH-Ops
CH-Ops Official GitHub Repository
CH-Ops Demo Video

Next in the CH-Ops series: ClickHouse® Alerts in Slack, Teams, Google Chat, and PagerDuty.

Manage ClickHouse® Without SSH with CH-Ops Pro

Kanishga Subramani — Wed, 14 Oct 2026 10:26:37 +0000

You need to restart ClickHouse®.

So you open a terminal.

SSH into the server.

Check whether the service is running.

Look through the logs.

Maybe edit a configuration file.

Restart ClickHouse® again.

Then check the logs one more time.

None of these steps are particularly difficult.

But there is a simple question worth asking:

Do you really need SSH for all of them?

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.

That's what Remote Admin in CH-Ops Pro is designed to solve.

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

No SSH session is required for those routine operations.

The Part of ClickHouse® Administration That Usually Lives in SSH

CH-Ops gives you a browser-based interface for managing ClickHouse®.

You can work with your databases, queries, monitoring, and other ClickHouse® operations.

But eventually, you run into questions that SQL alone can't answer:

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

Traditionally, those answers live on the server itself.

So the workflow becomes:

CH-Ops
   |
   | Database operations
   v
ClickHouse®

SSH
   |
   +-- systemctl
   +-- journalctl
   +-- log files
   +-- configuration files

You're using two different interfaces to manage one ClickHouse® deployment.

Remote Admin brings those routine server-side operations into CH-Ops Pro.

What Remote Admin Gives You

Remote Admin focuses on three areas:

Service Management
Logs
Server Settings

Together, they cover many of the routine tasks you would otherwise perform through SSH.

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.

The idea is simple:

Manage more of your ClickHouse® deployment from one place.

1. Manage the ClickHouse® Service

Start with the most basic question:

Is ClickHouse® actually running?

The Service Management page gives you the current state of the ClickHouse® service.

You can see information such as:

Load state
Active state
Sub-state
Unit state

So instead of connecting to the server and running:

systemctl status clickhouse-server

you can check the service state directly from CH-Ops Pro.

For example:

Load State     loaded
Active State   active
Sub State      running
Unit State     enabled

This gives you an immediate view of whether the service is loaded, active, and running.

Restart ClickHouse® Without SSH

Configuration changes are one common reason you might need to restart ClickHouse®.

Normally, that means running:

sudo systemctl restart clickhouse-server

With Remote Admin, you can perform the same operational task through the interface.

Select Restart ClickHouse®.

The service controls also take the current state of the service into account.

So you aren't simply presented with a collection of terminal commands disguised as buttons.

You can check the state, perform the required action, and verify the result from the same page.

Stop ClickHouse® When You Need To

Remote Admin also provides a Stop ClickHouse® action when the service is running.

This can be useful during maintenance or when you need to intentionally take a ClickHouse® node offline.

The workflow is straightforward:

Check service
     |
     v
Choose action
     |
     +---- Start
     |
     +---- Stop
     |
     +---- Restart
     |
     v
Verify state

No terminal required.

2. Search ClickHouse® Logs Without Opening a Terminal

Service status tells you what is happening.

Logs often tell you why.

When ClickHouse® starts behaving unexpectedly, the usual response is to SSH into the server and start searching through log files.

You might use:

grep
less
tail
journalctl

Then narrow things down by timestamp, severity, component, or keyword.

The Logs section in CH-Ops Pro puts that investigation into the same interface.

The Search interface lets you filter logs using:

From
To
Level
Limit
Offset
Component
Keyword

Available log levels include:

Trace
Debug
Information
Warning
Error
Fatal

Instead of manually searching through a large log file, you can narrow the results to exactly what you're looking for.

Search the Time Window That Actually Matters

Suppose ClickHouse® started producing errors around 11:20.

You don't necessarily need to inspect the entire day's logs.

Start with the incident window:

From:  11:20
To:    11:30
Level: Error

Then add a component or keyword if you need to narrow the results further.

The question changes from:

"What's in the ClickHouse® log?"

to:

"What errors did ClickHouse® produce during the period when the incident started?"

That's a much easier question to answer.

And during an incident, reducing the amount of information you have to scan can make troubleshooting considerably faster.

ClickHouse® Logs Aren't the Only Source of Information

Sometimes the ClickHouse® server log isn't enough.

The system journal can provide additional information about what happened to the ClickHouse® service.

That's where journalctl becomes useful.

Instead of leaving CH-Ops and running:

journalctl -u clickhouse-server

you can work with journal logs through the Remote Admin Logs section.

This gives you two useful perspectives:

ClickHouse® logs: Database-level events.

journalctl: Service-level events.

Together, they provide better troubleshooting context.

You can investigate both without switching to a terminal.

3. Change ClickHouse® Configuration Without SSH

Now we get to one of the most useful parts of Remote Admin.

ClickHouse® configuration is file-based.

Custom configuration is commonly placed under:

/etc/clickhouse-server/config.d/

For example:

config.d/custom.xml

Normally, changing that file means connecting to the server first:

ssh server
cd /etc/clickhouse-server/config.d
vim custom.xml

Then you need to validate the configuration.

Save it.

Restart ClickHouse®.

And finally, check whether ClickHouse® came back successfully.

With CH-Ops Pro, you can edit the configuration directly from Server Settings.

The interface provides access to:

config.d/custom.xml

Along with actions for:

Reload
Validate XML
Save Configuration

So a configuration change doesn't require you to open an SSH session just to find and edit the file.

Validate Before Saving

This is an important part of the configuration workflow.

ClickHouse® configuration uses XML, and a malformed configuration can cause problems when ClickHouse® attempts to load it.

A missing closing tag or incorrect XML structure is enough to cause trouble.

That's why Server Settings provides a dedicated Validate XML action.

The interface also warns:

"Invalid XML may cause ClickHouse® to fail starting."

The workflow becomes:

Create/Edit configuration
       |
       v
  Validate XML
       |
       +---- Invalid
       |       |
       |       v
       |      Fix
       |
       v
Save Configuration

You get an opportunity to catch an XML problem before saving the configuration.

Configuration Changes Still Require a Restart

There is one important thing to keep in mind.

Remote Admin doesn't make every ClickHouse® configuration change dynamic.

For the configuration managed through Server Settings, changes require a service restart to take effect.

So the complete workflow is:

Create/Edit
  |
  v
Validate XML
  |
  v
Save Configuration
  |
  v
Restart ClickHouse®
  |
  v
Verify service

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

Put the Three Together

This is where Remote Admin becomes more than three separate features.

Imagine a ClickHouse® node suddenly starts failing.

You don't know whether the problem is:

The ClickHouse® service
A configuration change
An application error
A service-level failure

You can work through the investigation from one place.

Step 1: Check the Service

Open:

Remote Admin → Service Management

Check whether ClickHouse® is active and running.

Step 2: Investigate the Logs

Open:

Remote Admin → Logs

Search around the time the problem started.

Start with:

Level: Error

Then narrow the results using a component or keyword.

If necessary, inspect the journalctl logs as well.

Step 3: Check the Configuration

If the logs point towards a configuration problem, open:

Remote Admin → Server Settings

Inspect the relevant configuration file.

Step 4: Make the Change

Update the configuration.

Run:

Validate XML

If the configuration is valid, save it.

Step 5: Restart ClickHouse®

Return to Service Management.

Select:

Restart ClickHouse®

Step 6: Verify

Check the service state again.

If everything is healthy, you're done.

If it isn't, go back to the Logs section and continue investigating.

The workflow becomes:

Service State
     |
     v
   Logs
     |
     v
Configuration
     |
     v
  Restart
     |
     v
  Verify
     |
     +--------> Logs

That's the real value of Remote Admin.

It's not simply about replacing systemctl with a button.

It's about keeping the investigate → change → restart → verify workflow together.

You Still Have SSH When You Need It

Remote Admin isn't intended to replace SSH.

There will always be situations where direct server access is necessary.

Maybe you're debugging networking.

Maybe you need to inspect the filesystem.

Maybe you're investigating an operating-system issue.

Maybe you need to perform an operation that isn't exposed through Remote Admin.

SSH remains the right tool for those situations.

The idea is much simpler:

Don't use SSH when you don't need SSH.

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.

Less Switching. More ClickHouse® Administration.

Running ClickHouse® isn't only about writing SQL.

At some point, you have to deal with the service underneath the database.

You need to know:

Is the service running?
Why did it stop?
What does the log say?
Did the configuration change?
Can I safely restart it?

Traditionally, those questions live outside the database interface.

Service state lives in systemd.

Logs live in log files and the system journal.

Configuration lives in XML files.

And the administrator moves between all of them through SSH.

CH-Ops Pro Remote Admin brings those routine operational tasks into the ClickHouse® administration workflow.

You still have the server.

You still have the underlying system tools.

But for common ClickHouse® administration tasks, you don't necessarily need to open another terminal.

Manage ClickHouse® Without Leaving CH-Ops Pro

With CH-Ops Pro Remote Admin, you can:

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

The important part isn't that CH-Ops Pro gives you graphical versions of systemctl or journalctl.

It's that database administration and server administration no longer have to feel like two completely separate workflows.

Less context switching.

Fewer SSH sessions for routine tasks.

One interface for managing your ClickHouse® deployment.

That's Remote Admin in CH-Ops Pro.

Next in CH-Ops Series

CH-Ops: A ClickHouse® Admin Tool Built for Simplicity

CH-Ops: A ClickHouse® Admin Tool Built for Simplicity

Kanishga Subramani — Sat, 10 Oct 2026 05:58:45 +0000

Introduction

CH-Ops is a lightweight ClickHouse® admin tool designed to make ClickHouse® administration easier without making the underlying deployment more complicated.

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.

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.

They give CH-Ops a small operational footprint while still allowing it to provide a substantial administration surface.

The Architecture

At a high level, CH-Ops looks like this:

                         Browser
                            |
                            | HTTP
                            v
                    +----------------+
                    |     CH-Ops     |
                    |                |
                    | React frontend |
                    | Express API    |
                    | SQLite state   |
                    +-------+--------+
                            |
                            | HTTP / HTTPS
                            v
                  +---------------------+
                  |     ClickHouse®     |
                  |                     |
                  |  Node 1   Node 2    |
                  |  Node 3   ...      |
                  +---------------------+

CH-Ops is a separate management application.

The ClickHouse® cluster remains the database.

That separation is intentional.

We wanted the cost of adopting CH-Ops to look roughly like this:

Install CH-Ops somewhere
        |
        v
Give it persistent storage
        |
        v
Give it network access
        |
        v
Point it at ClickHouse®

Not this:

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

That distinction is what we mean by lightweight.

It is not primarily about how many features CH-Ops has.

It is about how many things you have to install, coordinate, and operate.

Why HTTP?

ClickHouse® already exposes an HTTP interface.

The standard HTTP interface commonly listens on port 8123, while the native protocol commonly uses port 9000. SQL can be sent directly over HTTP:

curl "http://localhost:8123/" \
  --data-binary "SELECT version()"

That gives an application a straightforward way to communicate with ClickHouse® without implementing the native TCP protocol.

For CH-Ops, that is a useful boundary.

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.

We don't need CH-Ops to become another component inside the ClickHouse® server.

We need a reliable way to communicate with it.

HTTP provides that interface.

HTTP Is Not a Shortcut

It is worth being precise here.

Choosing HTTP does not mean we think the ClickHouse® native protocol is inferior.

It isn't.

There are workloads where the native protocol is exactly what an application should use.

Our decision was based on the requirements of an administration application.

HTTP gives us a standard network boundary that fits naturally into infrastructure people already operate:

Reverse proxies
Load balancers
Ingress controllers
TLS termination
Kubernetes networking
Firewalls
Monitoring systems

That doesn't automatically make HTTP faster.

It makes it operationally convenient.

For an administration UI, that trade-off is valuable.

The Biggest Benefit: No Agent on ClickHouse®

This is probably the most important architectural decision in CH-Ops.

There is no CH-Ops process that needs to run on every ClickHouse® node.

Consider an agent-based design:

CH-Ops
  |
  +---- Agent on Node 1
  |
  +---- Agent on Node 2
  |
  +---- Agent on Node 3

That immediately creates another operational system.

Every ClickHouse® node now has to maintain a CH-Ops agent.

The agent needs its own:

Binary
Configuration
Permissions
Upgrade process
Logs
Health checks
Network rules

And if the cluster grows from three nodes to thirty, the operational footprint of that architecture grows with it.

CH-Ops takes a different approach:

                 CH-Ops
                   |
              HTTP / HTTPS
                   |
        +----------+----------+
        |          |          |
     Node 1     Node 2     Node 3
  ClickHouse® ClickHouse® ClickHouse®

The ClickHouse® nodes don't need to know that CH-Ops exists.

They simply expose the interfaces ClickHouse® already provides.

This keeps the management plane separate from the database itself.

Why Not Use the Native Protocol?

The native ClickHouse® protocol has advantages, and there are good reasons for applications to use it.

But CH-Ops isn't a high-throughput analytical client.

It is an administration application.

Its workload looks more like:

Inspect system tables
Execute administrative SQL
Monitor queries
Inspect merges and mutations
Check replication
Inspect distributed DDL
Manage users
Run EXPLAIN
Build dashboards

For these operations, the HTTP interface gives us an appropriate transport without requiring CH-Ops to introduce another protocol dependency into the ClickHouse® deployment.

There's also a broader infrastructure advantage.

HTTP is something almost every modern infrastructure stack already understands.

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.

HTTP Still Has Protocol Semantics

Using HTTP doesn't eliminate ClickHouse®-specific behavior.

For example, an HTTP 200 OK 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.

ClickHouse® documents mechanisms such as wait_end_of_query=1 for handling this behavior.

This is an important distinction.

We aren't treating HTTP as “simple because it is HTTP.”

A serious ClickHouse® client still needs to correctly handle:

Authentication
Query parameters
Response formats
Streaming
Compression
Timeouts
Large result sets
HTTPS
ClickHouse®-specific settings
Query errors

The advantage is that all of those concerns remain at the network boundary.

CH-Ops doesn't need to be installed inside the ClickHouse® servers to deal with them.

The Other Half: One Binary

The second major design decision is on the CH-Ops side.

CH-Ops has a React frontend and an Express backend, running on Bun.

In development, those are naturally separate pieces.

For production, we can compile the application into a single executable.

Conceptually:

React source
     |
     | vite build
     v
   dist/
     |
     +-------------------+
                         |
Express backend ---------+
                         |
                         | bun build --compile
                         v
                  +--------------+
                  |    CH-Ops    |
                  |    binary    |
                  +--------------+

The frontend is built into dist/.

That output, together with the backend and its dependencies, is bundled into the compiled executable.

The result is a much simpler deployment artifact.

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.

The deployment can look like this:

Server
├── chops
├── .env
└── data/
    └── chops.db

That is a meaningful operational simplification.

Single Binary Does Not Mean Zero State

There is an important nuance here.

A single executable does not mean CH-Ops has no external requirements.

CH-Ops still needs:

An operating system
Network connectivity
Persistent storage
Configuration
Secrets
Access to ClickHouse®

CH-Ops also maintains its own application state in SQLite.

That state includes things such as cluster definitions, users, dashboards, and alerts.

So the more accurate model is:

CH-Ops binary
     +
configuration
     +
persistent application state
     +
network access

The benefit of the compiled binary is that the application runtime and application code don't need to be assembled on the target machine.

That is the useful property.

Why SQLite Belongs in the Picture

We deliberately keep CH-Ops' own state separate from ClickHouse®.

Conceptually:

              CH-Ops
                |
       +--------+--------+
       |                 |
       v                 v
    SQLite           ClickHouse®
       |                 |
 CH-Ops state        User's data

SQLite stores state belonging to the management application.

ClickHouse® remains the database being managed.

This separation has a few useful consequences.

CH-Ops doesn't need to create a special database inside ClickHouse® just to remember its own configuration.

It doesn't need to modify the ClickHouse® data model to maintain application state.

And the CH-Ops state can move with the CH-Ops deployment.

The application remains self-contained.

One Binary Does Not Mean One Deployment Model

Some environments prefer to run applications directly.

Others standardize on containers.

We don't want the architecture to force one answer.

The single executable works in both models:

              CH-Ops binary
                   |
          +--------+--------+
          |                 |
          v                 v
     Run directly       Container

A container can still provide isolation, resource limits, orchestration, and deployment consistency.

The difference is that the container isn't needed simply to provide Node.js, install npm dependencies, or assemble the frontend and backend.

It becomes a packaging and isolation mechanism rather than a prerequisite for running the application.

Why Bundle the Frontend?

CH-Ops is a browser application, so there are naturally two sides:

Frontend
Backend

Those can be deployed independently.

But they don't have to be.

We build the React application and package the resulting assets into the compiled application.

That gives us a production architecture like this:

Browser
   |
   v
+----------------------+
|       CH-Ops         |
|                      |
| React static assets  |
| Express API          |
| Bun runtime          |
+----------+-----------+
           |
           | HTTP / HTTPS
           v
      ClickHouse®

One application serves the UI and API.

One port exposes it.

One artifact represents the application version.

That makes upgrades and deployments considerably simpler.

Kubernetes Reinforces the Same Architecture

The separation becomes particularly interesting when CH-Ops is used with Kubernetes-managed ClickHouse® clusters.

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.

The architecture becomes:

                         +----------+
                         |  Browser |
                         +----+-----+
                              |
                              v
                         +---------+
                         | CH-Ops  |
                         +----+----+
                              |
                 +------------+------------+
                 |                         |
                 v                         v
          Kubernetes API             ClickHouse® HTTP
                 |                         |
                 v                         v
          Cluster metadata            Database nodes

Again, CH-Ops doesn't need to live inside the ClickHouse® cluster to manage it.

It can interact with the control plane and the database through their existing interfaces.

That is exactly the separation we wanted.

Lightweight Doesn't Mean Limited

This distinction is important.

CH-Ops can provide functionality such as:

Live query monitoring
Query history
Merges and mutations
Replication information
Distributed DDL
SQL editing
EXPLAIN
Query profiling
Dashboards
Exports
User and role management
Backups and restores
Kubernetes insights

None of that requires installing a CH-Ops agent across the ClickHouse® cluster.

So “lightweight” isn't a statement about functionality.

It is a statement about operational coupling.

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.

The Design Principle

This is ultimately why we made these choices.

HTTP keeps the ClickHouse® integration at the network boundary.

The single executable keeps the CH-Ops application deployment at the executable boundary.

SQLite keeps CH-Ops' own state separate from ClickHouse®.

Together, they produce a clean separation:

             Management plane
          +---------------------+
          |       CH-Ops        |
          | UI + API + state    |
          +----------+----------+
                     |
                   HTTP/S
                     |
          +----------v----------+
          |     ClickHouse®     |
          |       cluster       |
          +---------------------+
                  Data plane

CH-Ops can be upgraded independently.

ClickHouse® can be upgraded independently.

The ClickHouse® nodes don't need a CH-Ops installation.

The CH-Ops server doesn't need a ClickHouse® installation.

And the management application doesn't need to become part of the database itself.

That's the architecture we wanted.

The Trade-offs Are Real

This design isn't universally better.

The native ClickHouse® protocol remains valuable for applications that need its specific capabilities and performance characteristics.

A single binary doesn't eliminate configuration, persistent state, networking, or secrets.

SQLite isn't intended to replace a full production database for every possible application workload.

And when Kubernetes integration is involved, CH-Ops still needs appropriate network access to the Kubernetes API and ClickHouse®.

Lightweight doesn't mean infrastructure-free.

It means the infrastructure boundary is deliberately small.

The Real Meaning of Lightweight

When we started building CH-Ops, we didn't want to create another system that administrators had to distribute across every ClickHouse® node.

We wanted to build a management layer that could sit beside ClickHouse® rather than inside it.

That led naturally to two decisions:

Use the interface ClickHouse® already provides.

Package the management application into the smallest practical deployment artifact.

HTTP gives CH-Ops a clean network boundary.

The compiled executable gives CH-Ops a clean deployment boundary.

SQLite keeps the application's state independent.

And the absence of agents keeps the ClickHouse® cluster untouched.

So the real architecture is surprisingly simple:

Install CH-Ops somewhere
        |
        v
Give it persistent storage
        |
        v
Give it network access
        |
        v
Point it at ClickHouse®

No CH-Ops agent to distribute across the cluster.

No separate frontend service required for the production build.

No Node.js installation required on the target machine for the compiled binary.

No requirement for CH-Ops to become part of the ClickHouse® server.

That is what lightweight by design means to us.

Not fewer features

Native ClickHouse® Backups in CH-Ops: A Practical Guide

Kanishga Subramani — Wed, 07 Oct 2026 15:00:40 +0000

Native ClickHouse® Backups in CH-Ops: A Practical Guide

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.

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 Backups → Data Lifecycle.

If you only remember one thing before reading on: these two features share a destination (Storage Profiles) and nothing else.

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.

How this actually works under the hood

ClickHouse® has a BACKUP statement built in. You run it against the server, point it at a destination, and the server writes the data there.

CH-Ops doesn't copy anything itself — it composes that statement, sends it, and reports back what happened.

That one fact explains three things that otherwise look odd:

The server needs access to the destination, not CH-Ops. 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.
A backup is a directory of files, not one file. 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.
Consistency is per-table, not necessarily across tables at one instant. Fine for most purposes, but worth knowing before you lean on cross-table consistency for something that actually needs it.

Before you can back anything up

CH-Ops needs somewhere to send backups — a bucket and credentials to reach it.

That's a storage profile, configured once under Control Panel → Storage Profiles, and it's the same profile system used by App Data Backup.

Set the destination up once, and both features can use it.

Worth testing the profile before trusting it — but be precise about what a passing test actually proves.

It confirms CH-Ops 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.

A profile that validates here can still fail at backup time for exactly that reason.

Taking a manual backup

Go to:

Backups → Data Lifecycle → Manual Backup

The flow:

Choose a scope — whole cluster, one database, or one table.
Choose a storage profile.
Set any options. Defaults are reasonable for a first backup.
Read the preview — the exact statement, credentials masked, updating live as you change choices.
Run it.

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.

It's also, incidentally, the fastest way to learn BACKUP syntax: change one option and watch which clause appears or disappears.

Choosing a scope

Scope	Statement	Use when
Cluster	`BACKUP ALL`	You want everything
Database	`BACKUP DATABASE x`	One application's data
Table	`BACKUP TABLE x.y`	Before a risky change to one table

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.

Skip anything rebuildable, like staging copies or materialized data you could recompute instead.

And keep an eye on time: a cluster backup on a large deployment reads a lot of data and takes a while.

Save that scope for when you actually need it; use narrower scopes for routine work.

The two options worth knowing

Run in the background adds ASYNC.

The statement returns immediately and the server keeps going after that.

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.

Apply across a cluster adds ON CLUSTER, running the statement on every node instead of just the one you're connected to.

Necessary for a distributed setup; changes nothing on a single server.

There's also a free-text extra settings field for anything ClickHouse® backup settings support directly.

Three you'll likely reach for:

Setting	Does
`base_backup`	Makes this backup incremental against an earlier one
`compression_method`	e.g. `'lz4'` — trades CPU for size
`s3_storage_class`	Which S3 storage class to write to, for cost

Written exactly as you'd write it in SQL:

base_backup = S3('https://bucket.s3.amazonaws.com/full-2026-07-01'), compression_method = 'lz4'

These settings are passed straight through to ClickHouse®, so its own documentation is the authority on anything not covered here.

Restoring

On the same Manual Backup tab, choose List Available Backups.

CH-Ops scans your S3 storage and lists what's there, newest first.

Pick one, restore it.

A few things worth holding onto before clicking restore:

Restoring is not undo. Read what it will actually do to what already exists — restoring over a table that's still there isn't automatically safe.
Restore somewhere else first when you can. 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.
A backup you've never restored is a hope, not a backup. The first restore shouldn't happen during an actual incident. Try one on something small while nothing's on fire.

Browsing what you have

The Available Backups tab is the inventory.

Choose a storage profile, click Scan S3, and see everything that's actually out there.

Column	Meaning
ID	The backup's identifier
Scope	Cluster, database, or table
Created	When it was taken
Type	Full or incremental
Retention	How long it's set to be kept

Worth scanning this occasionally even when nothing's wrong.

It's how you find out a schedule quietly stopped running three weeks ago, instead of finding out at the worst possible moment.

Full vs. incremental

A full backup contains everything in its scope.

An incremental contains only what changed since an earlier one, referenced through base_backup.

Incrementals are smaller and faster to take.

The trade: restoring one needs its base — and the base of that base, all the way back to a full.

Break any link in that chain and everything after it stops being usable.

A practical pattern:

A full backup weekly
Incrementals daily against it
Each week starts a fresh chain

This keeps no single chain long enough to become fragile.

And keep at least one complete chain past your retention window.

Retention that deletes a base while its incrementals survive leaves you holding files that can't restore anything.

When something doesn't work

Fails immediately with an access error

The server can't reach the bucket.

Remember, the server writes the backup, not CH-Ops, so a profile that validated fine can still fail here.

Check that your ClickHouse® nodes have real network access to S3, and that the credentials are ones the server can use.

On a cloud deployment, that often means an attached instance role rather than static keys.

Runs forever and the page seems stuck

ASYNC wasn't enabled.

The request stays open until the server finishes.

Turn it on for anything large — the backup itself isn't affected by what the browser does.

Scan S3 returns nothing

Three possibilities, in order of likelihood:

The profile points at a different bucket or prefix than what was actually written to.
Nothing's been backed up with this profile yet.
Everything's aged past retention and been cleaned up.

Restore fails, missing base backup

This usually means a broken incremental chain.

Its base was deleted, probably by retention.

Find an earlier full and restore that instead, then fix retention so it stops deleting bases ahead of their incrementals.

Restore fails, table already exists

Expected.

ClickHouse® won't silently overwrite.

Restore under a different name, or deliberately remove the existing table first if you're certain.

Backup succeeded but looks smaller than expected

Usually exclusions.

Check what got left out in the preview.

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.

References

Backup and Restore: CH-Ops Documentation

Next in the CHOps series

CH-Ops: A ClickHouse® Admin Tool Built for Simplicity

CH-Ops Access Control and App-Data Backup

Kanishga Subramani — Tue, 06 Oct 2026 15:54:41 +0000

CH-Ops access control provides a dedicated permission layer for managing who can use the CH-Ops application and what they can do inside it.

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?

CH-Ops addresses both concerns with application-level access control and app-data backup capabilities.

The application maintains its own configuration separately from the ClickHouse® data it manages. This includes information such as users, dashboards, alerts, and cluster definitions.

That creates an important distinction:

A ClickHouse® backup protects ClickHouse® data. An app-data backup protects the state of CH-Ops itself.

In this article, we'll look at both capabilities and how they fit into a self-hosted ClickHouse® environment.

Application-Level Access Control

There are two permission systems to consider when operating ClickHouse® through CH-Ops.

The first is ClickHouse® access control, which determines what a ClickHouse® user can do inside the database.

The second is CH-Ops application access control, which determines what someone can do inside the CH-Ops application.

These systems serve different purposes.

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.

CH-Ops provides four application-level roles so access can be assigned according to the responsibilities of each user.

The important part is that these roles apply to CH-Ops itself, rather than replacing ClickHouse®'s own RBAC system.

CH-Ops Users vs. ClickHouse® Users

It is easy to confuse these two concepts.

A ClickHouse® deployment may contain database users such as:

ClickHouse®
├── analyst
├── data_engineer
├── reporting_user
└── admin

These accounts are managed by ClickHouse® and control access to databases, tables, queries, and other database operations.

CH-Ops has a separate set of application users:

CH-Ops
├── User A
├── User B
├── User C
└── User D

These accounts determine who can access the CH-Ops application and what application-level operations they can perform.

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.

Managing CH-Ops Users

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.

Why Back Up CH-Ops Itself?

Now consider a different failure scenario.

Your ClickHouse® data is backed up correctly, but the server running CH-Ops is lost.

What happens to the configuration you created inside CH-Ops?

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.

Reinstalling CH-Ops does not automatically recreate all of that configuration.

This is where app-data backup becomes useful.

The two backups protect different layers of the infrastructure.

One-Click App Data Backup

CH-Ops provides an application-data backup workflow through its Administration functionality.

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.

The workflow is intentionally straightforward.

This is particularly useful for self-hosted deployments where CH-Ops is running on a VM, server, or container.

App Backup vs. ClickHouse® Backup

The word "backup" can make these two operations sound interchangeable, but they protect different things.

A ClickHouse® backup is concerned with ClickHouse® data and database objects.

An app-data backup is concerned with CH-Ops configuration and application state.

For example:

Backup	Protects
ClickHouse® backup	ClickHouse® database data
CH-Ops app-data backup	Users, dashboards, alerts, cluster definitions, and application configuration

CH-Ops does not use its SQLite database to store the actual ClickHouse® table data.

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.

A production environment should treat these as two separate backup concerns.

Keeping the Backup Separate

A backup stored on the same machine as the application does not provide much protection against a machine-level failure.

CH-Ops supports app-data backups to external storage destinations such as S3, GCS, and Azure.

That makes it possible to separate the application from the location where its backup is stored.

If the CH-Ops host is lost, the backup remains available independently of that host.

The Backup Workflow

The practical workflow is simple:

Open CONTROL PANEL.
Open the app-data backup functionality.
Configure or select the backup destination.
Start the backup.
Verify that the operation completed successfully.

The important distinction is that this operation backs up CH-Ops application data, not the data stored in your ClickHouse® tables.

That makes it complementary to an existing ClickHouse® backup strategy rather than a replacement for it.

Restoring CH-Ops App Data

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: the CH-Ops server must be stopped during the restore.

Restoring replaces the existing application data, so this operation should be performed carefully.

1. Download the Backup

First, download the backup file from the configured S3-compatible storage.

For example, using the AWS CLI:

aws s3 cp s3://YOUR_BUCKET/chops-app-backups/BACKUP_ID.db ./chops-restore.db \
  --endpoint-url YOUR_ENDPOINT

The same approach can be used with S3-compatible storage by providing the appropriate endpoint.

2. Stop the CH-Ops Server

The server must not be running while the database is replaced.

For development, stop the running process:

Ctrl+C

For a production installation managed by systemd:

sudo systemctl stop chops

If CH-Ops is running as a foreground binary, stop the process with Ctrl+C or terminate the process normally.

3. Replace the Application Database

Before replacing the database, remove the existing SQLite WAL and SHM files:

rm -f data/chops.db-wal data/chops.db-shm

These files belong to the existing SQLite database and can contain unflushed state from the old database.

The backup itself is a self-contained SQLite database created using VACUUM INTO. The backup is stored as base64 text, so decode it first:

base64 -d chops-restore.db > chops_decoded.db

Then replace the current application database:

cp chops_decoded.db data/chops.db

At this point, the CH-Ops application database has been replaced with the backed-up state.

4. Start CH-Ops Again

For development:

bun run dev

For a systemd-managed production installation:

sudo systemctl start chops

For a foreground binary:

./chops

Once the server starts, CH-Ops will use the restored SQLite database.

What Gets Restored?

The app-data backup contains CH-Ops's internal application state, including:

Alert rules
Alert channels
Dashboards
Charts
Backup schedules
User accounts
Application settings

The restoration affects CH-Ops application state only. It does not restore, modify, or overwrite the underlying ClickHouse® data.

In other words:

CH-Ops backup
      │
      ▼
SQLite application state
      │
      ├── Users
      ├── Dashboards
      ├── Alerts
      ├── Charts
      ├── Backup schedules
      └── Settings

          X ClickHouse® data

      └── Not affected

Because restoration replaces the existing application database, this should be treated as a recovery operation rather than a routine configuration change.

A Practical Team Scenario

Consider a data platform team running several ClickHouse® clusters through CH-Ops.

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.

Application-level roles provide the separation between these responsibilities.

At the same time, the team may have accumulated dashboards, alerts, cluster definitions, and other configuration inside CH-Ops.

That configuration is now part of the team's operational environment.

The two features address these concerns together:

Access control protects who can change the management layer.

App-data backup protects the management layer itself.

A Simple Operational Checklist

For a self-hosted CH-Ops deployment:

Create individual CH-Ops accounts rather than sharing credentials.
Assign users the appropriate application role.
Keep CH-Ops credentials and ClickHouse® credentials protected.
Back up CH-Ops application data separately from ClickHouse® data.
Store backups on external or independent storage.
Verify that backups complete successfully.
Test the recovery process rather than assuming the backup works.

Final Thoughts

A ClickHouse® management platform becomes part of the infrastructure around your database.

That means it needs its own access controls and its own recovery strategy.

CH-Ops addresses these two areas through application-level access control and app-data backup.

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.

App-data backups protect the configuration stored by CH-Ops, including the application state needed to recreate a working management environment.

Together, they answer two simple operational questions:

Who is allowed to manage the management layer?

and:

Can we recover that management layer if the host is lost?

For teams using CH-Ops to manage their ClickHouse® infrastructure, those are small but important pieces of building a more resilient operational setup.

References

CH-Ops - User Management Documentation
CH-Ops - App Data Backups Documentation

Next in the CHOps series

Native ClickHouse® Backups in CH-Ops: A Practical Guide

CH-Ops Scheduled Alerts: Know Before Things Break

Kanishga Subramani — Sat, 03 Oct 2026 16:42:02 +0000

What is CH-Ops

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.

One of its useful features is Alert Rules, which lets you turn a SQL query into a scheduled check and notify you when a defined condition is met.

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.

Introducing CH-Ops Alert Rules

Monitoring a ClickHouse® cluster is not only about looking at dashboards. Some conditions are easier to detect with a simple query.

For example, you may want to know when:

The number of failed operations goes above a threshold.
A particular metric exceeds an expected value.
A query returns an unexpected result.
A cluster condition needs attention.

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.

Setting up an email notification channel

Before creating an alert, you need a notification channel that CH-Ops can use to deliver the alert.

You can find this under:

Control Panel → Notification Channels

Click New to create a notification channel.

For this walkthrough, select Email (SMTP) as the notification type. The form provides fields for configuring the email connection:

Field	What it's for
Name	A name to identify the notification channel
SMTP Host	The mail server address used to send the alert
SMTP Port	The port that server listens on
SMTP User	The account CH-Ops authenticates as
SMTP Password	That account's password
From	The sender address
To	The recipient address

Fill in the SMTP details provided by your email service and click Create.

Once configured, the channel can be reused by alert rules instead of configuring email details every time you create a new alert.

After creating an Email (SMTP) notification channel, use its Test 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.

Creating an Alert Rule

After configuring the notification channel, go to:

Custom Alerts → Alert Rules

If you haven't created any rules yet, CH-Ops displays an empty state with a New Rule button. Click New Rule to start creating an alert.

The alert rule form brings the main configuration into one place.

Field	What it does
Name	Identifies the rule
Severity	How serious a firing alert is, for example warning
Schedule (cron)	How often the rule runs, for example `/5 * * *` for every five minutes
Operator	How the result is compared to the threshold, for example greater than
Threshold	The value the operator compares against
SQL (single value)	A query that returns one number to check
Description	Context shown when the alert fires (optional), but it is useful for documenting what the alert is checking
Cluster	Which cluster the rule runs against, defaulting to all clusters
Target nodes	Which nodes to run on, left empty to mean all nodes

Before configuring, the Alert Rule also has its own Test button. It executes the configured SQL query and checks the result against the defined condition and threshold.

This lets you verify the alert logic before relying on the scheduled rule.

Once created, the channel shows up as a card with its status, when it was last tested, and Edit, Enable, and Delete actions.

From Query to Notification

Once the alert is created, the workflow becomes straightforward:

1. Run the SQL query
CH-Ops executes the configured query according to the schedule.

2. Check the result
The returned value is evaluated against the configured operator and threshold.

3. Determine whether the condition is met
If the result satisfies the alert condition, the rule is triggered.

4. Send the notification
The configured notification channel is used to deliver the alert. This turns a SQL query into a simple scheduled monitoring mechanism.

A Simple End-to-End Flow

The complete workflow can be summarized as:

SQL Query → Schedule → Condition → Notification

The important idea is that you don't need a separate script or external scheduler just to repeatedly check a SQL-based condition.

CH-Ops brings the query, condition, schedule, and notification configuration together in one interface.

Why This Is Worth Setting Up

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.

Email (SMTP) is available in the open source version and is enough for many setups. CH-Ops Pro adds additional notification channels (Slack, Microsoft Teams, Google Chat, and PagerDuty) for teams that need more immediate delivery.

Explore CH-Ops Pro features:
https://www.ch-ops.io/blog/clickhouse-alerts-in-slack-teams-google-chat-pagerduty

Next in the CHOps series

CH-Ops Access Control and App Data Backups

Search ClickHouse® Logs Without Writing SQL

Kanishga Subramani — Tue, 29 Sep 2026 16:16:04 +0000

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.

CH-Ops provides a visual way to explore these logs without writing SQL.

From the Logs section, you can choose a log type, select a time range, view an overview of the activity, and switch to Search when you need to investigate individual log records.

What Is CH-Ops?

CH-Ops is a browser-based operations platform for ClickHouse® that provides a visual interface for managing and monitoring ClickHouse® deployments.

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.

In this article, we'll focus on the Logs section and how CH-Ops makes ClickHouse® log investigation easier without requiring SQL for every search.

Exploring ClickHouse® Logs in CH-Ops

The Logs section provides dedicated views for different ClickHouse® system logs:

Crash Log - investigate server crashes
Error Log - understand recurring error types
Text Log - explore server messages across different log levels
Session Log - review login and logout activity

Each log provides two views:

Overview - understand the overall activity for a selected time range.
Search - find specific records using filters relevant to that log.

Quick time ranges such as 1h, 6h, 24h, 48h, 7d, and 30d make it easy to focus on a specific period.

1. Crash Log: Investigating Server Crashes

The Crash Log is based on system.crash_log and is useful when a ClickHouse® process unexpectedly stops or a node restarts.

The Overview provides a quick summary of crash activity, including:

Total crashes
Distinct signals
Affected ClickHouse® versions
Recent crash activity
Crash distribution by signal and version
Crash incidents

Crash Log Overview

Once a crash is identified, the Search view can be used to inspect individual crash records using details such as the event time, signal, query information, and exception trace.

Crash Log Search

This makes it easier to move from “Did a crash happen?” to “What exactly was recorded when it happened?”

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.

2. Error Log: Finding Recurring Errors

The Error Log is based on system.error_log and helps identify the types of errors occurring on the server.

The Overview summarizes the selected period through metrics and visualizations such as:

Total errors
Number of error types
Local versus remote errors
Top error types
Latest error

Error Log Overview

The Top Error Types chart provides a quick starting point. If one type accounts for a large portion of the errors, you can investigate that category further.

Searching Error Logs

The Search view lets you investigate error records without writing SQL. You can select a time range, filter by Error Type, search by Error Message, and set the row limit.

Error Log Search

The search results show the matching records along with details such as the event time, error type, error message, and query ID.

3. Text Log: Understanding Server Activity

The Text Log is based on system.text_log and provides a broader view of messages generated by the ClickHouse® server.

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.

The Overview provides information such as:

Total log lines
Errors
Warnings
Number of loggers
Recent activity
Log volume by level

Text Log Overview

The Log Volume by Level chart provides a quick picture of server activity across the selected period.

Searching Text Logs

When you need to investigate a particular event, switch to the Search tab.

You can filter logs by time range, log level, and message, and set the number of results to display.

The Search view presents the individual log records, including information such as:

Event time
Log level
Query ID
Logger name
Message
Source file and line

Text Log Search

This lets you quickly narrow down relevant server activity and inspect the underlying records without writing SQL against system.text_log.

4. Session Log: Tracking Login Activity

The Session Log is based on system.session_log and provides visibility into login and logout activity.

The Overview summarizes session activity through:

Total events
Successful logins
Failed logins
Logouts
Distinct users
Recent activity

Session Log Overview

The Login Outcomes chart provides a quick view of successful logins and logouts, while Top Users highlights the accounts generating session activity.

The Search view can be used when you need to investigate specific session activity. You can filter events by event type, user, or failure reason.

This makes it easier to investigate authentication activity directly from CH-Ops without manually querying system.session_log.

From Overview to Search

Across the different log types, CH-Ops follows a simple investigation pattern:

Choose a log → Select a time range → Load the Overview → Identify something interesting → Switch to Search → Investigate the records

The Overview helps you understand the bigger picture, while Search helps you narrow down specific events and investigate the underlying records.

When Should You Use Each Log?

Each log answers a different operational question:

Log	Useful when you want to know
Crash Log	Did the ClickHouse® process crash, and what was recorded?
Error Log	What types of errors are occurring, and how frequently?
Text Log	What has the ClickHouse® server been reporting about its activity?
Session Log	Who has been connecting, and are login attempts succeeding or failing?

The Overview helps identify the problem, while Search helps investigate it.

From Investigation to Alerts

Logs help you investigate what has already happened. For conditions that require proactive attention, CH-Ops Alert Rules can notify you when defined conditions occur.

The workflow becomes:

Monitor → Detect → Alert → Investigate

Use Alerts to be notified about important conditions, and Logs to investigate the details behind them.

Bringing ClickHouse® Log Investigation Into One Interface

ClickHouse® system logs contain valuable information for monitoring and troubleshooting, but accessing them shouldn't always require writing SQL.

With CH-Ops, you can explore Crash Log, Error Log, Text Log, and Session Log from a single Logs section. The Overview gives you the big picture, while the Search view lets you inspect the underlying records.

Instead of starting every investigation with a SQL query, you can follow a simple visual workflow:

Select the log → Choose the time range → Load the data → Understand the overview → Search the records

This makes ClickHouse® log investigation more accessible and helps you move from identifying an issue to finding the relevant details - without writing SQL for every search.

Explore CH-Ops

Want to learn more about CH-Ops and explore its features? Visit the official website and explore the resources below:

CH-Ops Official Website
Getting Started with CH-Ops
CH-Ops Official GitHub Repository
CH-Ops Demo Video

Next in the CHOps series

CH-Ops Scheduled Alerts: Know Before Things Break

Watch your clusters live on CHOps

Kanishga Subramani — Fri, 25 Sep 2026 06:20:52 +0000

Complete Visibility into Your ClickHouse® Infrastructure

Monitor cluster health, analyze performance, track system resources, and gain real-time insights into every ClickHouse® node — all from a single, intuitive interface.

Introduction

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.

Our custom management platform, CHOps, provides real-time telemetry across every layer of your ClickHouse® nodes, bridging hardware metrics with native database engine states.

Key Features & Dashboard Overview

1. Machine & Node Telemetry

CHOps presents real-time gauge metrics updated dynamically, for example every 5 seconds.

Resource Allocation: CPU utilization, OS Memory, ClickHouse®-specific Memory, and Thread Pool saturation.
Node Summary: Instant visibility into active database versions, total databases, table counts, active queries, running merges, and mutations.

2. Disk & Storage Health Check

Disk Partitioning: Tracks raw space utilization, with the default storage shown at 457.00 GiB and 53.6% used.
Part Formats: Monitors the breakdown between Compact (76.46%) and Wide (23.54%) storage parts to help understand write behavior.
System Health Checks: Automated monitoring across 15+ critical health states, including Delayed inserts, Spilling to disk, Keeper expired, Readonly replicas, and Broken disks.

3. Background Pools & Execution Shaping

Pool Capacity vs. Usage: Real-time histograms for background tasks including Merges, Fetches, Moves, Schedule, Buffer Flush, Distributed operations, and Message Brokers.
Efficiency & Compression Ratios: Live tracking of Read Amplification, Write Amplification, and Read Compression.

For example:

Read Amplification: 566.1 rows/row
Read Compression: 16.0x

4. Query & Time Execution Profiling

"Where the Time Goes": Granular thread-level decomposition showing exact thread usage spent on:

Disk Read
CPU User
Merge Exec
CPU Kernel
Disk Write

In-Flight Metrics: Tracking active query locks, I/O in flight, open read/write operations, memory consumption by mapped files versus server runtime, and thread distribution.

Dashboard Snapshot

Metric	Value
Storage Usage	457 GiB (53.6% Used)
Part Breakdown	Compact: 76.5% / Wide: 23.5%
Read Compression	16.0x
Read Amplification	566.1 rows/row

Advantages of Using CHOps

Early Detection of Bottlenecks

Instantly spot write-amplification spikes or thread pool exhaustion before queries degrade.

Data Compression Optimization

Track part churn and row compression ratios to optimize storage policies and partition schemes.

Simplified Cluster Management

Monitor cluster topology, replica delays, and Keeper/ZooKeeper connections in a single unified dashboard.

ClickHouse® Architecture: Clusters & Nodes Explained

1. What is a ClickHouse® Node?

A Node is a single running instance of the ClickHouse® server process (clickhouse-server) on an isolated physical machine, virtual server, or container.

Role:

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.

In Your Dashboard:

The dashboard highlights individual nodes, such as node-1 (localhost ::1:9000), showing specific runtime metrics such as uptime, active queries, CPU, and RAM allocation.

2. What is a ClickHouse® Cluster?

A Cluster is a logical grouping of multiple ClickHouse® nodes working together to handle large analytical datasets across distributed infrastructure.

Sharding & Replication:

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.

Horizontal Scaling:

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.

Conclusion

As analytical workloads grow in volume and complexity, maintaining deep visibility into database internals becomes a necessity — not a luxury.

CHOps bridges the gap between high-level database administration and low-level engine telemetry, helping keep ClickHouse® clusters resilient, performant, and cost-efficient.

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.

Why CHOps telemetry is different

Don't confuse general system-level host monitoring, such as standard Prometheus or Grafana OS exporters, with the engine-native telemetry provided by CHOps.

Standard infrastructure tools primarily track raw host metrics.

CHOps explicitly surfaces ClickHouse®-specific engine internals, including:

Background merge queues
Part formats such as Compact vs. Wide
Read/write amplification
Thread pool scheduling
ClickHouse®-specific memory and execution metrics

This blog focuses specifically on deep ClickHouse® database performance optimization using CHOps.

References

CH-OPS Open Source (OSS) Version – GitHub: https://github.com/Quantrail-Data/CH-Ops
CH-OPS Website: https://www.ch-ops.io/
CH-OPS Installation Guide: https://www.ch-ops.io/blog/install-ch-ops-in-10-minutes-docker-binary-or-source
CH-Ops Demo Video: https://www.linkedin.com/posts/quantrail-data_ClickHouse-opensource-dataengineering-activity-7489909665477091328-QOIY/

Next in the CHOps series

Search ClickHouse® Logs Without Writing SQL
https://www.ch-ops.io/blog/search-clickhouse-logs-without-writing-sql

CH-Ops Schema Tools: Visualizer, Indexes & Projections

Kanishga Subramani — Fri, 25 Sep 2026 05:19:00 +0000

A ClickHouse® deployment rarely stays simple. A materialized view gets added to pre-aggregate something, then a second MV reads its output. A dictionary shows up for user lookups, a Distributed table sits in front of a sharded local table — and soon the schema is fully documented in DDL, and still hard to hold in your head.

The questions that come up are relational, not definitional:

What feeds this table, and what does it feed?
What breaks if I drop it?
Which skipping indexes already exist here?
Is there already a projection for this query pattern?
How do I add or remove an index without hand-writing ALTER statements and remembering to backfill?

You can answer all of these by hand — SHOW CREATE TABLE, system.tables, cross-referencing MV definitions. It's just slow, and it gets worse fast once a pipeline crosses databases.

That's the gap Schema Tools in CH-Ops fills.

Four screens — Schema Visualizer, Data Skipping Indexes, Projections, Index Management — read as one workflow: see how the schema connects, check what optimization already exists, weigh whether a projection helps, then manage indexes when the evidence supports it.

It doesn't remove SQL — every index or projection action shows the exact statement before running it. What it removes is the friction around that: manual inspection, hand-written DDL, and the easy-to-miss materialization step.

The running example

Raw request logs land in staging.raw_logs.

An MV parses them into analytics.parsed_logs — sorted by service, then time — which feeds two more MVs rolling requests into hourly metrics and error summaries, plus a Distributed table for sharded reads.

A view in reporting aggregates the metrics for a dashboard.

There's also analytics.users_dict, a dictionary loaded from a users table.

Keep that sort order — service, then time — in mind. It's what decides which queries are already fast.

Schema Visualizer: how is this schema connected?

Reconstructing a pipeline from DDL by hand means reading each MV's source and target and searching for anything downstream, one hop at a time.

Cross-database dependencies are the worst case, since checking one database at a time hides them entirely.

The Schema Visualizer draws the graph instead: tables, MVs, dictionaries, Distributed tables, and views as cards, with lines showing data flow.

It's read-only — nothing here creates, alters, or drops anything.

It doesn't render the whole schema at once, on purpose — that's slow and unreadable.

Pick a database, then a table, and CH-Ops traces every relationship in both directions, draws just that connected subgraph top to bottom, and auto-fits it.

The traversal isn't limited to one database.

Starting at raw_logs pulls in all three databases on one canvas, each node labelled with its full name — the fastest way to find a dependency you didn't know existed.

Node types

Headers are colour-coded:

MergeTree family — stores data
MaterializedView — fires on every insert
Refreshable MV — fires on a schedule
Dictionary
Distributed — routes to shards, stores nothing itself
View — a saved query, recomputed on read
Other

One glance tells you what stores data versus what just moves it.

Navigation

Dropdowns pick what's drawn.

Search matches table and column names, highlighting hits and dimming the rest — handy for questions like "which tables use user_id."

Columns toggle switches between compact and detailed cards.

Fit and Re-layout handle the camera and layout separately.

The sidebar

Click a node and it opens:

Engine
Partition key
Sort order
Primary key, if different
Rows/bytes
What it reads from
What reads from it
Full CREATE statement

Reads and writes are clickable, so you can walk the pipeline hop by hop.

The heatmap

A graph shows how data moves, not what's doing the work.

The optional heatmap covers that for MVs, over the last 1/7/30 days, built from system.query_views_log.

When enabled, it colours and thickens edges by:

View duration
Rows/bytes written or read
Peak memory
Executions

The values are log-scaled, since volumes can span orders of magnitude.

An empty heatmap can mean no MVs fired, log_query_views is off, or the connecting user lacks SELECT on that table.

One hot edge next to cool ones makes the point: MVs at the same level of the graph don't cost the same to run.

Answers: how is my schema connected, and where does data flow?

Data Skipping Indexes: what already exists here?

An index doesn't sort anything.

It summarizes each block — min/max, distinct values, a bloom filter, or text terms — so a filtering query can skip blocks that can't match.

The sort key is what actually orders the data and makes those columns fast already.

Indexes are for the filters the sort key doesn't cover.

On parsed_logs, service is already fast.

user_id, status_code, and message are not.

The Data Skipping Indexes screen is read-only:

Database → table → each index, with type and covered expression, across everything.

The expression next to the type is the useful part — it catches a duplicate before you create it.

Every index costs on every insert, so a duplicate is pure downside.

Four types

minmax

Min/max per block.

Cheap and useful for dates and timestamps that correlate with insertion order.

set

Distinct values per block.

Good for low-cardinality equality filters; useless past its size limit.

bloom_filter

Probabilistic.

It can rule a block out for certain, but a "maybe" still means reading it.

Useful for equality on high-cardinality columns.

text

Indexes terms within a string for searching rather than matching exactly.

Answers: what indexes does this table already have, and what are they indexing?

Projections: a different representation of the same data

A skipping index narrows a query.

A projection changes what it reads from entirely — a second copy of the table, sorted differently or pre-aggregated.

ClickHouse® decides on its own whether to use one.

It's not free, and it's not an index.

Think of it this way:

Index → can I skip this block?

Projection → is there a better-suited copy of this data?

Dashboards on this pipeline repeatedly ask for counts and latency by service and hour, recomputed every time.

A pre-aggregated projection storing exactly that grouping solves it directly.

CH-Ops covers the lifecycle in five tabs:

View — the same tree as the index screen
Add Projection — a form with table, name, select, optional grouping/ordering, cluster-wide and skip-if-exists options
Materialize Projection — builds it for existing rows
Clear Projection — empties data while keeping the definition
Drop Projection — removes it entirely

One quirk: projections don't support SELECT DISTINCT — CH-Ops strips it and tells you.

Grouping and ordering

Grouping and ordering are separate fields.

A projection grouped by day can't serve a query grouped by hour.

Creating isn't the same as filling

Projection definition
        │
        ├── new inserts ──────────► maintained automatically
        │
        └── rows already in table ─► need Materialize Projection

New data is covered automatically.

Existing rows need materializing — real work on a big table, so it can be scoped per-partition to check whether it's worth it before doing the rest.

Clear then rebuild is the fix for a drifted projection.

Drop removes it for good.

Either way, remember the cost: a projection is a second copy of the data, written on every insert whether it's used or not.

Worth it for a query run constantly; not worth adding speculatively.

Answers: can I give ClickHouse® a better-suited representation for an important query pattern?

Index Management: create, materialize, drop

Three tabs — Create, Materialize, Drop.

This one needs admin access, unlike the read-only screens above.

Create

Database → table → column → name → type → granularity.

The column list comes with data types attached, so there's no typing from memory.

Some types add fields:

bloom_filter — false-positive rate
text — tokenizer

Name it for what it does:

idx_user_id, not idx1.

CH-Ops shows the exact statement before running it — a safer way to produce the same DDL, not a black box.

Granularity defaults to a sensible value and is one of the last things worth tuning — type and expression matter far more.

Materialize

Same gotcha as projections:

Create index  ──► applies to data inserted from now on
Materialize   ──► existing rows become covered

Nothing errors when this is skipped — the index just quietly doesn't help.

Unlike projections, there's no partition-scoping option here — MATERIALIZE INDEX runs against the whole table in one go.

This screen is the answer to:

"I added the index and nothing changed."

Drop

Removes the index, not the data.

Every index costs on writes for as long as it exists.

If it's not earning that back, drop it.

That's normal tuning, not a mistake.

Answers: creating, materializing, and dropping — the three states of an index.

From schema discovery to optimization

Here's the workflow:

Open the Visualizer and select parsed_logs.
See three MVs and a Distributed table depend on it.
Read the sort order from the sidebar.
Check Data Skipping Indexes for what's already covered.
Compare against your actual workload — endpoint/user filters aren't served by the sort key.
Check Projections for an existing pre-aggregation.
Decide: projection for a repeated aggregation, index for a plain filter.
Create it.
Materialize it.
Check whether it helped. Drop it if not.

CH-Ops doesn't tell you which index or projection to build.

It shows what exists and where the load sits, then removes the DDL friction.

The judgment calls — steps 5 and 7 — are still yours.

The Query Profiler is where you confirm a query is actually slow before reaching for any of this.

Same table across all three panels — one object, four views of it.

Conclusion

Understand the schema.

Check what's already optimized.

Weigh a projection.

Manage indexes when the evidence supports it.

The value isn't skipping SQL — you can write the DDL yourself, and CH-Ops shows you the statement anyway.

It's having a fast, structured way to see what's already there before you touch it.

Most bad schema decisions come from not seeing the dependency, the existing index, or the projection that was already there — not from an inability to write ALTER TABLE.

Pick your busiest table, open the Schema Visualizer, and see what's attached to it.

References

Schema Tools Documentation:

https://www.ch-ops.io/docs/guide/schema-visualizer?v=latest

Next in the CHOps series

Watch your clusters live on CHOps:

https://www.ch-ops.io/blog/ch-ops-cluster-dashboard

CHOps Is Now GA: Open-Source Operations for Self-Hosted ClickHouse®

Kanishga Subramani — Thu, 24 Sep 2026 12:44:28 +0000

CHOps is now generally available.

ClickHouse® Cloud has a nice UI and good tools to admin and monitor a
cluster. If you self-host the open-source ClickHouse® database, you get
control, but the tools are fragmented and the daily work is clumsy.

We built CHOps for our customers who self-host the ClickHouse® database.
It started as many small internal UIs. When we decided to make it public,
we put them into one app.

CHOps works with any deployment: VMs, bare metal, Kubernetes (Altinity®
operator, and the official ClickHouse® operator in early access), and
managed cloud services.

We released a beta in July 2026. After several rounds of testing by our
ClickHouse® DBAs and use on production clusters, CHOps is now generally
available. It is tested with ClickHouse® database 26.8 LTS.

The core is open source under Apache 2.0. CHOps Pro adds features for
production teams, such as SSO, audit logs, Slack, Teams and PagerDuty
alerts, and remote node management.

Thank you to everyone who tested the beta and sent feedback.

GitHub: https://github.com/Quantrail-Data/CH-Ops
CHOps home page: https://ch-ops.io

Design a ClickHouse® Table Without Hand-Writing DDL: Schema Studio

Kanishga Subramani — Wed, 23 Sep 2026 16:10:58 +0000

Designing a ClickHouse® table involves more than defining columns. Data types, sorting keys, partitioning, MergeTree configuration, indexes, and other settings can influence how the table performs as data grows.

Writing the complete DDL manually gives you control, but it also means translating each design decision into the correct ClickHouse® syntax.

Schema Studio simplifies this process by providing a guided workflow for designing and creating ClickHouse® tables while keeping the generated SQL visible and editable.

What is CH-Ops?

CH-Ops is a browser-based operations platform for ClickHouse®. It brings common database operations into a single web interface, including SQL querying, cluster monitoring, user management, backups, alerts, dashboards, and other operational workflows.

Alongside these operational capabilities, CH-Ops includes Schema Studio, a SQL tool designed to simplify the process of building ClickHouse® table definitions from source data.

Introducing Schema Studio in CH-Ops

Schema Studio is a guided table-design feature within CH-Ops.

Starting with your source data, it helps:

Infer and refine the schema
Configure ClickHouse®-specific table settings
Generate and validate the DDL
Optionally review the design with AI
Create the table

Importantly, Schema Studio creates the table structure only. The source data is used for schema inference and is not loaded into the newly created table.

You can access it under:

SQL Tools → Schema Studio

Building a ClickHouse® Table with Schema Studio

Schema Studio organizes the table-design process into four core stages.

Each stage focuses on a specific part of the design, starting with understanding the source data and ending with a validated table definition ready to be created in ClickHouse®.

1. Start with Your Source Data

Schema Studio first connects to the ClickHouse® instance selected in CH-Ops.

Enter the ClickHouse® username and password to establish the session.

Once connected, choose the data that will be used for schema inference.

You can upload a local file in formats such as:

CSV
TSV
JSON
NDJSON/JSONL
Parquet
ORC

You can also configure an object-storage source such as:

S3
Azure

2. Review and Shape the Schema

After analyzing the source, Schema Studio displays the inferred columns and corresponding ClickHouse® data types.

It also shows:

Approximate distinct values
Null percentages

This helps you review the structure before moving to table configuration.

The inferred schema remains editable.

You can:

Modify column names
Change data types
Add derived columns using DEFAULT, MATERIALIZED, ALIAS, or EPHEMERAL
Configure additional column options such as codecs and comments

Note: Schema inference may produce nullable types such as Nullable(Int64). Review the inferred types before configuring ORDER BY, and adjust the type if a nullable column needs to be used as the sorting key.

3. Define the ClickHouse® Table Design

The Engine step is where you configure how the table will be structured in ClickHouse®.

You can select the database and table name, choose the required MergeTree behavior, and define key table clauses such as:

ORDER BY
PRIMARY KEY
PARTITION BY
SAMPLE BY
TTL

For more advanced designs, you can also configure:

Data-skipping indexes
Projections
Replication
Distributed tables
Frequently filtered columns
Additional MergeTree settings

Note: For MergeTree tables, configure an ORDER BY or PRIMARY KEY before generating the DDL. If no sorting key is required, use tuple() for ORDER BY. Schema Studio flags a missing key in the Generate step.

Engine Configuration

The Engine Configuration step allows you to configure the MergeTree engine and key table clauses.

Advanced Configuration

Advanced Configuration allows you to add options such as:

Indexes
Projections
Replication
Distributed tables

4. Generate and Validate the DDL

After the table design is configured, Schema Studio generates the corresponding ClickHouse® CREATE TABLE statement in an editable SQL editor.

You can review or modify the generated SQL.

Use Validate to check the DDL before creating the table.

If you want to discard editor changes, Rebuild from Form regenerates the DDL using the configuration from the previous steps.

This keeps the generated SQL visible and gives you the ability to make manual adjustments before creating the table.

5. Review the Design with AI

Schema Studio also provides Evaluate with AI for an optional review using the supported AI provider configured in CH-Ops.

The evaluation can identify potential improvements related to:

Data types
Nullability
LowCardinality
Sorting keys
Partitioning
Codecs
Other table-design choices

Recommendations are presented for review and are not applied automatically.

When a recommendation is useful, Apply to Editor places the suggested DDL into the editor for further review and validation.

This provides an additional review step while keeping the final decision with the user.

6. Create the Table

After the DDL has been reviewed and validated, select Create Table.

Schema Studio displays a confirmation before executing the CREATE TABLE statement on the connected ClickHouse® instance.

Only the table structure is created.

The source data used for schema inference is not loaded into the table.

After creation, open the table in the SQL Editor to verify that it was created successfully and review its schema and configuration.

Why Schema Studio Matters

The value of Schema Studio is not simply that it generates a CREATE TABLE statement.

It brings the decisions involved in ClickHouse® table design into one workflow while keeping those decisions visible to the user.

Schema inference provides a starting point.

The Engine step exposes ClickHouse®-specific configuration.

Validation checks the generated DDL.

AI-assisted review provides an additional opportunity to refine the design before creation.

This reduces the repetitive work of translating a table design into SQL while keeping the final DDL available for inspection and modification.

Conclusion

Designing a ClickHouse® table requires decisions that can affect query performance, storage, and how the table behaves as data grows.

Schema Studio brings those decisions together in a guided workflow, from understanding the source data to creating the final table.

By combining:

Schema inference
ClickHouse®-specific configuration
DDL generation
Validation
Optional AI-assisted review

Schema Studio provides a more structured path to table creation without hiding the SQL or taking control away from the user.

References

CH-Ops Installation Guide
CH-Ops Official Repository
CH-Ops Demo Video

Next in the CH-Ops Series

CH-Ops Schema Tools: Visualizer, Indexes & Projections