PostgreSQL partition table's arithmetic tuning

Thanks Digoal

PostgreSQL for larger table, use partiton table to improved the performance.

The partition arithmetic use trigger to achieve. The good arithmetic can reduced CPU cost. In particular, the partition table in the large amount of concurrentoperations obviously.

Expample:

Parent table:

          Table "public.tbl_user"

  Column   |         Type          | Modifiers 

----------- ----------------------- -----------

 id        | integer               | not null

 firstname | character varying(30) | 

 lastname  | character varying(30) | 

 corp      | character varying(20) | 

 age       | integer               | 

Indexes:

    "tbl_user_pk" PRIMARY KEY, btree (id)

    "idx_user_age" btree (age)

Triggers:

    delete_tbl_user_trigger BEFORE DELETE ON tbl_user FOR EACH ROW EXECUTE PROCEDURE tbl_user_delete_trigger()

    insert_tbl_user_trigger BEFORE INSERT ON tbl_user FOR EACH ROW EXECUTE PROCEDURE tbl_user_insert_trigger()

Number of child tables: 16 (Use \d to list them.)

Child table:

        tbl_user0  

        tbl_user1  

        tbl_user10 

        tbl_user11 

        tbl_user12 

        tbl_user13 

        tbl_user14 

        tbl_user15 

        tbl_user2  

        tbl_user3  

        tbl_user4  

        tbl_user5  

        tbl_user6  

        tbl_user7  

        tbl_user8  

        tbl_user9  

Create child table function SQL :

CREATE OR REPLACE FUNCTION f_create_table(i_min integer, i_max integer)

 RETURNS void

 LANGUAGE plpgsql

AS $function$

declare

begin

for i in i_min..i_max loop

execute 'create table tbl_user'||i||' (like tbl_user including constraints including defaults including indexes) inherits (tbl_user);';

end loop;

return;

end;

$function$ ;

Check Function:

CREATE OR REPLACE FUNCTION f_ck_table(i_min integer, i_max integer)

 RETURNS void

 LANGUAGE plpgsql

AS $function$

declare

begin

for i in i_min..i_max loop

execute 'alter table tbl_user'||i||' add constraint ck_user'||i||' check (mod(id,16)='||i||');';

end loop;

return;

end;

$function$ ;

Create trigger function and trigger :

CREATE OR REPLACE FUNCTION tbl_userinsert_trigger()

RETURNS TRIGGER AS $$

BEGIN

    IF    ( mod(NEW.id,16) = 0 ) THEN

        INSERT INTO tbl_user0 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 1 ) THEN

        INSERT INTO tbl_user1 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 2 ) THEN

        INSERT INTO tbl_user2 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 3 ) THEN

        INSERT INTO tbl_user3 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 4 ) THEN

        INSERT INTO tbl_user4 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 5 ) THEN

        INSERT INTO tbl_user5 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 6 ) THEN

        INSERT INTO tbl_user6 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 7 ) THEN

        INSERT INTO tbl_user7 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 8 ) THEN

        INSERT INTO tbl_user8 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 9 ) THEN

        INSERT INTO tbl_user9 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 10 ) THEN

        INSERT INTO tbl_user10 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 11 ) THEN

        INSERT INTO tbl_user11 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 12 ) THEN

        INSERT INTO tbl_user12 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 13 ) THEN

        INSERT INTO tbl_user13 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 14 ) THEN

        INSERT INTO tbl_user14 VALUES (NEW.*);

    ELSIF ( mod(NEW.id,16) = 15 ) THEN

        INSERT INTO tbl_user15 VALUES (NEW.*);

    ELSE

        RAISE EXCEPTION 'ID out of range.  Please fix the tbl_userinsert_trigger() function!';

    END IF;

    RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tbl_userdelete_trigger()

RETURNS TRIGGER AS $$

BEGIN

    IF    ( mod(OLD.id,16) = 0 ) THEN

        DELETE FROM tbl_user0 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 1 ) THEN

        DELETE FROM tbl_user1 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 2 ) THEN

        DELETE FROM tbl_user2 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 3 ) THEN

        DELETE FROM tbl_user3 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 4 ) THEN

        DELETE FROM tbl_user4 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 5 ) THEN

        DELETE FROM tbl_user5 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 6 ) THEN

        DELETE FROM tbl_user6 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 7 ) THEN

        DELETE FROM tbl_user7 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 8 ) THEN

        DELETE FROM tbl_user8 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 9 ) THEN

        DELETE FROM tbl_user9 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 10 ) THEN

        DELETE FROM tbl_user10 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 11 ) THEN

        DELETE FROM tbl_user11 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 12 ) THEN

        DELETE FROM tbl_user12 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 13 ) THEN

        DELETE FROM tbl_user13 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 14 ) THEN

        DELETE FROM tbl_user14 where id=OLD.id;

    ELSIF ( mod(OLD.id,16) = 15 ) THEN

        DELETE FROM tbl_user15 where id=OLD.id;

    ELSE

        RAISE EXCEPTION 'ID out of range.  Please fix the tbl_userdelete_trigger() function!';

    END IF;

    RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE TRIGGER insert_tbl_user_trigger

    BEFORE INSERT ON tbl_user

    FOR EACH ROW EXECUTE PROCEDURE tbl_user_insert_trigger();

CREATE TRIGGER delete_tbl_user_trigger

    BEFORE DELETE ON tbl_user

    FOR EACH ROW EXECUTE PROCEDURE tbl_user_delete_trigger();

Performace testing:

insert into tbl_user select generate_series(0,16000000,16),'Huang','Kyle','Bsmart sz',27;

Time: 47938 (ms)

truncate table tbl_user;

Time: 482 (ms)

insert into tbl_user select generate_series(15,16000000,16),'Huang','Kyle','Bsmart sz',27; 

Time: 63191 (ms)

bmcv3=# select count(*) from tbl_user;

  count  

---------

 1000000

(1 row)

(Accurate arrived in target table)

bmcv3=# select count(*) from tbl_user15;

  count  

---------

 1000000

(1 row)

each insert command that check from first to last cost time :  (63191 - 47938)/ 100w = 0.015253 ms

not use trigger 

insert into tbl_user_single select generate_series(0,16000000,16),'Huang','Kyle','Bsmart sz',27; 

Time: 8599 (ms)

each check cost time = (47938 - 8599) / 100w = 0.039339 ms

total cost time = 0.054592