Re: Named vs Unnamed Partitions

On Wed, 2008-01-09 at 21:29 +0100, Dimitri Fontaine wrote:

> Le Wednesday 09 January 2008 19:27:41 Simon Riggs, vous avez écrit :
> > The WHERE clause approach might easily allow more than 2 chunks and they
> > need not be logically contiguous. So the phrase split point doesn't
> > really fit because it implies a one dimensional viewpoint, but I'm happy
> > for you to give it a name.
> 
> Maybe that's only me but I'm not yet clear, after reading this thread and the 
> previous one, whether or not Segment Exclusion would allow for multi-level 
> partitioning.
> 
> I have a use case at the moment, where we load logs-like data from several 
> server to a central one (batch loading), the central table having an 
> extra "server" column to identify each tuple origin. Will SE technique be 
> able to see that this table would be better partitionned by server AND date?

No, but it will be able to handle partitioning on other columns that
provide a degree of differentiation that you possibly hadn't considered
at design time.

> That's what I would have done if it was easier to do with constraint exclusion 
> (did only date partitioning), as the reporting queries will always have some 
> server (stats by services, each service being installed on 1 or more servers) 
> and date restrictions.

Hmm, well if you found declaring the partitions a problem with
constraint exclusion it's not going to be any easier using other
declarative approaches.

So it will work with what you currently use.

You can always use constraint exclusion to separate out the servers and
then segment exclusion to handle the date range.

> Please note I'd be happy to have this use case handled by explicitly 
> specifying the partitioning system I want PostgreSQL to use, and more than 
> happy if you answer me than an automatic transparent code is able to optimize 
> the data on disk for my need without me bothering about partitions, their 
> names and "split points"...
> 
> > If we want to perform manipulations on non-read-only chunks then we need
> > named or numbered partitions, locking, DDL etc.. That seems like too
> > much functionality for what we really need. I really am still open on
> > that point, but I would like to see a good description of a use case
> > that really needs it, rather than just saying "of course we do". Which
> > is exactly where *I* started, even as recently as 3 weeks ago now.
> 
> I like Markus ideas proposing to have SE at work inside partitions or tables, 
> partitions being another kind of relations holding data. Then the DBA who 
> needs to explicitly manage partitions to save faster tablespace for live data 
> is able to tell that to the system and benefit fully from it.

OK, thanks,

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com