Re: POC: Comparison of partitioning key values

Hi John,

On Fri, Apr 17, 2026 at 5:35 AM John Mikk <jomikk2706(at)gmail(dot)com> wrote:
>
> Dear David, thank you for the detailed response.
>
> I understand your concerns, so I have rethought my approach a bit and would like to discuss,
> in general terms, the concept that I would try to implement.
>
> The ability to define a B-tree operator class (opclass) in the clause:
> `PARTITION BY RANGE ( { column_name | ( expression ) } [ opclass ] [, ...] )`
> allows partitioning over a fairly broad class of sets with a defined order relation.
> For example, one can obtain an elegant example using an extension
> for ordinary fractions (p/q) represented as `row(p,q)` with a natural
> B-tree operator class for the order relation of ordinary fractions:
>
> ```sql
> drop table if exists axis cascade;
>
> create table axis (
> id serial,
> key fraction,
> label text
> ) partition by range (key fraction_ops);
>
> create table segment_1 partition of axis for values from ((0,1)::fraction) to ((1,3)::fraction);
> create table segment_2 partition of axis for values from ((2,5)::fraction) to ((4,5)::fraction);
> create table segment_3 partition of axis for values from ((15,45)::fraction) to ((2,5)::fraction);
> -- segment_1,2,3 : [0, 1/3], [2/5, 4/5], [1/3, 2/5], where 15/45 == 1/3
>
> insert into axis(key,label) select (1,5)::fraction, '1/5';
> -- insert to segment_1
> insert into axis(key,label) select (1,2)::fraction, '1/2';
> -- insert to segment_2
> insert into axis(key,label) select (1,3)::fraction, '1/3';
> -- insert to segment_3
> ```
>
> However, for multidimensional data structures where one desires
> a multidimensional partitioning key using a B-tree, the necessary ordering cannot be established.
> It is easy to prove that when attempting to introduce
> the concept of "to the left" (less than) / "to the right" (greater than) for rectangles on a plane,
> the transitivity of such a relation is violated.
>
> To achieve the intended goal,
> it would likely be necessary to use the GiST access method.
> According to the documentation, however,
> only B-tree is applicable when defining an operator class for a range partitioning key.
>
> **Proposal:** Make GiST available for partitioning in the `opclass` clause of `PARTITION BY RANGE`.

Thanks for the follow-up and the fraction example. The fraction case
already works under RANGE today, as you show, and nothing needs to
change there, or at least that's how I read that part.

I agree with David that the grid case wants a new strategy rather than
a reinterpretation of RANGE, and I'd push back on making GiST
available in RANGE's opclass slot specifically. The GiST opclasses
you'd actually want for this use case describe overlap and
containment, not order, so applying the RANGE machinery here seems
wrong to me. PARTITION BY RANGE has been around for about ~9 years and
most users understand what it means, so making it accept GiST
opclasses would be more confusing than helpful. But the fact that you
reached for GiST tells me the grid case wants something spatial, and
I've sometimes wondered whether something along the lines of PARTITION
BY BOX for rectangular partitioning could work as a new strategy. The
per-dimension non-overlap check you describe would cover partition
creation. The same logic applies to tuple routing and pruning, though
the algorithms and data structures would require careful design to
scale with many partitions.

Part of what got me thinking about BOX partitioning is the growth of
pgvector, where smaller indexes per partition could help when index
design hits scaling limits. Whether the decompositions there would
actually look like boxes is another question that I haven't studied
very deeply, but it's one more reason to think about spatial
partitioning strategies.

--
Thanks, Amit Langote