8 ms·

Function pipelines: Building functional programming into PostgreSQL

349 points by oconnor663 5y ago

milchada 5y ago
Love how easily you can nest functions/use the output of one as input to the other. Do these functions currently only work with timevector inputs? What if I had some other NxM matrix I wanted to pass through a function pipelines?
tytrdev 5y ago
laughs in datomic
- dkersten 5y ago
  Not everyone can use datomic though, for many reasons (including cost). I'd love to use datomic, but its just not an option for me, while postgres is (and the DigitalOcean hosted postgres is quite affordable too and supports TimescaleDB too).
  - claytonjy 5y ago
    Would that be the community version of Timescale then? Looks like this is a whole new extension, does DO allow you to install your own, or do you think it'd be easy to make a case for them adding this one? (I'm not a DO user myself)
    - djk447 5y ago
      (NB: Post author here) This is in the TimescaleDB Toolkit extension [1] which is licensed under our community license for now and it's not available on DO. It is available on our cloud service fully managed. You can also install it and run it for free yourself. [1]: https://github.com/timescale/timescaledb-toolkit https://github.com/timescale/timescaledb-toolkit
- ithrow 5y ago
  Until you use the very slow and extremely buggy black box in production.
- jaytaylor 5y ago
  For the uninitiated (like me): https://en.m.wikipedia.org/wiki/Datomic https://en.m.wikipedia.org/wiki/Datomic https://en.m.wikipedia.org/wiki/Datalog https://en.m.wikipedia.org/wiki/Datalog It's a distributed database implementation of Prolog.
ghop02 5y ago
While I agree the partition syntax may not be super straightforward, adding a new operator for another way to sort, sum, and abs etc. seems like chaos
- djk447 5y ago
  (NB: Post author here) Totally understand the concern, for now, it’s just for a particular datatype, all of the other operations are happening normally inside your query and you can still use window functions... And btw, I love window functions and will probably be doing a post on them, but they can be a little bit weird and have some really odd ordering properties with aggregates etc. The point of this is really much more about providing choices for folks. But yeah, if we find that people are just getting really confused we could re-consider, that’s part of why we released it experimentally.
  - akulkarni 5y ago
    Also, the team picked `->` as the operator, which I thought was a very natural and intuitive choice :-) e.g., SELECT device_id, timevector(ts, val) -> sort() -> delta() -> abs() -> sum() as volatility
    - OJFord 5y ago
      What if those functions (were something that) returned json{,b}? Oh or I see timevector returns a custom datatype, so I suppose the answer is it has to be one of a fixed number of provided types?
      - djk447 5y ago
        (NB: post author here) We're planning on providing various outputs, json is definitely one format we'd want to support, want to file a github issue with the layout that you'd want and we can start getting some feedback to implement?
      - Lockerman 5y ago
        > Oh or I see timevector returns a custom datatype, so I suppose the answer is it has to be one of a fixed number of provided types? Exactly! These aren't general arithmetic operators, they're specifically for mapping arithmetic operations over timevectors.
ecoulthard 5y ago
I'm looking for a more efficient way of detecting errors in metrics that store running totals, such as Odometer. I use the delta of Odometer to compute Distance. But decreases in Odometer causes incorrect Distance calculations. Odometer should never decrease. It is an error if Odometer decreases. Would function pipelines be able to help me detect decreases in Odometer?
- djk447 5y ago
  (NB: Post author here) Yes! You can use the `delta()` element followed by a `filter()` with a lambda for looking for negative numbers I think!
jbylund 5y ago
What's the explain plan for a pipeline like this look like? In my experience one of the biggest things scaring devs away from moving more logic over the wire towards the db is lubrication between version control and database state. If I end up defining 10s or 100s of custom pipeline operators in git how do I keep that in sync with the db? and am I responsible for doing all that legwork myself?
1point618 5y ago
This is immediately useful. The number of awkwardly nested subqueries that I write because I don't have this kind of functionality in BigQuery is a huge bummer. I am curious, how many Haskell programmers do you all have? And more seriously, do you have any plans to help provide this functionality to non-Postgres SQLs, or at least help those trying to take inspiration from it?
- djk447 5y ago
  (NB: Post author here) We don't have many Haskell programmers, we mostly work in Rust, C and Go, but we're always open to new things... This is pretty Postgres specific. From the beginning Postgres has focused on extensibility and allowed this sort of stuff with custom functions/operators/types. Many other SQL variants don't have that. It's one of the main things that sets Postgres apart from other databases, see Stonebreaker's great history of this, specifically the stuff on Object Relational databases [1]. We're pretty focused on building on top of Postgres because of that functionality. We do have some other stuff to make Postgres more scalable, and you're welcome to try us out, but if there's something specific that BigQuery offers that you need feel free to file a github issue around that too. But yeah, no plans to do things like this in other databases, they just don't have the infrastructure... [1]:https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/papers/SH05.pdf https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/pape...
  - akulkarni 5y ago
    (TimescaleDB co-founder) Agree - what would you need to see in PostgreSQL / TimescaleDB to enable you to switch from Big Query? We are all ears :-)
    - methyl 5y ago
      Not the op, but infinite scalability without any infrastructure work required besides loading the data is what holds me on BigQuery
      - 5y ago
chrisjc 5y ago
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.
darksaints 5y ago
This is really freaking cool. I'm not often impressed by clever hacks in SQL, but this solves a really annoying problem and the specific way which you solved this problem with just custom functions and operators is ingenious and reassuring, as opposed to some of the other crazy ideas that I've seen to extend SQL. I would love to see more about how you implemented the lambdas, as that's the one thing that is still non-obvious to me.
- djk447 5y ago
  (NB: Post author here) We're thinking about doing a post on them at some point in the future! Any specific questions we should answer?
  - darksaints 5y ago
    I guess mostly how the syntax works. The `$$` thing I mostly recognize as being a code block in custom functions, but I'm not sure how it works in the context of a lambda. Also, is `$value` just a special identifier for the input? A separate question...is it possible to have tuple-valued timevectors?
    - djk447 5y ago
      The docs have a bit more on it [1]. The $$ is really just a dollar quoted string[2], you can use any sort of string in there, but dollar quoting just makes it a lot easier to read. On your other question, not right now, but if it's something you want, file a github issue, it's something we've thought a bit about and it gets hairy pretty quickly, but also increases the power of these things dramatically. We'll probably do something with it, but it may be a little while and we may let this stuff bake for a bit first... [1]: https://docs.timescale.com/timescaledb/latest/how-to-guides/hyperfunctions/function-pipelines/#lambda-elements https://docs.timescale.com/timescaledb/latest/how-to-guides/... [2]:https://www.postgresqltutorial.com/dollar-quoted-string-constants/ https://www.postgresqltutorial.com/dollar-quoted-string-cons...
btown 5y ago
To do something similar without custom extensions, it's worth knowing about Postgres's lateral joins, which allow you to reuse columns from the left side of your join in the right side. It basically realizes the "enrich my results" promise that SQL should always have had, and makes incredibly flexible analytics queries possible without ever needing to indent more than once! https://heap.io/blog/postgresqls-powerful-new-join-type-lateral https://heap.io/blog/postgresqls-powerful-new-join-type-late... https://ddrscott.github.io/blog/2017/what-the-sql-lateral/ https://ddrscott.github.io/blog/2017/what-the-sql-lateral/
eska 5y ago
Cool stuff! Out of curiosity: how does the composition of the sort function work? Can you avoid knowing all rows at the point it occurs?
- Lockerman 5y ago
  Not yet, right now all datapoints must be known at the during the sort
ccleve 5y ago
It appears that all these extra functions are written in Rust, but the core Timescale product is written in C. Why did you do it that way? Would you do it differently if you were starting from scratch today?
- djk447 5y ago
  (NB: Post author here!) This is all true! We have a bit more info on that choice in this post [1]. But in general the Toolkit extension [2] is meant to have a bit of a lighter touch than the core TimescaleDB extension [3], it's meant for moving faster and working almost entirely with the custom functions/types/operators type approach rather than the core extension which is integrated much more deeply into the planner & executor and deals with a number of other hooks. It would be possible to do that in Rust, but because it's so deeply integrated with the main Postgres codebase there would just be a lot of translation going on and you'd get a lot less of the benefits of working in Rust. So C is more natural for much of the core DB extension. PGX [4] is also a huge help for writing this sort of extension. The point of Toolkit is really to move much faster, try lots of things, mostly using the more SQL facing API (CREATE FUNCTION, CREATE AGGREGATE and the like). And PGX helps manage all of that really nicely. The rest of Rust being available is also great and we like working in the language, but without PGX it'd be a lot harder and we couldn't move nearly as fast. [1]: https://blog.timescale.com/blog/introducing-hyperfunctions-new-sql-functions-to-simplify-working-with-time-series-data-in-postgresql/ https://blog.timescale.com/blog/introducing-hyperfunctions-n... [2]: https://github.com/timescale/timescaledb-toolkit https://github.com/timescale/timescaledb-toolkit [3]: https://github.com/timescale/timescaledb https://github.com/timescale/timescaledb [4]: https://github.com/zombodb/pgx https://github.com/zombodb/pgx
MattConfluence 5y ago
I rediscovered the joy of working with databases while working on an Elixir application with a Postgres/Timescale DB. Elixir's Ecto library (doesn't really fit the definition of an ORM) lets me compose queries in a functional syntax that then get compiled to SQL before execution. This both enables me to be more productive and results in more readable code that can be reasoned about and be tested easily. Sometimes I still have to dive into "raw" SQL to write e.g. a procedure or a view definition (like Timescale's continuous aggregates), and I definitely miss the pipeline operator when those expressions get complex. SQL is a powerful tool, but it gets clunky and has strange footguns. A feature like what this post describes is definitely a step in the right direction for Postgres. Looking forward to try it out.
- djk447 5y ago
  (NB: Post author here) Glad you liked it! Please do give us feedback especially about how it is to use with your library…will be intrigued to see how they interact. Also , cool to hear about the library more generally, any particularly good syntax you think we should try to learn from?
lincpa 5y ago
Best practices for functional pipelines: - [The Grand Unified Programming Theory: The Pure Function Pipeline Data Flow with Principle-based Warehouse/Workshop Model](https://github.com/linpengcheng/PurefunctionPipelineDataflow https://github.com/linpengcheng/PurefunctionPipelineDataflow) - [Implement relational data model and programming based on hash-map (NoSQL)](https://github.com/linpengcheng/PurefunctionPipelineDataflow/blob/master/doc/relational_model_on_hashmap.md https://github.com/linpengcheng/PurefunctionPipelineDataflow...) - [Everything is RMDB](https://github.com/linpengcheng/PurefunctionPipelineDataflow/blob/master/doc/Everything_is_RMDB.md https://github.com/linpengcheng/PurefunctionPipelineDataflow...) - [Clojure is a FP based on RMDB](https://github.com/linpengcheng/PurefunctionPipelineDataflow/blob/master/doc/Clojure_is_FP_based_on_RMDB.md https://github.com/linpengcheng/PurefunctionPipelineDataflow...)
noisy_boy 5y ago
I think a big value add would be to have print-based debugging features for the functions (I'm not familiar with PostgreSQL custom functions so apologies if that already exists). E.g. a very common scenario while debugging a complicated query is to verify why the output is not as expected and that means repeatedly taking subparts of the query until we zero in on the problem. CTEs make it somewhat better but not ideal. If we have support for some sort of (conditional) print output (separate to the main output), it might help turning them on if/when needed (sort of like loglevels). Just thinking aloud - I'm sure there are lots of practical/conceptual roadblocks with it.
ekzhu 5y ago
Database researcher here. This is really cool! I wonder what was the initial drive for this new feature? Is this meant to be a "short-cut" to express complicated SQL queries, or is this meant to adding new semantics beyond SQL? While I like the idea of custom data types with dataflow-like syntax, implementing a whole new query processing engine for the new data type seems like a lot of engineering work. Also you now have to handle many edge cases such as very very large time series -- I wonder if you have efficient lookup mechanisms on timevectors yet, and various timestamp and value types. If all these new syntax can actually be expressed using SQL, however complex, I think a "lazier" approach is to write a "translator" that rewrites the new syntax into good old SQLs or add a translator at planning stage. This way you can take advantage of Postgres' optimizer and let it do the rest of heavy lifting. Kusto has a similar data type "series" that is also created from aggregating over some columns (https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/make-seriesoperator https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...).
- Lockerman 5y ago
  Timescale engineer here The initial drive for this was some of the work we did on Promscale, along with some observations I made when experimenting with potential optimizations for compression. We saw that a bunch of workloads involved exporting datasets to external tools such as Prometheus and pandas, which would materialize them in memory, and performing analyses there. Furthermore, the queries in the external tool would often be more readable than the equivalent SQL. I'd also observed earlier, while experimenting with decompression optimizations, that, as long as it was small enough, materializing the dataset this way could be surprisingly efficient. This, along with some dissatisfaction in the difficulty of writing certain kinds of queries eventually led to this experiment. > Is this meant to be a "short-cut" to express complicated SQL queries, or is this meant to adding new semantics beyond SQL? This is a surprisingly difficult question to answer; due to Postgres's extensibility, the line around "new semantics" is a bit blurry. I believe that everything we implemented could be desugared into SQL queries, though it's possible it would need some custom functions. However, we don't actually do this yet: right now the pipelines are implemented entirely with custom functions and operators without touching the planner at all. There ended up being a 3-way tradeoff between experimentation speed, execution speed, and generality. While we could theoretically get the best performance on large datasets by by adding a translate pass expanding timevector pipelines into subqueries, this ends up in some difficult and brittle code, which isn't worth implementing at this stage in the experiment. In addition, it doesn't necessarily result in the best code for smaller datasets or less general workloads since there are overheads to its generality. Since our hypothesis right now is that there's a demand for tools to work with smaller datasets, we started out with the simpler implementation. > Kusto has a similar data type "series" that is also created from aggregating over some columns Cool! I'll have to look into it some point. I think Amazon Timestream also has something similar.

349 points by oconnor663 5y ago

Love how easily you can nest functions/use the output of one as input to the other. Do these functions currently only work with timevector inputs? What if I had some other NxM matrix I wanted to pass through a function pipelines?

tytrdev 5y ago

laughs in datomic

dkersten 5y ago
Not everyone can use datomic though, for many reasons (including cost). I'd love to use datomic, but its just not an option for me, while postgres is (and the DigitalOcean hosted postgres is quite affordable too and supports TimescaleDB too).
- claytonjy 5y ago
  Would that be the community version of Timescale then? Looks like this is a whole new extension, does DO allow you to install your own, or do you think it'd be easy to make a case for them adding this one? (I'm not a DO user myself)
  - djk447 5y ago
    (NB: Post author here) This is in the TimescaleDB Toolkit extension [1] which is licensed under our community license for now and it's not available on DO. It is available on our cloud service fully managed. You can also install it and run it for free yourself. [1]: https://github.com/timescale/timescaledb-toolkit https://github.com/timescale/timescaledb-toolkit
ithrow 5y ago
Until you use the very slow and extremely buggy black box in production.
jaytaylor 5y ago
For the uninitiated (like me): https://en.m.wikipedia.org/wiki/Datomic https://en.m.wikipedia.org/wiki/Datomic https://en.m.wikipedia.org/wiki/Datalog https://en.m.wikipedia.org/wiki/Datalog It's a distributed database implementation of Prolog.

ghop02 5y ago

While I agree the partition syntax may not be super straightforward, adding a new operator for another way to sort, sum, and abs etc. seems like chaos

djk447 5y ago
(NB: Post author here) Totally understand the concern, for now, it’s just for a particular datatype, all of the other operations are happening normally inside your query and you can still use window functions... And btw, I love window functions and will probably be doing a post on them, but they can be a little bit weird and have some really odd ordering properties with aggregates etc. The point of this is really much more about providing choices for folks. But yeah, if we find that people are just getting really confused we could re-consider, that’s part of why we released it experimentally.
- akulkarni 5y ago
  Also, the team picked `->` as the operator, which I thought was a very natural and intuitive choice :-) e.g., SELECT device_id, timevector(ts, val) -> sort() -> delta() -> abs() -> sum() as volatility
  - OJFord 5y ago
    What if those functions (were something that) returned json{,b}? Oh or I see timevector returns a custom datatype, so I suppose the answer is it has to be one of a fixed number of provided types?
    - djk447 5y ago
      (NB: post author here) We're planning on providing various outputs, json is definitely one format we'd want to support, want to file a github issue with the layout that you'd want and we can start getting some feedback to implement?
    - Lockerman 5y ago
      > Oh or I see timevector returns a custom datatype, so I suppose the answer is it has to be one of a fixed number of provided types? Exactly! These aren't general arithmetic operators, they're specifically for mapping arithmetic operations over timevectors.

ecoulthard 5y ago

I'm looking for a more efficient way of detecting errors in metrics that store running totals, such as Odometer. I use the delta of Odometer to compute Distance. But decreases in Odometer causes incorrect Distance calculations. Odometer should never decrease. It is an error if Odometer decreases. Would function pipelines be able to help me detect decreases in Odometer?

djk447 5y ago
(NB: Post author here) Yes! You can use the `delta()` element followed by a `filter()` with a lambda for looking for negative numbers I think!

jbylund 5y ago

What's the explain plan for a pipeline like this look like? In my experience one of the biggest things scaring devs away from moving more logic over the wire towards the db is lubrication between version control and database state. If I end up defining 10s or 100s of custom pipeline operators in git how do I keep that in sync with the db? and am I responsible for doing all that legwork myself?

1point618 5y ago

This is immediately useful. The number of awkwardly nested subqueries that I write because I don't have this kind of functionality in BigQuery is a huge bummer. I am curious, how many Haskell programmers do you all have? And more seriously, do you have any plans to help provide this functionality to non-Postgres SQLs, or at least help those trying to take inspiration from it?

djk447 5y ago
(NB: Post author here) We don't have many Haskell programmers, we mostly work in Rust, C and Go, but we're always open to new things... This is pretty Postgres specific. From the beginning Postgres has focused on extensibility and allowed this sort of stuff with custom functions/operators/types. Many other SQL variants don't have that. It's one of the main things that sets Postgres apart from other databases, see Stonebreaker's great history of this, specifically the stuff on Object Relational databases [1]. We're pretty focused on building on top of Postgres because of that functionality. We do have some other stuff to make Postgres more scalable, and you're welcome to try us out, but if there's something specific that BigQuery offers that you need feel free to file a github issue around that too. But yeah, no plans to do things like this in other databases, they just don't have the infrastructure... [1]:https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/papers/SH05.pdf https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/pape...
- akulkarni 5y ago
  (TimescaleDB co-founder) Agree - what would you need to see in PostgreSQL / TimescaleDB to enable you to switch from Big Query? We are all ears :-)
  - methyl 5y ago
    Not the op, but infinite scalability without any infrastructure work required besides loading the data is what holds me on BigQuery
    - 5y ago

chrisjc 5y ago

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.

darksaints 5y ago

This is really freaking cool. I'm not often impressed by clever hacks in SQL, but this solves a really annoying problem and the specific way which you solved this problem with just custom functions and operators is ingenious and reassuring, as opposed to some of the other crazy ideas that I've seen to extend SQL. I would love to see more about how you implemented the lambdas, as that's the one thing that is still non-obvious to me.

djk447 5y ago
(NB: Post author here) We're thinking about doing a post on them at some point in the future! Any specific questions we should answer?
- darksaints 5y ago
  I guess mostly how the syntax works. The `$$` thing I mostly recognize as being a code block in custom functions, but I'm not sure how it works in the context of a lambda. Also, is `$value` just a special identifier for the input? A separate question...is it possible to have tuple-valued timevectors?
  - djk447 5y ago
    The docs have a bit more on it [1]. The $$ is really just a dollar quoted string[2], you can use any sort of string in there, but dollar quoting just makes it a lot easier to read. On your other question, not right now, but if it's something you want, file a github issue, it's something we've thought a bit about and it gets hairy pretty quickly, but also increases the power of these things dramatically. We'll probably do something with it, but it may be a little while and we may let this stuff bake for a bit first... [1]: https://docs.timescale.com/timescaledb/latest/how-to-guides/hyperfunctions/function-pipelines/#lambda-elements https://docs.timescale.com/timescaledb/latest/how-to-guides/... [2]:https://www.postgresqltutorial.com/dollar-quoted-string-constants/ https://www.postgresqltutorial.com/dollar-quoted-string-cons...

btown 5y ago

To do something similar without custom extensions, it's worth knowing about Postgres's lateral joins, which allow you to reuse columns from the left side of your join in the right side. It basically realizes the "enrich my results" promise that SQL should always have had, and makes incredibly flexible analytics queries possible without ever needing to indent more than once! https://heap.io/blog/postgresqls-powerful-new-join-type-lateral https://heap.io/blog/postgresqls-powerful-new-join-type-late... https://ddrscott.github.io/blog/2017/what-the-sql-lateral/ https://ddrscott.github.io/blog/2017/what-the-sql-lateral/

eska 5y ago

Cool stuff! Out of curiosity: how does the composition of the sort function work? Can you avoid knowing all rows at the point it occurs?

Lockerman 5y ago
Not yet, right now all datapoints must be known at the during the sort

ccleve 5y ago

It appears that all these extra functions are written in Rust, but the core Timescale product is written in C. Why did you do it that way? Would you do it differently if you were starting from scratch today?

djk447 5y ago
(NB: Post author here!) This is all true! We have a bit more info on that choice in this post [1]. But in general the Toolkit extension [2] is meant to have a bit of a lighter touch than the core TimescaleDB extension [3], it's meant for moving faster and working almost entirely with the custom functions/types/operators type approach rather than the core extension which is integrated much more deeply into the planner & executor and deals with a number of other hooks. It would be possible to do that in Rust, but because it's so deeply integrated with the main Postgres codebase there would just be a lot of translation going on and you'd get a lot less of the benefits of working in Rust. So C is more natural for much of the core DB extension. PGX [4] is also a huge help for writing this sort of extension. The point of Toolkit is really to move much faster, try lots of things, mostly using the more SQL facing API (CREATE FUNCTION, CREATE AGGREGATE and the like). And PGX helps manage all of that really nicely. The rest of Rust being available is also great and we like working in the language, but without PGX it'd be a lot harder and we couldn't move nearly as fast. [1]: https://blog.timescale.com/blog/introducing-hyperfunctions-new-sql-functions-to-simplify-working-with-time-series-data-in-postgresql/ https://blog.timescale.com/blog/introducing-hyperfunctions-n... [2]: https://github.com/timescale/timescaledb-toolkit https://github.com/timescale/timescaledb-toolkit [3]: https://github.com/timescale/timescaledb https://github.com/timescale/timescaledb [4]: https://github.com/zombodb/pgx https://github.com/zombodb/pgx

MattConfluence 5y ago

I rediscovered the joy of working with databases while working on an Elixir application with a Postgres/Timescale DB. Elixir's Ecto library (doesn't really fit the definition of an ORM) lets me compose queries in a functional syntax that then get compiled to SQL before execution. This both enables me to be more productive and results in more readable code that can be reasoned about and be tested easily. Sometimes I still have to dive into "raw" SQL to write e.g. a procedure or a view definition (like Timescale's continuous aggregates), and I definitely miss the pipeline operator when those expressions get complex. SQL is a powerful tool, but it gets clunky and has strange footguns. A feature like what this post describes is definitely a step in the right direction for Postgres. Looking forward to try it out.

djk447 5y ago
(NB: Post author here) Glad you liked it! Please do give us feedback especially about how it is to use with your library…will be intrigued to see how they interact. Also , cool to hear about the library more generally, any particularly good syntax you think we should try to learn from?

lincpa 5y ago

Best practices for functional pipelines: - [The Grand Unified Programming Theory: The Pure Function Pipeline Data Flow with Principle-based Warehouse/Workshop Model](https://github.com/linpengcheng/PurefunctionPipelineDataflow https://github.com/linpengcheng/PurefunctionPipelineDataflow) - [Implement relational data model and programming based on hash-map (NoSQL)](https://github.com/linpengcheng/PurefunctionPipelineDataflow/blob/master/doc/relational_model_on_hashmap.md https://github.com/linpengcheng/PurefunctionPipelineDataflow...) - [Everything is RMDB](https://github.com/linpengcheng/PurefunctionPipelineDataflow/blob/master/doc/Everything_is_RMDB.md https://github.com/linpengcheng/PurefunctionPipelineDataflow...) - [Clojure is a FP based on RMDB](https://github.com/linpengcheng/PurefunctionPipelineDataflow/blob/master/doc/Clojure_is_FP_based_on_RMDB.md https://github.com/linpengcheng/PurefunctionPipelineDataflow...)

noisy_boy 5y ago

I think a big value add would be to have print-based debugging features for the functions (I'm not familiar with PostgreSQL custom functions so apologies if that already exists). E.g. a very common scenario while debugging a complicated query is to verify why the output is not as expected and that means repeatedly taking subparts of the query until we zero in on the problem. CTEs make it somewhat better but not ideal. If we have support for some sort of (conditional) print output (separate to the main output), it might help turning them on if/when needed (sort of like loglevels). Just thinking aloud - I'm sure there are lots of practical/conceptual roadblocks with it.

ekzhu 5y ago

Database researcher here. This is really cool! I wonder what was the initial drive for this new feature? Is this meant to be a "short-cut" to express complicated SQL queries, or is this meant to adding new semantics beyond SQL? While I like the idea of custom data types with dataflow-like syntax, implementing a whole new query processing engine for the new data type seems like a lot of engineering work. Also you now have to handle many edge cases such as very very large time series -- I wonder if you have efficient lookup mechanisms on timevectors yet, and various timestamp and value types. If all these new syntax can actually be expressed using SQL, however complex, I think a "lazier" approach is to write a "translator" that rewrites the new syntax into good old SQLs or add a translator at planning stage. This way you can take advantage of Postgres' optimizer and let it do the rest of heavy lifting. Kusto has a similar data type "series" that is also created from aggregating over some columns (https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/make-seriesoperator https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...).

Lockerman 5y ago
Timescale engineer here The initial drive for this was some of the work we did on Promscale, along with some observations I made when experimenting with potential optimizations for compression. We saw that a bunch of workloads involved exporting datasets to external tools such as Prometheus and pandas, which would materialize them in memory, and performing analyses there. Furthermore, the queries in the external tool would often be more readable than the equivalent SQL. I'd also observed earlier, while experimenting with decompression optimizations, that, as long as it was small enough, materializing the dataset this way could be surprisingly efficient. This, along with some dissatisfaction in the difficulty of writing certain kinds of queries eventually led to this experiment. > Is this meant to be a "short-cut" to express complicated SQL queries, or is this meant to adding new semantics beyond SQL? This is a surprisingly difficult question to answer; due to Postgres's extensibility, the line around "new semantics" is a bit blurry. I believe that everything we implemented could be desugared into SQL queries, though it's possible it would need some custom functions. However, we don't actually do this yet: right now the pipelines are implemented entirely with custom functions and operators without touching the planner at all. There ended up being a 3-way tradeoff between experimentation speed, execution speed, and generality. While we could theoretically get the best performance on large datasets by by adding a translate pass expanding timevector pipelines into subqueries, this ends up in some difficult and brittle code, which isn't worth implementing at this stage in the experiment. In addition, it doesn't necessarily result in the best code for smaller datasets or less general workloads since there are overheads to its generality. Since our hypothesis right now is that there's a demand for tools to work with smaller datasets, we started out with the simpler implementation. > Kusto has a similar data type "series" that is also created from aggregating over some columns Cool! I'll have to look into it some point. I think Amazon Timestream also has something similar.