Re: On partitioning

On Tue, Dec 9, 2014 at 1:42 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Dec 8, 2014 at 2:56 PM, Andres Freund <andres(at)2ndquadrant(dot)com>
wrote:
> >> I don't think that's mutually exclusive with the idea of
> >> partitions-as-tables. I mean, you can add code to the ALTER TABLE
> >> path that says if (i_am_not_the_partitioning_root) ereport(ERROR, ...)
> >> wherever you want.
> >
> > That'll be a lot of places you'll need to touch. More fundamentally: Why
> > should we name something a table that's not one?
>
> Well, I'm not convinced that it isn't one. And adding a new relkind
> will involve a bunch of code churn, too. But I don't much care to
> pre-litigate this: when someone has got a patch, we can either agree
> that the approach is OK or argue that it is problematic because X. I
> think we need to hammer down the design in broad strokes first, and
> I'm not sure we're totally there yet.

That's right, I think at this point defining the top level behaviour/design
is very important to proceed, we can decide about the better
implementation approach afterwards (may be once initial patch is ready,
because it might not be a major work to do it either way). So here's where
we are on this point till now as per my understanding, I think that direct
operations should be prohibited on partitions, you think that they should be
allowed and Andres think that it might be better to allow direct operations
on partitions for Read.

>
> >> - Direct access to individual partitions to bypass
> >> tuple-routing/query-planning overhead.
> >
> > I think that might be ok in some cases, but in general I'd be very wary
> > to allow that. I think it might be ok to allow direct read access, but
> > everything else I'd be opposed. I'd much rather go the route of allowing
> > to few things and then gradually opening up if required than the other
> > way round (as that pretty much will never happen because it'll break
> > deployed systems).
>
> Why?
>

Because I think it will be difficult for users to write/maintain more of
such
code, which is one of the complaints with previous system where user
needs to write triggers to route the tuple to appropriate partition.
I think in first step we should try to improve the tuple routing algorithm
so that it is not pain for users or atleast it should be at par with some of
the other competitive database systems and if we are not able
to come up with such an implementation, then may be we can think of
providing it as a special way for users to improve performance.

Another reason is that fundamentally partitions are managed internally
to divide the user data in a way so that access could be cheaper and we
take the specifications for defining the partitions from users and allowing
operations on internally managed objects could lead to user writing quite
some code to do what database actually does internally. If we see that
TOAST table are internally used to manage large tuples, however we
don't want users to directly perform dml on those tables.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com