How to link disaggregate data to aggregate data when aggregation adds 0/NULL values? 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.
It sounds to me like you want the TimeScaleDB extension with a hypertable and 15 min time buckets.


https://www.timescale.com/


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


https://github.com/timescale/timescaledb