ClickHouse DB::Exception: Too many materialized views

The "DB::Exception: Too many materialized views" error in ClickHouse occurs when the number of materialized views attached to a source table exceeds the configured limit. The TOO_MANY_MATERIALIZED_VIEWS error code prevents creating additional materialized views that would further increase the insert overhead and resource consumption for the source table.

Impact

New materialized view creation is blocked for the affected source table. Existing materialized views continue to function normally, and data inserts into the source table still trigger all existing views. However, the inability to add new views can block development of new features or analytics pipelines that depend on materialized view creation.

Common Causes

1. Gradually accumulating materialized views on a single high-traffic source table over time
2. Design patterns where many downstream aggregations or transformations all depend on the same source table
3. The max_materialized_views_count_for_table limit is set too low for the use case
4. Orphaned or unused materialized views that were never cleaned up
5. Automated systems that create materialized views programmatically without tracking total count

Troubleshooting and Resolution Steps

1. List materialized views attached to the source table:

   SELECT name, as_select, engine
   FROM system.tables
   WHERE engine = 'MaterializedView'
   AND create_table_query LIKE '%FROM my_source_table%';
   

2. Check the configured limit:

   SELECT name, value FROM system.settings
   WHERE name = 'max_materialized_views_count_for_table';
   

3. Identify unused or redundant materialized views. Check which views have not been read recently:

   -- Check if destination tables of views are being queried
   SELECT name, total_rows, total_bytes,
          metadata_modification_time
   FROM system.tables
   WHERE database = 'my_db'
   AND name LIKE '%_mv%'
   ORDER BY metadata_modification_time;
   

4. Drop unused materialized views:

   DROP VIEW IF EXISTS my_db.unused_materialized_view;
   

5. Increase the limit if all existing views are actively needed:

   -- Set at server level in config or per-session
   SET max_materialized_views_count_for_table = 50;
   

6. Consolidate views by combining multiple similar transformations into a single materialized view:

   -- Instead of separate views for each metric:
   -- CREATE MATERIALIZED VIEW mv_clicks ...
   -- CREATE MATERIALIZED VIEW mv_impressions ...
   -- CREATE MATERIALIZED VIEW mv_conversions ...
   
   -- Combine into one:
   CREATE MATERIALIZED VIEW mv_all_metrics
   ENGINE = SummingMergeTree() ORDER BY (date, event_type)
   AS SELECT date, event_type, count() as cnt, sum(value) as total
   FROM source_table GROUP BY date, event_type;
   

7. Use a cascading architecture with an intermediate table to distribute views:

   -- Create an intermediate table
   CREATE TABLE intermediate ENGINE = Null AS SELECT * FROM source_table WHERE 1=0;
   
   -- Attach a view that pipes data to the intermediate table
   CREATE MATERIALIZED VIEW source_to_intermediate TO intermediate
   AS SELECT * FROM source_table;
   
   -- Attach additional views to the intermediate table instead
   CREATE MATERIALIZED VIEW mv_from_intermediate ...
   AS SELECT ... FROM intermediate;
   

Best Practices

• Regularly audit materialized views and remove those that are no longer needed.
• Consolidate multiple similar views into fewer, more general views when possible.
• Use a cascading or fan-out architecture to distribute views across intermediate tables rather than attaching everything to one source.
• Document the purpose of each materialized view to make cleanup decisions easier.
• Monitor the insert performance of source tables, as each materialized view adds overhead to every insert.
• Set max_materialized_views_count_for_table as a safety measure to catch uncontrolled view proliferation.

Frequently Asked Questions

Q: Does each materialized view slow down inserts into the source table?
A: Yes. Every materialized view executes its query on each batch of inserted data. More views mean more processing per insert. This is one reason why the limit exists -- too many views can significantly degrade insert performance.

Q: Can I have materialized views on materialized views?
A: Yes, by creating a materialized view that reads from the destination table of another materialized view. This cascading pattern can help distribute the load and keep per-table view counts manageable.

Q: What happens to existing data when I drop a materialized view?
A: The materialized view's destination table (and its data) is dropped along with the view unless the view was created with the TO clause, in which case the target table remains. New data from the source will no longer flow to the dropped view.

Q: Is there a performance difference between many small views and one combined view?
A: Generally, fewer views with combined logic perform better because each view incurs fixed overhead per insert batch. However, very complex single views can also be problematic. The right balance depends on your specific workload.