How to Use the Data Retention Window

By digoal

Image for post
Image for post

In MongoDB, you can use rotate collate to set the maximum table capacity and the maximum number of records and enable or disable continuous writing and automatic overwriting of the oldest records. In reality, you can also use PostgreSQL together with PipelineDB to implement similar functions, as we’ll show in this tutorial.

This kind of function is suitable for log data, which requires no maintenance costs and features continuous writing and automatic overwriting of the oldest records.

In this tutorial, we’ll specifically show you can you can use PipelineDB CV TTL feature to work with the data retention window to change at which length of time are data records are deleted.

How to Use the PipelineDB CV TTL Function

You can follow these steps to learn how you can use the PipelineDB CV TTL function.

Creating a Data Stream and Getting CV TTL Ready

First, you’ll want to create a stream (that is, more specifically, define the data format) using the following command:

CREATE STREAM s1 (id int, info text, crt_time timestamp default now());

Next, you can create a CV TTL. In the example below, you will specifically set things such that retain data for one minute based on the crt_time field.

CREATE CONTINUOUS VIEW cv1 WITH (ttl = '1 min', ttl_column = 'crt_time') AS  
SELECT id,info,crt_time FROM s1;

Next, activate the CV using the following command:

activate cv1;

Testing How the Function Works

Next, in our little learning process, let’s conduct a test. To do so follow these steps. First, write data into a stream.

pipeline=# insert into s1 values (1,'test',now());  
INSERT 0 1

Then, view the data with the following command. The output should be something similar to what you can see below.

pipeline=# select * from cv1;  
id | info | crt_time
----+------+----------------------------
1 | test | 2017-06-12 17:11:45.774904
(1 row)

Next, view the data one minute later. What you should see is that the data is automatically deleted actually.

Now, try changing the TTL to 10s. You can do so by entering the following command to which you should have output that looks something like what you can see below.

pipeline=# select set_ttl('cv1', '10 second', 'crt_time');  
set_ttl
---------
(10,3)
(1 row)

And after doing this, you can conduct a test again to get even a better, clear idea of how this function works. With the setting we set above, the data is automatically deleted 10 seconds after being written.

References

Original Source

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store