---
title: MEMORY_LIMIT_EXCEEDED ClickHouse error
meta:
  description: Learn how to fix the MEMORY_LIMIT_EXCEEDED error in ClickHouse and Tinybird. Understand what causes it and see examples of how to resolve it
---

# MEMORY_LIMIT_EXCEEDED ClickHouse error

{% callout %}
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.
{% /callout %}

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

{% callout type="tip" %}
Memory limit errors often indicate that your query needs optimization rather than just increasing memory limits.
{% /callout %}

## Example errors

```sql {% title="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
```

```sql {% title="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
```

```sql {% title="Fails: large ORDER BY operation" %}
SELECT * FROM events
WHERE timestamp >= '2024-01-01'
ORDER BY user_id, timestamp
-- Error: Memory limit exceeded
```

```sql {% title="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:

```sql {% title="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:

```sql {% title="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:

```sql {% title="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:

```sql {% title="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:

```sql {% title="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:

```sql {% title="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:

```sql {% title="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:

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

{% callout type="tip" %}
Use Tinybird's query profiling to identify memory bottlenecks before they cause errors.
{% /callout %}

## 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

```sql {% title="Check current memory settings" %}
SELECT
    name,
    value,
    description
FROM system.settings
WHERE name LIKE '%memory%' OR name LIKE '%external%'
```

### Adjusting memory limits

```sql {% title="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

```sql {% title="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

```sql {% title="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

```sql {% title="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

```sql {% title="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
```

## See also

* [Query Optimization](/forward/copy-export-data)
* [Materialized Views](/forward/core-concepts/materialized-views) - Optimize query performance
* [Copy Pipes](/forward/core-concepts/copy-pipes) - Pre-aggregate data