/clickhouse-best-practices

Apply Safety Limits to Agent-Generated Queries

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.

Non-negotiable rules:

• ALWAYS use LIMIT to cap returned rows (default LIMIT 1000)
• ALWAYS bound scan size with max_rows_to_read or max_bytes_to_read — LIMIT alone does not prevent a full scan
• ALWAYS set max_execution_time (default 30)
• NEVER run SELECT * on large tables without LIMIT and scan caps
• NEVER query without filtering on sort key or partition key columns

Incorrect:

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 = 0

Recommended per-query settings:

Limits are checked at block boundaries, so actual scans and runtime can overshoot slightly.

Cloud vs self-hosted defaults that matter:

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 (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 error (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 (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 plus readonly=2 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 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 · Query-level settings