1

I have a table sensor_data in a PostgreSQL 9.6 database:

    Column     |            Type             |    
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 datetime_bin  | timestamp without time zone | not null (one minute resolution)
 category_a    | integer                     | not null
 category_b    | integer                     | not null
 sensor_data   | integer                     | not null

Indexes:

  • "station_logs_pkey" PRIMARY KEY, btree (id),
  • UNIQUE CONSTRAINT (station_id, datetime_bin, category_a, category_b),
  • btree (datetime_bin)
  • btree (sensor_id)

If the value for a combination of station_id, datetime_bin, category_a, category_b is 0, there is no row. We're aggregating this data to 15 minute bins, and want to add in 0s to make averaging on the aggregated data more user-friendly. We also periodically need to refresh a month of data, so wanted to have deleting the disaggregate data cascade to aggregates based on it.

Our current approach is the following, but I'd be curious to know if there is a better/more proper way.

Current approach

Aggregate table
    Column     |            Type             |    
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 datetime_bin  | timestamp without time zone | not null (fifteen minute resolution)
 category_a    | integer                     | not null
 category_b    | integer                     | not null
 summed_data   | integer                     | not null (with 0s)
Disaggregate table
    Column     |            Type             |    
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 datetime_bin  | timestamp without time zone | not null (one minute resolution)
 category_a    | integer                     | not null
 category_b    | integer                     | not null
 sensor_data   | integer                     | not null (without 0s)
 aggregate_id  | bigint                      | FOREIGN KEY aggregate(id)
Zeroes crossover table
    Column     |            Type             |    
---------------+-----------------------------+
 one_minute_id |bigint                       | FOREIGN KEY one_minute(id) ON DELETE CASCADE
 aggregate_id  |bigint                       | FOREIGN KEY aggregate(id)
Triggers
  • when deleting from disaggregate, delete from aggregate with same aggregate_id
  • when deleting from disaggregate, cascades to zeroes table with Foreign Key
  • when deleting from zeroes table, delete from aggregate with same aggregate_id.
  • never even heard of "Disaggregate table" – Evan Carroll Aug 22 '18 at 17:37
0

It sounds to me like you want the TimeScaleDB extension with a hypertable and 15 min time buckets.

It's a free and open source project, like Pg. And it's actively developed.

  • Sounds like a useful tool. We are unfortunately constrained in our infrastructure. – raphael Aug 22 '18 at 20:58

Your Answer

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

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