ClickHouse MaterializedView TTL Operations Failing

A ClickHouse MaterializedView TTL failure happens when an ALTER TABLE ... MODIFY TTL statement targets a materialized view instead of the table underneath it. ClickHouse rejects it with Code: 36. DB::Exception: Engine MaterializedView doesn't support TTL clause. The ALTER fails, but nothing else does, so retention silently stops applying while the view keeps serving queries normally.

What does the ClickHouse MaterializedView TTL error look like?

The error surfaces in clickhouse-server logs from the TCP handler, once per attempt. Retries produce identical bursts, which is why it often appears as a repeating pattern rather than a single event:

<Error> TCPHandler: Code: 36. DB::Exception: Engine MaterializedView
  doesn't support TTL clause. (BAD_ARGUMENTS), Stack trace (when copying
  this message, always include the lines below):
  

The statement that triggers it looks routine:

ALTER TABLE events_mv MODIFY TTL event_date + INTERVAL 30 DAY;
  

Two things make this hard to catch. The error appears only in the server log, not in application logs, and the client that issued the ALTER may have already exited. And SELECT against events_mv continues returning rows, so every health check stays green.

Why does ClickHouse reject TTL on a MaterializedView?

A ClickHouse materialized view is not a stored result set. It is an insert trigger. When rows land in the source table, the view’s SELECT runs against that block and the output is written somewhere else. The view itself holds no data parts.

TTL in ClickHouse is a property of data parts. It tells the merge scheduler when parts become eligible for deletion or movement. A view with no parts has nothing to expire, so the engine refuses the clause outright rather than accepting a no-op.

Where the data actually lives depends on how the view was created:

1. Created with a TO clause. Rows are written into an explicit table you already own. That table is the TTL target.
2. Created without a TO clause. ClickHouse creates an implicit inner table, named .inner_id.<uuid> on modern versions and .inner.<view_name> on older ones. That hidden table is the TTL target.

The second case is where most of these incidents originate. The storage exists, but it is not named in any migration file, so the ALTER gets pointed at the only object anyone can see.

What breaks when this fails silently?

Nothing, at first. That is the problem.

The rejected ALTER leaves the target table with no retention policy at all, or with a stale one from an earlier migration. Parts accumulate indefinitely:

• Unbounded disk growth on the ClickHouse volume, which on Kubernetes means a PVC filling toward its limit with no alert attached to it
• Query slowdown as scans cover partitions that should have been dropped
• Compliance exposure where the TTL existed to enforce a data-retention commitment
• Merge pressure from a growing part count, which raises background CPU and IO

By the time any of these become visible, the ALTER that failed is weeks or months in the past and no longer connected to the symptom in anyone’s mind.

How do I fix a ClickHouse MaterializedView TTL error?

1. Find where the view actually writes

SELECT name, engine, uuid, create_table_query
  FROM system.tables
  WHERE name = 'events_mv' AND database = currentDatabase()
  FORMAT Vertical;
  

If create_table_query contains TO some_table, that table is your target. If it does not, the target is the implicit inner table keyed to the view’s UUID.

2. Locate the implicit inner table, if there is one

SELECT database, name, total_bytes
  FROM system.tables
  WHERE name LIKE '.inner%'
  ORDER BY total_bytes DESC;
  

3. Apply the TTL to the target table

-- explicit target
  ALTER TABLE events_store MODIFY TTL event_date + INTERVAL 30 DAY;
  
  -- implicit inner table (quote the name, it is not a valid bare identifier)
  ALTER TABLE `.inner_id.a1b2c3d4-0000-0000-0000-000000000000`
    MODIFY TTL event_date + INTERVAL 30 DAY;
  

The TTL expression must reference a column that exists in the target table, not in the view’s SELECT alias space. This is a second common failure: the ALTER is aimed correctly but names a column the target does not have.

4. Confirm the TTL registered

SELECT name, engine, create_table_query
  FROM system.tables
  WHERE name = 'events_store' FORMAT Vertical;
  

The TTL clause should now appear in create_table_query. If it does not, the ALTER did not apply, regardless of whether the client reported success.

5. Check whether expired parts are actually being dropped

SELECT partition, min_date, max_date, rows, bytes_on_disk
  FROM system.parts
  WHERE table = 'events_store' AND active
  ORDER BY min_date ASC LIMIT 20;
  

Parts older than the TTL window mean the policy registered but merges have not run. Force the pass with OPTIMIZE TABLE events_store FINAL, then investigate merge_with_ttl_timeout if the backlog persists.

6. Prevent the recurrence

Put the TTL in the target table’s CREATE TABLE rather than a follow-up ALTER, and treat the materialized view as pure transformation logic. Migrations that pair a view with an explicit TO table remove the ambiguity permanently.

How does Dstl8 detect this?

Nobody writes an alert for BAD_ARGUMENTS on a TCP handler. It is not a crash, not a latency spike, and not a threshold anyone would think to define in advance. Dstl8 baselines each service against its own normal, so a repeating error pattern that was not present last week surfaces as an incident on its own, with the exact log pattern and burst count that produced it.

The incident names the failing engine, cites the pattern it was built from, and links the affected host and service in one view. No threshold was set and no rule was written. The pattern simply was not there before, and then it was.

Frequently asked questions

Related patterns

References

• ClickHouse docs: TTL for columns and tables
• ClickHouse docs: CREATE MATERIALIZED VIEW
• ClickHouse docs: system.parts
• ClickHouse error codes: BAD_ARGUMENTS (36)