Re: Declarative partitioning - another take

Hi Amit,

On 12/13/2016 09:45 AM, Amit Langote wrote:
> On 2016/12/13 0:17, Tomas Vondra wrote:
>> On 12/12/2016 07:37 AM, Amit Langote wrote:
>>>
>>> Hi Tomas,
>>>
>>> On 2016/12/12 10:02, Tomas Vondra wrote:
>>>>
>>>> 2) I'm wondering whether having 'table' in the catalog name (and also in
>>>> the new relkind) is too limiting. I assume we'll have partitioned indexes
>>>> one day, for example - do we expect to use the same catalogs?
>>>
>>> I am not sure I understand your idea of partitioned indexes, but I doubt
>>> it would require entries in the catalog under consideration. Could you
>>> perhaps elaborate more?
>>>
>>
>> OK, let me elaborate. Let's say we have a partitioned table, and I want to
>> create an index. The index may be either "global" i.e. creating a single
>> relation for data from all the partitions, or "local" (i.e. partitioned
>> the same way as the table).
>>
>> Local indexes are easier to implement (it's essentially what we have now,
>> except that we need to create the indexes manually for each partition),
>> and don't work particularly well for some use cases (e.g. unique
>> constraints). This is what I mean by "partitioned indexes".
>>
>> If the index is partitioned just like the table, we probably won't need to
>> copy the partition key info (so, nothing in pg_partitioned_table).
>> I'm not sure it makes sense to partition the index differently than the
>> table - I don't see a case where that would be useful.
>>
>> The global indexes would work better for the unique constraint use case,
>> but it clearly contradicts our idea of TID (no information about which
>> partition that references).
>>
>> So maybe the catalog really only needs to track info about tables? Not
>> sure. I'm just saying it'd be unfortunate to have _table in the name, and
>> end up using it for indexes too.
>
> Hmm, I didn't quite think of the case where the index is partitioned
> differently from the table, but perhaps that's possible with some other
> databases.
>

I haven't thought about that very deeply either, so perhaps it's an
entirely silly idea. Also, probably quite complex to implement I guess,
so unlikely to be pursued soon.

> What you describe as "local indexes" or "locally partitioned indexes" is
> something I would like to see being pursued in the near term. In that
> case, we would allow defining indexes on the parent that are recursively
> defined on the partitions and marked as inherited index, just like we have
> inherited check constraints and NOT NULL constraints. I have not studied
> whether we could implement (globally) *unique* indexes with this scheme
> though, wherein the index key is a superset of the partition key.
>

I think implementing UNIQUE constraint with local indexes is possible
and possibly even fairly simple, but it likely requires SHARE lock on
all partitions, which is not particularly nice.

When the partition key is referenced in the constraint, that may allow
locking only a subset of the partitions, possibly even a single one. But
with multi-level partitioning schemes that may be difficult.

Also, I don't think it's very likely to have the partitioning key as
part of the unique constraint. For example 'users' table is unlikely to
be distributed by 'login' and so on.

The global indexes make this easier, because there's just a single
index to check. But of course, attaching/detaching partitions gets more
expensive.

Anyway, starting a detailed discussion about local/global indexes was
not really what I meant to do.

>> Clearly, this is a consequence of building the partitioning on top of
>> inheritance (not objecting to that approach, merely stating a fact).
>>
>> I'm fine with whatever makes the error messages more consistent, if it
>> does not make the code significantly more complex. It's a bit confusing
>> when some use 'child tables' and others 'partitions'. I suspect even a
>> single DML command may return a mix of those, depending on where exactly
>> it fails (old vs. new code).
>
> So, we have mostly some old DDL (CREATE/ALTER TABLE) and maintenance
> commands that understand inheritance. All of the their error messages
> apply to partitions as well, wherein they will be referred to as "child
> tables" using old terms. We now have some cases where the commands cause
> additional error messages for only partitions because of additional
> restrictions that apply to them. We use "partitions" for them because
> they are essentially new error messages.
>
> There won't be a case where single DML command would mix the two terms,
> because we do not allow mixing partitioning and regular inheritance.
> Maybe I misunderstood you though.
>

Don't we call inheritance-related functions from the new DDL? In that
case we'd fail with 'child tables' error messages in the old code, and
'partitions' in the new code. I'd be surprised if there was no such code
reuse, but I haven't checked.

>> Am I right that one of the ambitions of the new partitioning is to improve
>> behavior with large number of partitions?
>
> Yes. Currently, SELECT planning is O(n) with significantly large constant
> factor. It is possible now to make it O(log n). Also, if we can do away
> with inheritance_planner() treatment for the *partitioned tables* in case
> of UPDATE/DELETE, then that would be great. That would mean their
> planning time would be almost same as the SELECT case.
>
> As you might know, we have volunteers to make this happen sooner [1], :)
>

Yes, I know. And it's great that you've managed to make the first step,
getting all the infrastructure in, allowing others to build on that.
Kudos to you!

>> At first I thought it's somewhat related to the FDW sharding (each node
>> being a partition and having local subpartitions), but I realize the
>> planner will only deal with the node partitions I guess.
>
> Yeah, planner would only have the local partitioning metadata at its
> disposal. Foreign tables can only be leaf partitions, which if need to be
> scanned for a given query, will be scanned using a ForeignScan.
>

Right, makes sense. Still, I can imagine for example having many daily
partitions and not having to merge them regularly just to reduce the
number of partitions.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services