Friday, November 5, 2021

Table Partitioning in Postgres DB

Partitioning can be done under different approaches like range partitioning, list partitioning, hash partitioning. 

Range Partitioning - ( Ex: Date ranges , Numeric ranges )

List Partitioning     - ( Ex: Transaction types )

Hash Partitioning   - ( Ex: Hash value based partitioning ) 


Create partitioned table with no primary key

Step 1: Create table

 CREATE TABLE Invoice (

    invoice_id   int,
    invoice_amount float,
    invoice_date date

) PARTITION BY RANGE(invoice_date);

Step 2 : Create the partition

CREATE TABLE Invoice_december_2021 PARTITION OF Invoice FOR VALUES FROM ('2021-12-01 00:00:00') TO ('2022-01-01 00:00:00');


Create partitioned table with primary key

Step 1: Create table

CREATE TABLE Patient (
    patient_id   int,
    patient_contact varchar(100),
    dob date,
    primary key (patient_id,dob)
)  PARTITION BY RANGE(dob);

Step 2 : Create the partition

CREATE TABLE Patient_december_2021 PARTITION OF Patient FOR VALUES FROM ('2021-12-01 00:00:00') TO ('2022-01-01 00:00:00');