Clickhouse Performance Tuning

What it does

When your agent uses it

• Queries are slow
• Investigating performance bottlenecks
• Tuning ClickHouse server settings
• With clickhouse performance

Example prompts

• “clickhouse performance”
• “optimize clickhouse query”
• “clickhouse slow query”
• “/clickhouse-performance-tuning”

Requirements

• Compatibility (from SKILL.md): Designed for Claude Code
• Pre-approved tools (allowed-tools): Read, Write, Edit

Workflow steps

7 steps, taken from the first numbered list in SKILL.md.

1. 1Diagnose slow queries — rank the last 24h of system.query_log by
2. 2ORDER BY key optimization — the primary lever. Filtering on the ORDER BY prefix
3. 3Data skipping indexes — bloom_filter for high-cardinality lookups, set for
4. 4Projections — automatic pre-aggregation ClickHouse picks transparently when a
5. 5Server settings — max_threads, external sort/group-by spill, async_insert,
6. 6Materialized views — pre-aggregate on INSERT into an AggregatingMergeTree so
7. 7Query patterns — PREWHERE, LIMIT BY, and avoiding FINAL.

What it can do on your machine

Read from SKILL.md and the folder at commit cfae287. It shows what the files ask for, not the result of running them.

• Tool permissions

  Pre-approves these tools, so the agent can use them without asking each time:

  • Read
  • Write
  • Edit

  From allowed-tools in the SKILL.md frontmatter.

• Runs code

  No scripts in the folder and no shell commands in SKILL.md (its code samples are sql).

  From the folder's file list and the shell code blocks in SKILL.md.

• Network

  Links to these hosts (documentation or services it may open):

  • clickhouse.com

  From URLs in SKILL.md, links to its own repository left out.

• Credentials

  Names no API keys, tokens, secrets or passwords.

  From names ending in _API_KEY, _TOKEN, _SECRET, _KEY or _PASSWORD in SKILL.md.

• Compatibility

  Designed for Claude Code

  From compatibility in the SKILL.md frontmatter.

Context cost

Clickhouse Performance Tuning loads about 1.2k tokens when it runs, and up to ~3.2k if it reads all its reference files. Until then it costs about 105 tokens; SKILL.md has 376 words of instructions outside code blocks.

Always · name and description, kept in context so the agent knows when to use it

~105

When it runs · the whole SKILL.md, loaded when a task matches

~1.2k

With references · SKILL.md plus every file in references/, read only if the agent opens them

~3.2k

Estimates: characters ÷ 4, the usual rule of thumb; real counts depend on the model's tokenizer. Scripts and assets cost tokens only if the agent reads them.

Safety

Automated static check — not a guarantee. Review scripts before installing. It scans the text of SKILL.md for risky patterns (piping downloads into a shell, reading credential files, hidden Unicode, destructive commands); files beside SKILL.md are not scanned.

SKILL.md

The full file from jeremylongshore/tons-of-skills-marketplace at commit cfae287, republished under its MIT licence (© jeremylongshore). 376 words, ~1,176 tokens.

name

clickhouse-performance-tuning

description

Optimize ClickHouse query performance with indexing, projections, settings tuning, and query analysis using system tables. Use when queries are slow, investigating performance bottlenecks, or tuning ClickHouse server settings. Trigger with "clickhouse performance", "optimize clickhouse query", "clickhouse slow query", "clickhouse indexing", "clickhouse tuning", "clickhouse projections".

allowed-tools

Read, Write, Edit

compatibility

Designed for Claude Code

version

1.7.0

license

MIT

author

Jeremy Longshore <jeremy@intentsolutions.io>

tags

saas, database, analytics, clickhouse, olap

SELECT event_time, query_duration_ms, read_rows, read_bytes,
       substring(query, 1, 300) AS query_preview
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 24 HOUR
  AND query_duration_ms > 1000   -- > 1 second
ORDER BY query_duration_ms DESC
LIMIT 20;

SELECT query_duration_ms, read_rows,
       formatReadableSize(read_bytes) AS read_size,
       formatReadableSize(memory_usage) AS memory
FROM system.query_log
WHERE query_id = currentQueryId() AND type = 'QueryFinish';

© jeremylongshore, MIT. Rendered from Markdown: HTML in the file is shown as text, images as links, and headings moved down two levels. Raw file

Files

SKILL.md and 2 other files (references) in skills/.curated/clickhouse-performance-tuning of jeremylongshore/tons-of-skills-marketplace.

• SKILL.md
• references/examples.md
• references/implementation.md

Open the folder on GitHubat commit cfae287