From: Laurenz Albe <laurenz.albe@cybertec.at>
To: James Robertson <james@jsrobertson.net>
To: pgsql-performance@postgresql.org
Subject: Re: multicolumn partitioning help
Date: Tue, 14 Mar 2023 22:41:32 +0100
Message-ID: <784863d0cce3f14dcb41252fe90c11de1e8da902.camel@cybertec.at> (raw)
In-Reply-To: <CAOA7M6tY5moDThZH=V2iFJ1ahxz6K2JRqGzDgu-gPP+GhLT4Lg@mail.gmail.com>
References: <CAOA7M6tY5moDThZH=V2iFJ1ahxz6K2JRqGzDgu-gPP+GhLT4Lg@mail.gmail.com>
On Sun, 2023-03-12 at 13:59 -0400, James Robertson wrote:
> I am having issues with multicolumn partitioning. For reference I am using the following link as my guide:
> https://www.postgresql.org/docs/devel/sql-createtable.html
>
> To demonstrate my problem, I created a simple table called humans. I want to partition by the year
> of the human birth and then the first character of the hash. So for each year I'll have year*16 partitions. (hex)
>
> CREATE TABLE humans (
> hash bytea,
> fname text,
> dob date
> )PARTITION BY RANGE (EXTRACT(YEAR FROM dob),substring(hash::text, 1, 1));
>
> Reading the documentation: "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".
>
> 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?
>
>
> This works:
> CREATE TABLE humans_1968_0 PARTITION OF humans FOR VALUES FROM (1968, '0') TO (1969, '1');
>
>
> These fail:
> 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
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: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, james@jsrobertson.net
Subject: Re: multicolumn partitioning help
In-Reply-To: <784863d0cce3f14dcb41252fe90c11de1e8da902.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox