Guide
Capabilities
Full reference for the agent's 32+ tools, 18 expert skills, plan-and-verify mode, and worked example questions.
The agent exposes a small set of powerful primitives (33 tools, plus 3 env-gated control tools, 4 env-gated cross-source Postgres tools, and 2 env-gated PeerDB tools). Anything without a dedicated tool is done by writing SQL with the query tool, guided by a skill recipe — or by asking search_tools which tool fits. You never call tools directly — the agent picks and chains them automatically.
Tools
| Category | What it covers | Tools |
|---|---|---|
| Schema & exploration | Databases, tables, columns, ad-hoc SQL | query, list_databases, list_tables, get_table_schema, explore_table_schema |
| Query analysis | Running, slow, failed queries; normalized slow patterns; EXPLAIN; pre-flight cost estimate | get_running_queries, get_slow_queries, list_slow_query_patterns, get_failed_queries, explain_query, estimate_query_cost |
| Health, storage, replication, merges | Metrics, disks, parts, replication, merges | get_metrics, get_disk_usage, get_table_parts, get_replication_status, get_merge_status |
| Capacity planning | Disk-full forecast, TTL/retention advice, mutation impact dry-run (all recommend-only) | forecast_disk_capacity, suggest_ttl_adjustment, estimate_mutation_impact |
| Aggregation advisor | Design MV/projection DDL from frequent aggregation queries, with a size estimate (recommend-only) | recommend_materialized_view |
| Plan & verify | A visible, adaptive step-by-step plan | update_plan |
| ClickHouse docs | Built-in docs for a function, setting, MergeTree/server setting, metric, or event, read from the host itself so they match its version | explain_clickhouse_symbol |
| Tool discovery | Find the right tool by describing what you want, when the tool name is not obvious | search_tools |
| Dashboard pages | Answer "what does the X page show?" by running that page's own table and chart queries | get_page_data |
| Knowledge & interaction | Load expert guides; ask the user | load_skill, ask_user |
| Charts & visualization | Run SQL and return an interactive chart | query_and_visualize |
| Insights | Explain a statistical anomaly baseline and z-score | explain_anomaly_score |
| Reports | Generate a weekly/monthly cluster health report to narrate (read-only) | generate_cluster_report |
| Advisor | Ranked skip-index/projection/partition/PREWHERE recommendations for a slow query (recommend-only) | get_optimization_recommendations |
| Fine-tune | Ranked schema lint + TTL/partition/engine/Distributed + settings tuning findings for a database/table (recommend-only) | get_tuning_suggestions |
| Unread objects | Largest tables and projections, and the columns of one table, that no SELECT in the query log read (this replica only, unread is not unused) | get_usage_findings |
| Optimization plan | One ranked plan built from the findings the advisors already stored, with owner, savings, apply cost, risk and DDL to review (recommend-only, never applied) | build_optimization_plan |
| Detached parts | Detached parts per table and reason with a review class, sizes, age and same-replica coverage (this replica only, never a safe-to-remove verdict) | get_detached_parts_triage |
| Dashboards | Suggest a dashboard layout from registry charts for a natural-language request (recommend-only) | suggest_dashboard |
| Control actions (env-gated) | Kill query/mutation, optimize table | kill_query, kill_mutation, optimize_table |
| Cross-source Postgres (env-gated) | Read-only SELECT, health metrics, slow-query patterns, and per-table stats against a Postgres source, for correlating with ClickHouse in one conversation | run_postgres_select_query, get_postgres_metrics, list_postgres_slow_query_patterns, get_postgres_table_stats |
| PeerDB mirrors (env-gated) | Worst-first fleet status plus per-mirror detail (rows synced, table counts, batches, errors) from the read-only PeerDB proxy | get_peerdb_mirror_status |
| PeerDB metrics (env-gated) | Replication-slot lag and its history, CDC rows-synced throughput, snapshot/initial-load progress, per-peer queries, and fleet aggregates | get_peerdb_metrics |
Everything else (expensive-query rankings, query patterns, anomalies, table-design advice, settings, logs, replication queue, ZooKeeper, users…) is done with query plus the relevant skill recipe — so the agent keeps full reach with a much smaller, more reliable tool surface. Not sure which primitive covers a request? Ask search_tools; it returns the matching tool names with when to use each, and only tools that are actually available in the conversation.
Control tools are off by default
Control tools (kill_query, kill_mutation, optimize_table) are on by default in self-hosted and off in cloud; AGENT_ENABLE_CONTROL_TOOLS overrides that (anonymous cloud visitors never get them). The agent always confirms before using them.
Cross-source Postgres tools are off by default
The Postgres tools (run_postgres_select_query, get_postgres_metrics, list_postgres_slow_query_patterns, get_postgres_table_stats) appear when at least one Postgres source is configured via the POSTGRES_* env lists and answers a quick probe, or when CHM_FEATURE_POSTGRES_SOURCE=true (false turns them off; see Configuration). They read Postgres over the read-only pg path and take a pgHostId (an index into the POSTGRES_* lists), so the agent can correlate ClickHouse and Postgres in one conversation. They read env-configured Postgres hosts; per-user (D1) Postgres connections are not yet visible to the agent — the same limitation as the ClickHouse agent tools.
PeerDB tools are off by default
get_peerdb_mirror_status and get_peerdb_metrics appear when PEERDB_API_URL is set and PeerDB answers a quick probe, or when CHM_FEATURE_PEERDB_AGENT=true (false, or CHM_FEATURE_PEERDB_ENABLED=false, turns them off; see Configuration). They read through the same read-only server-side PeerDB proxy as the /peerdb pages — fixed endpoints only, credentials stay server-side, mirror and peer configs are stripped — and need PEERDB_API_URL configured (see Configuration). Per-connection (?connection=) PeerDB links are not yet visible to the agent.
Tool reference
| Tool | Description | Notes |
|---|---|---|
query | Execute a read-only SQL query on ClickHouse. | SELECT / WITH / DESCRIBE / EXPLAIN only. Write statements are rejected. Results are capped at 1000 rows (truncated: true + a note when hit) — add your own LIMIT or aggregation for larger result sets. |
list_databases | List all databases with engine and comment metadata. | |
list_tables | List tables in a database with row counts and size. | |
get_table_schema | Get column definitions for a specific table. | |
explore_table_schema | Three-mode exploration: no args → databases; database only → tables; database + table → full schema with indexes and keys. | |
get_running_queries | Currently running queries ordered by elapsed time. Query text is truncated to 2000 characters. | Reads system.processes. |
get_slow_queries | Slowest completed queries from the last 1 hour by default (lastHours override). Query text is truncated to 2000 characters so it can be passed to explain_query. | Reads system.query_log (QueryFinish, is_initial_query = 1, event_time window). |
list_slow_query_patterns | Normalized slow query patterns — system.query_log grouped by normalized_query_hash, with calls, total/avg/p50/p95/p99/max duration, CPU time, peak memory, read/write bytes, errors, and cache-hit ratio per pattern. | Read-only. Use for "which query shape is expensive overall" — unlike get_slow_queries, which ranks individual executions. First step of the query-optimization diagnose loop. |
get_failed_queries | Recent failed queries from the last 24 hours by default (lastHours override). Query text is truncated to 2000 characters. | Reads system.query_log. |
explain_query | EXPLAIN PLAN / PIPELINE / PLAN with indexes for a query. | |
estimate_query_cost | Pre-flight cost estimate (rows scanned, bytes read, peak memory, wall time, confidence) from EXPLAIN alone. | Read-only and recommend-only — runs EXPLAIN only, never executes the analyzed query. |
get_metrics | Server health: version, uptime, active connections, memory. | Reads system.metrics. |
get_disk_usage | Per-disk free and total space. | Reads system.disks. |
get_table_parts | Part-level info for a table: rows, size, compression ratio. | Reads system.parts. |
forecast_disk_capacity | Forecast when disks will run out of free space from recent write-growth trend, plus top contributing tables. | Reads system.part_log (NewPart events) + system.disks. Reports a clear message instead of a forecast when part_log isn't enabled. |
suggest_ttl_adjustment | Recommend a TTL/retention change for a table to keep projected disk utilization ≤80%, never below a stated retention floor. | Returns a suggested ALTER TABLE ... MODIFY TTL ... string + risk note — recommend-only, never executed. Reports a clear message instead of a suggestion when part_log isn't enabled. |
estimate_mutation_impact | Pre-flight impact estimate for an ALTER TABLE ... UPDATE/DELETE: rows matched, parts/bytes to rewrite, projected duration from recent mutation throughput, and whether free disk can hold the rewrite. | Read-only and recommend-only — parses the statement as text and only ever runs derived read-only queries (SELECT count(), system.parts/part_log/disks); never executes the mutation. |
recommend_materialized_view | Mine frequent GROUP BY/aggregate query shapes and design a Summing/AggregatingMergeTree MV or projection to pre-aggregate them. | Returns DDL text + size estimate + impact + risk (added write-path/storage cost) — recommend-only, never executed. Reads system.query_log, system.parts, system.tables. |
get_replication_status | Per-table replication delay, queue size, and replica counts. | Reads system.replicas. |
get_merge_status | Currently running merges with progress and elapsed time. | Reads system.merges. |
update_plan | Create or update a step-by-step workflow plan for a 3+ step investigation. Skip it for one-tool answers. | Rendered as a live checklist in the UI. |
search_tools | Find the agent tool that fits a task when the name is not obvious: pass a plain-language query (e.g. "recommend a skip index", "which slot is lagging") and get back the matching tool names with a one-line summary, a category, and the live tool description. Omit query to list the core tools; pass category to narrow, or includeCore: false to see only the discoverable long tail. | Always available — it is itself a core tool, because discovery is useless if it has to be discovered. Read-only and does no I/O: it only reads the in-process tool catalog. It never advertises a tool that is not callable in the current conversation — a gated-off tool (destructive control actions, cross-source Postgres, PeerDB) is reported under unavailable_due_to_gates instead of being offered, so the agent never tries to call a tool that does not exist. Results are capped (default 15) with a truncated flag + note. |
load_skill | Load an expert guide (skill) with SQL recipes for a specific domain. | |
explain_clickhouse_symbol | ClickHouse's built-in docs for one symbol (kind: function, setting, merge_tree_setting, server_setting, metric, event), or a keyword search across function and setting names and descriptions. Functions return syntax, arguments, examples, and introduced_in; settings return type, default, current value, and tier. | Read-only, parameterized reads of system.functions / settings / merge_tree_settings / server_settings / metrics / events. Columns are discovered per host, so older versions return what they have. Long examples are truncated. Events that have not fired yet are hidden by ClickHouse. |
find_reference_query | Search the dashboard's built-in library of 100+ vetted, version-aware monitoring queries and return the closest matches (name, description, SQL). | Read-only, deterministic keyword-overlap lookup over the built-in QueryConfig catalog — executes nothing. Meant to be used before hand-writing system.* SQL. |
get_page_data | Return the live data a dashboard page shows by running that page's own table and chart queries. page is a route (/merges, /keeper/watches, /overview?tab=storage), a page title, or a single query-config / chart name; omit it to list the pages. Optional filters (page filter fields, e.g. {"user": "alice"}), lastHours, and limit (rows per source, default 20, max 200). | Read-only. Same registry and executor as the page's API routes, so version-aware SQL, filter WHERE clauses, the row cap, and the optional-table check all apply. A source on an optional table that is missing comes back as a note, not an error. Output is capped at about 16 KB (rows are trimmed and flagged truncated); at most 24 sources run per call. The page list lives in lib/ai/agent/page-data-map.ts. |
ask_user | Ask the user a question to gather information before proceeding. | |
query_and_visualize | Run a SQL query and return an interactive chart config. | Chart type is auto-detected from result columns. Rows are capped at 1000 (truncated: true + a note when hit) before the chart is built. |
explain_anomaly_score | Explain a per-host/per-metric statistical anomaly baseline (mean, stddev, median, MAD, sample count) and, given a value, its z-score and anomaly verdict. | Read-only. Reports "no baseline yet" during cold start (falls back to a static threshold). |
generate_cluster_report | Generate a cluster health report (top insight findings, severity/category breakdown, baselines count, disk-capacity outlook) over a weekly (7-day) or monthly (30-day) window, returned as structured summary + markdown for the agent to narrate. | Read-only — same deterministic builder the scheduled reports use; scheduling and delivery are configured in /report-settings. |
get_optimization_recommendations | Analyze a slow query (by queryId from system.query_log, or raw sql) and return ranked skip-index, projection, partition-key, and PREWHERE recommendations with DDL/rewrite text, rationale, risk, effort, and an estimated granules/bytes saved. | Read-only and recommend-only — reads EXPLAIN + system.tables/system.columns/system.data_skipping_indices/system.parts; never executes or applies any DDL or rewrite. Every impact figure is explicitly labeled an estimate. |
get_tuning_suggestions | Scan a database (or one table) for ranked schema lint findings — needless Nullable, oversized integers, compression-codec opportunities, LowCardinality candidates (ranked by on-disk bytes), table-level TTL / PARTITION BY bloat / non-replicated MergeTree / missing Distributed wrappers / UUID-leading ORDER BY, plus server/merge-tree settings that differ from defaults in risky ways. Each finding carries evidence (bytes/rows/ratios or partition counts), an estimated benefit, ready-to-review DDL (including local + Distributed / ON CLUSTER variants when topology is known), and (for data-dependent rules) a verification query. | Read-only and recommend-only — reads system.columns/system.parts/system.tables/system.clusters/system.settings/system.merge_tree_settings only (no user-table data); never executes or applies any DDL or settings change. Every benefit figure is explicitly labeled an estimate. |
get_usage_findings | List what the query log shows is not being read: the largest MergeTree tables no finished SELECT read, unused projections (largest first), and, when database and table are both given, that table's columns no SELECT referenced. Every result states the log window start. | Read-only — fixed SELECTs over system.query_log, system.tables, system.projections, system.projection_parts and system.columns; never changes anything and never says an object is safe to drop. Sees this replica only; unread is not the same as unused (rarer jobs, views and INSERT ... SELECT are invisible); a column read only through SELECT * looks unread; data-skipping indices are not covered. |
get_detached_parts_triage | List detached parts grouped by database, table and reason (largest first, top 50) with a review class, part and byte counts, partitions, oldest and newest modification time and covered_by_active. Optional database and table narrow it. | Read-only — one fixed SELECT over system.detached_parts and system.parts, the same query as the detached-parts-triage page; database and table are bound query parameters. Sees this replica only; coverage here does not prove other replicas hold the data; parts ClickHouse still owns (attaching_, deleting_, tmp-fetch_) cannot be removed via SQL; no class means safe to remove, and it never produces a command to remove or attach a part. |
build_optimization_plan | Rank the findings the advisors already stored (unread tables and unused projections, detached parts, TTL rewrite and OPTIMIZE-on-unchanged-data merge cost, retention, schema suggestions) into one plan. Each item has the owner database, saved bytes when measured, apply cost with an hours estimate (default 50 MB/s, rewriteMBps overrides), risk, DDL to review, evidence/verify/rollback SQL and dependsOn ordering. Also returns skipped collectors with a reason and testedAndRejected items. | Read-only and recommend-only: one SELECT on the stored findings (no system-table scan) and it never applies anything (notApplied: true). Unmeasured savings stay null, never guessed. High-risk items need owner approval. Detached parts and unread tables get no DDL. |
suggest_dashboard | Map a natural-language request to a dashboard layout built only from charts in the chart registry, auto-placed on the plan-57 12-column grid. | Recommend-only — never persists anything. The chat UI shows an "Apply to dashboard" action that loads the layout into the dashboard builder's unsaved working grid; saving still requires the existing save action. Rejects any chart name not present in both the client and API chart registries. |
kill_query | Kill a running query by query_id. | DESTRUCTIVE. Gated by AGENT_ENABLE_CONTROL_TOOLS (default on self-hosted, off in cloud). Agent always confirms first. |
kill_mutation | Cancel a running mutation on a table. | DESTRUCTIVE. Gated by AGENT_ENABLE_CONTROL_TOOLS (default on self-hosted, off in cloud). Agent always confirms first. |
optimize_table | Trigger an OPTIMIZE on a table to force merges. | DESTRUCTIVE. Gated by AGENT_ENABLE_CONTROL_TOOLS (default on self-hosted, off in cloud). Agent always confirms first. |
run_postgres_select_query | Execute a read-only SQL query against a Postgres source (pgHostId). | Integration-gated: on when a Postgres source is reachable or CHM_FEATURE_POSTGRES_SOURCE=true. SELECT / WITH / SHOW / EXPLAIN / TABLE / VALUES only — writes and multi-statement strings are rejected and the session is pinned read-only. Results capped (default 1000 rows) with a truncated flag + note. |
get_postgres_metrics | Postgres health for a source (pgHostId): version, uptime, connection counts by state with max_connections + saturation %, buffer-cache hit ratio, transaction commit/rollback + deadlocks, database size, and replication status. | Integration-gated: on when a Postgres source is reachable or CHM_FEATURE_POSTGRES_SOURCE=true. Reads pg_stat_activity, pg_settings, pg_stat_database, pg_stat_replication. The Postgres analog of get_metrics. |
list_postgres_slow_query_patterns | Top normalized slow-query patterns from pg_stat_statements (pgHostId): calls, total/mean exec time, rows, shared-buffer cache-hit ratio, WAL bytes. | Integration-gated: on when a Postgres source is reachable or CHM_FEATURE_POSTGRES_SOURCE=true. Returns an informative message (not an error) when the pg_stat_statements extension is not installed. The Postgres analog of list_slow_query_patterns. |
get_postgres_table_stats | Per-table health for a source (pgHostId): worst dead-tuple bloat (dead vs live, dead %, last vacuum/autovacuum/analyze) and unused indexes (idx_scan = 0, excluding PK/unique, with on-disk size). | Integration-gated: on when a Postgres source is reachable or CHM_FEATURE_POSTGRES_SOURCE=true. Reads pg_stat_user_tables, pg_stat_user_indexes, pg_index. The per-table companion to get_postgres_metrics. |
get_peerdb_mirror_status | PeerDB mirror status without arguments → worst-first fleet overview (failed > non-running > highest lag); with mirrorName → one mirror's state, authoritative rows-synced total, per-table counts, recent batches, recent errors, and those errors grouped by message pattern (recent_error_patterns). | Integration-gated: on when PeerDB is reachable or CHM_FEATURE_PEERDB_AGENT=true (and PeerDB not disabled). Read-only fixed endpoints; mirror configs stripped; every list capped with truncation flags. |
get_peerdb_metrics | PeerDB pipeline metrics, selected by metric: fleet (default) aggregates mirrors by status, failed/paused names, CDC vs QRep split, total rows synced, and the worst replication-slot lag plus a worst-first slot table; slots gives per-peer slot health (lag in MiB, active flag, WAL status) classified ok/warn/critical; slot_lag_history (needs peerName + slotName) returns the lag time series and a growing/recovering/flat verdict; rows_synced (needs mirrorName) returns the CDC rows-synced series with current/peak rows per second; snapshot (needs mirrorName) returns initial-load progress per table; peer_stats (needs peerName) returns active queries plus peer type and version; log_patterns (optional mirrorName, logWindow 1h/24h/7d) groups error logs across mirrors by message pattern and returns the top 15 with count, last seen, and affected mirrors. | Integration-gated: on when PeerDB is reachable or CHM_FEATURE_PEERDB_AGENT=true (and PeerDB not disabled). Read-only fixed endpoints. Fleet mode reuses the same summarizePeerDBFleet aggregation as the fleet pages, so it agrees with the UI. Mirror and peer config blocks (which may embed connector secrets) are never returned. Partial fan-outs degrade to partial: true rather than failing. Every list and time series is capped with a truncation flag. Answers "which slot is lagging worst, and is it recovering?" — use get_peerdb_mirror_status instead for "which mirrors are failing?". |
Skills
Skills are expert guides — with copy-pasteable SQL recipes against system.* — that the agent loads on demand. Because the toolset is lean, skills are how the agent stays powerful. Twenty skills are included:
| Skill | Covers |
|---|---|
system-tables-reference | Exact columns of key system tables; recipes; tools vs raw SQL |
data-analysis | Aggregation & time-series recipes (largest scan, expensive queries, patterns, period comparison) |
anomaly-detection | Recent-vs-baseline comparisons (error spikes, p95 regressions, part explosions) |
query-tuning-advisor | Diagnose a slow query and propose concrete rewrites & better joins |
query-optimization | PREWHERE, JOIN patterns, materialized views, EXPLAIN, indexes |
schema-design-advisor | ORDER BY/partition keys, codecs, skip indexes, column type right-sizing |
storage-optimization | Compression codecs, TTL, tiered storage, part management |
version-upgrade-advisor | Whether/how to upgrade ClickHouse and what is gained |
hardware-tuning | Size settings to the box's cores/RAM/disk |
concept-explainer | Teach core ClickHouse concepts |
replication-guide | ReplicatedMergeTree, failover, lag diagnosis, Keeper |
cluster-operations | Distributed tables, resharding, node management, topology |
migration-patterns | ALTER patterns, zero-downtime schema changes |
security-hardening | RBAC, row policies, quotas, audit logging |
clickhouse-best-practices | Schema design, query tuning, operational guidelines |
troubleshooting | OOM, slow merges, stuck mutations, error-code diagnosis |
incident-response | Structured triage recipes (disk full, errors, replication lag, health sweep) |
plan-and-verify | Decompose with update_plan and verify each result before concluding |
optimization-plan | Present a build_optimization_plan result: NOT APPLIED banner, fenced DDL, owner approval for high-risk items |
settings-audit | Read-only audit of changed settings; flags Experimental, Beta, Obsolete |
List available skills with GET /api/v1/agent/skills.
Plan and verify
For multi-step tasks the agent authors a live checklist with update_plan, keeps exactly one step in progress, and adapts the plan as results come in. Crucially, it verifies each result before stating it — re-querying or cross-checking a second system table for a finding, running explain_query on both versions before claiming a rewrite is faster, and separating what is verified from what is a hypothesis. The plan-and-verify and incident-response skills encode the recipes. Simple one-step questions skip the plan entirely.
Example questions
Ask in plain English:
- "Which queries are running right now and how long have they been executing?" — lists query id, user, elapsed time, and memory, sorted by duration.
- "What were the 10 slowest queries in the last 24 hours?" — fetches from the query log and offers to EXPLAIN any of them.
- "What's the largest data scan ever performed on this cluster?" — loads
data-analysisand runs thesystem.query_logrecipe. - "Anything abnormal in the last hour versus baseline?" — loads
anomaly-detectionand compares recent activity to the preceding window. - "Show me query volume per hour over the last day as a chart." — runs the aggregation and renders a line chart inline.
- "Suggest a better ORDER BY and which columns should be LowCardinality for
analytics.events." — loadsschema-design-advisor, inspects the schema and parts, and recommends changes.
Related
AI Agent
Overview of the agent, its architecture, and quick start.
Configuration
All environment variables, LLM providers, and access control.
Conversation history
How chat history works and how to enable server-side persistence.
ClickHouse query optimization
The diagnose loop above, explained end to end with runnable SQL.