public inbox for [email protected]
help / color / mirror / Atom feedFrom: John Mikk <[email protected]>
To: David Rowley <[email protected]>
Cc: [email protected]
Subject: Re: POC: Comparison of partitioning key values
Date: Thu, 16 Apr 2026 23:35:34 +0300
Message-ID: <CADY9qXcHhsUUMUh+q-g-PnJbOrC6AoG5Zhig52+2WXkvejWNHw@mail.gmail.com> (raw)
In-Reply-To: <CAApHDvqSP-E4g3ZQ0WxBbfeMbkmPhMjOm15aWXaT5+pDfO_6AA@mail.gmail.com>
References: <CADY9qXf_RsNNfZ88qYpzW7-3kreQq2fd+6Gezbm85pm_-FZHGQ@mail.gmail.com>
<CAApHDvqSP-E4g3ZQ0WxBbfeMbkmPhMjOm15aWXaT5+pDfO_6AA@mail.gmail.com>
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 <[email protected]> wrote:
> On Tue, 14 Apr 2026 at 09:11, John Mikk <[email protected]> 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
>
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: POC: Comparison of partitioning key values
In-Reply-To: <CADY9qXcHhsUUMUh+q-g-PnJbOrC6AoG5Zhig52+2WXkvejWNHw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox