Proposal for syntax to support creation of partition tables whencreating parent table - Mailing list pgsql-hackers
| From | Muhammad Usama |
|---|---|
| Subject | Proposal for syntax to support creation of partition tables whencreating parent table |
| Date | |
| Msg-id | CAEJvTzU+R22OQx6E7KQHvs_AXX1HTeEH1ey22SREYLZ3OerRqQ@mail.gmail.com Whole thread Raw |
| Responses |
Re: Proposal for syntax to support creation of partition tables whencreating parent table
Re: Proposal for syntax to support creation of partition tables when creating parent table |
| List | pgsql-hackers |
Hi Hackers,
CREATE TABLE table_name (..)
PARTITION BY { RANGE | LIST | HASH } (..)
(
list of partitions
) ;
CREATE TABLE All_Sales ( year INT, month INT, day INT, info TEXT)
PARTITION BY RANGE(year)(
PARTITION sale_2019_2020 FOR VALUES FROM (2019) TO (2021)
PARTITION BY LIST(month)
(
PARTITION sale_2019_2020_1 FOR VALUES IN (1,2,3,4)
PARTITION BY RANGE(day)(
PARTITION sale_2019_2020_1_1 FOR VALUES FROM (1) TO (10)
PARTITION BY HASH(info)
(
PARTITION sale_2019_2020_1_1_1 FOR VALUES WITH (MODULUS 2,REMAINDER 0),
PARTITION sale_2019_2020_1_1_2 FOR VALUES WITH (MODULUS 2,REMAINDER 1)
),
PARTITION sale_2019_2020_1_2 FOR VALUES FROM (10) TO (20),
PARTITION sale_2019_2020_1_3 FOR VALUES FROM (20) TO (32)),
PARTITION sale_2019_2020_2 FOR VALUES IN (5,6,7,8),
PARTITION sale_2019_2020_3 FOR VALUES IN (9,10,11,12)
),
PARTITION sale_2021_2022 FOR VALUES FROM (2021) TO (2023),
PARTITION sale_2023_2024 FOR VALUES FROM (2023) TO (2025),
PARTITION sale_default default
I want to propose an extension to CREATE TABLE syntax to allow the creation of partition tables along with its parent table using a single statement.
In this proposal, I am proposing to specify the list of partitioned tables after the PARTITION BY clause.
PARTITION BY { RANGE | LIST | HASH } (..)
(
list of partitions
) ;
Below are a few examples of the proposed syntax, in a nutshell, I am leveraging the syntax currently supported by Postgres for creating partitioned tables. The purpose of this proposal is to combine the creation of the parent partition table and its partitions in one SQL statement.
CREATE TABLE Sales (salesman_id INT, salesman_name TEXT, sales_region TEXT, hiring_date DATE, sales_amount INT )
PARTITION BY RANGE (hiring_date)
(
PARTITION part_one FOR VALUES FROM ('2008-02-01') TO ('2008-03-01'),
PARTITION part_two FOR VALUES FROM ('2009-02-01') TO ('2009-03-01'),
PARTITION part_def DEFAULT
);
PARTITION BY RANGE (hiring_date)
(
PARTITION part_one FOR VALUES FROM ('2008-02-01') TO ('2008-03-01'),
PARTITION part_two FOR VALUES FROM ('2009-02-01') TO ('2009-03-01'),
PARTITION part_def DEFAULT
);
CREATE TABLE Sales2 (salesman_id INT, salesman_name TEXT, sales_region TEXT, hiring_date DATE, sales_amount INT )
PARTITION BY HASH (salesman_id)
(
PARTITION par_one FOR VALUES WITH (MODULUS 2, REMAINDER 0),
PARTITION par_two FOR VALUES WITH (MODULUS 2, REMAINDER 1)
PARTITION BY HASH (salesman_id)
(
PARTITION par_one FOR VALUES WITH (MODULUS 2, REMAINDER 0),
PARTITION par_two FOR VALUES WITH (MODULUS 2, REMAINDER 1)
);
CREATE TABLE Sales3(salesman_id INT, salesman_name TEXT, sales_region TEXT, hiring_date DATE, sales_amount INT)
PARTITION BY LIST (sales_region)
(
PARTITION pt_one FOR VALUES IN ('JAPAN','CHINA'),
PARTITION pt_two FOR VALUES IN ('USA','CANADA'),
PARTITION pt_def DEFAULT
);
(
PARTITION pt_one FOR VALUES IN ('JAPAN','CHINA'),
PARTITION pt_two FOR VALUES IN ('USA','CANADA'),
PARTITION pt_def DEFAULT
);
-- Similarly for specifying subpartitions of partitioned tables
PARTITION BY RANGE(year)(
PARTITION sale_2019_2020 FOR VALUES FROM (2019) TO (2021)
PARTITION BY LIST(month)
(
PARTITION sale_2019_2020_1 FOR VALUES IN (1,2,3,4)
PARTITION BY RANGE(day)(
PARTITION sale_2019_2020_1_1 FOR VALUES FROM (1) TO (10)
PARTITION BY HASH(info)
(
PARTITION sale_2019_2020_1_1_1 FOR VALUES WITH (MODULUS 2,REMAINDER 0),
PARTITION sale_2019_2020_1_1_2 FOR VALUES WITH (MODULUS 2,REMAINDER 1)
),
PARTITION sale_2019_2020_1_2 FOR VALUES FROM (10) TO (20),
PARTITION sale_2019_2020_1_3 FOR VALUES FROM (20) TO (32)),
PARTITION sale_2019_2020_2 FOR VALUES IN (5,6,7,8),
PARTITION sale_2019_2020_3 FOR VALUES IN (9,10,11,12)
),
PARTITION sale_2021_2022 FOR VALUES FROM (2021) TO (2023),
PARTITION sale_2023_2024 FOR VALUES FROM (2023) TO (2025),
PARTITION sale_default default
);
This new syntax requires minimal changes in the code. I along with my colleague Movead.li have drafted a rough POC patch attached to this email.
Please note that the patch is just to showcase the new syntax and get a consensus on the overall design and approach.
As far as I know, there are already few ongoing discussions related to the partition syntax enhancements, but the proposed syntax will not interfere with these ongoing proposals. Here is a link to one such discussion:
Please feel free to share your thoughts.
Best Regards
...
Muhammad Usama
Highgo Software Canada
ADDR: 10318 WHALLEY BLVD, Surrey, BC
Attachment
pgsql-hackers by date: