Self-monitoring ClickHouse with ClickStack for deep debugging
When a self-managed ClickHouse cluster is slow, rejecting inserts or failing queries, the on-call team needs to see which queries, merges and inserts caused the problem, and they need to see it quickly. ClickStack's HyperDX UI can read ClickHouse system tables directly, which makes it a useful place for that investigation. It ships with a bundled Selects, Inserts and Infrastructure dashboard, and it offers both Lucene-style and SQL search over the underlying data. The question this section answers is when that view helps during debugging and when it adds little or misleads. Panel names, layouts and default queries change between ClickStack releases, so confirm the panels in the release you run before you build runbooks around them.
The view helps most when you already have a hypothesis and need to narrow it down. Query history in system.query_log, in-flight work in system.processes, and insert activity in the part and merge tables can be correlated by time window and table name in one interface, which is faster than switching between clients on several nodes. SQL search is the better tool for precise questions, such as which user ran the heaviest reads against a table in the last hour. Lucene search suits quick filtering on text fields. Both depend on the system tables retaining the rows you need, so check the TTL and logging settings on each cluster first. HyperDX is also a read path: it queries the same servers you are debugging, so heavy dashboard refreshes or unbounded searches can add load during an incident. Scope time ranges tightly and point the connection at a user with a restricted, read-only role.
The view does not replace alerting. Dashboards are opened by people, and an incident that nobody is watching will not page anyone. Keep alert rules in the monitoring stack that already pages your on-call team, such as Prometheus with Alertmanager, and use ClickStack for the investigation that follows. Before you commit to this setup, confirm three things with your own environment: which system tables are enabled and how long they are kept, which account HyperDX uses and what it is permitted to read, and whether the ClickStack release you deploy supports your ClickHouse version. Test those checks on a pre-production cluster with representative query load, since the value of a debugging view depends on whether the data is there when the incident happens.
Which monitoring view fits which question
This table compares four ways to watch a self-managed ClickHouse cluster: the built-in dashboard, Prometheus with Grafana, ClickStack on system tables, and direct SQL against system.query_log or system.processes. Read each row as a question first, then check the user and limits before choosing a view, since most teams end up using two or three of them together.
| View | Question it answers best | Who uses it | What it does not do |
|---|---|---|---|
| Built-in observability dashboard | Is this node busy right now, and are merges, memory or parts trending the wrong way? | On-call engineers checking a single server during an incident | Keep long history, alert or page anyone, or cover a whole cluster in one view unless you confirm that in your version |
| Prometheus and Grafana | Are metrics drifting over days or weeks across all replicas, and when did a threshold get crossed? | Platform and SRE teams who already run Prometheus and alerting | Explain individual slow or failed queries, since it works from aggregated metrics rather than per-query records |
| ClickStack on system tables | Which queries, inserts or parts caused a spike, and what did they read or write? | Engineers doing deep debugging after an alert has fired | Replace alerting or act as the paging system; keep it separate from alert rules and confirm retention of system tables in your setup |
| Direct SQL on system.query_log or system.processes | What exactly ran, who ran it, how long it took, and what is running at this moment? | DBAs and engineers who can query the cluster and want precise, scriptable answers | Provide dashboards or alerts on its own, and keep history beyond the TTL and sampling you configure, so confirm those settings first |
Lucene or SQL search: choosing the query style per investigation
When you know a fragment of an error message, a user name, a database or a table name, a simple field filter or free-text search is usually the fastest path. You are looking for a handful of rows, and the goal is to see the exception text and the query that produced it. A short filter on the exception field, or a keyword match on the query text, gets you there with little typing, which matters when a dashboard is already red and people are waiting. Confirm the exact Lucene syntax supported by the HyperDX version you deploy, including how it handles quoted phrases, wildcards and field names that contain dots, before you rely on it in a runbook.
- Start with the error code or message fragment: search the exception field for the text you saw in the application log or the client error.
- Filter by user or database: narrow to the account or database that ran the failing statement to remove unrelated noise.
- Use a time window first: set the time range to the minutes around the failure so that results stay small.
- Expect text matches to be loose: a keyword search can match the same words in comments or in unrelated queries, so read the matched rows before concluding anything.
- Save the working filter: record the exact search string in the incident notes so the next responder can repeat it.
Before relying on a filter, check that the results include the query you expect to see for a known failure in a test environment.
Questions such as which query shapes consumed the most time last night, or whether latency rose after a deployment, need counts, sums and percentiles over a window rather than individual rows. A field filter can narrow the set, but it cannot group or rank. This is where SQL gives precision, because you can compute aggregates directly against the system tables and control the grouping key, the time bucket and the ordering. Confirm in your deployed HyperDX version whether SQL statements are accepted in the search or chart editor, and which ClickHouse functions and table references work there.
- Group by normalized query shape: aggregate on a stable identifier rather than the raw text, so repeated statements with different literals count together.
- Bucket by time: choose a fixed interval, such as a minute or an hour, so that you can compare the window before and after a change.
- Rank by total time and count: a query that runs rarely but takes long may matter less than one that runs constantly, so sort both ways.
- Filter out internal traffic: exclude monitoring and replication queries if they distort the ranking.
- Check the sampling setting: if the query log is sampled, the totals are estimates, so compare them against an unsampled period before reporting numbers.
Check that the time column you aggregate on uses the same time zone as your incident timeline.
Some questions cross more than one source. For example, you may need to connect a slow query in the query log to the memory usage recorded for the same query, or to parts and merges that were active at the same moment. These joins are awkward in a field-based search and are a natural reason to write SQL. Keep the joins narrow, filter both sides by time before joining, and confirm that the system tables you reference exist and have the columns you expect in your ClickHouse version, since column sets change between releases.
- Join on the query identifier: match rows from the query log to other system views that share a query identifier, after filtering both sides to the same time range.
- Filter before you join: apply the time and host restrictions first so that the join does not scan the full retention period.
- Account for multiple replicas: the same statement can appear on several nodes, so decide whether you need one node's view or a cluster-wide view.
- Expect version differences: check column names against the ClickHouse documentation for your release before you publish a query.
- Treat results as evidence, not proof: a correlation shows what happened together, so confirm a cause with a controlled test.
Check that each joined query returns the same row count it returns when you run the sides separately.
Not every investigation needs SQL. A field filter is enough when the question is about a single event, a specific host or a known identifier, and when the answer is a short list of rows you can read yourself. It is also the right choice for junior responders or for handovers, because the filter is easier to explain and to repeat. The discipline is to switch to SQL as soon as you need a count, a percentile, a ranking or a join, rather than stretching a text search to answer a question it cannot express. Confirm which filter operators your HyperDX version offers for numeric and date fields before you build a shared runbook around them.
- Single identifiers: look up one query identifier, one user or one table name with an exact field match.
- Short time windows: use a filter when the result set is small enough to read in full.
- Repeatable handovers: a simple filter is easier for another engineer to reproduce during an incident.
- Signals of a bad fit: if you find yourself counting rows by hand or scrolling pages of matches, move to an aggregate.
- Shared saved searches: keep the saved filters that cover common checks, and document their intent.
Check that each saved filter returns results from a known test event before the team depends on it during an incident.
Why self-monitoring ClickHouse with ClickStack should stay off the alerting path
A debugging UI answers questions after someone asks them, while alerting needs a signal that is always on, has defined thresholds and routes to a named responder. HyperDX pointed at ClickHouse system tables is excellent for working out why a merge backed up or which query spiked memory, but an analyst opening a dashboard is a different control from an on-call rule that fires at 3 a.m. Treat the two as separate jobs with separate owners, even when the same system tables feed both.
The risk is circular dependency. If the ClickStack dashboard, its query path and the ClickHouse cluster it reads from all sit on one set of nodes, a degraded cluster can slow or blank the very charts you need to diagnose it. Alert rules that depend on those queries can go silent exactly when the cluster is failing, which is the worst time for silence. Before you go live, confirm where the HyperDX deployment runs, which ClickHouse endpoint it uses, and what happens to its panels when a replica is down or the cluster rejects queries.
Keep alert rules on signals that survive a degraded cluster. The ClickHouse replication guide describes how replicas resync after recovery, which is the behavior you want to alert on rather than assume. Scrape the Prometheus endpoint from a process outside the cluster, evaluate rules in a separate alerting stack, and route pages through a path that does not depend on ClickStack. Use ClickStack for investigation after an alert fires. Confirm that your alerting path would still page if the ClickHouse servers were unreachable.
Setting up self-monitoring ClickHouse with ClickStack
This sequence takes a self-managed ClickHouse cluster from a version and logging check through a read-only monitoring user and a ClickStack deployment, ending with the bundled dashboard loading against live system data. Each step closes with a check you can run before moving on.
Confirm the ClickHouse version and support status
The platform team checks which ClickHouse version each node runs and compares it against the supported versions in the ClickHouse security policy before any monitoring work begins. Versions outside the supported list receive no security updates, so an unsupported cluster should be scheduled for upgrade before you add more tooling to it. Confirm the release line you intend to keep (stable or LTS) against the production FAQ guidance. Done when every node reports the same version in a query such as SELECT version() and that version appears on the supported list.
Enable the system tables and logging you need
An engineer confirms that the system log tables used by the dashboard, such as query and metric logs, are enabled and receiving rows on each node. Some of these tables depend on server configuration files and retention settings, so confirm the exact configuration keys and defaults for your version in the official documentation rather than relying on memory. Check that log tables are not being truncated faster than your dashboard time window. Done when a query against system.query_log and system.metric_log on each node returns recent rows.
Create a read-only monitoring user
A ClickHouse administrator creates a dedicated user with the readonly setting and grants only the SELECT privileges on the system tables the dashboard needs, following the SQL-driven access control workflow. Do not reuse the default user or an application account, and restrict the default user as part of the same change. Store the credential in your secrets manager and confirm the user can read but cannot write or alter anything. Done when a test SELECT from system.tables succeeds for the monitoring user and an attempted CREATE TABLE or INSERT is refused.
Deploy ClickStack inside your infrastructure
The platform team deploys ClickStack in the same network boundary as the cluster, using the packaging and sizing your environment supports, and keeps its storage and ingress under your control. Place the UI behind your existing identity and TLS controls rather than exposing it directly. Confirm the component versions you deploy against the ClickStack documentation, since configuration keys change between releases. Done when the UI loads over your internal address and the login page enforces your access policy.
Connect ClickStack to the cluster
An engineer creates a ClickHouse data source in ClickStack that points at the monitoring user, using the cluster endpoint and TLS settings your environment requires. Use a hostname that resolves to the node or load balancer you intend to query, and confirm the connection uses the same certificate chain as your other clients. Run a simple test query from the UI to confirm connectivity. Done when the data source test succeeds and a query for a known table, such as SELECT count() FROM system.parts, returns a number that matches a direct clickhouse-client run.
Load the bundled dashboard and confirm data
A platform engineer imports the bundled Selects, Inserts and Infrastructure dashboard and sets its time window to a range where you know activity occurred. Compare at least one chart, such as query counts or insert volume, against the same figure from a direct query to system.query_log. Charts that stay empty usually point to a missing log table, a wrong database name in the data source, or a time zone mismatch. Done when the dashboard renders populated panels for the chosen window and at least one panel matches your direct query result.
Validate the cluster-wide view and keep alerting separate
The platform team runs a query on each replica to confirm that the dashboard shows activity from every node in the cluster, not only the one the UI connects to. Record which alerting path you rely on, such as Prometheus rules or a separate alert manager, and agree that ClickStack is used for investigation rather than paging. Store the dashboard definition under version control so changes are reviewed. Done when a known query executed on a different replica appears in the dashboard within the expected window and the alerting owner is named in the runbook.
Security and operational considerations for ClickStack monitoring
ClickStack reads the same system tables and servers it monitors, so the monitoring path carries its own access, exposure and load risks. The cards below cover the controls to set before the UI goes live and the checks to repeat after each change.
Dedicated monitoring user
Create a separate ClickHouse user for HyperDX rather than reusing the default user or an application account. Grant it read access to the system tables it needs through roles created with SQL, and nothing that writes data or changes settings. ClickHouse documentation recommends restricting the default user, which starts with all permissions, so confirm that the default user is locked down or disabled before the monitoring user is added. Review the grants whenever the dashboard queries change.
UI exposure and TLS
The ClickStack UI should sit behind your internal network, reverse proxy or SSO gateway, not on a public address. Terminate TLS for browser sessions and for the connection from the UI to ClickHouse, and confirm the certificate chain your users trust. Check which ports are reachable from outside the monitoring segment, because a reachable UI exposes query text and cluster layout to anyone who can log in.
Sensitive literals in query logs
Query logs record the SQL text as sent, so a query that filters on an email address, account number or token stores that value in system.query_log and shows it in the search views. Decide which fields must be masked before logs are written, using query masking rules or by changing how applications send values, and test the result with a known sensitive literal. Treat the logs as data with the same classification as the tables they describe.
Load from dashboard queries
Dashboards run queries on a timer, and each refresh scans system tables that can grow large on a busy cluster. Set a refresh interval that fits the cluster, limit the time range on default views, and watch the monitoring user's queries in system.query_log to see their cost. Run the heaviest panels on one replica where possible, so monitoring does not compete with ingest on every node.
Retention of system log tables
System log tables such as query_log and part_log grow continuously, and their retention sets how far back investigations can reach. Define TTL or rotation settings for each log table, record the retention period in your data governance records, and confirm that backups do not copy log tables you have decided not to keep. Compliance teams often ask for this period in writing, so put it in the configuration rather than leaving the defaults in place.
Reviewing who can see query text
Anyone with ClickStack access who can read query text can see the shape of your data and sometimes its values. Review the list of users and roles with access to the UI at a fixed interval, remove accounts for people who changed teams, and separate the group that sees full query text from the group that sees timing and error summaries. Keep a record of each review so the access decisions can be shown to auditors.
Questions teams ask before running ClickStack on their own ClickHouse
What does it cost to point ClickStack at our own ClickHouse cluster?
Cost comes from people time and infrastructure, not from a license for the ClickHouse server, which is open source under the Apache License 2.0. Infrastructure covers hosts for the HyperDX UI, its own data store, networking and storage for the telemetry you choose to keep. Labor covers scoping the cluster, creating a read-only monitoring user, wiring network paths, configuring dashboards and handing over runbooks. Setup is largely one-time, while hosting, storage growth and any ongoing review of dashboards recur. Effort rises with the number of clusters, the amount of system table data you retain, access control requirements and how many teams need their own views. Ask any quote to itemize one-time work, recurring infrastructure and support, and to state what is excluded.
How long does a first ClickStack setup take?
The duration depends on how many clusters you connect, how quickly access and network approvals move through your change process, and whether the UI is hosted in a place your team already runs. A single cluster with an existing host and an approved monitoring account is the simplest case. Multiple replicas, separate environments, SSO for the UI and retention tuning for system tables add steps. Confirm the timeline in writing as part of a fixed scope, and ask which of your approvals (firewall changes, service accounts, certificate issuance) sit on the critical path.
What must our team provide before the engagement starts?
Plan to provide the following before work begins:
- Read access to the ClickHouse clusters, ideally through a dedicated monitoring user with only the grants needed to read system tables, not the default user.
- Network paths from the UI host to each ClickHouse HTTP or native port, including any TLS settings your certificates require.
- A host or container platform for the HyperDX UI and its supporting services, with storage, backups and access controls defined by your team.
- Named owners for the UI, for access approvals and for the decision on what telemetry is stored and for how long.
- A test or pre-production cluster where dashboards and queries can be checked before they touch production.
Can our own engineers set up ClickStack without an outside partner?
Yes, if your team already runs ClickHouse and container or Linux hosts, and has someone who can read system tables and manage a service account. The tasks are not exotic: connect the UI to the cluster, verify that system tables are readable, adjust retention for log tables and restrict access. Where teams struggle is in the surrounding work, such as TLS, SSO, separating environments, deciding what must not be exposed to every user and writing runbooks that survive staff changes. An outside partner is most useful when those areas must be designed and documented together, or when the deadline is fixed. Either way, have a named engineer own the monitoring user and its permissions.
Does ClickStack replace Prometheus alerting?
No. ClickStack is for deep investigation: searching system tables, reviewing query history, inspecting parts and merges, and understanding what happened during an incident. Paging and threshold alerts belong in a system built for that job, such as Prometheus alert rules routed through Alertmanager, which is where most teams keep their on-call rules. Keep alert definitions in version control, owned by the operations team, and keep dashboards separate so that a broken or slow UI query never silences a page. Use ClickStack to explain an alert, not to be the only thing that raises it.
Who can see query text and system data through the UI, and how do we control it?
Query text, user names and table names in system tables can reveal sensitive business logic and personal data, so access to the UI should be treated as access to the cluster's operational data. Use a dedicated read-only account, apply the access control features documented for ClickHouse (users, roles and row policies), and avoid granting the default user, which starts with all permissions. Confirm which external authenticator your organization will use and how UI users are mapped to roles. Ask your security reviewer to sign off on retention of system log tables, since they often contain query text that a compliance review will want to limit.
What should we confirm in a quote for ClickStack on our ClickHouse?
Ask for a written scope that names each ClickHouse cluster and environment in scope, the hosting model for the UI and its data store, the monitoring account and its grants, the authentication method, and the dashboards and saved searches to be delivered. Confirm what is one-time and what recurs, who performs upgrades to the UI and to ClickHouse, and what handover material you receive. Check the acceptance criteria, what is excluded, and how changes to scope are priced. Also ask how the work fits with your change calendar and whether a restore test or alert migration is included.
Limits to confirm in your environment before you rely on this view
The ClickStack view reads ClickHouse's own system tables, so what it shows depends on your server version, your logging settings and the health of every node. Confirm the points below in vendor documentation or a test environment before an incident depends on them.
Server version and system tables
The dashboards and searches assume specific system tables and columns exist on your ClickHouse version. Compare the table and column names your cluster exposes against the version you run, and check them in a test environment before relying on any panel. Confirm this with the ClickHouse documentation for your exact release, since column names can change between versions.
ClickStack release matching
The bundled Selects, Inserts and Infrastructure dashboard and the Lucene and SQL search features belong to a specific ClickStack release. Confirm that the dashboard definitions and search syntax in the release you deploy match what your team has tested, and record the release you rely on so upgrades do not change panels silently.
System log retention settings
System log tables keep history only as long as your configuration allows, and retention is often shorter than teams expect. Check the TTL and flush settings for the query, part and metric logs in your configuration, and decide how far back an investigation can look before you commit to a response window.
Query cost during incidents
Querying large system tables while the cluster is under pressure can add load at the worst moment. Test representative searches against your largest log tables, limit time ranges and result sizes, and agree which queries responders run first. Ask your team to rehearse these queries under load before an incident.
Security support status of your version
A view is only as current as the server behind it. Check the ClickHouse security policy to confirm your version still receives security updates, because unsupported versions leave the monitored system exposed regardless of how good the dashboard is.
Coverage across replicas and Keeper
Each replica writes its own system tables, and Keeper nodes report their own health, so a view connected to one node shows only that node. Confirm that every replica and every Keeper node is reachable by the ClickStack collection path, and that the data you expect from each one appears before you treat the view as cluster-wide.
How DeployOpen scopes and delivers ClickStack monitoring
DeployOpen plans, builds, documents and hands over self-monitoring for ClickHouse inside your own infrastructure, so the HyperDX UI reads your system tables without becoming a dependency for alerting. Engagements start with a written scope that names the clusters, the users who get access and the dashboards to ship. Work is fixed-scope, and ongoing support is available as a retainer.
How we scope
- The written scope names which ClickHouse clusters, nodes and system tables the ClickStack deployment will read.
- We confirm the ClickHouse version in use and the support status of that line before planning queries against its system tables.
- Scope records which teams get HyperDX access, how their roles map to ClickHouse users, and which default user permissions are restricted.
How we build
- We deploy ClickStack in your environment and connect the HyperDX UI to ClickHouse with a dedicated read-only monitoring account.
- We configure the bundled Selects, Inserts and Infrastructure dashboards and test each panel against live system table data.
- We write and test Lucene and SQL search examples for slow queries, insert pressure and merge activity, and check retention settings on the log tables.
How we hand over
- You receive a runbook covering access setup, dashboard meanings, search examples and where each system table is read from.
- We document the boundary between ClickStack dashboards and your alerting system, so on-call paging does not depend on the UI being available.
- Handover includes a walkthrough with your operators, with written acceptance criteria agreed before the build started.
How we support
- Ongoing support covers ClickStack upgrades, dashboard changes and access reviews when new teams or clusters are added, available as a retainer.
- We recheck dashboards after ClickHouse upgrades, since system table columns can change between releases and the changelog should be read first.
- Continuous testing of monitoring access and query cost can be added to the retainer if your environment changes.
