query_rows_limit_exceeded

{  "meta": {    "requestId": "req_4dgzrNP3Je5mU1tD"  },  "error": {    "detail": "Query exceeded the maximum rows to scan limit of 100 million",    "status": 422,    "title": "Unprocessable Entity",    "type": "https://unkey.com/docs/errors/user/unprocessable_entity/query_rows_limit_exceeded"  }}

What Happened?#

Your query tried to scan more than 100 million rows! We limit the number of rows that can be scanned to keep queries fast and prevent resource exhaustion.

This happens when you query large time ranges or don't filter your data enough, causing ClickHouse to scan millions of rows even if the final result is small.

How to Fix It#

1. Add Time Range Filters#

Always filter by time to limit the number of rows scanned:

SELECT COUNT(*)FROM key_verifications_v1WHERE outcome = 'VALID'

2. Use More Selective Filters#

Add filters that reduce the data before aggregation:

SELECT api_id, COUNT(*) as totalFROM key_verifications_v1WHERE time >= now() - INTERVAL 90 DAYGROUP BY api_id

3. Use Pre-Aggregated Tables#

For historical queries, use aggregated tables that have fewer rows:

SELECT  toStartOfDay(time) as day,  COUNT(*) as totalFROM key_verifications_v1WHERE time >= now() - INTERVAL 90 DAYGROUP BY day

4. Query in Smaller Batches#

Instead of one large query, break it into smaller time windows:

// Instead of querying 90 days at onceconst results = [];for (let i = 0; i < 90; i += 7) {  const start = `now() - INTERVAL ${i + 7} DAY`;  const end = `now() - INTERVAL ${i} DAY`;  const result = await query(`    SELECT COUNT(*) as total    FROM key_verifications_v1    WHERE time >= ${start} AND time < ${end}  `);  results.push(result);}

Need Higher Row Limits?#