ClickHouse DB::Exception: Quota exceeded

The "DB::Exception: Quota exceeded" error in ClickHouse means that a user has consumed more resources than their assigned quota allows within a given time interval. The QUOTA_EXCEEDED error code is ClickHouse's way of enforcing resource usage limits that administrators configure to prevent any single user or application from monopolizing server capacity.

Impact

Once a quota is exceeded, all subsequent queries from the affected user are rejected until the quota interval resets. This can halt data ingestion, block dashboards from loading, and disrupt any automated process tied to that user account. The severity depends on the quota interval length -- a short interval resets quickly, while a daily quota means the user is locked out for the remainder of the day.

Common Causes

1. The user ran more queries than the configured max queries limit within the interval
2. Queries read more rows or bytes than the max read_rows or max read_bytes quota allows
3. Heavy ad-hoc query usage by analysts exhausting shared quotas
4. An application bug causing excessive query retries or loops
5. Quota limits set too low for the user's actual workload requirements
6. Multiple applications sharing the same ClickHouse user account, collectively exceeding the quota

Troubleshooting and Resolution Steps

1. Check current quota usage:

   SELECT * FROM system.quota_usage WHERE quota_name = 'my_quota';
   

   This shows how much of each limit has been consumed.

2. View the quota definition:

   SHOW CREATE QUOTA my_quota;
   

   Identify which specific limit was exceeded (queries, rows, bytes, etc.).

3. Wait for the interval to reset:

   • If the quota resets hourly, the user will regain access at the start of the next hour.
   • Check the end_of_interval column in system.quota_usage to see when the reset occurs.

4. Increase the quota limits if they are too restrictive:

   ALTER QUOTA my_quota
     FOR INTERVAL 1 hour MAX queries = 5000, read_rows = 10000000;
   

5. Investigate excessive usage:

   • Query system.query_log to find which queries consumed the most resources:

     SELECT query, read_rows, read_bytes, elapsed
     FROM system.query_log
     WHERE user = 'my_user'
       AND event_date = today()
     ORDER BY read_rows DESC
     LIMIT 20;
     

6. Distribute load across separate user accounts:

   • If multiple applications share one account, create dedicated users with individual quotas for each.

7. Optimize expensive queries:

   • Reduce the data scanned by adding filters, using materialized views, or leveraging projections.

Best Practices

• Set quotas based on observed usage patterns rather than arbitrary limits, then tighten gradually.
• Use separate ClickHouse user accounts for different applications and teams so quotas are tracked independently.
• Monitor system.quota_usage proactively to catch users approaching their limits before they hit them.
• Implement multiple quota intervals (e.g., per-hour and per-day) to allow bursts while capping overall consumption.
• Communicate quota limits to users and provide guidance on optimizing their queries.
• Consider using ClickHouse's system.query_log to build alerts for users approaching quota thresholds.

Frequently Asked Questions

Q: How do I find out which specific quota limit I exceeded?
A: Query system.quota_usage and compare the usage columns against the limits. The column where usage meets or exceeds the limit is the one that triggered the error.

Q: Can I reset a quota manually without waiting for the interval?
A: There is no direct command to reset quota usage. However, you can temporarily increase the limit, or drop and recreate the quota with a fresh interval.

Q: Does the quota reset at a fixed clock time or relative to the first query?
A: By default, ClickHouse uses calendar-aligned intervals. For example, an hourly quota resets at the top of each hour, not one hour after the first query.

Q: Can I set different quotas for different client applications using the same user?
A: Yes, quotas can be keyed by client attributes such as quota_key set in the session. This allows different quota tracking for different applications sharing the same user account.