DBA Page - Oracle Training | Postgresql Training

Postgresql - Partitions

PostgreSQL Partitions

PostgreSQL supports table partitioning, which allows you to divide a large table into smaller, more manageable pieces called partitions. This can improve query performance and make data management easier.

Partition Demo

 CREATE TABLE TXN
                                        (    TDATE DATE,
                                                START_DATE integer,
                                                END_DATE int,
                                                PNUMBER int,
                                                ACODE VARCHAR(2)
                                        ) ;

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 parition:

 CREATE TABLE TXN_RANGE (
                                                TDATE DATE,
                                                START_DATE INT,
                                                END_DATE INT,
                                                PNUMBER INT,
                                                ACODE VARCHAR(2))
                                        PARTITION BY RANGE (TDATE);

✔ LIST PARTITION

				CREATE TABLE TXN_LIST (
					TDATE DATE,
					START_DATE INT,
					END_DATE INT,
					PNUMBER INT,
					ACODE VARCHAR(2))
				PARTITION BY LIST (ACODE);


				--   PARTITION 
				CREATE TABLE TXN_ACODE_CNMD PARTITION OF TXN_LIST     FOR VALUES in ('CN','MD')
				CREATE TABLE TXN_ACODE_COTR PARTITION OF TXN_LIST     FOR VALUES in ('CO','TR');

✔ HASH PARTITION

                CREATE TABLE TXN_HASH (
                TDATE DATE,
                START_DATE INT,
                END_DATE INT,
                PNUMBER INT,
                ACODE VARCHAR(2))
                PARTITION BY HASH (PNUMBER); 
                                
                CREATE TABLE TXN_HASH1 PARTITION OF TXN_HASH FOR VALUES WITH (MODULUS 4, REMAINDER 0);
                CREATE TABLE TXN_HASH2 PARTITION OF TXN_HASH FOR VALUES WITH (MODULUS 4, REMAINDER 1);
                CREATE TABLE TXN_HASH3 PARTITION OF TXN_HASH FOR VALUES WITH (MODULUS 4, REMAINDER 2);
                CREATE TABLE TXN_HASH4 PARTITION OF TXN_HASH FOR VALUES WITH (MODULUS 4, REMAINDER 3);

✔ COMPOSITE PARTITION

 
                CREATE TABLE TXN_RANGE_dec_mar24 PARTITION OF TXN_RANGE FOR VALUES FROM ('2024-02-01') TO ('2024-09-01');  

                CREATE TABLE TXN_RANGE_apr_jun24 PARTITION OF TXN_RANGE FOR VALUES FROM ('2024-09-01') TO ('2025-02-28'); 

                --   PARTITION 
                CREATE TABLE TXN_RANGE_dec_mar24p PARTITION OF TXN_RANGE1   FOR VALUES FROM ('2024-02-01') TO ('2024-09-01')
                partition by LIST(ACODE); 

                --  SUB-PARTITION                   
                CREATE TABLE TXN_RLIST_CNMD_dec_mar24 PARTITION OF TXN_RANGE_dec_mar24p
                FOR VALUES in ('CN','MD'); 

                CREATE TABLE TXN_RLIST_COTR_dec_mar24 PARTITION OF TXN_RANGE_dec_mar24p
                FOR VALUES in ('CO','TR'); 

                --   PARTITION 
                CREATE TABLE TXN_RANGE_apr_jun24p PARTITION OF TXN_RANGE1
                FOR VALUES FROM ('2024-09-01') TO ('2025-02-28')
                partition by LIST(ACODE);

                --  SUB-PARTITION   
                CREATE TABLE TXN_RLIST_CNMD_apr_jun24 PARTITION OF TXN_RANGE_apr_jun24p
                FOR VALUES in ('CN','MD');

                CREATE TABLE TXN_RLIST_COTR_apr_jun24 PARTITION OF TXN_RANGE_apr_jun24p
                FOR VALUES in ('CO','TR');

                --   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);

                --  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');

                --   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);

                --  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');

✔ INHERITS

            create table th (A int );
  create table th1() inherits (th);
  create table th2() inherits (th);
  
  alter table th1 add check (a >=0 and a<=10);
  alter table th2 add check (a >=11 and a<=20);
  insert into th1 values(generate_series(1,10);
  insert into th2 values(generate_series(11,20);

✔ INSERT DATA

insert into txn_range select * from txn;

✔ DETACH PARTITION

 
        alter table txn_range detach partition txn_range_apr_jun24;  
        alter table txn_range attach partition txn_range_apr_jun24 FOR VALUES FROM ('2024-09-01') TO ('2025-02-28');

✔ DROP PARTITION

                            drop table TXN_RANGE_dec_mar24;
                            drop table TXN_RANGE_apr_jun24;
                            drop table TXN_RANGE_jul_oct24;
                            drop table TXN_RANGE_nov_dev_24;

 -- Sub  partitions 
                            drop table TXN_RLIST_CNMD_dec_mar24;
                            drop table TXN_RLIST_COTR_dec_mar24;
                            drop table TXN_RLIST_CNMD_apr_jun24;
                            drop table TXN_RLIST_COTR_apr_jun24;
                            drop table TXN_RLIST_CNMD_jul_oct24;
                            drop table TXN_RLIST_COTR_jul_oct24;
                            drop table TXN_RLIST_CNMD_nov_dev_24;
                            drop table TXN_RLIST_COTR_nov_dev_24;

✔ Related Views

List all partitions of a table:

                SELECT
                 inhrelid::regclass AS partition_name
                FROM
                 pg_inherits
                WHERE
                 inhparent = 'txn_range'::regclass;

  
                        
 Show partition structure (with parent and child):   
                        SELECT
                 c.relname AS child,
                 p.relname AS parent
                FROM
                 pg_inherits
                    JOIN pg_class c ON (inhrelid = c.oid)
                    JOIN pg_class p ON (inhparent = p.oid)
                WHERE
                 p.relname = 'txn_range';
                                        
 Show partition structure Other commands    
                     \dt+
             \d+ < table name>
              \d < partiton tablename>