Re: Partitioning: issues/ideas (Was: Re: On partitioning)

On 1/25/15 7:42 PM, Amit Langote wrote:
> On 21-01-2015 PM 07:26, Amit Langote wrote:
>> Ok, I will limit myself to focusing on following things at the moment:
>>
>> * Provide syntax in CREATE TABLE to declare partition key
>
> While working on this, I stumbled upon the question of how we deal with
> any index definitions following from constraints defined in a CREATE
> statement. I think we do not want to have a physical index created for a
> table that is partitioned (in other words, has no heap of itself). As
> the current mechanisms dictate, constraints like PRIMARY KEY, UNIQUE,
> EXCLUSION CONSTRAINT are enforced as indexes. It seems there are really
> two decisions to make here:
>
> 1) how do we deal with any index definitions (either explicit or
> implicit following from constraints defined on it) - do we allow them by
> marking them specially, say, in pg_index, as being mere
> placeholders/templates or invent some other mechanism?
>
> 2) As a short-term solution, do we simply reject creating any indexes
> (/any constraints that require them) on a table whose definition also
> includes PARTITION ON clause? Instead define them on its partitions (or
> any relations in hierarchy that are not further partitioned).
>
> Or maybe I'm missing something...

Wasn't the idea that the parent table in a partitioned table wouldn't actually have a heap of it's own? If there's no heap there can't be an index.

That said, I think this is premature optimization that could be done later.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com