PostgreSQL – How to Store Time Series Data

Architecturepostgresql

I have what I believe is a time series data set (please correct me if I'm wrong) that has a bunch of associated values.

An example would be modeling a car and tracking its various attributes during a trip. For example:

timestamp | speed | distance traveled | temperature | etc

What would be the best way to store this data so that a web application can efficiently query the fields to find max, mins, and plot each data set over time?

I started a naive approach of parsing the data dump and caching the results so that they would never have to be stored. After playing with it a bit, however, it appears this solution would not scale long term due to memory constraints and if the cache were to be cleared, then all data would need to be re-parsed and re-cached.

Also, assuming that data is tracked every second with the rare possibility of 10+ hour data sets, is it generally advised to truncate the data set by sampling every N seconds?

Best Answer

There's really no one 'best way' to store time series data, and it honestly depends on a number of factors. However, I'm going to focus on two factors primarily, with them being:

(1) How serious is this project that it deserves your effort to optimize the schema?

(2) What are your query access patterns really going to be like?

With those questions in mind, let's discuss a few schema options.

Flat Table

The option to use a flat table has a lot more to do with question (1), where if this isn't a serious or large-scale project, you'll find it much easier not to think too much about the schema, and just use a flat table, as:

CREATE flat_table(
  trip_id integer,
  tstamp timestamptz,
  speed float,
  distance float,
  temperature float,
  ,...);

There aren't many cases where I'd recommend this course, only if this is a tiny project which doesn't warrant much of your time.

Dimensions and Facts

So, if you've cleared the hurdle of question (1), and you want a more performance schema, this is one of the first options to consider. It includes some basic normailization, but extracting the 'dimensional' quantities from the measured 'fact' quantities.

Essentially, you'll want a table to record info about the trips,

CREATE trips(
  trip_id integer,
  other_info text);

and a table to record timestamps,

CREATE tstamps(
  tstamp_id integer,
  tstamp timestamptz);

and finally all of your measured facts, with foreign key references to the dimension tables (that is meas_facts(trip_id) references trips(trip_id) & meas_facts(tstamp_id) references tstamps(tstamp_id))

CREATE meas_facts(
  trip_id integer,
  tstamp_id integer,
  speed float,
  distance float,
  temperature float,
  ,...);

This may not seem like it is all that helpful at first, but if you have for example thousands of concurrent trips, then they may all be taking measurements once per second, on the second. In that case, you'd have to re-record the time stamp each time for each trip, rather than just using a single entry in the tstamps table.

Use case: This case will be good if there are many concurrent trips for which you are recording data, and you don't mind accessing all of the measurement types all together.

Since Postgres reads by rows, any time you wanted, for example, the speed measurements over a given time range, you must read the whole row from the meas_facts table, which will definitely slow down a query, though if the data set you are working with is not too large, then you wouldn't even notice the difference.

Splitting Up Your Measured Facts

To extend the last section just a bit further, you could break apart your measurements into separate tables, where for example I'll show the tables for speed and distance:

CREATE speed_facts(
  trip_id integer,
  tstamp_id integer,
  speed float);

and

CREATE distance_facts(
  trip_id integer,
  tstamp_id integer,
  distance float);

Of course, you can see how this might be extended to the other measurements.

Use case: So this won't give you a tremendous speed up for a query, perhaps only a linear increase in speed when you are querying about one measurement type. This is because when you want to look up info about speed, you need only to read rows from the speed_facts table, rather than all the extra, unneeded info that would be present in a row of the meas_facts table.

So, you need to read huge bulks of data about one measurement type only, you could get some benefit. With your proposed case of 10 hours of data at one second intervals, you'd only be reading 36,000 rows, so you'd never really find a significant benefit from doing this. However, if you were to be looking at speed measurement data for 5,000 trips that were all around 10 hours, now you're looking at reading 180 million rows. A linear increase in speed for such a query could yield some benefit, so long as you only need to access one or two of the measurement types at a time.

Arrays/HStore/ & TOAST

You probably don't need to worry about this part, but I know of cases where it does matter. If you need to access HUGE amounts of time series data, and you know you need to access all of it in one huge block, you can use a structure which will make use of the TOAST Tables, which essentially stores your data in larger, compressed segments. This leads to quicker access to the data, as long as your goal is to access all of the data.

One example implementation could be

CREATE uber_table(
  trip_id integer,
  tstart timestamptz,
  speed float[],
  distance float[],
  temperature float[],
  ,...);

In this table, tstart would store the time stamp for the first entry in the array, and each subsequent entry would be the value of a reading for the next second. This requires you to manage the relevant time stamp for each array value in a piece of application software.

Another possibility is

CREATE uber_table(
  trip_id integer,
  speed hstore,
  distance hstore,
  temperature hstore,
  ,...);

where you add your measurement values as (key,value) pairs of (timestamp, measurement).

Use case: This is an implementation probably better left to someone who is more comfortable with PostgreSQL, and only if you are sure about your access patterns needing to be bulk access patterns.

Conclusions?

Wow, this got much longer than I expected, sorry. :)

Essentially, there are a number of options, but you'll probably get the biggest bang for your buck by using the second or third, as they fit the more general case.

P.S.: Your initial question implied that you will be bulk loading your data after it has all been collected. If you are streaming the data in to your PostgreSQL instance, you will need to do some further work to handle both your data ingestion and query workload, but we'll leave that for another time. ;)

Related Solutions

PostgreSQL – Building a Trending Query

You don't need the recursive CTE, just use generate_series():

You also need to use an outer join in order for the days without inspection to show up as zero:

with days as (
  select day::date
  from generate_series(date '2013-12-30', date '2013-12-30' + 365, interval '1' day) day
) 
select d.day as date_column, 
       count(i.reporting_id) as cnt
from days d
  left join inspection i on i.date = d.day
group by d.day

generate_series(date '2013-12-30', date '2013-12-30' + 365, interval '1' day) generates one row for each date between '2013-12-30' and '2013-12-30' + 365. For details on generate_series() see the manual: http://www.postgresql.org/docs/current/static/functions-srf.html

The ::date notation is just a shorthand for cast(.. as date)

Postgresql – Mixed granularity weeks/months time series data in a data warehouse

I don't know if there is a better term for fixing the problem, but the name for the problem in the first place is "mixed granularity" or "overlapping granularity". It is most common in calendar related data but can occur in data split by geography (different sources using different boundaries when aggregating their results) especially when reporting over time (as official boundaries can change in significant ways some times deliberately (https://en.wikipedia.org/wiki/Gerrymandering)). Fixing the issue so you have some useful merging of the data in your warehouse depends on how you need to report upon the data.

If you are reporting on calendar quarters then it is fairly easy: you know the three months that go in each quarter, and 13 weeks go into each too. As weeks and quarters don't align perfectly you are going to have some data at each end of the quarter that has the possibility of not quite being counted right and there are one or two extra days (two in a leap year). One suggestion would be to count January the 1st as day 1 of week 1, and count weeks 1 through 13 quarter 1 and so on. For the one or two days that make up the partial week 53, add them to quarter 4. There is going to be some "error" here but that is unavoidable and should be more than small enough (given large enough samples/figures) to no significantly affect the trends you are trying to detect and report upon.

The other that springs to mind you have already suggested: average out all the data sources, breaking them down to a common such as a day during your ETL process - so record of £123,000 sales in June becomes £4,100 counted against each day, and £35,000 in the week beginning averages to £5,000 on each of the seven days. You can then aggregate this back up to whatever larger granularity you desire. Again you are losing some definition in your data, but the results will be less "lumpy" than, for example, mapping whole weeks to months and having some with four and some with five.

Of course the ideal solution is to try get hold of the original data before it was rolled up at all so you have it all at the finest granularity to start with, but that is often not practical or not even possible because only the aggregated data remains.