Timevector Pipeline API Discussion

[JLockerman]

Note: we started this discussion back when we were calling these timeseries rather than timevectors so there may be some references to that throughout.

In issue #2 we describe and API to work on "timeseries", ordered-sets of (time, value) pairs, for things like gapfilling, interpolation, and extrapolation. Conceptually, such an API has two components:

1. An aggregation function that aggregates a set of SQL data points into a form more convenient to work on.
2. A "pipeline" of transformations and analysis on the aggregated data that.

One open question we have is what the API for the transformation pipeline should look like. Right now we've thought of three leading candidates with various tradeoffs between them:

1. Regular function calls

   interpolate(sample(timeseries(time,value), '1 minute'))

2. External Pipeline

   -- variant a
   timeseries(time, value) |> sample('1 minute') |> interpolate()
   -- variant b
   timeseries(time, value) |> (sample('1 minute') |> interpolate())

3. Internal Pipeline

   timeseries(time, value, sample('1 minute') |> interpolate())

Below I'll discuss various pros and cons to each approach.

Regular Function Calls

interpolate(sample(timeseries(time,value), '1 minute'))

pros:

• no new syntax; anyone used to using SQL function calls should be able to understand how it works.

cons:

• As the level of nesting increases it becomes difficult to determine which arguments go with which pipeline elements, requiring a spiral reading pattern to determine what's going on.
• The Postgres calling convention forces us to materialize the full Timeseries state between each element of the pipeline, making this the least performant of the options.

External Pipeline

-- variant a
timeseries(time, value) |> sample('1 minute') |> interpolate()
-- variant b
timeseries(time, value) |> (sample('1 minute') |> interpolate())

pros:

• Can be read in a simple left-to-right order, arguments are colocated with their pipeline element.
• The second variant allows us to only materialize the initial and final timeseries values, skipping the materialization of intermediate stages

cons:

• Requires learning about a new operator.
• Error message quality will need to be determined.
• Must still materialize the entire timeseries (though compression may mitigate this)
• Requires either semantic parenthesis (), or the less efficient variant a to also be enabled.

Internal Pipeline

timeseries(time, value, sample('1 minute') |> interpolate())

pros:

• Most efficient; allows us to compute the pipeline incrementally in some cases.
• Better argument co-location and read order than regular function calls.

cons: all the cons of the External Pipeline plus

• Pipeline location is unusual.
• Terminal aggregations eg. feeding a timeseries into avg() may require a different convention.

[JLockerman]

Note that we don't necessarily need to stick to just one of these; from my experiments it looks like multiple of these options can happily coexist, and I expect that we'd likely want to have the Regular Function Calls syntax in addition to the a pipeline one. Still, I think it's worth determining which of these is easiest to learn and use.

[davidkohn88]

One open question we have is what the API for the transformation pipeline should look like. Right now we've thought of three leading candidates with various tradeoffs between them:

1. Regular function calls

   interpolate(sample(timeseries(time,value), '1 minute'))

2. External Pipeline

   -- variant a
   timeseries(time, value) |> sample('1 minute') |> interpolate()
   -- variant b
   timeseries(time, value) |> (sample('1 minute') |> interpolate())

3. Internal Pipeline

   timeseries(time, value, sample('1 minute') |> interpolate())

I'm wondering if the examples become a bit more stark as you introduce arguments to functions in the pipeline, ie suppose interpolate took some arguments, say 'linear' or 'spline' it seems like the regular function call approach becomes much more difficult to read in that case.

[JLockerman]

Another dimension to this is what the textual format of the timeseries should be? This would primarily be used pgdump/restore and quick looks at short time-series. As a straw proposal we could do something like (<time type>, <data type>) { (<time value>, <data valeu>),* } as in

(timestamptz, int) { (2020-01-01, 1), (2020-01-02, 2), (2020-01-02, 4) }

though there are many other options.

[WireBaron]

Looking at the options here, I think I'd cast my vote for the internal pipeline as the cost of materialization might be a bit too high with the other approaches. The extra parens in the external pipeline seem a little unnatural too me, and the fact that it may perform poorly without them is terrible in my mind (if we can detect this case and print an error that would mitigate this case a lot).

Can you explain a bit more what the issue terminal aggregations in the third case are, and why we can't easily work around this?

[kitmacleod]

Just wanted to flag up use of pipeline (pipe operator) in R, esp in Tidyverse. I am not a PostgreSQL expert and I am aware of %wildcard syntax, so just highlighting maybe some learning from R Tidyverse community (not all like the excessive use of the Pipe operator and some comparisons show it is slower than nested function calls, though these are harder for most to read).

[JLockerman]

That does look very similar to what we're discussing here! Some other prior art is the |> operator from F#, and the venerable | from bash.

(@kitmacleod I edited your post slightly for link syntax, but I think the links might be reversed?)

[kvc0]

I may be missing a clear implication here but I’m curious: How will this syntax handle a table with multiple dimension options? E.g.,

time | device_id | country_code | version | pulse_count

How do you look at pulse_count by country_code versus by version or by device_id?
How do you look at a count of devices by version? By country, version?

Also what will it do if pulse_count is a (possibly resetting) counter rather than a value per time interval?

[davidkohn88]

@WarriorOfWire sorry for the late reply! I think I'm a bit confused by what you're looking for here, so I'm not quite sure I'm understanding what you're asking, can you clarify what it is you want to do?

Also I think for now, you might have a look at the counter_agg stuff as a starting point. There you'd probably have to first group by device_id, get a delta, then sum the deltas over country and version? Counters are weird because you have to do the analysis at the device_id level first in this case.

The timeseries api is partially meant for things like that, but not always, i think you'd probably do that analysis using the counter_agg, and then feed the results into the timeseries api for treating it as a full series...but I think everything is a bit early stages anyway. The re-grouping portion definitely gets weird.

Anyway, let us know if I've sort of addressed what you're asking about or if I've entirely misinterpreted it, which is probably pretty likely!

[davidkohn88]

Hmmm...yes, looking again, working with multiple dimensions in the timeseries api itself seems like it would be hard generally, you'd probably have to group by all the things you want (device, country, version, etc.) do an aggregate over that and then do another analysis on top, but perhaps part of the confusion here is that this is not meant to replace the entire standard SQL interface, but just meant to be an add-on on top of it for certain types of things that are harder/less efficient to express in normal SQL.

[kvc0]

So 2A look the way I'd expect for a simplified query api - but I'm not sure what value this has if there isn't something novel that it does over sql, which seems like a high bar to clear ;-)

timeseries(time, value) |> sample('1 minute') |> interpolate()

I think this translates to something like:

select
  time_bucket_gapfill('1 minute', time),
  interpolate( last(value, time) )
[how do you propose FROM to be specified?]
[how do you propose WHERE to be specified?]
group by 1
[does timeseries() in the proposal also imply ORDER BY 1?]

It's hard for me to see where this language expansion is a net improvement. I've got more questions than answers from the best form of the expressions, and even that one is "suboptimal" because it doesn't have semantic parens. I'm imagining myself needing to teach a new language and new subtle caveats to a bunch of smart people who already know how to SQL. IMHO: Why hide from your core value add? Just make more, better time series sql functions, like nonnegative_difference() :-D

[davidkohn88]

Man. I'm clearly missing some of my Github notifications.

Anyway, that's totally valid, and you should definitely file an issue for the nonnegegative_difference function you want.

With that said, the timeseries api does solve some issues that are otherwise very difficult to solve in normal SQL, namely, aggregates cannot otherwise return multiple values, so you need some construct to return a set from an aggregate, once you have that, the question is how do you then do further operations on the data from there (for instance, we need something like this to do LTTB downsampling and ASAP smoothing). This is also a way to reduce the overhead of certain operations considerably, and it will fit into the normal SQL framework in many cases, for instance, the timeseries(time, value) is just an aggregate, think of it kinda like array_agg, so the from and group bys would work just as normal. The order by is implied, and there will be internal representations that even partially "compress" the timeseries object.

There are a few reasons for wanting to do this, one is that there are certain things that SQL just doesn't do, the other is that there are things that the row-oriented SQL approach doesn't do horribly efficiently and this is a way around that for those cases. The efficiency bit is cropping up a lot in Promscale and other places where we've started passing around arrays of times and values and then operating over those, and having a data type to do that better with will be helpful. Anyway, that's some of the thinking behind this and may help, but we'd definitely love to hear more about nonnegative_difference and other functionality you need as well.

[noctarius]

I'm not sure this is a valid use case for what you folks consider a pipeline api, but I wonder if and how I'd merge multiple data streams using the considered pipeline api. Right now it looks like it's just going to simplify function nesting but not being a general use case pipeline calculation api.

An example from our system, that right now is implemented as an external api with raw data from timescale being streamed in, in pseudo code:

queryResult: [created: timestamptz, distance: float4]

dataSet := []
foreach entry in queryResult:
  volume := calculateVolume(silo, entry.distance) // calculates the filling level of the silo
  deltaVolume := lag(volume) - volume
  deltaTime := lag(entry.created) - entry.created
  slope := deltaVolume / deltaTime
  dataSet := dataSet + [{entry.created, volume, slope}]

stdDev := calcStdDev(collectSlopes(dataSet))
median := calcMedian(collectSlopes(dataSet))

buckets := calc_time_buckets(3600, ...)

consumptions := []
foreach bucket in buckets:
  data := []
  foreach entry in dataSet:
    if !bucket.contains(entry):
      continue
    if slope < 0 or slope > median + stdDev * 3:
      continue
    data := data + [slope]
  consumptions := consumptions + [sum(data)]

I guess in the pipeline api, as shown above, it'd be something like that?

SELECT
  time_bucket('3600s', d.data_set.created),
  SUM(d.data_set.slope * 3600)
FROM (
  SELECT
    STDDEV(c.data_set.slope) as std_dev,
    MEDIAN(c.data_set.slope) as median,
    c.data_set
  FROM (
    SELECT created, distance |> map_to_volume(dimension) |> calculate_slope |> map_to_created_volume_slope AS data_set
  ) c
) d
WHERE d.data_set.slope >= 0
  AND d.data_set.slope <= d.median + d.std_dev * 3

What I mean, in the end, I guess, standard operations like filtering and stuff would require multiple nested selects, wouldn't it? I hope the example itself makes sense :D

[JLockerman]

I think it does: you want to transform the data (turning it from height-of-fill to Δvolume), filter the transformed data, then bucket the filtered data?

This seems reasonable, and looking at your example, I have two thoughts:

1. I think we want map() and fold() elements to make it easy to use existing postgres functions within a pipeline.
2. As you noticed, we do not yet have plans to add a filtering element for pipelines. It's a tough tradeoff: on the one hand the lack forces subselects, but on the other hand it currently looks like implementing it would require duplicating much of the SQL expression syntax.

In summary, I think your analysis is spot on 👍 and I'd love to hear your thoughts!