Re: Table partition with primary key in 11.3

On Thu, Jun 6, 2019 at 10:03 AM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> If you are saying that you think that Postgres should support primary
> keys that don't necessarily overlap partition keys, then I agree with
> you. Please send a patch to implement that capability.

FWIW, I could probably be convinced to work on the nbtree parts of
global indexes if there was a reasonably worked out design that had
some momentum behind it. I would at least need to know what the
partition identifiers will need to look like in indexes. Andres has
suggested that I work on variable-width table identifiers in nbtree
for the benefit of pluggable storage engines, but I don't have much
enthusiasm for the idea of doing that without delivering a clear
benefit to users in the same release.

It seems pretty obvious to me that the right general approach for
global indexes is to make the partition identifier a tiebreaker column
that comes right before the heap TID tiebreaker column (which would be
per-partition). It probably needs to be variable-width, so that the
overhead isn't too bad most of the time. I don't think that it's
necessary to remove the restriction on the size of relations for
global indexes, though maybe global indexes could focus our attention
on that problem.

--
Peter Geoghegan