Time-To-Live

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:

• Trigger schedule tasks, like emails or messages
• Store temporal offers for an e-commerce
• Maintain time-sensitive security blockers
• Saving-up memory

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

Pattern

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:

  CREATE VIEW TTL_VIEW
  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
  FROM TTL_VIEW;

Example

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
     SELECT
       name,
       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

• Run a query to know the time to live for a row:

    SELECT expiration_time - mz_logical_timestamp() AS remaining_ttl
    FROM tracking_tasks
    WHERE name = 'time_to_eat';
  

• Check if a particular row is still available:

  SELECT true
  FROM tracking_tasks
  WHERE name = 'security_block';
  

• Trigger an external process when a row expires:

    INSERT INTO tasks VALUES ('send_email', now(), INTERVAL '5 seconds');
    COPY( TAIL tracking_tasks WITH (SNAPSHOT = false) ) TO STDOUT;
  
  

  mz_timestamp | mz_diff | name       | expiration_time   |
  -------------|---------|------------|-----------------|
  ...          | -1      | send_email | ...             | <-- Time to send the email!