0

Let's say we need to store reading of temperature sensors and keep a history, for every day. Each measure is a tuple day: number, value: string (some days could be missed that's why we need to store the day explicitly). There are hundreds of thousands of sensors.

Adding new measurement should not require re-reading and re-write the whole object, it should be small incremental addition.

And, there could be multiple reading for the same day. In such case, if day is the same only the latest measurement for that day should be kept.

What data structure should be used? I can see the following ways:

CREATE TABLE sensor_history (
  sensor_id integer,
  time      integer[],
  value     text[]
);

or

CREATE TABLE sensor_history (
  sensor_id integer,
  history   json/jsonb/hstore
);
Alex Craft
  • 13,598
  • 11
  • 69
  • 133
  • 1
    Regardless of the design, you are going to be storing a lot of information. Have you looked into using NoSQL? – Tim Biegeleisen Aug 11 '17 at 05:55
  • Yes, I see your point about NoSQL but for some other reasons in this case PostgreSQL would be better choice. – Alex Craft Aug 11 '17 at 06:00
  • It depends on what you plan to do with the data. If you don't need a log of RDBMS functionality, then a NoSQL data store becomes more attractive. – Tim Biegeleisen Aug 11 '17 at 06:04

1 Answers1

1

Why not just store one row per tuple? e.g.

CREATE TABLE sensor_history (
  sensor_id integer,
  time      integer,
  value     text
);
wmorrell
  • 4,988
  • 4
  • 27
  • 37
  • Hmm, maybe. But would it be efficient? If we have 1million sensors, after 1 month there will be 30mln in history, and after 1 year 360mln. – Alex Craft Aug 11 '17 at 05:50
  • 1
    Efficient in what way? I have worked with databases having billions of rows in a table without problems. As long as you are not trying to run queries that will do full table scans, there is not much of an issue. Putting an index on `sensor_id` will prevent full scans for loading data from a specific sensor, for example. – wmorrell Aug 11 '17 at 05:55