Re: Auto creation of Partitions - Mailing list pgsql-hackers

On Wed, 2007-03-07 at 22:32 -0500, Luke Lonergan wrote:
> Andreas,
> 
> On 3/7/07 11:45 AM, "Zeugswetter Andreas ADI SD" <ZeugswetterA@spardat.at>
> wrote:
> 
> > Whoa, do you have anything to back that up ?
> 
> Sure - when we start to consider designs that implement advanced data
> management features, we run into problems with the architecture of
> "tables->tables->tables...".  Here are some examples:
> 1 - people think of partitions as a logical building block for tables, they
> would like to move partitions around underneath a table without the table
> definition being involved.  In the current implementation, there are
> explicit linkages between the table definition and the child tables -
> imagine an "ALTER TABLE foo_parent ADD COLUMN" and how it would need to
> cascade to 1,000 child tables and you get the beginning of it - this
> connection should not exist.

The inheritance can work at multiple levels, so its up to you how you
manage things. If you want to add a column to only the newest tables,
you just add a new mid-level table, add the new column only to that and
then make all new partitions inherit from that table rather than the
main table. So I don't see the objection here, I see a benefit.

> 2 - INSERT/UPDATE/DELETE processing through the SQL rewrite layer (rules) is
> terribly slow and gets slower as you add more partitions.  If done closer to
> the storage layer, this can be done in ways that use access methods shared
> with other storage entities, e.g. Indices, and the code path would flow more
> naturally.

That bit is just syntactic sugar and unnecessary, for most applications,
in my experience - especially because it doesn't work with COPY.

People do seem to want it, so as I said upthread, we need a way of
speeding up the selection of the appropriate partition, so we can get
this to work for individual INSERTs.

This needs substantial improvement, but I don't see this challenging the
partition == table assumption.

> 3 - Parallel query can be accomplished more easily by separating scans
> across relations split among tablespaces.  This is more natural than trying
> to parallelize APPEND nodes within existing plans

Tables-> Partitions -> Tablespaces

Changing Partitions from Tables to SomethingElse won't increase the
current capability to define the disk layout for concurrent I/O.

Parallel Query needs to be planner-aware, so if we change partitions
from being tables, then we'd need to reintroduce them to the planner.
APPEND is clearly not the only thing that's needed for parallel query.

Note to Nikhil: Make sure the new syntax doesn't prevent partitions from
being placed upon multiple tablespaces in some manner, at CREATE TABLE
time.

> > You would need to elaborate what you actually mean, but I think it is
> > moot.
> > Sure, the constraint technique can be further extended (e.g. during
> > runtime), but imho the approach is very good.
> 
> Well, it's being used and that's good, but it needs to be better IMO and I
> think that before we go too far down the current path we should consider the
> alternatives more carefully.

I'm happy to reconsider things, but we need to do that with some clear
analysis of what doesn't work yet and how best to implement that.

None of what's been mentioned requires us to reconsider the 
Partition == Table assumption. There were other ways considered, but
they didn't win out in the analysis, for documented reasons.

If there are benefits to having partitions act like tables, then maybe
we can make them behave differently in just those circumstances. Pack
animals behave differently in a group, so why not tables, if need be?
But when is that exactly?

There is a ton of work to make partitioning the elegant beast we'd like
it to be, but that seems like extension only, not rip and replace.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com