Re: multicolumn partitioning help

[James Robertson <james@xxxxxxxxxxxxxxx>]

Laurenz, Justin,
Thank you both for thinking of this problem.

Laurenz your solution is how I thought I would work around my (lack of) understanding of partitioning. (nested partitions).

I was hesitant because I didn't know what sort of performance problems I would create for myself.

If we have true multi-column don't we get the benefit of:

TopLevelTable

|

|----> worker-thread 1
|

|----> worker-thread 2

|
|----> worker-thread n

Doesn't that give me more performance than:

TopLevelTable
|
|----> worker-thread 1

........|----> sub-table 1.1

........|----> sub-table 1.2

........|----> sub-table 1.n
|
|----> worker-thread 2

........|----> sub-table 2.1
........|----> sub-table 2.2
........|----> sub-table 2.n

or do we get?

TopLevelTable
|
|----> worker-thread 1 (default catch)
........|----> worker thread 2 -> sub-table 1.1
........|----> worker thread 3 -> sub-table 1.2
........|----> worker thread 4 -> sub-table 1.n
|
|----> worker-thread 5 (default catch)
........|----> worker thread 6 -> sub-table 2.1
........|----> worker thread 7 -> sub-table 2.2
........|----> worker thread 8 -> sub-table 2.n


Summary: 

1) if we create nested partitions, do we create performance issues:

2) if nested partitions are the solutions, what is the point of multi-column partitioning? 

wish list) wouldn't it be neat if we can do mult-mode multi-column? like PARTITION BY RANGE (EXTRACT(YEAR FROM dob)) LIST (SUBSTRING(hash, 1, 1));

On Tue, Mar 14, 2023 at 5:41 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:

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