Just for reference, and since I work with Trino these days.

The benefit it gives me is that I almost never have to care about RAM or CPU. It just scales in all directions.

The negative compared to vector languages is that order is not guaranteed (as can be seen)

And the other negative of course is that it is not possible to write generalized functions.

I am not saying it is better, but I'll leave it here.

And yeah, it does not work with empty arrays. But this is my lunch break!

  WITH measurements AS
  (
      SELECT index, m
        FROM UNNEST (ARRAY[3, 2, 5, 5, 3, 6, 5, 7, 2, 9, 2, 6, 1, 7, 2, 6, 9, 1, 1, 4, 2, 9, 2]) WITH ORDINALITY as t(m, index)
  ),
  tmp AS
  (
    SELECT index, 
        m,
        LEAST(MAX(m) OVER ( ORDER BY index), MAX(m) OVER( ORDER BY index DESC)) - m as water_level
    FROM measurements
  )
  SELECT MAX(water_level) as max_level, 
          SUM(water_level) as total_water
  FROM tmp