ALTER TABLE SET TTL

Sets the time-to-live (TTL) period on a table.

Refer to the section on TTL for a conceptual overview.

Syntax​

Flow chart showing the syntax of the ALTER TABLE keyword

Flow chart showing the syntax of ALTER TABLE with SET TTL keyword

Description​

To store and analyze only recent data, configure a time-to-live (TTL) period on a table using the ALTER TABLE SET TTL command.

Follow the TTL keyword with a number and a time unit, one of:

  • HOURS
  • DAYS
  • WEEKS
  • MONTHS
  • YEARS

TTL units fall into two categories:

  1. Fixed time periods:
    • HOURS
    • DAYS
    • WEEKS
  2. Calendar-based periods:
    • MONTHS
    • YEARS

Fixed-time periods are always exact durations: 1 WEEK is always 7 days.

Calendar-based periods may vary in length: 1 MONTH from January 15th goes to February 15th and could be between 28 and 31 days.

QuestDB accepts both singular and plural forms:

  • HOUR or HOURS
  • DAY or DAYS
  • WEEK or WEEKS
  • MONTH or MONTHS
  • YEAR or YEARS

It also supports shorthand notation: 3h for 3 hours, 2M for 2 months.

note

QuestDB drops data that exceeded its TTL only a whole partition at a time. For this reason, the TTL period must be a whole number multiple of the table's partition size.

For example:

  • If a table is partitioned by DAY, the TTL must be a whole number of days (24 HOURS, 2 DAYS and 3 MONTHS are all accepted)
  • If a table is partitioned by MONTH, the TTL must be in months or years. QuestDB won't accept the HOUR, DAY, or WEEK units

Refer to the section on TTL in Concepts for detailed information on the behavior of this feature.

Examples​

Set the TTL to 3 weeks:

ALTER TABLE weather SET TTL 3 WEEKS;

Set the TTL to 12 hours, using the shorthand syntax for the time unit:

ALTER TABLE weather SET TTL 12h;