MEMORY_LIMIT_EXCEEDED ClickHouse error¶

This error occurs when a query requires more memory than the configured limit. It's common with large aggregations, complex JOINs, or insufficient memory settings.

The MEMORY_LIMIT_EXCEEDED error in ClickHouse (and Tinybird) happens when a query execution requires more memory than the configured memory limit. This typically occurs with large aggregations, complex JOINs, sorting operations, or when processing very large datasets that exceed available memory resources.

What causes this error¶

You'll typically see it when:

Large GROUP BY operations with many distinct values
Complex JOINs between large tables
Sorting large result sets
Aggregating data without proper filtering
Insufficient memory limits for the operation
Processing very wide tables with many columns
Using functions that require significant memory

Memory limit errors often indicate that your query needs optimization rather than just increasing memory limits.

Example errors¶

Fails: large GROUP BY without filtering

SELECT
    user_id,
    event_type,
    COUNT(*) as event_count,
    AVG(value) as avg_value
FROM events
GROUP BY user_id, event_type
-- Error: Memory limit exceeded

Fails: complex JOIN without optimization

SELECT
    u.id,
    u.name,
    COUNT(e.id) as event_count
FROM users u
JOIN events e ON u.id = e.user_id
JOIN sessions s ON u.id = s.user_id
GROUP BY u.id, u.name
-- Error: Memory limit exceeded

Fails: large ORDER BY operation

SELECT * FROM events
WHERE timestamp >= '2024-01-01'
ORDER BY user_id, timestamp
-- Error: Memory limit exceeded

Fails: wide table aggregation

SELECT
    user_id,
    COUNT(*) as total_events,
    COUNT(DISTINCT event_type) as unique_events,
    AVG(value1) as avg_value1,
    AVG(value2) as avg_value2,
    AVG(value3) as avg_value3
FROM wide_table
GROUP BY user_id
-- Error: Memory limit exceeded

How to fix it¶

Optimize query structure¶

Break down complex queries into smaller parts:

Fix: use CTEs for complex queries

WITH user_events AS (
    SELECT user_id, COUNT(*) as event_count
    FROM events
    WHERE timestamp >= '2024-01-01'
    GROUP BY user_id
    HAVING event_count > 100
)
SELECT
    u.id,
    u.name,
    ue.event_count
FROM users u
JOIN user_events ue ON u.id = ue.user_id

Add proper filtering¶

Use WHERE clauses to limit data early:

Fix: add time filtering

SELECT
    user_id,
    event_type,
    COUNT(*) as event_count
FROM events
WHERE timestamp >= '2024-01-01'
  AND timestamp < '2024-02-01'
GROUP BY user_id, event_type

Use streaming aggregation¶

Enable external aggregation for large operations:

Fix: use external aggregation

SELECT
    user_id,
    COUNT(*) as event_count
FROM events
WHERE timestamp >= '2024-01-01'
GROUP BY user_id
SETTINGS max_bytes_before_external_group_by = 1000000000

Limit result sets¶

Use LIMIT clauses to prevent large results:

Fix: add LIMIT clause

SELECT
    user_id,
    COUNT(*) as event_count
FROM events
WHERE timestamp >= '2024-01-01'
GROUP BY user_id
ORDER BY event_count DESC
LIMIT 1000

Common patterns and solutions¶

Large GROUP BY optimization¶

Optimize aggregations with pre-filtering:

Optimized GROUP BY

-- Step 1: Filter and pre-aggregate
WITH filtered_events AS (
    SELECT user_id, event_type, COUNT(*) as count
    FROM events
    WHERE timestamp >= '2024-01-01'
    GROUP BY user_id, event_type
)
-- Step 2: Final aggregation
SELECT
    user_id,
    SUM(count) as total_events,
    COUNT(DISTINCT event_type) as unique_events
FROM filtered_events
GROUP BY user_id

Complex JOIN optimization¶

Break down complex JOINs:

Optimized JOIN approach

-- Step 1: Get filtered base data
WITH base_events AS (
    SELECT user_id, event_type, timestamp
    FROM events
    WHERE timestamp >= '2024-01-01'
),
user_summary AS (
    SELECT user_id, COUNT(*) as event_count
    FROM base_events
    GROUP BY user_id
    HAVING event_count > 100
)
-- Step 2: Join with user data
SELECT
    u.id,
    u.name,
    us.event_count
FROM users u
JOIN user_summary us ON u.id = us.user_id

Memory-efficient sorting¶

Use LIMIT with ORDER BY:

Memory-efficient sorting

-- Instead of sorting all results
SELECT * FROM events ORDER BY timestamp DESC

-- Sort only what you need
SELECT * FROM events
WHERE timestamp >= '2024-01-01'
ORDER BY timestamp DESC
LIMIT 1000

Tinybird-specific notes¶

In Tinybird, MEMORY_LIMIT_EXCEEDED errors often occur when:

Creating complex Materialized Views
Processing large amounts of data in Pipes
Running queries against very large datasets
Using complex transformations in API endpoints

To debug in Tinybird:

Use the Query Builder to test query performance
Check the query execution plan
Monitor resource usage in the UI
Consider breaking complex operations into smaller Pipes

Use Tinybird's query profiling to identify memory bottlenecks before they cause errors.

Best practices¶

Query design¶

Start with simple queries and add complexity gradually
Use appropriate WHERE clauses to filter data early
Avoid SELECT * on large tables
Use LIMIT clauses for exploratory queries

Memory optimization¶

Set appropriate memory limits for different query types
Use external aggregation for large GROUP BY operations
Implement data partitioning strategies
Monitor memory usage patterns

Resource management¶

Monitor system memory usage
Use connection pooling for multiple queries
Implement proper error handling for memory errors
Consider using streaming queries for large datasets

Configuration options¶

Memory settings¶

Check current memory settings

SELECT
    name,
    value,
    description
FROM system.settings
WHERE name LIKE '%memory%' OR name LIKE '%external%'

Adjusting memory limits¶

Set memory limits

SET max_memory_usage = 8000000000; -- 8GB
SET max_bytes_before_external_group_by = 1000000000; -- 1GB
SET max_bytes_before_external_sort = 1000000000; -- 1GB

External aggregation settings¶

Enable external aggregation

SET max_bytes_before_external_group_by = 1000000000;
SET max_bytes_before_external_sort = 1000000000;
SET group_by_overflow_mode = 'any';

Alternative solutions¶

Use approximate functions¶

Approximate aggregation

-- Instead of exact COUNT(DISTINCT)
SELECT
    user_id,
    COUNT(*) as total_events,
    uniqHLL12(event_type) as approximate_unique_events
FROM events
WHERE timestamp >= '2024-01-01'
GROUP BY user_id

Sampling approach¶

Data sampling

SELECT
    user_id,
    COUNT(*) * 10 as estimated_event_count
FROM events
WHERE timestamp >= '2024-01-01'
  AND cityHash64(user_id) % 10 = 0
GROUP BY user_id

Incremental processing¶

Incremental aggregation

-- Process data in smaller chunks
SELECT
    user_id,
    COUNT(*) as event_count
FROM events
WHERE timestamp >= '2024-01-01'
  AND timestamp < '2024-01-02'
GROUP BY user_id

UNION ALL

SELECT
    user_id,
    COUNT(*) as event_count
FROM events
WHERE timestamp >= '2024-01-02'
  AND timestamp < '2024-01-03'
GROUP BY user_id