Markus Wanner <markus@bluegap.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