Efficient Evenly Distributed Sampling of Time Series Records in PostgreSQL

Not entirely the same use case, but I've had some success displaying charts of time series data using Postgres's width_bucket function to group values into a histogram. width_bucket computes a histogram bucket for a value, which then can be used in a "group by" clause to group and calculate aggregate statistics for each bucket. This is pretty similar to the approach detailed in the post, but it's providing averaged values instead of a randomly sampled distribution.

If you want to display a line chart that is 500px wide, for example, you know you really only need 500 data points. You can treat this chart as a histogram, and compute a min/max/avg for each value. The chart could display the min & max as a high-low band, and if you zoom in further, adjust the histogram interval and requery.

This is what a query would look like with this strategy:

  start_timestamp: The start of the histogram range
  end_timestamp: The end of the histogram range
  num_divisions: The number of histogram divisions

  select
      start_timestamp + ((bin_id - 1) * ((end_timestamp - start_timestamp) / num_divisions)) as bin_start_at,
      start_timestamp + ((bin_id) * ((end_timestamp - start_timestamp) / num_divisions)) as bin_end_at,
      average_value,
      min_value,
      max_value,
      num_values
  from (
      select
             avg(value) as average_value,
             min(value) as min_value,
             max(value) as max_value,
             count(*) as num_values,
             width_bucket(data_points.timestamp, start_timestamp, end_timestamp, num_divisions) as bin_id
      from data_points
      where
            timestamp > start_timestamp and timestamp < end_timestamp
      group by bin_id
      order by bin_id asc
   ) t;

I'm interested to see if anyone else has taken this approach, and if there are any performance considerations I haven't considered!

This is really cool!

I'm curious why CTEs don't seem to be getting used here?

  SELECT
  (
    SELECT
      (
        t.data,
        t.created_at
      )::jsonb_x_timestamp
    FROM
      telemetries t
    WHERE
      server_id = 'a0dcc312-0623-af60-4dc0-238301cc9bf8'
      AND data ? 'load_avg'
      AND t.created_at >= series.target
      AND t.created_at <= (series.target + INTERVAL '2592 seconds')
      LIMIT 1
  )
  FROM
  (
    SELECT
      target
    FROM
      GENERATE_SERIES(
        '2020-09-01 00:00:00'::timestamp,
        '2020-09-15 23:59:59'::timestamp,
        INTERVAL '2592 seconds'
      ) target
  ) series

I'm curious on the performance of:

  WITH series AS SELECT
    target
  FROM
  GENERATE_SERIES(
    '2020-09-01 00:00:00'::timestamp,
    '2020-09-15 23:59:59'::timestamp,
    INTERVAL '2592 seconds'
  ) target
  SELECT
  (
    t.data,
    t.created_at
  )::jsonb_x_timestamp
  FROM
    telemetries t
  WHERE
    server_id = 'a0dcc312-0623-af60-4dc0-238301cc9bf8'
    AND data ? 'load_avg'
    AND t.created_at >= series.target
    AND t.created_at <= (series.target + INTERVAL '2592 seconds')
  LIMIT 1

or even shuffling some of the other selects and chaining CTEs together. I'm even curious if you could somehow use a combination of CTEs to generate something more suitable for use with SYSTEM_ROWS (TIL!! will keep this in my pocket for the future!).

CTEs are never a guarantee for performance improvements (they make things worse at times) but I see them getting ignored a lot when at times they are a huge boost.

Edit: wow the formatting is really bad, hopefully the code stuff doesn't kill it for mobile users

I like this write up a lot, particularly the examples and the metrics, but I wonder, what would be the relative performance of sampling using some of the middle digits of the UUID?