5 ms·

This is exciting. That said, it's important to note that this is unlikely to help much with most OLTP queries just looking up information quickly via indexed da

This is exciting. That said, it's important to note that this is unlikely to help much with most OLTP queries just looking up information quickly via indexed data. Where this could really shine is CPU-bound queries that are performing complex operations, where the SQL interpreter is actually a bottleneck. I wonder if AWS will incorporate these improvements into Redshift, which is Postgres forked for OLAP use cases where this is far more prevalent. In the past, they haven't done a great job at this (ran into a float bug fixed upstream 5+ years ago), but this may be a big enough value proposition to justify the effort.

anarazel 9y ago
> That said, it's important to note that this is unlikely to help much with most OLTP queries just looking up information quickly via indexed data. It'll definitely not help for that, at least for now. There's not yet any caching around JITed programs, which makes it completely unsuitable for OLTP. With caching (which requires some larger changes to postgres, which is why this won't be in v11), it's possible to observe some moderate gains. > I wonder if AWS will incorporate these improvements into Redshift, which is Postgres forked for OLAP use cases where this is far more prevalent. In the past, they haven't done a great job at this (ran into a float bug fixed upstream 5+ years ago), but this may be a big enough value proposition to justify the effort. I can't imagine them doing that. This uses a whole rewritten expression evaluation engine (introduced in v10), and a large number of infrastructure changes all over the executor (reimplementation of targetlist set returning functions, projection, ...). Not impossible, but certainly not something lightly done.
- tejasmanohar 9y ago
  There's not yet any caching around JITed programs, which makes it completely unsuitable for OLTP. Oh, I see. Thanks for clarifying! You mentioned it was merged. Does this mean it'll land in stable soon? Will it be behind a flag, or does the planner just know when to use it? I can't imagine them doing that. RE: Redshift, That's very unfortunate :|. I suppose that's another downside of AWS forking vs. say Citus who moved to the extension model. Though, this would really be perfect for Redshift's use cases... so I guess we'll see. -- This is a step in the right direction regardless, but I'm curious how many people are specifically using pure Postgres for large-scale OLAP models (not including occasionally running one-off queries on their OLTP data).
  - anarazel 9y ago
    > Oh, I see. Thanks for clarifying! You mentioned it was merged. Does this mean it'll land in stable soon? It has been merged into the master branch, which'll become PostgreSQL 11. First beta will be in a few weeks. Release is planned to be in September (early I'd hope). > Will it be behind a flag, or does the planner just know when to use it? There's cost based logic that chooses it, yes. See https://www.postgresql.org/docs/devel/static/jit-decision.html https://www.postgresql.org/docs/devel/static/jit-decision.ht... You can change those if you want to force JIT on/off.
ankrgyl 9y ago
> I wonder if AWS will incorporate these improvements into Redshift, which is Postgres forked for OLAP use cases where this is far more prevalent. In the past, they haven't done a great job at this (ran into a float bug fixed upstream 5+ years ago), but this may be a big enough value proposition to justify the effort. Redshift is forked from ParAccel, which was originally forked from Postgres several years ago. ParAccel has had code generation (via transpilation to C) since long before Amazon even purchased its source code. In other words, Redshift has had this feature since its inception, albeit via C-transpilation (compiled with GCC) not LLVM codegen. In addition, Redshift's primary index is a column store which is particularly well-suited to code generation (you can inline and even vectorize expressions across rows, without hopping pointers in between rows). Postgres does not yet include a column store, but this piece of infrastructure is a big step in the right direction!
- tejasmanohar 9y ago
  Oh, neat! I hadn't heard of ParAccel. Suppose I'm missing some history here.
polskibus 9y ago
Isn't it against AWS interest to boost base postgresql or reduce differentiation against base postgresql? I wish they contributed to postgresql but that would weaken the lock-in of their core offering.
- tejasmanohar 9y ago
  Yes and no. Maybe yes for OLAP features but not in general, because Redshift isn’t fit for everything, and they have plain RDS Postgres offerings. Also, my original post questioned whether they would bring in improvements from Postgres core into their fork (Redshift)... in case you read the opposite. I know the RDS team has contributed some fixes and the like to Postgres, but I’m unsure of their overall significance.
  - anarazel 9y ago
    > I know the RDS team has contributed some fixes and the like to Postgres, but I’m unsure of their overall significance Negligible.

anarazel 9y ago

> That said, it's important to note that this is unlikely to help much with most OLTP queries just looking up information quickly via indexed data. It'll definitely not help for that, at least for now. There's not yet any caching around JITed programs, which makes it completely unsuitable for OLTP. With caching (which requires some larger changes to postgres, which is why this won't be in v11), it's possible to observe some moderate gains. > I wonder if AWS will incorporate these improvements into Redshift, which is Postgres forked for OLAP use cases where this is far more prevalent. In the past, they haven't done a great job at this (ran into a float bug fixed upstream 5+ years ago), but this may be a big enough value proposition to justify the effort. I can't imagine them doing that. This uses a whole rewritten expression evaluation engine (introduced in v10), and a large number of infrastructure changes all over the executor (reimplementation of targetlist set returning functions, projection, ...). Not impossible, but certainly not something lightly done.

tejasmanohar 9y ago
There's not yet any caching around JITed programs, which makes it completely unsuitable for OLTP. Oh, I see. Thanks for clarifying! You mentioned it was merged. Does this mean it'll land in stable soon? Will it be behind a flag, or does the planner just know when to use it? I can't imagine them doing that. RE: Redshift, That's very unfortunate :|. I suppose that's another downside of AWS forking vs. say Citus who moved to the extension model. Though, this would really be perfect for Redshift's use cases... so I guess we'll see. -- This is a step in the right direction regardless, but I'm curious how many people are specifically using pure Postgres for large-scale OLAP models (not including occasionally running one-off queries on their OLTP data).
- anarazel 9y ago
  > Oh, I see. Thanks for clarifying! You mentioned it was merged. Does this mean it'll land in stable soon? It has been merged into the master branch, which'll become PostgreSQL 11. First beta will be in a few weeks. Release is planned to be in September (early I'd hope). > Will it be behind a flag, or does the planner just know when to use it? There's cost based logic that chooses it, yes. See https://www.postgresql.org/docs/devel/static/jit-decision.html https://www.postgresql.org/docs/devel/static/jit-decision.ht... You can change those if you want to force JIT on/off.

ankrgyl 9y ago

> I wonder if AWS will incorporate these improvements into Redshift, which is Postgres forked for OLAP use cases where this is far more prevalent. In the past, they haven't done a great job at this (ran into a float bug fixed upstream 5+ years ago), but this may be a big enough value proposition to justify the effort. Redshift is forked from ParAccel, which was originally forked from Postgres several years ago. ParAccel has had code generation (via transpilation to C) since long before Amazon even purchased its source code. In other words, Redshift has had this feature since its inception, albeit via C-transpilation (compiled with GCC) not LLVM codegen. In addition, Redshift's primary index is a column store which is particularly well-suited to code generation (you can inline and even vectorize expressions across rows, without hopping pointers in between rows). Postgres does not yet include a column store, but this piece of infrastructure is a big step in the right direction!

tejasmanohar 9y ago
Oh, neat! I hadn't heard of ParAccel. Suppose I'm missing some history here.

polskibus 9y ago

Isn't it against AWS interest to boost base postgresql or reduce differentiation against base postgresql? I wish they contributed to postgresql but that would weaken the lock-in of their core offering.

tejasmanohar 9y ago
Yes and no. Maybe yes for OLAP features but not in general, because Redshift isn’t fit for everything, and they have plain RDS Postgres offerings. Also, my original post questioned whether they would bring in improvements from Postgres core into their fork (Redshift)... in case you read the opposite. I know the RDS team has contributed some fixes and the like to Postgres, but I’m unsure of their overall significance.
- anarazel 9y ago
  > I know the RDS team has contributed some fixes and the like to Postgres, but I’m unsure of their overall significance Negligible.