Postgresql Table Partitioning example

This guide explain, how idempiere AD_Changelog table can be migrated to postgres Table partitioning.

DB Server AWS RDS: Postgres 14.2

https://www.postgresql.org/docs/14/ddl-partitioning.html

STEP 1. ALTER OLD TABLE

alter table ad_changelog rename to ad_changelog_default;

STEP 2. CREATE NEW TABLE with PARTION BY CLAUSE

<nowiki>

  DROP TABLE IF EXISTS ad_changelog CASCADE;

  CREATE TABLE ad_changelog

(

  ad_changelog_id  numeric(10)     NOT NULL,
  ad_session_id    numeric(10)     NOT NULL,
  ad_table_id      numeric(10)     NOT NULL,
  ad_column_id     numeric(10)     NOT NULL,
  ad_client_id     numeric(10)     NOT NULL,
  ad_org_id        numeric(10)     NOT NULL,
  isactive         char(1)         DEFAULT 'Y'::bpchar NOT NULL,
  created          timestamp       DEFAULT now() NOT NULL,
  createdby        numeric(10)     NOT NULL,
  updated          timestamp       DEFAULT now() NOT NULL,
  updatedby        numeric(10)     NOT NULL,
  record_id        numeric(10)     NOT NULL,
  oldvalue         varchar(2000),
  newvalue         varchar(2000),
  undo             char(1),
  redo             char(1),
  iscustomization  char(1)         DEFAULT 'N'::bpchar NOT NULL,
  trxname          varchar(1000),
  description      varchar(255),
  eventchangelog   char(1),
  ad_changelog_uu  varchar(36)     DEFAULT NULL::character varying,
  CONSTRAINT ad_changelog_isactive_check CHECK ((isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))),
  CONSTRAINT ad_changelog_iscustomization_check CHECK ((iscustomization = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])))

) PARTITION BY LIST ( AD_Client_ID );

ALTER TABLE ad_changelog

  ADD CONSTRAINT ad_changelog_pkey
  PRIMARY KEY (ad_client_id, ad_changelog_id, ad_session_id, ad_table_id, ad_column_id); ---  added ad_client_id -- IMPORTANT  - you need change Parent Link coolumn in Application otherwise you kill you deployment CPU---heap scanning..

ALTER TABLE ad_changelog

 ADD CONSTRAINT adcolumn_adchangelog FOREIGN KEY (ad_column_id)

ALTER TABLE ad_changelog

 ADD CONSTRAINT adcolumn_adchangelog FOREIGN KEY (ad_column_id)
 REFERENCES ad_column (ad_column_id) 
 ON UPDATE NO ACTION
 ON DELETE CASCADE
 DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE ad_changelog

 ADD CONSTRAINT adtable_adchangelog FOREIGN KEY (ad_table_id)
 REFERENCES ad_table (ad_table_id) 
 ON UPDATE NO ACTION
 ON DELETE CASCADE
 DEFERRABLE INITIALLY DEFERRED;

GRANT REFERENCES, TRUNCATE, UPDATE, TRIGGER, INSERT, SELECT, DELETE ON ad_changelog TO clde_appserver_user; <nowiki>

Create new index

CREATE INDEX ad_changelog_tablerecord_idx ON adempiere.ad_changelog USING btree (ad_client_id, ad_table_id, record_id); -- do we need client ??

note: partitioned table doesn't support CONCURRENT CLAUSE

ATTACH OLD DATA TABLE TO new MASTER TABLE

ALTER TABLE AD_Changelog attach partition ad_changelog_default DEFAULT;

DISABLE ALL TRIGGERS DANGER - OVER DATABASE

set session_replication_role = replica; set session_replication_role = default;

MIGRATE all data by custom function using AD_CLIENT_ID as List partition condition ------------

SELECT migrateTenantPartitions('AD_Changelog', 'ad_changelog_default');

CREATE OR REPLACE FUNCTION adempiere.migratetenantpartitions(p_tablename text, p_migratefromtablename text)

 RETURNS void
 LANGUAGE plpgsql

AS $body$ DECLARE

 v_partition TEXT;
   v_client                record;

BEGIN

 IF NOT EXISTS(SELECT 1 FROM pg_partitioned_table JOIN pg_class parent ON pg_partitioned_table.partrelid = parent.oid WHERE lower(relname)=lower(p_tablename)) THEN
   RAISE NOTICE 'Table Is not Partion Table %',p_tablename;
   RETURN;
 END IF;
   --  Go though Tenants
   FOR v_client IN
       SELECT ad_client_ID
       FROM AD_Client
   LOOP
   -- Create Partition
   v_partition := p_tablename || '_' || v_client.ad_client_ID::text;
   IF NOT EXISTS(SELECT relname FROM pg_class WHERE lower(relname)=lower(v_partition)) THEN
         RAISE NOTICE 'A partition has been created %',v_partition;
     EXECUTE 'CREATE TABLE ' || v_partition || '( like ' || p_MigrateFromTableName || ' including all);';
     
     EXECUTE 'WITH x as (
         DELETE FROM ' || p_MigrateFromTableName || ' WHERE ad_client_ID = ' || v_client.ad_client_ID || ' returning *
     )
     INSERT INTO ' || v_partition || '
         SELECT * FROM x;';
         
     EXECUTE 'ALTER TABLE ' || p_tablename || ' attach partition ' || v_partition || ' for values in (' || v_client.ad_client_ID || ');';
     RAISE NOTICE 'A partition has been migrated %',v_partition;
   END IF;
   END LOOP;
   RETURN;

END; $body$

 VOLATILE
 COST 100;

PERFORMANCE

vacuum analyze ad_changelog