Materialize Documentation
Join the Community github/materialize


The time to live (TTL) pattern helps to remove rows in views, queries, tails, or sinks using expiration times, keeping rows until they are no longer useful, and enabling new use cases.

The following examples are possible TTL use cases:

Before continuing, make sure to understand how temporal filters work.


The pattern uses a temporal filter over a row’s creation timestamp plus a TTL. The sum represents the row’s expiration time. After reaching the expiration time, the query will drop the row.

Pattern example:

  SELECT (created_ts + ttl) as expiration_time
  FROM events
  WHERE mz_logical_timestamp() < (created_ts + ttl);

To know the remaining time to live:

  SELECT (expiration_time - mz_logical_timestamp()) AS remaining_ttl


For a real-world example of the pattern, let’s build a task tracking system. It will consist on a view filtering rows from a table. Each row in the table contains a name, creation timestamp, and TTL of a task.

  1. First, we need to set up the table:

      CREATE TABLE tasks (name TEXT, created_ts TIMESTAMP, ttl INTERVAL);
  2. Add some tasks to track:

      INSERT INTO tasks VALUES ('send_email', now(), INTERVAL '5 minutes');
      INSERT INTO tasks VALUES ('time_to_eat', now(), INTERVAL '1 hour');
      INSERT INTO tasks VALUES ('security_block', now(), INTERVAL '1 day');
  3. Create a view using a temporal filter over the expiration time. For our example, the expiration time represents the sum between the task’s created_ts and its ttl.

      CREATE MATERIALIZED VIEW tracking_tasks AS
        extract(epoch from (created_ts + ttl)) * 1000 as expiration_time
      FROM tasks
      WHERE mz_logical_timestamp() < extract(epoch from (created_ts + ttl)) * 1000;

    The filter clause will discard any row with an expiration time less than or equal to mz_logical_timestamp().

  4. That’s it! Use it in the way that best fits your use case.

Usage examples