Compare Oracle's & PostgreSQL's Partition Table write performance

PostgreSQL的对于应用透明的分区表需要依赖RULE或TRIGGER来实现，应用-awareness的除外。因此在数据库端做分区需要消耗大 量的CPU资源。
那么ORACLE的分区表是不是也要消耗大量CPU资源呢？
下面来简单的测试一下：
ORACLE：
分区 表
create table tbl_user(id int,firstname varchar2(64),lastname varchar2(64),corp varchar2(64),age int,primary key (id)) partition by range ( id ) (
partition p_1 values less than (-14000001),
partition p_2 values less than (-13000001),
partition p_3 values less than (-12000001),
partition p_4 values less than (-11000001),
partition p_5 values less than (-10000001),
partition p_6 values less than (-9000001),
partition p_7 values less than (-8000001),
partition p_8 values less than (-7000001),
partition p_9 values less than (-6000001),
partition p_10 values less than (-5000001),
partition p_11 values less than (-4000001),
partition p_12 values less than (-3000001),
partition p_13 values less than (-2000001),
partition p_14 values less than (-1000001),
partition p_15 values less than (-1),
partition p_16 values less than (1000001)
);
SQL> insert into tbl_user(id,firstname,lastname,corp,age) select level,'zhou','digoal','sky-mobi',27 from dual connect by level <1000001;
1000000 rows created.
Elapsed: 00:00:04.71
SQL> commit;
Elapsed: 00:00:00.02
SQL> select count(*) from tbl_user partition (p_16);
  COUNT(*)
----------
   1000000
SQL> drop table tbl_user;
Elapsed: 00:00:36.76
插入耗时 : 4730ms
单表
SQL> create table tbl_user (id int,firstname varchar2(64),lastname varchar2(64),corp varchar2(64),age int,primary key (id));
Elapsed: 00:00:00.03
SQL> insert into tbl_user(id,firstname,lastname,corp,age) select level,'zhou','digoal','sky-mobi',27 from dual connect by level <1000001;
1000000 rows created.
Elapsed: 00:00:04.43
SQL> commit;
Elapsed: 00:00:00.02
SQL> drop table tbl_user;
Elapsed: 00:00:33.44
插入耗时 : 4450ms

PostgreSQL :
分 区表
create table tbl_user (id int,firstname varchar(64),lastname varchar(64),corp varchar(64) ,age int,primary key (id));

CREATE OR REPLACE FUNCTION digoal.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$;

select * from f_create_table(0,15);

CREATE OR REPLACE FUNCTION tbl_user_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF    ( NEW.id < -14000001 ) THEN
        INSERT INTO tbl_user_0 VALUES (NEW.*);
    ELSIF ( NEW.id < -13000001 and NEW.id >= -14000001 ) THEN
        INSERT INTO tbl_user_1 VALUES (NEW.*);
    ELSIF ( NEW.id < -12000001 and NEW.id >= -13000001 ) THEN
        INSERT INTO tbl_user_2 VALUES (NEW.*);
    ELSIF ( NEW.id < -11000001 and NEW.id >= -12000001 ) THEN
        INSERT INTO tbl_user_3 VALUES (NEW.*);
    ELSIF ( NEW.id < -10000001 and NEW.id >= -11000001 ) THEN
        INSERT INTO tbl_user_4 VALUES (NEW.*);
    ELSIF ( NEW.id < -9000001 and NEW.id >= -10000001 ) THEN
        INSERT INTO tbl_user_5 VALUES (NEW.*);
    ELSIF ( NEW.id < -8000001 and NEW.id >= -9000001 ) THEN
        INSERT INTO tbl_user_6 VALUES (NEW.*);
    ELSIF ( NEW.id < -7000001 and NEW.id >= -8000001 ) THEN
        INSERT INTO tbl_user_7 VALUES (NEW.*);
    ELSIF ( NEW.id < -6000001 and NEW.id >= -7000001 ) THEN
        INSERT INTO tbl_user_8 VALUES (NEW.*);
    ELSIF ( NEW.id < -5000001 and NEW.id >= -6000001 ) THEN
        INSERT INTO tbl_user_9 VALUES (NEW.*);
    ELSIF ( NEW.id < -4000001 and NEW.id >= -5000001 ) THEN
        INSERT INTO tbl_user_10 VALUES (NEW.*);
    ELSIF ( NEW.id < -3000001 and NEW.id >= -4000001 ) THEN
        INSERT INTO tbl_user_11 VALUES (NEW.*);
    ELSIF ( NEW.id < -2000001 and NEW.id >= -3000001 ) THEN
        INSERT INTO tbl_user_12 VALUES (NEW.*);
    ELSIF ( NEW.id < -1000001 and NEW.id >= -2000001 ) THEN
        INSERT INTO tbl_user_13 VALUES (NEW.*);
    ELSIF ( NEW.id < -1 and NEW.id >= -1000001 ) THEN
        INSERT INTO tbl_user_14 VALUES (NEW.*);
    ELSIF ( NEW.id < 1000001 and NEW.id >= -1 ) THEN
        INSERT INTO tbl_user_15 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'ID out of range.  Please fix the tbl_user_insert_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();

digoal=> insert into tbl_user select generate_series(1,1000000),'zhou','digoal','sky-mobi',27;
INSERT 0 0
Time: 30157.443 ms
digoal=> select count(*) from tbl_user_15;
  count 
---------
 1000000
digoal=> insert into tbl_user select generate_series(-15000001,-14000000),'zhou','digoal','sky-mobi',27;
INSERT 0 0
Time: 21707.555 ms
digoal=> select count(*) from tbl_user_0;
  count 
---------
 1000000
插入耗时 : 30157ms
PostgreSQL 分区表的插入性能完全取决于触发器的开销，你会发现第一次判断跳出和最后一次判断跳出相差了30157-21707=8450ms。

单表
digoal=> insert into tbl_user_info_single select generate_series(-15000001,-14000000),'zhou','digoal','sky-mobi',27;
INSERT 0 1000002
Time: 1469.540 ms
插入耗时 1469ms

分区表比较:
oracle insert 100W 耗时 4730ms
postgresql insert 100W 耗时 30157ms
单表比较:
oracle insert 100W 耗时 4450ms
postgresql insert 100W 耗时 1469ms
从以上对比可以看 出，如果要做应用透明的分区，PostgreSQL不如Oracle。如果做application-awareness的分区，也就是让应用来处理分区 的代码，PostgreSQL的性能好过Oracle。在做PostgreSQL开发是开发人员需要认识到这点。