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

   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;

Create new index

CREATE INDEX   ad_changelog_tablerecord_idx ON adempiere.ad_changelog USING btree (ad_client_id, ad_table_id, record_id);

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