4 ms·

So does this functional SQL just get "transpiled" into analytical SQL? If so, where does "transpilation" occur, in the client, intercepted before hitting the qu

So does this functional SQL just get "transpiled" into analytical SQL? If so, where does "transpilation" occur, in the client, intercepted before hitting the query engine, etc? Or has the query engine itself been modified to handle functional SQL? If the query engine itself has been modified, are there any performance gains or losses? This is a pretty interesting and compelling idea. I do wonder if the functional SQL approach might entice developers to write very bad/expensive/ineffective queries. Then again, nothing is stopping developers from doing the same with analytical SQL.

deleted 5y ago
[deleted]
djk447 5y ago
(NB: Post author here) So this is a great question and we may do a whole post on this at some point in the future. There are definitely ways to shoot yourself in the foot, but hopefully we can avoid at least some of them. The main way this happens now is that the pipeline elements actually return a custom data type that represents the composed set of functions, and we can (for at least part of the pipeline) handle simplification and combination of that into a single set of functions to run over the `timevector`. With that said, there isn't great support for common subexpression elimination in PG right now, we're hoping we can solve some of that with both subqueries and maybe some fun stuff in the planner/executor, but we're going to try to solve that as we think about moving this from experimental into stable. Right now we hardly modify the query engine at all, these are just normal functions that are run and they'll probably remain that way for a while. The optimization here will happen more inside the datastructure or within a row to make sure we're not scanning the same `timevector` multiple times ideally. We do hope to add in some vectorization and other bits as we keep going but not 100% where that's going to go yet. I'm also going to ask a colleague to respond and clarify this cause he's working more heavily on it and I know some of the bits but only some of it...
- chrisjc 5y ago
  > return a custom data type that represents the composed set of functions, and we can (for at least part of the pipeline) handle simplification and combination of that into a single set of functions to run over the `timevector` I'm really having trouble wrapping my head around this idea. I guess it speaks volumes about the potential and power of PostgreSQL in general. Has PostgreSQL ever been used like this in the past, or is this really thinking outside of the box? We don't use TimeScale or have any intention of in the immediate future (Snowflake provides 99% of what we need atm), but I'm always interested and impressed to hear what they're doing.
  - djk447 5y ago
    (NB: Post author here) Yeah. It's a bit mind bending I guess. Will try to explain but might just need a longer format thing and we will probably do a piece on this in the future. Essentially, each function returns a special "PipelineElement" datatype that identifies the function, but not what it's acting on. When you string them together, it returns a "PipelineElement" that represents the composition of all the functions with the correct ordering etc. That can then be applied to the incoming `timevector`. As far as I know, no one's done this before. I don't think it's a completely new idea, Stonebreaker thought about how you can treat code as data and thought that bringing data and code closer together was an important goal of the Postgres project, but this specific way of doing it is pretty new and out there I think! I think most people would think that in order to do this you'd need to do something like modify the parser or something more invasive. (We do use one planner hook on the function call, the expression simplification one that allows us to convert something like `timevector(ts, val) -> sort() -> delta() -> abs() -> sum()` into `timevector(ts, val) -> (sort() -> delta() -> abs() -> sum())` so that all the operations can be done in a single pass. But that's pretty minor. If we need to figure something out around the common subexpression elimination we may need to hook into the planner a little more, we'll see).
    - deleted 5y ago
      [deleted]
    - chousuke 5y ago
      From your description it sounds like you've implemented in PostgreSQL something akin to Haskell's IO, which is also a datastructure representing operations, with ordering enforced by it beind a monad. That sounds pretty cool.
Lockerman 5y ago
(Timescale engineer here) To summarize a bit on what David said here[1]: there are no modifications to the query engine, this is all using Postgres's custom operator support. https://news.ycombinator.com/item?id=28920110 https://news.ycombinator.com/item?id=28920110
- ccleve 5y ago
  Is this just syntactic sugar, then? Is this: SELECT device_id, timevector(ts, val) -> sort() -> delta() -> abs() -> sum() as volatility the same as this? SELECT device_id, sum(abs(delta(sort(timevector(ts, val))))) as volatility
  - djk447 5y ago
    (NB Post author) Kinda. It's close to that, except in order to make it do that we had to actually make the functions return special types so it's more equivalent to something like SELECT device_id, apply(sum, apply(abs, apply(delta, apply(sort, timevector(ts, val)))))) Where each of the items in there is an instance of our special "PipelineElement" type, which defines the function it's applying. Does that make any sense at all? Not sure if I'm explaining this well...
    - ccleve 5y ago
      It does make sense. Thanks.
    - funcDropShadow 5y ago
      Am I right, that functional pipelines substantially decreases the possibilities of the query planner to consider different plans for execution? I am a long-term functional programmer and avid user of advanced PostgreSQL features. But I've always looked at this dichotomy the other way around. How to bring the capability to choose implementations dynamically based on actual statistics to functional programming? Would you say that the reduced flexibility of the query planner --- if I am right about that --- is not important to the target use cases of Timescale? I guess typical timeseries data models are a well understood subset of all relational data models. Therefore the set of useful plans is smaller than usual, I guess.
stingraycharles 5y ago
I can assume that this special syntax allows for better optimizations; the SQL variant is much more flexible, which means you can make much less assumptions, and thus less room for optimizations. Not sure if they’re doing that (especially for the v1 version of what looks to be primarily a syntactic selling point, rather than a perf selling point). But that’s what I would do.
- djk447 5y ago
  (NB: post author here) Yep! you got it exactly right! We're doing it for syntax first and then hopefully go back and start doing interesting performance optimizations on top. There are definitely some things we're thinking about.
- chrisjc 5y ago
  Wow, great explanation! Now I really like this idea!
ccleve 5y ago
Duplicating my question to Lockerman below. Sorry for the double post. Is the -> operator just syntactic sugar? Is this: SELECT device_id, timevector(ts, val) -> sort() -> delta() -> abs() -> sum() as volatility the same as this? SELECT device_id, sum(abs(delta(sort(timevector(ts, val))))) as volatility
- djk447 5y ago
  Sorry I missed before was out grabbing lunch, answered above!
- WireBaron 5y ago
  This is essentially correct. It's a little more complicated inside, and the expansion's really more like: SELECT device_id, arrow_run_pipeline(timevector(ts, val), arrow_add_element(sort(), arrow_add_element(delta(), arrow_add_element(abs(), sum()))); The notable difference here is that this presents a lot more optimization potential, as the entire pipeline can conceivably be applied in one pass through the table.

deleted 5y ago

[deleted]

djk447 5y ago

(NB: Post author here) So this is a great question and we may do a whole post on this at some point in the future. There are definitely ways to shoot yourself in the foot, but hopefully we can avoid at least some of them. The main way this happens now is that the pipeline elements actually return a custom data type that represents the composed set of functions, and we can (for at least part of the pipeline) handle simplification and combination of that into a single set of functions to run over the `timevector`. With that said, there isn't great support for common subexpression elimination in PG right now, we're hoping we can solve some of that with both subqueries and maybe some fun stuff in the planner/executor, but we're going to try to solve that as we think about moving this from experimental into stable. Right now we hardly modify the query engine at all, these are just normal functions that are run and they'll probably remain that way for a while. The optimization here will happen more inside the datastructure or within a row to make sure we're not scanning the same `timevector` multiple times ideally. We do hope to add in some vectorization and other bits as we keep going but not 100% where that's going to go yet. I'm also going to ask a colleague to respond and clarify this cause he's working more heavily on it and I know some of the bits but only some of it...

chrisjc 5y ago
> return a custom data type that represents the composed set of functions, and we can (for at least part of the pipeline) handle simplification and combination of that into a single set of functions to run over the `timevector` I'm really having trouble wrapping my head around this idea. I guess it speaks volumes about the potential and power of PostgreSQL in general. Has PostgreSQL ever been used like this in the past, or is this really thinking outside of the box? We don't use TimeScale or have any intention of in the immediate future (Snowflake provides 99% of what we need atm), but I'm always interested and impressed to hear what they're doing.
- djk447 5y ago
  (NB: Post author here) Yeah. It's a bit mind bending I guess. Will try to explain but might just need a longer format thing and we will probably do a piece on this in the future. Essentially, each function returns a special "PipelineElement" datatype that identifies the function, but not what it's acting on. When you string them together, it returns a "PipelineElement" that represents the composition of all the functions with the correct ordering etc. That can then be applied to the incoming `timevector`. As far as I know, no one's done this before. I don't think it's a completely new idea, Stonebreaker thought about how you can treat code as data and thought that bringing data and code closer together was an important goal of the Postgres project, but this specific way of doing it is pretty new and out there I think! I think most people would think that in order to do this you'd need to do something like modify the parser or something more invasive. (We do use one planner hook on the function call, the expression simplification one that allows us to convert something like `timevector(ts, val) -> sort() -> delta() -> abs() -> sum()` into `timevector(ts, val) -> (sort() -> delta() -> abs() -> sum())` so that all the operations can be done in a single pass. But that's pretty minor. If we need to figure something out around the common subexpression elimination we may need to hook into the planner a little more, we'll see).
  - deleted 5y ago
    [deleted]
  - chousuke 5y ago
    From your description it sounds like you've implemented in PostgreSQL something akin to Haskell's IO, which is also a datastructure representing operations, with ordering enforced by it beind a monad. That sounds pretty cool.

Lockerman 5y ago

(Timescale engineer here) To summarize a bit on what David said here[1]: there are no modifications to the query engine, this is all using Postgres's custom operator support. https://news.ycombinator.com/item?id=28920110 https://news.ycombinator.com/item?id=28920110

ccleve 5y ago
Is this just syntactic sugar, then? Is this: SELECT device_id, timevector(ts, val) -> sort() -> delta() -> abs() -> sum() as volatility the same as this? SELECT device_id, sum(abs(delta(sort(timevector(ts, val))))) as volatility
- djk447 5y ago
  (NB Post author) Kinda. It's close to that, except in order to make it do that we had to actually make the functions return special types so it's more equivalent to something like SELECT device_id, apply(sum, apply(abs, apply(delta, apply(sort, timevector(ts, val)))))) Where each of the items in there is an instance of our special "PipelineElement" type, which defines the function it's applying. Does that make any sense at all? Not sure if I'm explaining this well...
  - ccleve 5y ago
    It does make sense. Thanks.
  - funcDropShadow 5y ago
    Am I right, that functional pipelines substantially decreases the possibilities of the query planner to consider different plans for execution? I am a long-term functional programmer and avid user of advanced PostgreSQL features. But I've always looked at this dichotomy the other way around. How to bring the capability to choose implementations dynamically based on actual statistics to functional programming? Would you say that the reduced flexibility of the query planner --- if I am right about that --- is not important to the target use cases of Timescale? I guess typical timeseries data models are a well understood subset of all relational data models. Therefore the set of useful plans is smaller than usual, I guess.

stingraycharles 5y ago

I can assume that this special syntax allows for better optimizations; the SQL variant is much more flexible, which means you can make much less assumptions, and thus less room for optimizations. Not sure if they’re doing that (especially for the v1 version of what looks to be primarily a syntactic selling point, rather than a perf selling point). But that’s what I would do.

djk447 5y ago
(NB: post author here) Yep! you got it exactly right! We're doing it for syntax first and then hopefully go back and start doing interesting performance optimizations on top. There are definitely some things we're thinking about.
chrisjc 5y ago
Wow, great explanation! Now I really like this idea!

ccleve 5y ago

Duplicating my question to Lockerman below. Sorry for the double post. Is the -> operator just syntactic sugar? Is this: SELECT device_id, timevector(ts, val) -> sort() -> delta() -> abs() -> sum() as volatility the same as this? SELECT device_id, sum(abs(delta(sort(timevector(ts, val))))) as volatility

djk447 5y ago
Sorry I missed before was out grabbing lunch, answered above!
WireBaron 5y ago
This is essentially correct. It's a little more complicated inside, and the expansion's really more like: SELECT device_id, arrow_run_pipeline(timevector(ts, val), arrow_add_element(sort(), arrow_add_element(delta(), arrow_add_element(abs(), sum()))); The notable difference here is that this presents a lot more optimization potential, as the entire pipeline can conceivably be applied in one pass through the table.