Postgresql - Partition

A PostgreSQL database optimization technique called partitioning splits a big table into smaller, easier-to-manage sections known as partitions. By scanning less data in queries, it enhances query performance and maintenance efficiency.

Types of Partitions :
1.Range partitions :
A range of numbers is used to divide the data into segments. Organizing a sales table by year is one example.

2.List Partitions :
A list of predetermined values is used to divide the data. Example: Dividing client information by nation.
Table : 
    CREATE TABLE TXN
    ( TDATE DATE,
    START_DATE integer,
    END_DATE int,
    PNUMBER int,
    ACODE VARCHAR(2)
    ) ;
Range partition : 
    CREATE TABLE TXN_RANGE (TDATE DATE, START_DAT INT,END_DATE INT,PNUMBER INT, ACODE VARCHAR(2)) PARTITION BY RANGE (TDATE);
Partition : 
    PLPGSQL Code:

    do $$
    declare
    z int;
    i int;
    j int;
    begin
    for z in 0..11 loop
    for i in 1..323 loop
        for j in 1..31 loop
    insert into txn values(current_date-433+j+(31*z),1212*i,2323232*i,122323*i,
        (select case round(random()*5) when  0 then 'CN' when  1 then 'CN' when  2 then 'MD' when  3 
                                                    then 'CO' when  4 then 'TR' when  5 then 'CN' END));
        end loop;
    end loop;
    end loop;
    commit;
    end $$;
Range partition : 
    CREATE TABLE TXN_RANGE (
            TDATE DATE,
            START_DATE INT,
            END_DATE INT,
            PNUMBER INT,
            ACODE VARCHAR(2))
    PARTITION BY RANGE (TDATE);
Range partition : 
    CREATE TABLE TXN_RANGE_dec_mar24 PARTITION OF TXN_RANGE FOR VALUES 
                    FROM ('2023-12-17') TO ('2024-04-01') partition by LIST(ACODE);

        List SUB partition :
            CREATE TABLE TXN_RLIST_CNMD_dec_mar24 PARTITION OF TXN_RANGE_dec_mar24
                        FOR VALUES in ('CN','MD');
                        
            CREATE TABLE TXN_RLIST_COTR_dec_mar24 PARTITION OF TXN_RANGE_dec_mar24
                        FOR VALUES in ('CO','TR');
Range partition : 
    CREATE TABLE TXN_RANGE_apr_jun24 PARTITION OF TXN_RANGE FOR VALUES 
                    FROM ('2024-04-01') TO ('2024-07-01') partition by LIST(ACODE);

        List SUB partition :
            CREATE TABLE TXN_RLIST_CNMD_apr_jun24 PARTITION OF TXN_RANGE_apr_jun24
                        FOR VALUES in ('CN','MD');
            
            CREATE TABLE TXN_RLIST_COTR_apr_jun24 PARTITION OF TXN_RANGE_apr_jun24
                        FOR VALUES in ('CO','TR');
Range partition : 
    CREATE TABLE TXN_RANGE_jul_oct24 PARTITION OF TXN_RANGE FOR VALUES 
                    FROM ('2024-07-01') TO ('2024-11-01') partition by LIST(ACODE);

        List SUB partition :
            CREATE TABLE TXN_RLIST_CNMD_jul_oct24 PARTITION OF TXN_RANGE_jul_oct24
                        FOR VALUES in ('CN','MD');
            
            CREATE TABLE TXN_RLIST_COTR_jul_oct24 PARTITION OF TXN_RANGE_jul_oct24
                        FOR VALUES in ('CO','TR');
Range partition : 
    CREATE TABLE TXN_RANGE_nov_dev_24 PARTITION OF TXN_RANGE FOR VALUES
                    FROM ('2024-11-01') TO ('2024-12-23') partition by LIST(ACODE);

        List SUB partition :
            CREATE TABLE TXN_RLIST_CNMD_nov_dev_24 PARTITION OF TXN_RANGE_nov_dev_24
                        FOR VALUES in ('CN','MD');
            
            CREATE TABLE TXN_RLIST_COTR_nov_dev_24 PARTITION OF TXN_RANGE_nov_dev_24
                        FOR VALUES in ('CO','TR');
Data Insert : 
    insert into txn_range select * from txn;
How to add a partition from existing table :
    alter table txn_range attach partition txn_range_apr_jun24 for values 
                        from ('2024-04-01') TO ('2024-07-01');
How to drop a partition from existing table :
    alter table txn_range detach partition txn_range_apr_jun24;


(Postgresql - DBlink and FDW)