In several projects, we must store, aggregate, evaluate simple measurement values. A single line typically consists of a timestamp, value, and some value attributes. In some applications, we would like to save 1000 values per second or more. These values should not only be inserted, but also deleted at the same speed, since the service life of the value is limited to a year or so (at different stages of aggregation we do not store 1000 / s throughout the year).
So far, we have developed various solutions. One is based on Firebird, one on Oracle and one on some do-it-yourself storage engine. But none of them is a very satisfactory solution.
Both RDBMS solutions cannot process the required data stream. In addition, applications that deliver values (such as device drivers) cannot be easily tied to databases; insert instructions are cumbersome. And finally, with an SQL interface for data, it is imperative that typical estimates are difficult to formulate in SQL and slow down execution. For example. Find the maximum value with a time stamp of 15 minutes for all measurements in the last month.
A home-made solution can handle input speed and has a client API for this, but has nothing to do with the query language and cannot be used by other applications through some standard interface, for example. for reporting.
The best solution in my dreams would be a database system that:
- has an API for very quick insertion
- able to delete / truncate values at the same speed
- provides a standard SQL interface with specific support for typical time series data
Do you know some database that approaches these requirements, or will you approach the problem differently?
performance database time-series
Kit Fisto Jan 11 '12 at 8:45 2012-01-11 08:45
source share