Re: Automating Partitions in PostgreSQL - Query on syntax

On Tue, Apr 21, 2009 at 2:29 PM, steven king <vacuum(at)quantentunnel(dot)de> wrote:
>> Rather than SWITCH <expression> CASE <value> ... you probably would
>> want to reuse the existing PostgreSQL syntax of CASE <expression> WHEN
>> <value>...
>
> I think - at first we've to ask for the problem we have to solve.
>
> The syntax it isnt. If we get confused with CASE of CASE THEN ELSE - we can use other keywords .. forinstance SWITCH <expression> ON <value> USE ... that should not the problem.
>
> You talking about 1000s of partitions - I cant see that this is the major use-case of table partitioning .. Who wants thousands of partitions?

It's come up in previous discussions on pgsql-hackers. Besides, even
if you have only 100 partitions, a binary search requires only 8
comparisons whereas a linear search through a list requires 100. I'd
be hesistant to say that doesn't matter without testing it; I suspect
it WILL matter.

> We simply need a tool to create partitions for common use-cases. Maybe we should provide two or more types of partitioning strategies.
>
> 1. key-range partitioning
> 2. constraint exclusion partitioning
> 3.? auto-partitioning (for performance issues only)

Yep, probably. I don't think a "tool to create partitions for common
use-cases" is very interesting. To really make partitioning work in a
simple, reliable, and efficient fashion, you're going to need to do a
lot more than that. Of course if you just want to make a simple tool,
that's fine too, but in that case I'd recommend designing it as an
add-on rather than something that modifies the core syntax of
PostgreSQL, because I doubt you're going to have much luck getting a
patch of that type accepted.

...Robert