I'm storing a large amount of time series data/value (varchar) with timestamp for a set of parameters (varchar) in PostgreSQL.

I'm looking for a way to avoid storing duplicate if the value is the same, which 90% of the time it is. What's important for me is to be able to know when the value first appeared for the particular parameter and when it changes and also when it disappear. I'll be representing the data in a graph.

Example of the schema and data, this is an oversimplified version of what I'm actually storing http://sqlfiddle.com/#!17/cd02b

Is there a general pattern to solving this kind of problem?

  • 1
    Generally I would suspect the overhead of checking the existing row to see if the value has changed outweighs any savings you would get by not just writing the new row blindly. – Aaron Bertrand Jul 9 '19 at 12:09
  • @AaronBertrand you're right, that's the downside, which is what I was hoping to find a design patterns that can overcome that – faulty Jul 10 '19 at 1:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.