I am storing sensor data in a CB bucket and am curious as to whether there would be any performance gains if I were to index documents based on their year, month and day properties to filter down the result set during querying. I will also need an index on the actual timestamp field to further filter by time during that period. Would this provide any performance value?
Also, are there any general recommendations for storing time series data in documents in CB?
If you store the timestamp as ISO-8601 standard. you can index on this timestamp. You can archive all you described and also you can use any N1QL date time functions.
https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/datefun.html
Thanks.
Is this the most efficient way when it comes to indexing? For example, what If I were to create documents to represents time buckets by their key… such as tenantid_yyyy-MM-dd-HH and store all samples (including their timestamps) in an array inside of this document?
Would this allow for more efficient indexing/querying since I would be limiting the queries to specific document buckets where I can deterministically build the key and then only search for events within that document?
INSERT INTO default VALUES ("k01",{"date":"2019-01-21T14:56:43.011-08:00","tid":1});
INSERT INTO default VALUES ("k02",{"date":"2018-12-21T14:56:43.011-08:00","tid":1});
INSERT INTO default VALUES ("k03",{"date":"2017-10-21T14:56:43.011-08:00","tid":1});
CREATE INDEX ix1 ON default(tid, date);
SELECT * FROM default AS d
WHERE d.tid = 1 AND d.date BETWEEN "2018" AND "2019";
SELECT * FROM default AS d
WHERE d.tid = 1 AND d.date BETWEEN "2018-11" AND "2019-02";
SELECT * FROM default AS d
WHERE d.tid = 1 AND d.date BETWEEN "2017-10" AND "2019-01-20T12:00:00";