Re: Hash partitioning.

Markus Wanner <markus(at)bluegap(dot)ch> wrote:
> On 06/25/2013 11:52 PM, Kevin Grittner wrote:
>> At least until we have parallel
>> query execution.  At *that* point this all changes.
>
> Can you elaborate on that, please? I currently have a hard time
> imagining how partitions can help performance in that case,
> either.

Well, partitioning will *still* be a net loss for overall
throughput on a machine with enough active connections to keep all
the resources busy.  Where it will help is when you have a machine
with a lot of cores and a few big "reporting" style queries.  Since
we currently can only use one core for a single query, we leave a
lot of CPU time (often the bottleneck for such queries) unused.  If
we allow a large query to search multiple partitions in parallel, a
big query can complete sooner.  It will consume more resources
overall in doing so, but if those resources would otherwise sit
idle, it could be a big win for some use cases.

> Put another way: ideally, the system should take care of
> optimally distributing data across its physical storage itself.

Agreed.  That's not where I see the win.  Most cases where I've
seen people attempt to micro-manage object placement have performed
worse than somply giving the OS a big RAID (we had 40 spindles in
some of ours at Wis. Courts) and letting the filesystem figure it
out.  There are exceptions for special cases like WAL.  I found out
by accident how much that can matter.

> If you need to do partitioning manually for performance reasons,
> that's actually a deficiency of it, not a feature.

+1

> I certainly agree that manageability may be a perfectly valid
> reason to partition your data.

It can definitely help there.

> Maybe there even exist other good reasons.

I'm arguing that better concurrency can be one in the future.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company