ClickHouse DB::Exception: Query is too large

The "DB::Exception: Query is too large" error in ClickHouse means that the SQL text of a query exceeds the configured max_query_size limit. The QUERY_IS_TOO_LARGE error code is raised during query parsing, before any execution begins, when the raw text of the SQL statement is larger than the allowed maximum.

Impact

The query is rejected at parse time and never executed. No data is read, written, or modified. This primarily affects queries with very large IN lists, extensive UNION ALL chains, deeply nested subqueries, or queries with large embedded literals. The error is a protective measure to prevent excessive memory usage during parsing.

Common Causes

1. A query contains a very large IN (...) list with thousands or millions of values
2. The SQL statement includes a large number of UNION ALL clauses generated programmatically
3. Application code dynamically generates queries with embedded large data payloads instead of using parameterized inserts
4. A migration or ETL tool generates a single monolithic SQL statement that exceeds the limit
5. The max_query_size setting is set to a low value in the user profile or server configuration
6. Copy-pasting or generating CREATE TABLE statements with extremely long default expressions or comments

Troubleshooting and Resolution Steps

1. Check the current max_query_size setting:

   SELECT name, value
   FROM system.settings
   WHERE name = 'max_query_size';
   

   The default is 262144 bytes (256 KB).

2. If the query legitimately needs to be large, increase max_query_size for the session:

   SET max_query_size = 10485760;  -- 10 MB
   SELECT ...;
   

3. For large IN lists, replace the inline list with a subquery or a temporary table:

   -- Instead of: SELECT * FROM table WHERE id IN (1, 2, 3, ..., 1000000)
   -- Use a temporary table:
   CREATE TEMPORARY TABLE tmp_ids (id UInt64);
   INSERT INTO tmp_ids VALUES (1), (2), (3);  -- or bulk insert
   SELECT * FROM table WHERE id IN (SELECT id FROM tmp_ids);
   

4. For large IN lists, you can also read the values from an external file instead of embedding them in the SQL:

   SELECT * FROM table
   WHERE id IN (SELECT id FROM file('ids.csv', CSV, 'id UInt64'));
   

5. Refactor queries with many UNION ALL clauses into multiple separate queries or use the merge() table function:

   -- Instead of many UNION ALL statements
   SELECT * FROM merge('database', 'table_pattern_.*');
   

6. For programmatically generated queries, switch to parameterized queries or batch processing:

   -- Use query parameters instead of embedding values
   SELECT * FROM table WHERE id = {id:UInt64};
   

7. To set a permanent higher limit for a specific user profile:

   ALTER SETTINGS PROFILE 'etl_profile' MODIFY SETTINGS max_query_size = 10485760;
   

Best Practices

• Avoid generating SQL with large inline value lists; use temporary tables, external tables, or parameterized queries instead.
• Set max_query_size appropriately per user profile rather than raising it globally to very high values.
• Use ClickHouse's INSERT ... SELECT pattern to move data between tables instead of generating massive INSERT statements.
• When building queries programmatically, implement query size checks in your application before sending to ClickHouse.
• Break large batch operations into smaller, manageable chunks.
• Use the VALUES format with streaming inserts rather than embedding all data in the SQL text.

Frequently Asked Questions

Q: What is the default max_query_size?
A: The default is 262144 bytes (256 KB). This is sufficient for most normal queries but may need to be increased for queries with large IN lists or complex generated SQL.

Q: Does max_query_size limit the amount of data I can INSERT?
A: No. max_query_size only limits the size of the SQL text itself, not the data payload. When using INSERT ... FORMAT, the data portion is streamed separately and is not subject to this limit. The limit applies to the SQL part of the statement only.

Q: Is there a risk in setting max_query_size very high?
A: Setting it very high increases the memory the parser may consume for a single query. This could become a problem if many concurrent users send very large queries. Set it to the minimum size needed for your workload.