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:

  1. 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.
  2. 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.
  3. 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.

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

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

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

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.

INSERT/UPSERT into SELECT and retain the document expiration time

MERGE documents and preserve the expiration time.

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

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

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

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

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.

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

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

 

Author

Posted by Binh Le

Binh Le is a Principal Product Manager for Couchbase Query service. Prior to Couchbase, he worked at Oracle and led the product management team for Sales Clould Analytics and CRM OnDemand. Binh holds a Bachelor's Degree in Computer Science from the University of Brighton, UK.

Leave a reply