Time-To-Live (TTL): Manage Documents with Couchbase N1QL

There is one feature that I wish had existed with RDBMS, and that is the built-in support for setting a Time-To-Live value for the data records. In NoSQL databases, the support of TTL is very common. NoSQL databases were designed to handle certain types of data that are often not found in the traditional use cases for RDBMS. These include the management of logs and events, where not only because of the unstructured nature of the data but also its sheer volume which often can be difficult and expensive to be managed in RDBMS. It is this type of data that makes the Time-to-Live (TTL) a popular feature in NoSQL databases.

But a quick googling of TTL with traditional RDBMS returns over 1MM hits, which indicates that there are numerous use cases where developers would wish for this type of support in RDBMS. These are some of the use cases that I have seen:

Applications that generate a high volume of temporary data for the purpose of calculation. For instance, an Incentive Compensation system that allows plan admin to design and perform what-if analysis to see the plan effectiveness. Then when the plans are deployed, the temporary data for calculations would also need to be kept for validation/user inquiry purposes.
Management information systems often have the need to pre-generate aggregated data from multi geographical sources, which often involves complex currency conversion rules. The requirement for these aggregated data is often needed only for the current accounting reporting period.
Systems that include an auto-archiving process to remove old data once the data have been moved to a different storage medium.

All of the above mentioned use cases would also include the additional steps of the actual delete of the data. But in many cases, the system would want to decouple the process to avoid the potentially lengthy process of deleting data in a transactional-based RDBMS.

Here the Time-to-Live (TTL) feature would allow the application to simply set the data records with the expiry time, and defer the actual DELETE to a different database process.

For Couchbase, the support of TTL has always been available with Couchbase Key-Value Operations. But from Couchbase 6.5.1, this feature is now available with N1QL DMLs which allows users to query and set the expiry value directly in the N1QL statements.

N1QL TTL with OPTIONS

To support TTL, the N1QL syntax has been extended with an optional parameter OPTIONS.

INSERT INTO default (KEY, VALUE [, OPTIONS])
      VALUES (kval, docval [, {"expiration":eval}]);

1 2	INSERT INTO default (KEY, VALUE [, OPTIONS]) VALUES (kval, docval [, {"expiration":eval}]);

Please note that the OPTIONS parameter is optional to ensure backward compatibility.

INSERT/UPSERT and set the document to expire in 10 minutes.

INSERT INTO default (KEY, VALUE) VALUES ("k01", {"id":"k01"}, {"expiration":10*60});
UPSERT INTO default (KEY, VALUE) VALUES ("k01", {"id":"k01"}, {"expiration":10*60});

1 2	INSERT INTO default (KEY, VALUE) VALUES ("k01", {"id":"k01"}, {"expiration":1060}); UPSERT INTO default (KEY, VALUE) VALUES ("k01", {"id":"k01"}, {"expiration":1060});

INSERT/UPSERT into SELECT and set the document to expiration time to 1hour

INSERT INTO default (KEY key, VALUE doc, OPTIONS {"expiration": 60*60}) 
       SELECT META(t).id AS key, t AS doc FROM `travel-sample` AS t;
UPSERT INTO default (KEY key, VALUE doc, OPTIONS {"expiration": 60*60}) 
       SELECT META(t).id AS key, t AS doc FROM `travel-sample` AS t;

INSERT INTO default (KEY key, VALUE doc, OPTIONS {"expiration": 60*60})

SELECT META(t).id AS key, t AS doc FROM `travel-sample` AS t;

UPSERT INTO default (KEY key, VALUE doc, OPTIONS {"expiration": 60*60})

SELECT META(t).id AS key, t AS doc FROM `travel-sample` AS t;

To preserve the TTL during update/upsert

Prior to Couchbase 6.5.1, N1QL update statement will not preserve the existing value on the document. This behavior will remain unchanged, and if users want to preserve the TTL, the N1QL update statement must explicitly set the expiration to the same value.

UPDATE default AS d SET d.comment = "xyz" , META(d).expiration = META(d).expiration;

1	UPDATE default AS d SET d.comment = "xyz" , META(d).expiration = META(d).expiration;

INSERT/UPSERT into SELECT and retain the document expiration time

INSERT INTO default (KEY key, VALUE doc, OPTIONS {"expiration": exptime}) 
       SELECT META(t).id AS key, t AS doc, META(t).expiration AS exptime FROM `travel-sample` AS t;
UPSERT INTO default (KEY key, VALUE doc, OPTIONS {"expiration": exptime}) 
       SELECT META(t).id AS key, t AS doc, META(t).expiration AS exptime FROM `travel-sample` AS t;

INSERT INTO default (KEY key, VALUE doc, OPTIONS {"expiration": exptime})

SELECT META(t).id AS key, t AS doc, META(t).expiration AS exptime FROM `travel-sample` AS t;

UPSERT INTO default (KEY key, VALUE doc, OPTIONS {"expiration": exptime})

SELECT META(t).id AS key, t AS doc, META(t).expiration AS exptime FROM `travel-sample` AS t;

MERGE documents and preserve the expiration time.

MERGE INTO `travel-sample` AS route
   USING `travel-sample` AS airport
ON route.sourceairport = airport.faa
WHEN MATCHED THEN
   UPDATE SET route.old_equipment = route.equipment,
              route.equipment = ""797"",
              route.updated = true,
              META(route).expiration = META(route).expiration
          WHERE airport.country = ""France""
            AND route.airline = ""BA""
            AND CONTAINS(route.equipment, ""319"");"

MERGE INTO `travel-sample` AS route

USING `travel-sample` AS airport

ON route.sourceairport = airport.faa

WHEN MATCHED THEN

UPDATE SET route.old_equipment = route.equipment,

route.equipment = ""797"",

route.updated = true,

META(route).expiration = META(route).expiration

WHERE airport.country = ""France""

AND route.airline = ""BA""

AND CONTAINS(route.equipment, ""319"");"

META().expiration field

The META().expiration field has always been available with N1QL. But with N1QL TTL, users can directly change the field value.

To select and use expiration in a predicate

SELECT META(d).id, META(d).expiration FROM default d WHERE META(d).expiration > 0;

1	SELECT META(d).id, META(d).expiration FROM default d WHERE META(d).expiration > 0;

To update all documents and set the comment and expiration time for 10 minutes.

UPDATE default AS d SET d.comment = "xyz" , META(d).expiration = 10*60;

1	UPDATE default AS d SET d.comment = "xyz" , META(d).expiration = 10*60;

To clear the expiration time, so that the document will not be deleted.

UPDATE default AS d SET META(d).expiration = 0;

1	UPDATE default AS d SET META(d).expiration = 0;

To delete all documents where the document will expire in more than a day

DELETE default d WHERE META(d).expiration > <span style="font-weight: 400">(NOW_MILLIS()/1000)+ (60*60*24);</span>

1	DELETE default d WHERE META(d).expiration > <span style="font-weight: 400">(NOW_MILLIS()/1000)+ (606024);</span>

Note that relative value is used only for INSERT/UPDATE/UPSERT. Absolute expiration value must be used in all other cases.

Expiry time value

Expiration time that is less than 30 days into the future can be set as the number of seconds. The value of 60*60*24*14 will mark the document for deletion in 14 days. For 30 days or more into the future, the Unix time should be used. For more information about Couchbase expiration time, please consult the Couchbase documentation.

Create an index on the expiration field

The ability to query and index the META().expiration field has always been possible in Couchbase.

CREATE INDEX idx_expir ON `travel-sample` ( META().expiration );

SELECT META().id, META().expiration FROM `travel-sample` WHERE META().expiration = 0 ORDER BY META().id LIMIT 2;

CREATE INDEX idx_expir ON `travel-sample` ( META().expiration );

SELECT META().id, META().expiration FROM `travel-sample` WHERE META().expiration = 0 ORDER BY META().id LIMIT 2;

Note that you may see a delay between the time the document is deleted and when the deletion propagates to the index service. For this reason, queries that are fully covered by the index with the expiration may return stale data. But this behavior is only temporary.

Performance consideration

N1QL accesses the TTL information by using the SUBDOC api This API will return the full document, and it also has some overhead in the request packet size. The additional overhead can incur additional query latency. However, the SUBDOC API will only be used if the META().expiration field is referenced in the query.

Additional Resources

Couchbase support for TTL: https://docs.couchbase.com/server/current/learn/buckets-memory-and-storage/expiration.html
How to set the expiration time: https://docs.couchbase.com/server/current/learn/buckets-memory-and-storage/expiration.html
Couchbase SUBDOC API: https://docs.couchbase.com/server/5.0/developer-guide/sub-doc-api.html
Indexing of the Expiration field: https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-meta-info.html

Binh Le

Platform

Self-Managed

Services

Capabilities

Why Couchbase?

Migrate to Capella

By Use Case

By Industry

By Application Need

Popular Docs

By Developer Role

COMMUNITY

Join the Developer Community

Resource Center

Education

Compare

About

Partnerships

Our Services

Partners: Register a Deal

Ready to register a deal with Couchbase?

Marriott

All Posts

Manage Time-To-Live (TTL) Documents with Couchbase N1QL