Setting the file. One moment.
Chapter 04 · Clickhouse Best Practices
Subchapter 4.8
rules/agent-query-safety.mdMarkdown5 KBView on GitHub
Impact: CRITICAL
Every agent-generated query must have explicit safety limits. A single unbounded query can scan billions of rows, consume all memory, or run for minutes.
LIMIT to cap returned rows (default LIMIT 1000)max_rows_to_read or max_bytes_to_read — LIMIT alone does not prevent a full scanmax_execution_time (default 30)SELECT * on large tables without LIMIT and scan capsIncorrect:
SELECT * FROM events WHERE user_id = '123'Correct:
SELECT *
FROM events
WHERE event_date >= today() - 7 AND user_id = '123'
LIMIT 100
SETTINGS max_execution_time = 30,
max_rows_to_read = 1000000000,
timeout_before_checking_execution_speed = 0Recommended per-query settings:
| Setting | Recommended | Effect |
|---|---|---|
max_rows_to_read | 1e9 | Caps rows scanned before materialization — the real guardrail |
max_bytes_to_read | 1e11 | Caps bytes scanned |
max_execution_time | 30 | Interrupts query when projected execution time exceeds N seconds (see timeout_before_checking_execution_speed) |
timeout_before_checking_execution_speed | 0 | Makes max_execution_time behave as a wall-clock limit (default 10 gives queries 10s of grace before timeouts kick in) |
max_estimated_execution_time | 60 | Rejects queries whose projected runtime exceeds N seconds — kills expensive queries before they start |
max_result_rows | 10000 | Caps output rows |
result_overflow_mode | 'break' | Returns partial result of ≥ max_result_rows, rounded up to the next block boundary (it does not truncate exactly) |
Limits are checked at block boundaries, so actual scans and runtime can overshoot slightly.
Cloud vs self-hosted defaults that matter:
| Setting | Self-hosted default | Cloud default |
|---|---|---|
max_memory_usage | 0 (unlimited) | Depends on replica RAM — not unlimited |
max_bytes_before_external_group_by | 0 (no spill) | Half the memory per replica — spills automatically |
max_bytes_before_external_sort | 0 (no spill) | Half the memory per replica — spills automatically |
max_rows_to_read / max_bytes_to_read | 0 (unlimited) | 0 (unlimited) — must be set explicitly on both |
max_execution_time | 0 (unlimited) | 0 (unlimited) — must be set explicitly on both |
On self-hosted, GROUP BY and ORDER BY have no automatic memory ceiling — set the max_bytes_before_external_* settings explicitly or enforce via profile. On Cloud, GROUP BY / ORDER BY spill to disk automatically and per-query memory is bounded, but scan and execution-time caps are still your job.
When things go wrong:
TIMEOUT_EXCEEDED): Narrow the time range, add sort key filters, run EXPLAIN ESTIMATE to check scan size before retrying. Consider max_estimated_execution_time to reject expensive queries up front.MEMORY_LIMIT_EXCEEDED): Reduce actual memory use — narrow filters, add LIMIT, lower GROUP BY cardinality, enable max_bytes_before_external_group_by (already on by default in Cloud, off on self-hosted), or split into smaller time windows. Raising max_memory_usage only helps if you’re authorized and the ceiling is genuinely the problem; lowering it makes the error happen sooner, not later.TOO_MANY_PARTS): Back off inserts — merges are behind. Wait and retry.Role-level hardening (belt-and-suspenders):
Per-query SETTINGS only applies if the agent remembers to emit it. For production, the primary mechanism should be a settings profile (opens in a new tab) plus readonly=2 (opens in a new tab) on the agent’s role, so limits apply even when the agent forgets. Per-query settings are then defense in depth, not the fence.
Per-query limits also don’t stop abuse via many small queries — use quotas (opens in a new tab) to bound requests or scanned bytes per interval.
Progressive exploration pattern:
Start narrow, widen only if needed:
-- 1. Count first (cheap)
SELECT count() FROM events WHERE event_date = today();
-- 2. Small sample (if count is reasonable)
SELECT * FROM events WHERE event_date = today() LIMIT 10;
-- 3. Full query with LIMIT and scan caps
SELECT user_id, count() as events
FROM events
WHERE event_date = today()
GROUP BY user_id
ORDER BY events DESC
LIMIT 100
SETTINGS max_execution_time = 30,
max_rows_to_read = 1000000000,
timeout_before_checking_execution_speed = 0;Reference: Query complexity restrictions (opens in a new tab) · Query-level settings (opens in a new tab)