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

If accuracy doesn't have to be 100%, a number of options open up.

---

Data Acquisition

When monitoring the state of a system, it is convenient to have the data available at a constant time interval. Unfortunately, you may not always be able to fetch data at exactly the time you want to. Therefore RRDtool lets you update the log file at any time you want. It will automatically interpolate the value of the data-source (DS) at the latest official time-slot (interval) and write this interpolated value to the log. The original value you have supplied is stored as well and is also taken into account when interpolating the next log entry.

Consolidation

You may log data at a 1 minute interval, but you might also be interested to know the development of the data over the last year. You could do this by simply storing the data in 1 minute intervals for the whole year. While this would take considerable disk space it would also take a lot of time to analyze the data when you wanted to create a graph covering the whole year. RRDtool offers a solution to this problem through its data consolidation feature. When setting up an Round Robin Database (RRD), you can define at which interval this consolidation should occur, and what consolidation function (CF) (average, minimum, maximum, last) should be used to build the consolidated values (see rrdcreate). You can define any number of different consolidation setups within one RRD. They will all be maintained on the fly when new data is loaded into the RRD.

Round Robin Archives

Data values of the same consolidation setup are stored into Round Robin Archives (RRA). This is a very efficient manner to store data for a certain amount of time, while using a known and constant amount of storage space.

It works like this: If you want to store 1000 values in 5 minute interval, RRDtool will allocate space for 1000 data values and a header area. In the header it will store a pointer telling which slots (value) in the storage area was last written to. New values are written to the Round Robin Archive in, you guessed it, a round robin manner. This automatically limits the history to the last 1000 values (in our example). Because you can define several RRAs within a single RRD, you can setup another one, for storing 750 data values at a 2 hour interval, for example, and thus keep a log for the last two months at a lower resolution.

The use of RRAs guarantees that the RRD does not grow over time and that old data is automatically eliminated. By using the consolidation feature, you can still keep data for a very long time, while gradually reducing the resolution of the data along the time axis.

Using different consolidation functions (CF) allows you to store exactly the type of information that actually interests you: the maximum one minute traffic on the LAN, the minimum temperature of your wine cellar, ... etc.

I have personally seen millions of records saved per minute on a top end SSD server.

If you have less than 10 billion items, Postgres will be fine, and is easier to manage IMO.

If you do use postgres, you should vertically partition the table. This will help keep indexes smaller, improve the the cache hit rate, vastly improve the ease with which you can drop older data, and make various other admin tasks easier.

I've done this in the past with a compound primary key of (topic_id, t) where t was a microseconds-past-the-epoch timestamp (bigint) unique within a topic. Then set up a parent table: CREATE TABLE events (topic_id, t, data_fields..) and "CREATE TABLE .. INHERITS events" from it into multiple subtables, named based on the timespan they will hold, like events_2013, events_2014.

Depending on how much data you have, either partition by day/month/year/etc. I partitioned every million seconds (~11 days), since that kept the resulting table sizes a bit more manageable (gigs not TBs).

Add a CHECK CONSTRAINT to each sub-table to constrain the timespan (ie, WHERE t BETWEEN ?? and ??).

When you do a SELECT * FROM events WHERE topic_id = 1 AND t BETWEEN $x AND $y ORDER BY t DESC; the query planner knows which sub-table(s) to query, and doesn't touch the other tables at all.

You can also add a BEFORE INSERT trigger to the parent table that inserts into the correct sub-table, otherwise get clients to compute the correct table name when inserting.

We offer cyclic time warp search, time warp search and any other metric of pseudo-metric you can come up with :-)