Re: Partitioning WIP patch - Mailing list pgsql-hackers

On 2/25/15 7:57 PM, Amit Langote wrote:
> On 26-02-2015 AM 10:31, Jim Nasby wrote:
>> On 2/25/15 7:24 PM, Amit Langote wrote:
>>>>> Does ALTER TABLE parent_monthly_xxxxx_201401 ADD COLUMN foo still
>>>>> operate the same as today? I'd like to see us continue to support that,
>>>>> but perhaps it would be wise to not paint ourselves into that corner
>>>>> just yet.
>>> Nothing prevents that from working, at least at the moment.
>>
>> Ok, but is that what we really want? If we release it that way we'll be
>> stuck with it forever.
>>
>
> AIUI, as far as we stay with a design where partitions (children) are
> individually planned, that might be OK. But, I guess things will get
> more complicated. I think the role of a parent in planning would remain
> limited to drive partition-pruning. Am I missing something?

Isn't the point of adding explicit partitioning to make it faster than 
plain inheritance? Presumably as part of that we'll eventually want to 
NOT plan children individually.

>> I would certainly prefer that we support the capabilities of inheritance
>> along with partitioning (because in some cases you want both). But it's
>> going to limit what we can do internally.
>
> Just to clarify are you referring to inheritance relationship between a
> partitioned table and partitions?

Yes. If it helps, the exact use-case I have in mind is using list-based 
partitioning + additional columns in some/all children (different 
between children). For example, if you need to track different types of 
customer payment methods, you'd have a payment parent table, a list 
partition for credit & debit cards, a different list partition for bank 
accounts, etc.

The reason I'd like to do this with partitioning vs plain inheritance is 
presumably as we build out partitioning we'll get very useful things 
like the ability to have FKs to properly partitioned tables. Insert 
tuple routing could also be useful.

> With explicit partitioning, shouldn't we go in direction where we remove
> some restrictions imposed by inheritance (think multiple inheritance)? I
> recall a link Alvaro had started the discussion with think link to a
> Tom's remark about something very related:
>
> http://www.postgresql.org/message-id/1598.1399826841@sss.pgh.pa.us

That post looks like Tom figured out a way to eliminate a problem that 
hurts inheritance, so that's good.

My fear is that at some point we'll hit a problem with partitioning that 
we can't solve in the inheritance model. If we allow inheritance 
features into partitioning now we'll painted into a corner. If we 
disallow those features now we can always re-enable them if we get to 
the point where we're in the clear.

Does that make sense?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com