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:
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
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');
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.
- PostgreSQL 10.0 Preview Function Enhancement — Background Running (pg_background)