Re: Partitioning wiki page - Mailing list pgsql-hackers

Hi Robert,

Thanks all for the time you are spending commenting on this during this 
busy Commit Fest.
> I am not a fan of the proposed syntax.  It is conceptually similar to
> what we already do with constraints, but invents a whole new syntax to
> no obvious benefit that I can see.  
Actually I did not invent a new syntax but tried to map the Oracle 
syntax which seems to be a requirement that appeared at multiple 
occasions in the discussions. The question is more whether Postgres 
wants to have its own syntax (and there might be good reasons for that) 
or if we want to use a syntax similar to other databases for easier 
migration (after all Postgres is very late on that topic compared to 
other databases).
> I think we would do well to look
> at what other systems besides Oracle do, as well as considering any
> newer ideas Oracle may have introduced.  Perhaps this would be a good
> thing to add to the Wiki page - instead of saying, this is the design,
> say, here are some different possibilities, what do we like?
>   
Maybe I was not clear on the intent of the wiki page. I was not trying 
to pretend that this was the design but I was just trying to do my 
homework on what are the issues that we will have to address (that's 
basically the section headers) and I just filled the sections with what 
I understood so far from the discussions, the Oracle and MySQL 
documentations.
So yes, there is more work to do on understanding what other systems 
besides Oracle & MySQL do, and then figuring out what we want to propose 
in Postgres.
>> Oracle's new interval partitioning sounds great, but it ignores the
>> reality that most data varies considerably over time, either growing or
>> fluctuating. I much prefer the idea of a size-equalized partitioning
>> scheme, as implemented by Coppereye's Greenwich. That method gives equal
>>     
>
> Sometimes (though certainly not always), the structure of the
> underling data makes interval partitioning a win, as when for example
> you are accumulating transactions that are billed at the end of each
> month.  If you do a lot of queries on the open transactions for the
> current month, you want to make sure that there's a partition break at
> the start of the month so that you're not unnecessarily scanning some
> of the previous month's entries.
>   
Partitions of similar sized are usually achieved by hash partitioning 
(in both Oracle and MySQL). There are many use cases where 
range/interval partitioning makes a lot of sense. One use case that was 
mentioned to me is multi-level hierarchies like year->month->week->day 
that would require more than 2 levels of partitioning. I think we can 
think of a way of having an arbitrary depth of partitions even though 
partition management might become slightly more complex/

I will try to integrate the comments as I see them on the mailing list 
but feel free also to update the wiki page with your thoughts and use 
cases.

Thanks again for the feedback,
Emmanuel

-- 
Emmanuel Cecchet
Aster Data Systems
Web: http://www.asterdata.com