Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wDTSF-00312x-1d for pgsql-hackers@arkaria.postgresql.org; Thu, 16 Apr 2026 20:36:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wDTRF-006m1F-20 for pgsql-hackers@arkaria.postgresql.org; Thu, 16 Apr 2026 20:35:49 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wDTRF-006m17-0c for pgsql-hackers@lists.postgresql.org; Thu, 16 Apr 2026 20:35:49 +0000 Received: from mail-oa1-x2d.google.com ([2001:4860:4864:20::2d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wDTRC-00000001Mny-3VbJ for pgsql-hackers@lists.postgresql.org; Thu, 16 Apr 2026 20:35:48 +0000 Received: by mail-oa1-x2d.google.com with SMTP id 586e51a60fabf-4042905015cso14671fac.0 for ; Thu, 16 Apr 2026 13:35:47 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1776371746; cv=none; d=google.com; s=arc-20240605; b=lowZ/KBUSQNtAM7/ScM+gPzO+qwVt+F4tDRpojxA3vBhHUQQ9PamapEnWKZD1qNFE8 FIUHdWD8zrKha16IB3ns5JdlcGIJK7UEGUalZj1weLF9hv8yavUpdv71A/as8V48RYWL bRBsGh6Y2KBsvymePxIz0xgoNFzVSeqV/A5MbPQ4rQ+uc17FRjk5FQ3GQiI0hf9QIEvc Xw87uAH8fby7BbvQiC1zzKFd3IDWm2Lmn/5DQeNS4WP1QevhuampDlZqhon3hKAvvvMe Wxz9/d1XmoViAVHC1KPOnxbY3r4fX5X5XsGYTc9pIsvQKxEHkgs5dlBZqHt0zhJ+TYoo ktIQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=1pcqTeTkX840JcDW9KlO9DEeLPAkACOSbeiYeWhbfUw=; fh=LPWFjkISMvZpTym+fM6KElgjyFiiO2RfbSaFMVFfr+Q=; b=VdURojzGxF67z2WJFlPfSKBWyVJ4MExCZBJFix2EDkHfTnYF9tRMBRTzXurCcSBAQ9 sa7sM87PgApv3sUwUviE+ij54j1Dt7e00Rm9O+qhRyZtMYVyasbTt5Fu/sBD4Q3c3EnL QeTB4X4LGRh3tF9YSs81cbAy9ux3nFWxc4fCwfIZ2iA2BTIVpbv1Y+w2Hf06PR6oRE+F 0yDRn29+ramQaD4p6hx3QrtPSiOXTcCH7Gud7c5UzMI9xDIYqj9RfA7SZxro5YjSxa5M oBXjlE7grSw/n1sUDxi0JxLGtHQi0YeJBSqb37p+4k9wRsgL5MSo5NJAG4o95GCEyh0P GBog==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1776371746; x=1776976546; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=1pcqTeTkX840JcDW9KlO9DEeLPAkACOSbeiYeWhbfUw=; b=GYZ1kvJJr7lzzTfxBsd9so5/pCExTb3YMIuPE9j1+lOIABZF3RelnzSIuaU7TCX9JT 9fGJqzYBFZX3KQx921dd7Yuh5mIaZ4XIYZUnC86K/UtTNeBJW1vsnGTvvI2Iy904x0hl ZrzazuQfr05x9D2tWOX4LNvKNKY7SV/3yN1/cu8gQIfS/UKcBaMFBDILnFXJHrNnXX/v xZDAHnFwuMLo50mOQlh8H3NwKqev7TmTAx7Om/KlrwlSjuZ6nzFw4EaQsWjgatO2EYa0 YX/VRjsZY4vehM4fT6zvUIToIR9WTuOh0FkKNDwlRlmhZwmcLYLcF/VMnjSweDQaLx5k ih0g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1776371746; x=1776976546; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=1pcqTeTkX840JcDW9KlO9DEeLPAkACOSbeiYeWhbfUw=; b=BWkfP1mo1L+XV8sne04/evqAesKwU2s8EzmiCBJ/QIOQBF92vcfxfx5vpCvwI92T9u ekRzbDJm4ItWCTacd1PWSbkzPb8xb20HoeVOhtBVAd7HwIwMelCwSxHnixNjfSnQsJkZ RkGr4PRzn3hqjiZwavCbuO9QQ+GGF9sfFZk9LtdQsU9PT5AtoXoyL74LvzV+H85x7Exj BHStfDMjfU8zoE/9217Xe5L9fmeiqSQ85X7oCCzDxso3NQE20FLY5FaShyDPU83kquAc QMNqUyCUsQRczrTcXyXwlnRrcHYbwux1xGd7YqMEDafxRR/lubL5QIKn/FDIat5UFU0R sDLg== X-Gm-Message-State: AOJu0YxznYxrMz9wdpyZfnQwXSyzdEoR8FYjDxHriAVMPoIgjFhIuP3f 8nDZh+OefUrjixhNxh/7lbm8ZKHT1bHE9ZXXoSvIoIb6mrcj2VcstHTHL5OEtnZ954Uqz1mvKvV Cfy9JPbpk+PQ41GSW2IGGhDYQi5FTPec= X-Gm-Gg: AeBDiesf27vi+JF8ju9QyF6umZTPUumE8yfwWHKyR6jGBOqj0Z3yBvrS6gEArRyimoE ijC9Shf7lWBVU75WqPUm8RksR1DY4BW4sN5KALyhRBzV3+pbvXHw8l2+NYAI8t0rJ5r7bx2YqUe sAMYjgtJNGtxpIlnBi7+2CL3s8BfDTHPByw87fuXbDPMYSSaU5D1CuEac+5ejREjBXj51XY0yDt fNaIEyTK2GolkZvLXip/p2ft2L3z3RA6cANlNhQ3enX0k4DUPEJoTRoADZLeLdiAwcXCpUMRsP7 WlJW5LHWO6EBh1tu X-Received: by 2002:a05:6820:c91:b0:67c:27a7:8c4b with SMTP id 006d021491bc7-69462f3179emr92163eaf.54.1776371746365; Thu, 16 Apr 2026 13:35:46 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: John Mikk Date: Thu, 16 Apr 2026 23:35:34 +0300 X-Gm-Features: AQROBzBbEKf6V5C94-vwIe8z1Q7FgD4TjMUDZQ_0H89Ypnc4NUEf_3ReHrxL1g4 Message-ID: Subject: Re: POC: Comparison of partitioning key values To: David Rowley Cc: pgsql-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000ee6694064f99c60f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ee6694064f99c60f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =3D=3D 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=E2=80=AFAM David Rowley = wrote: > On Tue, 14 Apr 2026 at 09:11, John Mikk 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 > --000000000000ee6694064f99c60f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 
Dear=
 David, thank you for the detailed response.

I understand your conce=
rns, so I have rethought my approach a bit and would like to discuss, 
i=
n general terms, the concept that I would try to implement.

The abil=
ity to define a B-tree operator class (opclass) in the clause:
`PARTITION BY RANGE ( { column_name | ( e=
xpression ) } [ opclass ] [, ...] )`allows partitioning over a fairly broad class of sets with a define=
d 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 i=
f exists axis cascade;

create table axis (
    id serial,    key fraction,
    label text
) partition by range (key fr=
action_ops);

create table segment_1 partition of axis for values=
 from ((0,1)::fraction) to ((1,3)::fraction);
create table segment_2 p=
artition of axis for values from ((2,5)::fraction) to ((4,5)::fraction);create table segment_3 partition of axis for values from ((15,45)::fract=
ion) to ((2,5)::fraction);
-- segment_1,2,3 : [0, 1/3], [2/5, 4/5], [1=
/3, 2/5], where 15/45 =3D=3D 1/3  

insert into axis(key,label) s=
elect (1,5)::fraction, '1/5';
-- insert to segment_1
inse=
rt into axis(key,label) select (1,2)::fraction, '1/2';
-- inse=
rt to segment_2
insert into axis(key,label) select (1,3)::fraction, &#=
39;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 lef=
t" (less than) / "to the right" (greater than) for rectangle=
s on a plane, 
the transitivity of such a relation is violated.

T=
o achieve the intended goal, 
it would likely be necessary to use the Gi=
ST access method. 
According to the documentation, however, 
only B-t=
ree is applicable when defining an operator class for a range partitioning =
key.

**Proposal:** Make GiST available for partitioning i=
n the `opclass` clause of `<=
/span>PARTITION BY RANGE`.
John.

On Tue, Apr 14, 2026 at 8:19= =E2=80=AFAM David Rowley <dgrowl= eyml@gmail.com> wrote:
On Tue, 14 Apr 2026 at 09:11, John Mikk <jomikk2706@gmail.com> wrote= :
...
You can't change how RANGE partitioning works and not break things for<= br> 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<= br> partitioning method, then that's different. It's still a hefty amou= nt
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/curr= ent/sql-createtable.html
--000000000000ee6694064f99c60f--