Postgresql Table Partitioning example

This guide explain, how idempiere AD_Changelog table can be migrated to postgres Table partitioning. Our goal was get good performance for Record Info - select (*) ad_changelog approx 12mill tooks up to 3-5 sec to show result, after migration approx 100ms.

Method: list partitioning data by AD_Client_ID (let say tenant has own data on a dedicated, table)

DB Server AWS RDS: Postgres 14.2

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

This implementation doesn't support live migration. Means, you need to stop proxy and implement in maintenance window.

STEP 1. ALTER original Table

first we need "deprecate" original table, so we rename it first.

alter table ad_changelog rename to ad_changelog_default;

STEP 2. CREATE new Table with PARTION BY CLAUSE

based on original table DDL you should create new table ad_changelog, don't forget add list parition clause on create table DDL.

   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

This index help seek data only on active tenant partition

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 original ad_changelog(_default) table to new one

atm you can see again data in changelog. however data are loaded from default partition.

ALTER TABLE AD_Changelog attach partition ad_changelog_default DEFAULT;

DISABLE ALL TRIGGERS DANGER - OVER DATABASE

This temporary disable all triggers like mainly FK.

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

We wrote a PG function, first run it.

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