SQL insert row if field has changed (high performance)

My Table has these fields:

SENSOR_ID INTEGER, 
SENSOR_READING REAL, 
TIME_OF_READING TIMESTAMP, 
HASCHANGED BOOLEAN

Each sensor fires >10'000 readings/day. I want to log only those datapoints where SENSOR_READING is different from the last logged value.

Alternatively, I may log everything but set HASCHANGED to true whenever SENSOR_READING is different from the last logged value.

What is the most performant syntax for accomplishing that (with PostgreSQL, without php logics)?

Answers


You could do something like this

insert into readings (sensor_id, sensor_reading, time_of_reading)
select 42, 1234.5678, current_timestamp
from readings
where not exists (select * 
                  from readings 
                  where sensor_reading = 1234.5678
                    and time_of_reading = (select max(time_of_reading)
                                           from readings
                                           where sensor_id = 42);

This will require the sensor_reading column to be indexed but I still doubt that it will actually be faster.

You don't need to store the haschanged attribute because you can calculate that while retrieving the data:

select sensor_id,
       sensor_reading,
       time_of_reading,
       lag(sensor_reading) over (partition by sensor_id order by time_of_reading) = sensor_reading as has_changed
from readings;

This assumes that sensor_id isn't actually unique, otherwise you couldn't store more than one reading for a sensor

It further assumes that you change that REAL column to a NUMERIC column, because REAL values using = isn't accurate (actually the storing isn't accurate to begin with)


You should have a table with the sensors and their current readings, and UPDATE it on the sensor event. Then use a trigger to write to the log table if the new reading differs.


I suppose that you have to add a column where place the last value, then at every "read" you can make a comparison between those two columns.

This is a "pure sql" approach but there are solution much better, like callback (you told about php so i suggest symfony and doctrine that can do that for you and this is a valid approach if you have to do some step if the values are changed or something like this...)


Need Your Help

GPS location and deep sleep

android gps wakelock android-wake-lock

I've got a foreground service (with START_STICKY so no problem for the aspect "killed by OS")that receive a location (GPS) update every 2 seconds for navigation purpose. I don't take any wakelock. My

Do queries executed in a Postgres trigger procedure run in the same transaction?

sql postgresql stored-procedures triggers postgresql-9.1

I have a BEFORE DELETE trigger which inserts rows into another table using SPI_exec.