This question already has an answer here:
I am storing Time Series Data in a Postgresql database.
I am wondering what the performance ramifications are of the following two proposed design patterns.
Option 1: A single table with a timestamp and many columns ( maybe 100 ) containing various measurement data.
TABLE measurement (id, timestamp, measurementA, measurementB, measurementC ...)
Two tables. One with an id and timestamp and another table which contains a reference to the measurement table and then multiple fields describing the recorded data.
TABLE measurement (id, timestamp) TABLE measurement_data (id, measurement_id, description, type, value)
I like the idea of option two. As my table structure is much smaller and simpler. However I have concerns of the effects of queries on the table. Will there be a huge performance hit for a simple query such as the following?
Using Option 1s Design
SELECT measurementA FROM measurement WHERE timestamp > X AND timestamp < Y
Using Option 2s Design
SELECT value FROM measurement_data WHERE description = 'measurementA' AND measurement_id IS IN (SELECT id FROM measurement WHERE timestamp > X AND timestamp < Y )