Re: POC: Comparison of partitioning key values

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`.

John.

On Tue, Apr 14, 2026 at 8:19 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 14 Apr 2026 at 09:11, John Mikk <jomikk2706(at)gmail(dot)com> wrote:
> ...
> You can't change how RANGE partitioning works and not break things for
> everyone using RANGE partitioning when they upgrade. If your patch is
> proposing that, then it's going to fail. If you're proposing a new
> partitioning method, then that's different. It's still a hefty amount
> of work. If you're proposing that then do a detailed proposal here
> before doing too much work. Remember that with declarative
> partitioning, there can be only (at most) a single partition for any
> given tuple. The tuple routing done during INSERT and UPDATE requires
> that. Finding the correct partition must also be fast as INSERT/UPDATE
> performance needs to run that code for every affected tuple.
>
> David
>
> [1] https://www.postgresql.org/docs/current/sql-createtable.html
>