Message-ID: <784863d0cce3f14dcb41252fe90c11de1e8da902.camel@cybertec.at>
Subject: Re: multicolumn partitioning help
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: James Robertson <james@jsrobertson.net>, pgsql-performance@postgresql.org
Date: Tue, 14 Mar 2023 22:41:32 +0100
In-Reply-To: 
 <CAOA7M6tY5moDThZH=V2iFJ1ahxz6K2JRqGzDgu-gPP+GhLT4Lg@mail.gmail.com>
References: 
	<CAOA7M6tY5moDThZH=V2iFJ1ahxz6K2JRqGzDgu-gPP+GhLT4Lg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/784863d0cce3f14dcb41252fe90c11de1e8da902.camel%40cybertec.at>
Precedence: bulk

On Sun, 2023-03-12 at 13:59 -0400, James Robertson wrote:
> I am having issues with multicolumn partitioning. For reference I am usin=
g the following link as my guide:
> https://www.postgresql.org/docs/devel/sql-createtable.html
>=20
> To demonstrate=C2=A0my problem, I created a simple table called humans. I=
 want to partition by the year
> of the human birth and then the first character=C2=A0of the hash. So for =
each year I'll have year*16 partitions. (hex)
>=20
> CREATE TABLE humans (
> =C2=A0 =C2=A0 hash bytea,
> =C2=A0 =C2=A0 fname text,
> =C2=A0 =C2=A0 dob date
> =C2=A0 =C2=A0 )PARTITION BY RANGE (EXTRACT(YEAR FROM dob),substring(hash:=
:text, 1, 1));
> =C2=A0 =C2=A0=20
> Reading the documentation:=C2=A0 =C2=A0"When creating a range partition, =
the lower bound specified with
> FROM is an inclusive bound, whereas the upper bound specified with TO is =
an exclusive bound".
>=20
> However I can't insert any of the following after the first one, because =
it says it overlaps.
> Do I need to do anything different when defining multi-column partitions?
>=20
>=20
> This works:
> CREATE TABLE humans_1968_0 PARTITION OF humans FOR VALUES FROM (1968, '0'=
) TO (1969, '1');
>=20
>=20
> These fail:=C2=A0
> CREATE TABLE humans_1968_1 PARTITION OF humans FOR VALUES FROM (1968, '1'=
) TO (1969, '2');

Justin has explained what the problem is, let me supply a solution.

I think you want subpartitioning, like

  CREATE TABLE humans (
     hash bytea,
     fname text,
     dob date
  ) PARTITION BY LIST (EXTRACT (YEAR FROM dob));

  CREATE TABLE humans_2002
     PARTITION OF humans FOR VALUES IN (2002)
     PARTITION BY HASH (hash);

  CREATE TABLE humans_2002_0
     PARTITION OF humans_2002 FOR VALUES WITH (MODULUS 26, REMAINDER 0);

  [...]

  CREATE TABLE humans_2002_25
     PARTITION OF humans_2002 FOR VALUES WITH (MODULUS 26, REMAINDER 25);

and so on for the other years.

Yours,
Laurenz Albe