PostgreSQL分区管理 实现数据库优化（附图片详解）

PostgreSQL属于关系型数据库，我认为功能比MySql更加丰富强大。

如果合理利用PostgreSQL一些功能，那么使用关系数据库处理亿级数据量将不再是梦想。

本文将给大家介绍如何使用PostgreSQL进行分区管理实现数据库优化。

 

创建bus_data主表:

CREATE TABLE "public"."bus_data" (
  "dt" date,
  "dealtime" timestamp(0),
  "cardnum" varchar(25) COLLATE "pg_catalog"."default",
  "linenum" varchar(8) COLLATE "pg_catalog"."default",
  "carnum" varchar(15) COLLATE "pg_catalog"."default"
) PARTITION BY RANGE (dt);

 

创建bus_data_分表:

CREATE TABLE bus_data_2020_01_1 PARTITION OF bus_data FOR VALUES FROM ('2020-01-01') TO ('2020-01-15');
CREATE TABLE bus_data_2020_01_2 PARTITION OF bus_data FOR VALUES FROM ('2020-01-15') TO ('2020-02-01');

CREATE TABLE bus_data_2020_02_1 PARTITION OF bus_data FOR VALUES FROM ('2020-02-01') TO ('2020-02-15');
CREATE TABLE bus_data_2020_02_2 PARTITION OF bus_data FOR VALUES FROM ('2020-02-15') TO ('2020-03-01');

CREATE TABLE bus_data_2020_03_1 PARTITION OF bus_data FOR VALUES FROM ('2020-03-01') TO ('2020-03-15');
CREATE TABLE bus_data_2020_03_2 PARTITION OF bus_data FOR VALUES FROM ('2020-03-15') TO ('2020-04-01');

CREATE TABLE bus_data_2020_04_1 PARTITION OF bus_data FOR VALUES FROM ('2020-04-01') TO ('2020-04-15');
CREATE TABLE bus_data_2020_04_2 PARTITION OF bus_data FOR VALUES FROM ('2020-04-15') TO ('2020-05-01');

CREATE TABLE bus_data_2020_05_1 PARTITION OF bus_data FOR VALUES FROM ('2020-05-01') TO ('2020-05-15');
CREATE TABLE bus_data_2020_05_2 PARTITION OF bus_data FOR VALUES FROM ('2020-05-15') TO ('2020-06-01');

CREATE TABLE bus_data_2020_06_1 PARTITION OF bus_data FOR VALUES FROM ('2020-06-01') TO ('2020-06-15');
CREATE TABLE bus_data_2020_06_2 PARTITION OF bus_data FOR VALUES FROM ('2020-06-15') TO ('2020-07-01');

CREATE TABLE bus_data_2020_07_1 PARTITION OF bus_data FOR VALUES FROM ('2020-07-01') TO ('2020-07-15');
CREATE TABLE bus_data_2020_07_2 PARTITION OF bus_data FOR VALUES FROM ('2020-07-15') TO ('2020-08-01');

CREATE TABLE bus_data_2020_08_1 PARTITION OF bus_data FOR VALUES FROM ('2020-08-01') TO ('2020-08-15');
CREATE TABLE bus_data_2020_08_2 PARTITION OF bus_data FOR VALUES FROM ('2020-08-15') TO ('2020-09-01');

CREATE TABLE bus_data_2020_09_1 PARTITION OF bus_data FOR VALUES FROM ('2020-09-01') TO ('2020-09-15');
CREATE TABLE bus_data_2020_09_2 PARTITION OF bus_data FOR VALUES FROM ('2020-09-15') TO ('2020-10-01');

CREATE TABLE bus_data_2020_10_1 PARTITION OF bus_data FOR VALUES FROM ('2020-10-01') TO ('2020-10-15');
CREATE TABLE bus_data_2020_10_2 PARTITION OF bus_data FOR VALUES FROM ('2020-10-15') TO ('2020-11-01');

CREATE TABLE bus_data_2020_11_1 PARTITION OF bus_data FOR VALUES FROM ('2020-11-01') TO ('2020-11-15');
CREATE TABLE bus_data_2020_11_2 PARTITION OF bus_data FOR VALUES FROM ('2020-11-15') TO ('2020-12-01');

CREATE TABLE bus_data_2020_12_1 PARTITION OF bus_data FOR VALUES FROM ('2020-12-01') TO ('2020-12-15');
CREATE TABLE bus_data_2020_12_2 PARTITION OF bus_data FOR VALUES FROM ('2020-12-15') TO ('2021-01-01');

在pgAdmin4中可查看分区情况如下：

注意：

create table table_2020_01_1 partition of table for values from ('2020-01-01') to ('2020-01-15')

  分区表的范围相当于 2020-01-01 <= dt < 2020-01-15;

 

给分表建索引 postgre分区表中实际数据都是存在分区表上的，所以只有给分区表建索引才有作用。

CREATE INDEX bus_data_2020_01_1_dt_idx ON bus_data_2020_01_1 USING btree(dt);

CREATE INDEX bus_data_2020_01_2_dt_idx ON bus_data_2020_01_2 USING btree(dt);

CREATE INDEX bus_data_2020_02_1_dt_idx ON bus_data_2020_02_1 USING btree(dt);

CREATE INDEX bus_data_2020_02_2_dt_idx ON bus_data_2020_02_2 USING btree(dt);

CREATE INDEX bus_data_2020_03_1_dt_idx ON bus_data_2020_03_1 USING btree(dt);

CREATE INDEX bus_data_2020_03_2_dt_idx ON bus_data_2020_03_2 USING btree(dt);

CREATE INDEX bus_data_2020_04_1_dt_idx ON bus_data_2020_04_1 USING btree(dt);

CREATE INDEX bus_data_2020_04_2_dt_idx ON bus_data_2020_04_2 USING btree(dt);

CREATE INDEX bus_data_2020_05_1_dt_idx ON bus_data_2020_05_1 USING btree(dt);

CREATE INDEX bus_data_2020_05_2_dt_idx ON bus_data_2020_05_2 USING btree(dt);

CREATE INDEX bus_data_2020_06_1_dt_idx ON bus_data_2020_06_1 USING btree(dt);

CREATE INDEX bus_data_2020_06_2_dt_idx ON bus_data_2020_06_2 USING btree(dt);

CREATE INDEX bus_data_2020_07_1_dt_idx ON bus_data_2020_07_1 USING btree(dt);

CREATE INDEX bus_data_2020_07_2_dt_idx ON bus_data_2020_07_2 USING btree(dt);

CREATE INDEX bus_data_2020_08_1_dt_idx ON bus_data_2020_08_1 USING btree(dt);

CREATE INDEX bus_data_2020_08_2_dt_idx ON bus_data_2020_08_2 USING btree(dt);

CREATE INDEX bus_data_2020_09_1_dt_idx ON bus_data_2020_09_1 USING btree(dt);

CREATE INDEX bus_data_2020_09_2_dt_idx ON bus_data_2020_09_2 USING btree(dt);

CREATE INDEX bus_data_2020_10_1_dt_idx ON bus_data_2020_10_1 USING btree(dt);

CREATE INDEX bus_data_2020_10_2_dt_idx ON bus_data_2020_10_2 USING btree(dt);

CREATE INDEX bus_data_2020_11_1_dt_idx ON bus_data_2020_11_1 USING btree(dt);

CREATE INDEX bus_data_2020_11_2_dt_idx ON bus_data_2020_11_2 USING btree(dt);

CREATE INDEX bus_data_2020_12_1_dt_idx ON bus_data_2020_12_1 USING btree(dt);

CREATE INDEX bus_data_2020_12_2_dt_idx ON bus_data_2020_12_2 USING btree(dt);

在pgAdmin4中可查看分区表索引情况如下：

 

SELECT * FROM bus_data WHERE dt = '2020-01-01'会在后台转为对分区表查询。

或者使用 SELECT * FROM public.bus_data_2020_01_1 直接对分区表进行查询。

在单表数据过大时查询速度会明细变慢，使用分区表对大数据量进行分表存储而所有增删改只需对主表进行操作会大大提高查询效率。