Time series database

[quinmars]

There is a high demand for time series database, in particular, for high frequent data. Those databases are useful in a wide field of application, for example manufacturing automation or mobile devices. There are plenty of server or even cloud based databases. With little research I found a dozen of those. But I haven't found any file based time series database. It's a pity, because especially for manufacturing automation and mobile device, you usually do not need a full-fledge database. A file based database offers here some benefits, which I probably do not need to mention here.

Today, I'm using SQLite for those applications. That works quite well, but has one major disadvantage. The files get really big. And one can see that there is high potential to improve this, is that after zipping those files reduce the file size by a factor of 5 to 10. With a compression that takes the data format into account this could be improved even more.

Here is my vision how time series could be store in litedb. The time series are compressed by a gorilla-like compression page-wise. So in the case of no compression you would end up with around 500 values per page. In some cases, where the value does not change and the time step is always the same you could store around 30k values in one page. There be an index for the beginning and the end of each page. Hence if you search for a window of data. You'll can easily find the beginning and ending page.

I know this is not the original purpose of litedb, and I can understand if you are not interested in expanding the scope of this database. There are two ways how litedb could offer support for such an extension. The first one is direct implementation. Another one would be to give library users access to the page and index management. The time series support could be than just an external addition.

. already played around with the gorilla compression, if you are interested you can find it here.

[lbnascimento]

I don't know much about TSDBs. Is there a particular reason why LiteDB wouldn't be currently suitable for such applications? Would there be performance issues? Would the files be too large?

[quinmars]

To be honest, I haven't run any tests with LiteDB, but obviously the file size could be reduced dramatically, since in a best case scenario, a datapoint can be stored in two bits. Infact, a timeseries database is not directly related to a document storage DB. The reason, why I came to LiteDB is, because it already has all the page management build-in. An TSDB implementation can hence profite from the work already done. Since the page management is not publicly available, there is only the way to extend LiteDB directly. I guess, I just start to work on it, and once (and please do not count on it that this will ever happen) I will let you know how LiteDB could be extended.

[seikosantana]

I actually came to discussion to ask about this. One thing i am curious about is since LiteDB is a document database, which reads whole file and writes whole file on its read/write operation, does that means if it is being used as time series database, and I'm inserting a 8 byte data on 1MB database causes a read of 1MB database and a write of 1MB + 8bytes?

[lbnascimento]

LiteDB operates in pages of 8192 bytes and it will only read from and write to pages as needed. A typical insert operation will read three pages and write two pages.

[seikosantana]

Does that object structure matters? For example does nested objects makes writes having to involve more pages? If yes, is it significant?

I think LiteDB as TSDB would be great

[shblue21]

I'm using LiteDB and it's a good database, but be sure to use a time series-only database (especially for factory data).
If weight is a consideration, review Apahce IoTDB.

[quinmars]

There are plenty of timeseries databases and indeed we are evaluating some of them at the moment. I haven't come in to touch with Apache IoTDB, but it looks interesting. Thanks for that advice. What I haven't seen so far, however, is a file based TSDB.