public inbox for [email protected]
help / color / mirror / Atom feedFrom: Amit Langote <[email protected]>
To: John Mikk <[email protected]>
Cc: David Rowley <[email protected]>
Cc: [email protected]
Subject: Re: POC: Comparison of partitioning key values
Date: Mon, 20 Apr 2026 13:49:16 +0900
Message-ID: <CA+HiwqHxf45YDzo5U=aUDXfRJC8FDB9wUw2UvfSnJkutY6DJjQ@mail.gmail.com> (raw)
In-Reply-To: <CADY9qXcHhsUUMUh+q-g-PnJbOrC6AoG5Zhig52+2WXkvejWNHw@mail.gmail.com>
References: <CADY9qXf_RsNNfZ88qYpzW7-3kreQq2fd+6Gezbm85pm_-FZHGQ@mail.gmail.com>
<CAApHDvqSP-E4g3ZQ0WxBbfeMbkmPhMjOm15aWXaT5+pDfO_6AA@mail.gmail.com>
<CADY9qXcHhsUUMUh+q-g-PnJbOrC6AoG5Zhig52+2WXkvejWNHw@mail.gmail.com>
Hi John,
On Fri, Apr 17, 2026 at 5:35 AM John Mikk <[email protected]> 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
view thread (4+ messages)
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], [email protected]
Subject: Re: POC: Comparison of partitioning key values
In-Reply-To: <CA+HiwqHxf45YDzo5U=aUDXfRJC8FDB9wUw2UvfSnJkutY6DJjQ@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