Hacker News Reader

Comment by gshulegaard | Hacker News Reader

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.

View on HN

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/

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

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

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.

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.

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.

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.

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)

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.

Reply on news.ycombinator.com