postgres分表

1. 基于partition by  

v10 及之后的版本支持

CREATE TABLE orders (
    id serial,
    user_id int4,
    create_time timestamp(0) 
) PARTITION BY RANGE(create_time);

CREATE TABLE orders_history PARTITION OF orders FOR VALUES FROM ('2000-01-01') TO ('2020-03-01');

CREATE TABLE orders_202003 PARTITION OF orders FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');

　　

2.  基于 inherits  trigger

CREATE TABLE orders2 (
    id serial,
    user_id int4,
    create_time timestamp(0) 
) ;

CREATE TABLE orders2_202003 (
	CHECK (create_time BETWEEN '2020-03-01' and '2020-04-01')
) inherits (orders2);

CREATE TABLE orders2_202004 (
	CHECK (create_time BETWEEN '2020-04-01' and '2020-05-01')
) inherits (orders2);

---- trigger
CREATE OR REPLACE FUNCTION orders2_partition_function()
  RETURNS TRIGGER AS $$
BEGIN
  IF NEW.create_time >= '2020-03-01' and NEW.create_time < '2020-04-01'
  THEN
    INSERT INTO orders2_202003 VALUES (NEW.*);
  ELSIF NEW.create_time >= '2020-04-01' and NEW.create_time <'2020-05-01'
    THEN
      INSERT INTO orders2_202004 VALUES (NEW.*);
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_orders2_partition_trigger
BEFORE INSERT ON orders2
FOR EACH ROW EXECUTE PROCEDURE orders2_partition_function();

3. 基于 inherits rule

CREATE TABLE orders3 (
    id serial,
    user_id int4,
    create_time timestamp(0) 
) ;

CREATE TABLE orders3_202003 (
	CHECK (create_time BETWEEN '2020-03-01' and '2020-04-01')
) inherits (orders3);

CREATE TABLE orders3_202004 (
	CHECK (create_time BETWEEN '2020-04-01' and '2020-05-01')
) inherits (orders3);

---- rule
CREATE or replace  RULE orders3_inter_rule_202003 AS  
ON INSERT TO orders3  
WHERE     new.create_time >= '2020-03-01' and NEW.create_time < '2020-04-01'
DO INSTEAD  INSERT INTO orders3_202003 VALUES (NEW.*);  

CREATE or replace  RULE orders3_inter_rule_202004 AS  
ON INSERT TO orders3  
WHERE     new.create_time >= '2020-04-01' and NEW.create_time < '2020-05-01'
DO INSTEAD  INSERT INTO orders3_202004 VALUES (NEW.*);