Partition and Subpartition in PostgreSQL12

Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits:

• Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. 

• When queries or updates access a large percentage of a single partition, performance can be improved by taking advantage of sequential scan of that partition instead of using an index and random access reads scattered across the whole table.

• Bulk loads and deletes can be accomplished by adding or removing partitions, if that requirement is planned into the partitioning design. Doing ALTER TABLE DETACH PARTITION or dropping an individual partition using DROP TABLE is far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.

• Seldom-used data can be migrated to cheaper and slower storage media.

PostgreSQL provides three built-in partitioning methods:

(A)List Partitioning

(B)Range Partitioning

(C)Hash Partitioning

LIST Partition: 
Now let’s start with list partitioning.Partition a table by a list of known values. This is typically used when the partition key is a categorical value, like country or city code, and each partition will define the list of codes that map to it.Take a simple example.Create a table with three fields.
CREATE TABLE list_partition_example
( company_code smallint,
division_code smallint,
location_code smallint)PARTITION BY list (division_code);
Now we will create partition tables: 
CREATE TABLE list_partition_example_1 PARTITION OF list_partition_example FOR VALUES IN (1);
CREATE TABLE list_partition_example_2 PARTITION OF list_partition_example FOR VALUES IN (2);
CREATE TABLE list_partition_example_3 PARTITION OF list_partition_example FOR VALUES IN (3);
CREATE TABLE list_partition_example_4 PARTITION OF list_partition_example FOR VALUES IN (4);
CREATE TABLE list_partition_example_5 PARTITION OF list_partition_example FOR VALUES IN (5);
CREATE TABLE list_partition_example_6 PARTITION OF list_partition_example FOR VALUES IN (6);
CREATE TABLE list_partition_example_default PARTITION OF list_partition_example DEFAULT;
Now Insert Data(records) in parent table.
INSERT INTO list_partition_example VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1),(1,5,1),(1,6,1);
Now we can check data separately by executing query on partition tables:
SELECT * FROM list_partition_example_1;
SELECT * FROM list_partition_example_2;
SELECT * FROM list_partition_example_3;
SELECT * FROM list_partition_example_4;
SELECT * FROM list_partition_example_5;
SELECT * FROM list_partition_example_6;
When we create table with DEFAULT keyword, all records will come under this partition table whose values are not present in other tables. 
Below two records will come in list_partition_example_default table. Because values division_code=7 and 8 are not define in any partition table , due to that it will inserted in list_partition_example_default table.
INSERT INTO list_partition_example VALUES (1,7,1), (1,8,12);
SELECT * FROM list_partition_example_default;
Check other scenarios:
When we add primary key on parent table. It will automatically create PRIMARY KEYs on entire partition tables.
ALTER TABLE list_partition_example ADD CONSTRAINT list_partition_example_pkey PRIMARY KEY (division_code, location_code);
Eg.
INSERT INTO list_partition_example VALUES(1,1,1);
division_code=1 and location_code=1 already present in table, due to that It will give an error like below:
ERROR:  duplicate key value violates unique constraint "list_partition_example_1_pkey"
DETAIL:  Key (division_code, location_code)=(1, 1) already exists.
Earlier PostgreSQL version not support when we give a reference of Parent table( e.g. list_partition_example) to child table. Now it works properly. To check FOREIGN KEY constraints, create another table list_partition_example_fr.
CREATE TABLE list_partition_example_fr AS SELECT * FROM list_partition_example WHERE 1 = 2;
It will create list_partition_example_fr table with same structure of list_partition_example.
Now add foreign key on list_partition_example_fr:
ALTER TABLE list_partition_example_fr ADD CONSTRAINT fkey_ list_partition_example_fr
FOREIGN KEY (division_code, location_code) REFERENCES list_partition_example (division_code,location_code);
Now insert  records to check its validity
INSERT INTO list_partition_example_fr VALUES(1,1,1);
Record will be inserted without any error, because division_code =1 and location_code = 1 is present in list_partition_example table.
INSERT INTO list_partition_example_fr VALUES(1,8,13);
It will give an error
ERROR:  insert or update on table "list_partition_example_fr" violates foreign key constraint " fkey_ list_partition_example_fr"
DETAIL:  Key (division_code, location_code)=(8, 13) is not present in table "list_partition_example".
********** Error **********
From PostgreSQL12 triggers are working properly at most of the cases as per developer requirement when we write it on parent table. For validate it ,we will create a small table list_partition_example_trg
CREATE  TABLE list_partition_example_trg
(
  company_code  smallint,
  old_division_code smallint,
  new_division_code smallint,
  location_code smallint
);
Now we will write trigger function and trigger on list_partition_example
CREATE OR REPLACE FUNCTION list_partition_example_trg()
  RETURNS trigger AS
$BODY$
DECLARE
begin
if TG_OP = 'UPDATE' then
if  new.division_code != old.division_code then
raise notice ' new div cd list_partition_example_trg ---->%',new.division_code;
raise notice ' old div cd list_partition_example_trg ----> %',old.division_code;
INSERT INTO list_partition_example_trg
(
company_code  ,
old_division_code ,
new_division_code ,
location_code 
) VALUES
(
new.company_code,
old.division_code,
new.division_code,
new.location_code
);
end if;
RETURN NEW;
end if;
RETURN NEW;
exception when others then
raise exception 'ERROR IN PROCEDURE list_partition_example_trg %',SQLERRM;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
;

CREATE TRIGGER list_partition_example_trg
AFTER insert or update 
ON list_partition_example
FOR EACH ROW
EXECUTE PROCEDURE list_partition_example_trg();
BEFORE / FOR EACH ROW will not work on partition table 
e.g. when we try to create below trigger with BEFORE / FOR EACH ROW it will give an error
CREATE TRIGGER list_partition_example_trg
BEFORE insert or update 
ON list_partition_example
FOR EACH ROW
EXECUTE PROCEDURE list_partition_example_trg();
Output:
ERROR:  "list_partition_example" is a partitioned table
DETAIL:  Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.
********** Error **********
To check new and old values for a column execute below query on parent table:
update list_partition_example set division_code = 9 where location_code = 12;
Output:
NOTICE:   new div cd list_partition_example_trg ---->9
CONTEXT:  PL/pgSQL function list_partition_example_trg() line 9 at RAISE
NOTICE:   old div cd list_partition_example_trg ----> 8
CONTEXT:  PL/pgSQL function list_partition_example_trg() line 10 at RAISE
Query returned successfully: one row affected, 55 msec execution time.
Means it will work properly. We can check it by executing below query. old values and new values has stored in table list_partition_example_trg.
SELECT * FROM list_partition_example_trg ;
RANGE Partition: Partition a table by a range of values. This is commonly used with date fields.Take a small example for RANGE partition
CREATE TABLE sales_orders (
order_id  integer ,
customer_id smallint,
order_date DATE NOT NULL,
order_qty  numeric(21,2) NOT NULL
)
 PARTITION BY RANGE (order_date);
CREATE TABLE sales_orders_y20m0103 PARTITION OF sales_orders FOR VALUES FROM ('2020-01-01') TO ('2020-03-31');
CREATE TABLE sales_orders_y20m0406 PARTITION OF sales_orders FOR VALUES FROM ('2020-04-01') TO ('2020-06-30');
CREATE TABLE sales_orders_y20m0709 PARTITION OF sales_orders FOR VALUES FROM ('2020-07-01') TO ('2020-09-30');
CREATE TABLE sales_orders_y20m1012 PARTITION OF sales_orders FOR VALUES FROM ('2020-10-01') TO ('2020-12-31');
HASH Partition:Partition a table using a hash function on the partition key. This is especially useful when there is no obvious way of dividing data into logically similar groups and is often used on categorical partitioning keys that are accessed individually.
CREATE TABLE hash_partition_example
(
  company_code smallint,
  division_code smallint,
  location_code smallint
)
Making it partitioned by hash (of division_code) could be done like this:
CREATE TABLE hash_partition_example
(
  company_code smallint,
  division_code smallint,
  location_code smallint
)PARTITION BY HASH (  division_code);
Partitions are then defined using 2 numbers:
MODULUS
REMAINDER
MODULUS is number of partitions, and REMAINDER is number, 0 or more, but less than MODULUS.
For example, assuming we'd want to partition into 6 partitions, I could:
CREATE TABLE hash_partition_example_0 PARTITION OF hash_partition_example ( PRIMARY KEY (division_code) ) FOR VALUES WITH (MODULUS 7, REMAINDER 0);
CREATE TABLE hash_partition_example_1 PARTITION OF hash_partition_example ( PRIMARY KEY (division_code) ) FOR VALUES WITH (MODULUS 7, REMAINDER 1);
CREATE TABLE hash_partition_example_2 PARTITION OF hash_partition_example ( PRIMARY KEY (division_code) ) FOR VALUES WITH (MODULUS 7, REMAINDER 2);
CREATE TABLE hash_partition_example_3 PARTITION OF hash_partition_example ( PRIMARY KEY (division_code) ) FOR VALUES WITH (MODULUS 7, REMAINDER 3);
CREATE TABLE hash_partition_example_4 PARTITION OF hash_partition_example ( PRIMARY KEY (division_code) ) FOR VALUES WITH (MODULUS 7, REMAINDER 4);
CREATE TABLE hash_partition_example_5 PARTITION OF hash_partition_example ( PRIMARY KEY (division_code) ) FOR VALUES WITH (MODULUS 7, REMAINDER 5);
CREATE TABLE hash_partition_example_6 PARTITION OF hash_partition_example ( PRIMARY KEY (division_code) ) FOR VALUES WITH (MODULUS 7, REMAINDER 6);

SUBPARTITION:

At present I think SUBPARTITION will work only on ENTERPRISED DB not in Open source PostgreSQL.
Now take an example of LIST LIST PARTITIONING
CREATE  TABLE list_list_partition_example
(
  company_code smallint,
  division_code smallint,
  location_code smallint
)
PARTITION BY list(division_code) SUBPARTITION BY LIST (location_code)
  (
PARTITION "list_list_partition_example_div_1" VALUES  (1)
  (
    SUBPARTITION list_list_partition_example_loc_1_1 VALUES (1),
    SUBPARTITION list_list_partition_example_loc_1_2 VALUES (2),
    SUBPARTITION list_list_partition_example_loc_1_3 VALUES (3)
  ) ,
PARTITION "list_list_partition_example_div_2" VALUES (2)
  (
    SUBPARTITION list_list_partition_example_loc_2_1 VALUES (1),
    SUBPARTITION list_list_partition_example_loc_2_2 VALUES (2),
    SUBPARTITION list_list_partition_example_loc_2_3 VALUES (3)
  )
); 
ATTACH and DETACH partitions to and from an existing partitioned table.
CREATE TABLE list_partition_example_9 ( LIKE list_partition_example INCLUDING ALL) 
INSERT INTO list_partition_example_9  VALUES (1,7,10);
ALTER TABLE list_partition_example ATTACH partition list_partition_example_9 FOR VALUES IN (7);
It is often preferable to remove the partition from the partitioned table but retain access to it as a table in its own right:
ALTER TABLE measurement DETACH PARTITION list_partition_example_1;
The simplest option for removing old data is to drop the partition that is no longer necessary:
DROP TABLE list_partition_example_1;