Projections and TTL

Two MergeTree-only features that change how data is stored and aged.

Projections

A projection is a materialized alternative layout of a table, stored alongside the main parts. The optimizer can choose to read from a projection when it is cheaper. Source: src/Storages/ProjectionsDescription.cpp, with planner integration in src/Processors/QueryPlan/Optimizations/optimizeUseProjections.cpp.

Two flavors

ALTER TABLE events ADD PROJECTION p_user_event (
    SELECT user_id, event_type, count()
    GROUP BY user_id, event_type
);

ALTER TABLE events ADD PROJECTION p_by_country (
    SELECT *
    ORDER BY country, event_time
);

• Aggregating projection — pre-aggregated rollups on selected dimensions. Cheap for GROUP BY queries that match.
• Normal projection — same data but reordered (different sort key). Cheap for filters on different columns than the table's primary key.

How they live on disk

Each part has a projections/<name>/ subdirectory containing its own primary index, marks, and column files for that projection. Merges and mutations rewrite projections alongside the main data. Projections inherit the part's lifecycle.

Selection by the optimizer

OptimizeUseProjections (in the planner-side optimizer) runs after the regular plan is built. For each SELECT:

1. Enumerate the candidate projections of the source table.
2. Check whether the projection covers the required columns / aggregations.
3. Estimate the cost.
4. If a projection wins, replace the ReadFromMergeTree step with a read from the projection's storage (StorageFromMergeTreeProjection.cpp).

The optimizer is rule-based (force_optimize_projection_name can pin a specific projection for testing).

Caveats

• A projection adds storage cost. Aggregating projections are usually small; normal projections roughly double the data.
• Mutations rewrite all projections too — heavier than regular tables.
• Not every query shape can use a projection. The current matching rules are conservative.

TTL

A TTL is a time-to-live expression that determines what to do with data outside a window. Source: src/Storages/TTLDescription.cpp, executor in src/Storages/MergeTree/MergeTreePartsMover.cpp, integration in MergeTask.cpp.

Where TTL applies

Level	Syntax	What happens
Table-level row TTL	TTL toDateTime(event_time) + INTERVAL 30 DAY	Rows older than the cut-off are dropped during merges.
Column-level TTL	event_payload String TTL toDateTime(event_time) + INTERVAL 90 DAY	After 90 days, the column is reset to its default value (effectively zeroing out the data on disk after the next merge).
Move TTL	TTL toDateTime(event_time) + INTERVAL 7 DAY TO DISK 's3', toDateTime(event_time) + INTERVAL 30 DAY TO VOLUME 'cold'	The part is moved between disks/volumes as it ages.
TTL with aggregation	TTL date + INTERVAL 1 MONTH GROUP BY user_id SET sum_metric = sum(metric)	TTL becomes a roll-up that compacts old rows into aggregated ones.
Recompression	TTL date + INTERVAL 30 DAY RECOMPRESS CODEC(ZSTD(15))	Old data is rewritten with a heavier codec.

Execution

TTL actions fire during merges. MergeTask.cpp checks each part for TTL eligibility:

• Drop TTL rewrites the merged part without the expired rows.
• Move TTL detaches the merged part and re-attaches it on the destination disk via MergeTreePartsMover.cpp.
• Recompress TTL rewrites with the new codec.
• Aggregate TTL produces aggregate output as part of the merge.

TTL evaluations are cached per part in MergeTreeDataPartTTLInfo.cpp so that selecting candidates is cheap.

Configuration

• merge_with_ttl_timeout — how often to check for TTL-eligible merges (default 4 hours).
• merge_with_recompression_ttl_timeout — same for recompression TTL.
• ttl_only_drop_parts — drop entire parts when all rows expire (avoids per-row rewrites).
• materialize_ttl_after_modify — backfill TTLs after ALTER TABLE ... MODIFY TTL.

Diagnostics

• system.parts.move_ttl_info_min, system.parts.move_ttl_info_max.
• system.merges includes TTL-driven merges.
• system.part_log records each TTL action.

When to use which

• Projection — speed up specific query shapes at the cost of more disk and merge work.
• Skip index — narrow a granule range during reads (cheaper than a projection but less powerful).
• TTL drop — bound retention.
• TTL move — tier hot vs cold storage.
• TTL recompress — reduce footprint of cold data.
• TTL aggregate — historical roll-up while keeping recent rows fine-grained.

Related pages

• MergeTree
• Materialized views — alternative for some projection use cases.
• IO and disks — storage policies that TTL TO DISK / TO VOLUME operates on.