Older monitor reports that score higher trump more recent monitor reports.

[squell]

Right now this is not noticeable since we only have one monitor.

[squell]

Relevant query:

WITH latest_per_monitor AS (
  SELECT DISTINCT ON (time_source_id, monitor_id, protocol) time_source_id, protocol, monitor_id, received_at, score
  FROM monitor_samples
  ORDER BY time_source_id, monitor_id, protocol, received_at DESC
)
SELECT DISTINCT ON (time_source_id, protocol) time_source_id, protocol, monitor_id, received_at, score
FROM latest_per_monitor
ORDER BY time_source_id, protocol, score DESC

[squell]

Note: benchmarking on a table of 2945604 rows (with 10 monitors and 5000 sources that's about 4 hours worth of monitoring?), makes this query run at 1.5s already.

[squell]

Suggested fix for the trump behaviour is to introduce a liveness window:

WITH latest_per_monitor AS (
  SELECT DISTINCT ON (time_source_id, monitor_id, protocol) time_source_id, protocol, monitor_id, received_at, score
  FROM monitor_samples
  WHERE received_at >= now() - interval '1' hour
  ORDER BY time_source_id, monitor_id, protocol, received_at DESC
)
SELECT DISTINCT ON (time_source_id, protocol) time_source_id, protocol, monitor_id, received_at, score
FROM latest_per_monitor
ORDER BY time_source_id, protocol, score DESC

Since this in realistic database should also cut down the samples to 1500000 rows that should also make the performance more manageable. But some caching seems like it should be easy to do.