Performance issues

Common issues and solutions for performance problems in ClickHouse and Tinybird.

Overview

This section covers troubleshooting for performance-related issues that don't necessarily cause errors but impact query speed and resource usage.

Performance categories

Slow queries

Common causes of slow query performance:

  • Missing indexes - Queries scanning entire tables
  • Large result sets - Queries returning too much data
  • Inefficient WHERE clauses - Non-selective filters
  • Complex aggregations - Heavy operations on large datasets

View slow queries troubleshooting →

Too many rows or bytes

Issues with query result limits:

  • Large result sets - Queries exceeding memory limits
  • Missing WHERE clauses - Queries without proper filters
  • Memory management - Streaming and chunked processing
  • Configuration limits - Adjusting query limits

View too many rows or bytes troubleshooting →

Merge aggregations

Performance issues with merge operations:

  • Inefficient merge operations - Large dataset merging
  • Memory pressure - Merge operations consuming too much memory
  • Pre-aggregation strategies - Optimizing merge performance
  • Materialized views - Using views for common aggregations

View merge aggregations troubleshooting →

Joins

Performance issues with join operations:

  • Large table joins - Joining tables without optimization
  • Missing join conditions - Cartesian products
  • Join order optimization - Efficient join ordering
  • Memory pressure - Joins consuming too much memory

View join performance troubleshooting →

Nullable columns

Performance impact of nullable columns:

  • Performance impact - Nullable columns being slower
  • Index considerations - Nullable columns in indexes
  • Memory overhead - Storage considerations
  • Storage optimization - Handling nullable data efficiently

View nullable columns troubleshooting →

Common patterns

Query optimization

General strategies for improving performance:

  1. Use indexes - Add indexes for frequently filtered columns
  2. Limit results - Always use LIMIT for large datasets
  3. Pre-aggregate - Do heavy aggregations in data sources
  4. Use selective filters - Filter early and often
  5. Monitor query plans - Use EXPLAIN to understand execution

Memory management

Strategies for managing memory usage:

  1. Use streaming - For large datasets, use streaming queries
  2. Process in chunks - Break down large operations
  3. Monitor memory usage - Track memory consumption
  4. Optimize data types - Use appropriate column types
  5. Use materialized views - For frequently accessed data

Best practices

  1. Profile queries - Use EXPLAIN and monitoring tools
  2. Test with realistic data - Use production-like datasets
  3. Monitor resource usage - Track CPU, memory, and I/O
  4. Optimize incrementally - Make small improvements
  5. Document performance patterns - Keep track of what works