Annotation
In this article, we delve into a scenario where the seamless storage of sensor data in a database is coupled with the concurrent computation of the variance between successive measurements.
Problem statement
Sensor data serves as a temporal representation of metric states. An added layer of valuable information can be derived by quantifying the rate of change, or the "velocity," between consecutive readings. However, the computation of these differences, commonly referred to as deltas, can be resource-intensive when performed on demand for each sensor.
The proposal is to use the trigger mechanism during data insertion, making the computation of these deltas automatic and cost-effective. This also shifts the computation burden from the application layer to the database.
What is a trigger?
From Wiki A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries. Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.
Example
Proceeding with our case, let's consider the following table:
CREATE TABLE sensor_data
(
"timestamp" INTEGER NOT NULL,
"sensor_id" INTEGER NOT NULL,
"value" FLOAT NOT NULL
);
ALTER TABLE sensor_data ADD UNIQUE ("timestamp", "sensor_id");
With columns to store value and time deltas the creation script will look as follows:
CREATE TABLE sensor_data
(
"timestamp" BIGINT NOT NULL,
"sensor_id" INTEGER NOT NULL,
"value" FLOAT NOT NULL,
"dv" FLOAT,
"dt" BIGINT
);
ALTER TABLE sensor_data ADD UNIQUE ("timestamp", "sensor_id");
Creating a trigger
CREATE OR REPLACE FUNCTION calculate_deltas()
RETURNS trigger AS
$$
DECLARE
last_value FLOAT;
last_ts BIGINT;
now BIGINT;
BEGIN
RAISE NOTICE 'NEW: %', NEW;
SELECT "timestamp", value
INTO last_ts, last_value
FROM sensor_data t
WHERE t.sensor_id = NEW.sensor_id
ORDER BY "timestamp" DESC
LIMIT 1;
RAISE NOTICE 'last_ts = %; last_value = %', last_ts, last_value;
IF FOUND THEN
SELECT (EXTRACT(EPOCH FROM now()) * 1000)::BIGINT INTO now;
NEW."timestamp" := now;
NEW.dv := NEW.value - last_value;
NEW.dt := now - last_ts;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER tr_sensor_data_deltas
BEFORE INSERT
ON sensor_data
FOR EACH ROW
EXECUTE PROCEDURE calculate_deltas();
Showcase
Now let's insert a couple of values into sensor_data
table.
INSERT INTO my_sensor_data ("timestamp", sensor_id, "value")
VALUES ((EXTRACT(EPOCH FROM now()) * 1000)::BIGINT, 1, 3.14);
-- wait a bit
INSERT INTO sensor_data("timestamp", sensor_id, "value")
VALUES((EXTRACT(EPOCH FROM now()) * 1000)::int, 1, 2.74);
Now let's review the data in the table:
SELECT * FROM sensor_data;
Output should be like this
timestamp | sensor_id | value | dv | dt |
---|---|---|---|---|
1701109996686 | 1 | 2.74 | -0.39 | 4814 |
1701109991872 | 1 | 3.14 | NULL | NULL |
The dv
and dt
columns were populated by the trigger automatically.
Top comments (0)