Postgres schema for IoT sensors

Grafana PostgreSQL
1

I’m trying to get started using postgres for IoT sensor time-series storage and grafana. I know about time-series specific databases and such, as well as timescaledb, but would prefer to go with plain postgres if possible for a variety of reasons. I’m writing-up my plans in the hope that it spurs some discussion that may help me find a better design or that helps others reading this in the future.

Requirements

I have relatively modest requirements: ~1000 time-series with a data point every minute, more specifically:

  • ~1000 time-series mostly with a data point every minute
  • typ message from a device contains about 10 metrics, or put differently, groups of about 10 metrics share timestamps and much of the meta-data
  • quad-core ARM w/2GB memory, eMMC storage (i.e. not a big x64 box)
  • at least 10-years lifetime of the DB (not necessarily the HW)

Option 1

My first option is to use a narrow data table for the time-series with just three columns: time-stamp, tags, and value; where tags is a foreign key to a record that has all the meta-data, e.g., device, sensor, metric, name, scale, unit, location, etc… This provides great flexibility because a device can report whatever metrics it wants to and they can be represented individually.

I believe this means that assuming timestamp, integer, real columns a data table row uses 23+8+4+4=39 Edit: 23+1+8+4+4=40 bytes. Thus 1000 metrics, for one year, every minute would accumulate to about 21GB of data. That’s not impossibly large but not small either, given the “low end” system I’m contemplating.

One thing that is clear is that I need to partition the table by timestamp, probably monthly, so I can deal with old data, whether that’s deleting or archiving.

I’m pretty sure I will need to produce aggregated tables, e.g. hourly, daily or whatnot. Otherwise I expect that the time to produce the data for a dashboard full of yearly graphs will take forever. What is not clear to me is how to automatically switch between the raw and the various aggregated tables on the grafana end, I wonder whether a view can do that so I don’t have to have mile-long SQL in each graph’s definition.

Option 2

An alternative design would be to use a wider table for the time-series. E.g. a row could hold timestamp, foreign key to meta-data, and an array of values (all the values reported by a device in one message). Assuming an average of 10 values in the array that would result in 23+8+4+10*4=75 bytes per row and 4GB per year. Edit: I forgot the array overhead, which seems to be ~20 bytes. I ended up using pg_column_size to find out that a row with 10 values uses 97 bytes, which sums to 5GB per year.

The 4x size reduction over option 1 is certainly attractive. The downside is that the SQL becomes more complex because the meta-data has to specify which array element to extract the value from.

Thoughts? I’m sure others have gone down this or a very similar path, but I haven’t found a whole lot of write-ups or experiences…

2

I went ahead and implemented option 2 and am pretty happy so far. Using a view makes it very easy to draw graphs in grafana and the multiple tables make data entry and switching devices around very flexible as well. Since there doesn’t seem to be a lot of interest here I’ll just paste my schema in case it helps someone in the future.

/* formats - record the individual metrics for each data row format.
 * For example, fmt=2, metric=temperature, unit=C, col=4 says that records of format 2 have the
 * temperature in Celsius in column 4 of the values array in the raw data table.
 * Data row formats are per schema, they are not per-device. */
CREATE TABLE IF NOT EXISTS formats (
  fmt smallserial NOT NULL,
  metric text NOT NULL,
  unit text,
  col smallint NOT NULL,
  CONSTRAINT metric_fmt UNIQUE (metric, fmt)
);

/* devices - map hardware device IDs into short int to save space.
 * For example, dev_id=02eb4fa0, dev=5 says that data for the device with hex id 02eb4fa0 is
 * stored using dev=5 in the raw data table.*/
CREATE TABLE IF NOT EXISTS devices (
  dev smallint UNIQUE NOT NULL,
  dev_id text PRIMARY KEY
);

/* locations - record which device is placed in which logical location at what point in time.
 * For example, location=attic, dev=5, start_at=2019-01-01, end_at=2019-01-08 says that device
 * 5 recorded data for the attic for a period of 7 days. Or put differently, to find attic data for
 * that period need to look at dev=5 records in the raw data table. If end_at is null it means the
 * device is still there. */
CREATE TABLE IF NOT EXISTS locations (
  location text NOT NULL,
  dev smallint NOT NULL,
  start_at timestamp without time zone NOT NULL,
  end_at timestamp without time zone
);

CREATE INDEX IF NOT EXISTS locations_location ON locations (location);

/* raw - data table containing measurements.
 * For example, ts='2019-02-17 01:14:18Z', dev=5, fmt=2, values=[0, 1, 3, 20, null, null, 23] says
 * that the temperature in the attic was 20C on Feb 17th at 1:14am (assuming the examples above for
 * the other tables). The row has 7 columns, so we would expect to find 7 entries in the formats
 * table for fmt=2, and in this particular row two values were not reported (this device may not
 * have the specific sensors fitted). */
CREATE TABLE IF NOT EXISTS raw (
  ts timestamp without time zone NOT NULL,
  dev smallint NOT NULL,
  fmt smallint NOT NULL,
  values real[],
  CONSTRAINT dev_fmt_ts UNIQUE (dev, fmt, ts)
) PARTITION BY RANGE (ts);

CREATE TABLE IF NOT EXISTS raw_2019 PARTITION OF raw
  FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
CREATE TABLE IF NOT EXISTS raw_2018 PARTITION OF raw
  FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
CREATE TABLE IF NOT EXISTS raw_2017 PARTITION OF raw
  FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');
CREATE TABLE IF NOT EXISTS raw_2016 PARTITION OF raw
  FOR VALUES FROM ('2016-01-01') TO ('2017-01-01');

CREATE INDEX IF NOT EXISTS raw_dev_fmt_ts ON raw (dev, fmt, ts);

/* logical - view to combine all above tables into something easy to use in queries.
 * For example, SELECT ts, location, value FROM logical WHERE metric = 'temperature' AND
 * ts BETWEEN '2019-01-01' AND '2019-01-08' returns all temperature recordings tagged by location
 * for a 1-week time-range. */
CREATE OR REPLACE VIEW logical AS
  SELECT raw.ts, locations.location, formats.metric, formats.unit, raw.values[formats.col] AS value
    FROM raw
      JOIN formats ON raw.fmt = formats.fmt
      JOIN locations
      ON raw.dev = locations.dev AND (raw.ts >= locations.start_at)
        AND (raw.ts <= locations.end_at OR locations.end_at IS NULL)
    WHERE raw.values[formats.col] IS NOT NULL
;

The way I insert data is as follows (a device’s string ID needs to be translated into a short int for the raw table and created if it doesn’t exist).

    {   query: 'begin' },
    {   query: "set local schema 'wcc'" },
    // ensure we have a record for the device
    {   query: 'INSERT INTO devices (dev, dev_id) ' +
               'SELECT MAX(dev)+1, $hwid FROM devices ' +
               'ON CONFLICT (dev_id) DO NOTHING',
        params: { hwid: msg.hwid },
    },
    {   query: 'INSERT INTO raw (ts, dev, fmt, values) ' +
               'SELECT $ts, dev, $fmt, $values FROM devices WHERE dev_id = $hwid ' +
               'ON CONFLICT ON CONSTRAINT dev_fmt_ts DO UPDATE SET values = EXCLUDED.values ',
        params: {
            hwid: msg.hwid,
            ts: msg.at,
            fmt: msg.fmt,
            values: msg.payload,
        },
        output: true,
    },
    {   query: 'commit' },

This could be done more efficiently by keeping an in-memory cache client-side, but it’s more convenient for me to offload it. It allows me to just bring a new device online and have it send data and worry later about properly identifying it in the locations table.