PostgreSQL 分区

https://zhuanlan.zhihu.com/p/112054799

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

-- 创建父表   CREATE TABLE orders_history_subs ( id serial, user_id int4, create_time timestamp(0)) PARTITION BY RANGE(create_time);   -- 创建分区表   CREATE TABLE orders_202002 PARTITION OF orders_history_subs FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');   CREATE TABLE orders_history_before_202002 PARTITION OF orders_history_subs FOR VALUES FROM ('2000-01-01') TO ('2020-02-01');   CREATE TABLE orders_history_before_202003 PARTITION OF orders_history_subs FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');   -- 创建索引   CREATE INDEX order_idx_history_202002_create_time ON orders_202002 USING btree(create_time);   CREATE INDEX order_idx_history_before_202002_create_time ON orders_history_before_202002 USING btree(create_time);       -- 绑定“二级"分区到父表上   ALTER TABLE orders ATTACH PARTITION orders_history_subs FOR VALUES FROM ('2000-01-01') TO ('2020-03-01');     SELECT * from orders_history_subs     INSERT INTO orders_history_subs (user_id,create_time) VALUES (4,'2020-04-11 17:23:12');   UPDATE orders_history_subs SET user_id = 3 WHERE id = 3;   DELETE FROM orders_history_subs WHERE id = 8;

　

 

(2)，解绑要进行拆分的分区

ALTER TABLE orders DETACH PARTITION orders_history;

(4)，绑定“二级"分区到父表上

ALTER TABLE orders ATTACH PARTITION orders_history_subs FOR VALUES FROM ('2000-01-01') TO 
('2020-03-01');

(5)，迁移旧数据

将原 orders_history 表中数据迁移过来

INSERT INTO orders_history_subs select * from orders_history;