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 ...) 

Option 2:

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 )

marked as duplicate by mustaccio, Michael Green, Randi Vertongen, hot2use, Gaius Mar 7 at 9:53

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Please consider also checking other related questions that show in the right-hand side column on the page. imgur.com/a/U2yop04 – mustaccio Mar 4 at 18:59

(type of option 2 is not represented in option 1. Makes the comparison slightly unfair, but hardly matters.)

The major difference between the two options is storage size. Assuming datatype real for your measurements and bigint for IDs (for lack of info), the row size of option 1 is:

8 + 8 + 100 x 4 + 24 + 4 = 444 bytes.

While the same occupies with option 2:

8 + 8 + 24 + 4 = 44 bytes in table measurement. Plus ...
(8 + 8 + 13 + 4 + 4 + 24 + 4) x 100 = 6500 bytes in measurement_data.
So 6544 bytes (!) total in option 2 for one row in option 1.

Plus you need more indexes in option 2, half of which are bigger by the same factor than in option 1 as well. Seems hopeless. Size matters.

OTOH the query for option 2 retrieves only rows with 65 bytes per hit in measurement_data, but it adds the overhead of at least an additional index-only scan on measurement. Still might prevail for a large number of hits and clustered (more considerations!) data. And updates should be cheaper, producing less table bloat - but such a design typically does not see many updates. Many related considerations depending on the complete picture.

In any case, ditch option 2 which stores some attributes redundantly over and over. Consider option 3 instead, a classical m:n relationship:

TABLE measurement (measurement_id bigint PK, ts timestamp)
TABLE measurement_kind (measurement_kind_id int PK, type int?, description text)
TABLE measurement_data (measurement_id bigint, measurement_kind_id int, value real, PK (measurement_id, measurement_kind_id))

Still 44 bytes for measurement. Plus:
(8 + 4 + 4 + 24 + 4) * 100 = 4400 bytes in measurement_data
So 4444 bytes total in option 3 for one row in option 1.

(And a negligible 100 x (4 + 4 + 13 + 3 + 24 + 4) = 5200 bytes once --> 8k mini-table for measurement_kind.)

The equivalent query to go with it:

SELECT md.value 
FROM   measurement m
   SELECT measurement_kind_id
   FROM   measurement_kind
   WHERE  description = 'measurementA'
   ) mk
JOIN   measurement_data md USING (measurement_id, measurement_kind_id)
WHERE  m.ts > X
AND    m.ts < Y;

Resumé: Maybe option 1, maybe option 3, never option 2.

Related (suggested!):

  • 1
    Thanks very much @Erwin Brandstetter really appreciate your input. Your OPTION 3 sounds like a better version of my approach for OPTION 2. I really like that. I will mark this answer as complete and correct. In some respects I know this is an OPINION question/ circumstance question however I think you laid out the implication of the various approaches and even better, provided a more appropriate option. Thanks very much! – Phillip Stack Mar 5 at 2:35
  • The value attribute on the measurement_data table. In some circumstances we store varchar and booleans. Would it be reasonable to store this data all as varchar and use the measurement_kind to cast to the relevant type on an application level? And again we now open another line of discussion of the implications of having to cast every object pulled from the database on the application. – Phillip Stack Apr 16 at 13:53
  • There is the additional cast, there's also additional storage and more error opportunities for data not stored as the naturally fitting type. In the "option 3" model, you might have separate tables for boolean or character data. Or have multiple columns with different types in one table and only one column can be filled. NULL storage is cheap. It all depends on the complete use case. – Erwin Brandstetter Apr 17 at 20:44
  • Thanks again @Erwin Brandstetter I will review this again. More discussion with the Engineers makes it appear that we may have to go with option 3. – Phillip Stack Apr 18 at 0:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.