---
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 datasets that exceed available memory resources.

## What causes this error

You 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 wide tables with many columns
* Using functions that require significant memory

{% callout type="tip" %}
Memory limit errors often indicate that your query needs optimization instead of simply 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 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