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.
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:
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,
and a table to record timestamps,
and finally all of your measured facts, with foreign key references to the dimension tables (that is
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
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:
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
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
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
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.
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. ;)