4 ms·

Just...wow. * Native partitioning * Parallel query Honestly, some very welcome quality of life improvements for use cases even outside of what I would consid

Just...wow. * Native partitioning * Parallel query Honestly, some very welcome quality of life improvements for use cases even outside of what I would consider "Big Data". > This means that users no longer need to create triggers for routing data; it's all handled by the system. Trigger routing has always been a performance foot gun...to the point that it's sometimes better to handle table routing in application logic. I wonder what the performance is comparatively between this new "native" partitioning and existing methods (triggers and rules) and whether or not this makes application routing always inferior.

koolba 9y ago
Parallel query was already there in 9.6 though this release improves on it further.
- Tostino 9y ago
  It makes parallel query much more useful. There were very few places that parallel query helped with my workload in 9.6, that is not true for 10.
  - snuxoll 9y ago
    Parallel index scan is going to be huge for me, I’ve got a table with 80 million rows I have to constantly dig through, indexes help a lot but when they get so stinking big being limited to one thread really hampers performance.
    - deleted 9y ago
      [deleted]
adrianratnapala 9y ago
I worry that having things "all handled by the system" can be even more of a performance footgun. I mean that users will be able to do things that are allowed by the docs, but which result in, expensive and hard-to-see things are happening under the hood.
- flukus 9y ago
  This is a valid concern, I've seen simple automatic optimizations backfire before. An example I remember from MS Sql Server (older versions) of a very simple optimization of using indexes can backfire with the dynamic filter pattern: where ((@foo is null) or (foo = @foo)) and ((@bar is null) or (bar = @bar)) Depending on statistics, order of execution, indexes in place and phase of the moon this could produce fast results or have to revert to a full table scan (because it uses a cached query plan with the wrong index). Devs still have some awareness of how queries translate to actual running instructions and to measure that the results are in line with what is expected.
  - mst 9y ago
    Modern pg libraries only cache the plan with the query params involved as well so this generally doesn't happen. (I made it happen with an older version of things and ... yes, argh)
- mst 9y ago
  This is a completely reasonable worry though I must note that postgres has done it way more rarely than anything else I've dealt with which may explain the voting response to this comment.
bonesmoses 9y ago
So far as the speed is concerned, we ran some tests a while back and it came back as nearly native performance when compared to interacting with a non-partitioned table: https://blog.2ndquadrant.com/pg-phriday-dearly-de-parted/ https://blog.2ndquadrant.com/pg-phriday-dearly-de-parted/
- gshulegaard 9y ago
  That's really awesome to hear. I think it is fair to say that app-level partitioning on top of Postgres is now obsolete.

koolba 9y ago

Parallel query was already there in 9.6 though this release improves on it further.

Tostino 9y ago
It makes parallel query much more useful. There were very few places that parallel query helped with my workload in 9.6, that is not true for 10.
- snuxoll 9y ago
  Parallel index scan is going to be huge for me, I’ve got a table with 80 million rows I have to constantly dig through, indexes help a lot but when they get so stinking big being limited to one thread really hampers performance.
  - deleted 9y ago
    [deleted]

adrianratnapala 9y ago

I worry that having things "all handled by the system" can be even more of a performance footgun. I mean that users will be able to do things that are allowed by the docs, but which result in, expensive and hard-to-see things are happening under the hood.

flukus 9y ago
This is a valid concern, I've seen simple automatic optimizations backfire before. An example I remember from MS Sql Server (older versions) of a very simple optimization of using indexes can backfire with the dynamic filter pattern: where ((@foo is null) or (foo = @foo)) and ((@bar is null) or (bar = @bar)) Depending on statistics, order of execution, indexes in place and phase of the moon this could produce fast results or have to revert to a full table scan (because it uses a cached query plan with the wrong index). Devs still have some awareness of how queries translate to actual running instructions and to measure that the results are in line with what is expected.
- mst 9y ago
  Modern pg libraries only cache the plan with the query params involved as well so this generally doesn't happen. (I made it happen with an older version of things and ... yes, argh)
mst 9y ago
This is a completely reasonable worry though I must note that postgres has done it way more rarely than anything else I've dealt with which may explain the voting response to this comment.

bonesmoses 9y ago

So far as the speed is concerned, we ran some tests a while back and it came back as nearly native performance when compared to interacting with a non-partitioned table: https://blog.2ndquadrant.com/pg-phriday-dearly-de-parted/ https://blog.2ndquadrant.com/pg-phriday-dearly-de-parted/

gshulegaard 9y ago
That's really awesome to hear. I think it is fair to say that app-level partitioning on top of Postgres is now obsolete.