Postgresql Table Partitioning example
From iDempiere en
This guide explain, how idempiere AD_Changelog table can be migrated to postgres Table partitioning.
Our Use Case
Table: AD_Changelog Records Count: 12-15 mill, with data retention 1 year excl system tenant. DB Server AWS RDS: Postgres 14.2
2 issues we discover
- changelog is heavy, big index write are slow
- Record Info - open opened up to 3-5 sec to show result, after migration approx 100ms.
Solution
PG support table partitioning since version 10.x. We are using 14.2 so the functionality is mature. We decide to partition ad_changelog table with type list partitioning by key AD_Client_ID.
This implementation doesn't support live migration. Means, you need to stop proxy and implement in maintenance window.
Migration Process
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;
STEP 3. 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
STEP 4. 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;
STEP 5. 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;
STEP 6. PG Function Report the data migration result
Warnings:
A partition has been created AD_Changelog_1000024
A partition has been migrated AD_Changelog_1000024
.....
A partition has been created AD_Changelog_1000003
A partition has been migrated AD_Changelog_1000003
A partition has been created AD_Changelog_1000019
A partition has been migrated AD_Changelog_1000019
....
A partition has been migrated AD_Changelog_1000017
A partition has been created AD_Changelog_1000008
A partition has been migrated AD_Changelog_1000008
A partition has been created AD_Changelog_1000027
A partition has been migrated AD_Changelog_1000027
A partition has been created AD_Changelog_1000028
A partition has been migrated AD_Changelog_1000028
A partition has been created AD_Changelog_1000020
A partition has been migrated AD_Changelog_1000020
..........
A partition has been created AD_Changelog_0
A partition has been migrated AD_Changelog_0
A partition has been created AD_Changelog_1000015
A partition has been migrated AD_Changelog_1000015
SELECT executed successfully
Execution time: 3m 7s
STEP 7. Update PG statistics
vacuum analyze ad_changelog
