documentation/query/sql/alter-mat-view-set-ttl.md
Sets the time-to-live (TTL) period on a materialized view, automatically dropping partitions older than the specified duration.
ALTER MATERIALIZED VIEW viewName SET TTL n timeUnit
Where timeUnit is: HOURS | DAYS | WEEKS | MONTHS | YEARS (or shorthand:
h, d, w, M, y)
| Parameter | Description |
|---|---|
viewName | Name of the materialized view to modify |
n | Number of time units to retain |
timeUnit | Time unit for the retention period |
Set a TTL when:
QuestDB automatically drops partitions that exceed the TTL. Data removal happens at partition boundaries, not row-by-row.
:::note
The TTL period must be a whole number multiple of the view's partition size.
For example, a view with PARTITION BY DAY can have TTL 7 DAYS but not
TTL 36 HOURS.
:::
| Unit | Singular | Plural | Shorthand |
|---|---|---|---|
| Hours | HOUR | HOURS | h |
| Days | DAY | DAYS | d |
| Weeks | WEEK | WEEKS | w |
| Months | MONTH | MONTHS | M |
| Years | YEAR | YEARS | y |
ALTER MATERIALIZED VIEW trades_hourly SET TTL 3 DAYS;
ALTER MATERIALIZED VIEW trades_hourly SET TTL 12h;
ALTER MATERIALIZED VIEW trades_daily SET TTL 1 YEAR;
| Aspect | Description |
|---|---|
| Granularity | Data dropped at partition boundaries only |
| Independence | View TTL is separate from base table TTL |
| Immediate effect | Expired partitions dropped on next maintenance cycle |
Changing TTL requires the ALTER MATERIALIZED VIEW permission:
GRANT ALTER MATERIALIZED VIEW ON trades_hourly TO user1;
| Error | Cause |
|---|---|
materialized view does not exist | View with specified name doesn't exist |
invalid TTL | TTL not a multiple of partition size |
invalid time unit | Unrecognized time unit |
permission denied | Missing ALTER MATERIALIZED VIEW permission (Enterprise) |