Ask HN: What DB to use for huge time series?

36-100MB/person per day ~250 days/year expecting ~20,000 (an educated stupid wild ass guess) initially when the system is actually put into production. ~100-400TB per year(?). Most of the data would only be of interest for a month or so, but we do want to preserve the data in general in some usable fashion for testing and some research stuff.

The number of horror stories I've seen about mongo is up to around 10 this month alone.

I'm now glad I never made the jump... in the meantime, pgsql is still on my list

Beware of using Elasticsearch as a primary DB. Kyle Kingsbury has shown that it loses an awful lot of data during a partition, despite their claims. Example: http://aphyr.com/posts/317-call-me-maybe-elasticsearch

I've only had about a year and a half of working in that space, but can confirm that Cassandra's quite good for truly huge time-series data. If you want to record an event every time someone makes a purchase at a Wal-Mart, turn to Cassandra or a similar system.

It is also much more highly regarded as a primary data store than Elasticsearch.

Development of graphite is effectively dead. The datastore component (carbon and whisper) has design issues, and the official replacement (ceres) hasn't seen any commits this year. There are a some alternatives, though.

For data storage Cyanite [0] speaks the graphite protocol and stores the data in Cassandra. Alternately, InfluxDB [1] speaks the graphite protocol and stores the data in itself

To get the data back out, there's graphite-api [2] which can be hooked up to cyanite [3] or influxdb [4]. You can then connect any graphite dashboard you like, such as grafana [5], to it.

[0] https://github.com/pyr/cyanite [1] http://influxdb.com [2] https://github.com/brutasse/graphite-api [3] https://github.com/brutasse/graphite-cyanite [4] https://github.com/vimeo/graphite-influxdb [5] http://grafana.org

Does treasure data have a dedicated storage engine for time series? This kind of data has specific needs which are not met by general purpose storage layers.

The data is going to be sensor readings. Just numeric readouts over time. There will probably be about 8-16 physical sensor points per person per reading. I'll want to retrieve slices of time rather than individual records and likely produce some averages/basic algebra over those slices in order to produce more meaningful data for the rest of the system which is pretty vanilla in terms of data requirements.

TempoDB has renamed itself into TempoIQ and no longer offer their storage service. I've heard some angry comments from customers who recently received an email telling them the storage service they were using was to be shutdown at the end of october!

Yep, banks heavily use these to store tickers (currencies, instruments) and do calculations of VWAP, etc. Pretty much standard in the industry for these kind of applications.

Is this just your typical columnar OLAP database, like Vertica or Big Query?

>loading those files into Pandas or R

What is "massive" for you? I was under impression you can't use R or pandas for anything that doesn't fit into memory.

Some tips for querying timeseries in Postgres: http://no0p.github.io/postgresql/2014/05/08/timeseries-tips-...

HN discussion: https://news.ycombinator.com/item?id=7809819

>I find that most of the time, "Big Data" isn't really all that big for modern hardware, and so going through all of the extra software work for specialized data stores isn't really all that necessary. YMMV, of course, depending on the nature of your queries.

I totally agree. Most of useful "big data" is time-series data, and they aren't all that huge compared to images/videos/etc.

That being said, I think the reason to adopt something like Hadoop/MPP engines is not for storage but ease of querying: while Postgres can handle storing terabytes of data, joining two terabyte-scale tables can get a little iffy. This gets even more complex if you start packing data into array columns for space efficiency.

There is an argument to be made that historical/archival data aren't all that useful and thus do not need to be analyzed: that was definitely my assumption coming from finance. However, I've been surprised how far back some of our customers at Treasure Data go to mine insights from data.

Clarification: InfluxDB only crashed on me when I requested a lot of data without chunking. With chunking I didn't have any problems.

I like InfluxDB and still use it.

While KDB+ is infinitely better for TS data, hiring people who know what they're doing, and buying the physical hardware you need to make it fly isn't what most modern firms are interested in. If you want to run on the EC2, Redshift is super great. Time/date range queries though, holy shit those suck.

All these questions should ideally not be a concern when you are looking for a database. A general purpose database which can handle all the above and more is AmisaDB. http://www.amisalabs.com/

Part of the reason the question was light on details is that this is just at the very beginning and a lot of relevant things aren't locked in yet. Below are the back of napkin results and are subject to the risk of being laughably wrong.

Writes: not totally sure in terms of how the data is being packaged before being sent yet, but it'll probably be more than 10 writes a second but less than 1000 initially(?). Not sure yet if we're aggregating and batching before sending or if we are, to what degree.

Availability: If it has brief breaks where it just misses some data (<3seconds?) probably not the worst thing, but really trying to avoid big gaps in the data.

Reads will likely be grabbing the last n records of a given set of sensors maybe with some light math on it if the query language supports it, though there might be an easier way to cache recent history and then only need to go to the big list for responding to a longer-term issue. Also the nature of reads is very subject to change since there's a bunch of use-cases for the data being kicked around and I haven't gone through what each use's reads would look like yet.

New data needs to show up in reads in soft-real time. The napkin-estimate indicates that we might be looking at asking for about 6-80MB returned per query as a generally large but perhaps not max query, bigger operations that dealt with legitimately huge amounts of data will probably be scheduled around lighter periods/put on different machines (not sure how adding more machines reading would impact since I don't know what db it will be yet).

Ideally keep as much history as humanly possible, possibly moving them to physical archival at some point (1yr+?).

Roshi sits on top of Redis, so this solution can be quite expensive.

What features are you from InfluxDB? I am a long-time graphite user, and I just saw InfluxDB, and it looked really good.

I have had good experience using HDF5 to store time series data, but just research datasets and nothing that has been put into production. I don't really know how well it works with threading, for example. It does work very well with PyTables and Pandas for analysis and definitely beats CSV files, which is the the normal way these research datasets are stored.

If you are interested in using HDF5 and PyTables to store time series data, check out this little library that I created: http://andyfiedler.com/projects/tstables-store-high-frequenc...