PostgreSQL分区

发表于 更新于 分类于 阅读次数： Changyan： 本文字数： 2.9k

分区的具体好处是：

某些类型的查询性能可以得到极大提升

更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问

批量删除可以用简单的删除某个分区来实现

可以将很少用的数据移动到便宜的、转速慢的存储介质上

在PostgreSQL里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。无论何时，都应保证主表里面是空的。

当表本身大小超过了机器物理内存的实际大小时（the size of the table should exceed the physical memory of the database server），可以考虑分区。

PostgreSQL目前仅支持范围分区和列表分区，尚未支持散列分区

实现

创建主表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create table tbl_weather_partition(
  id varchar(64) primary key ,
  city_code smallint ,
  created_time timestamp,
  weather char(6),
  temperature smallint,
  wind smallint,
  humidity smallint,
  air_quality smallint
);

comment on table tbl_weather_partition is '天气表主表';

comment on column tbl_weather_partition.city_code is '城市编码';
comment on column tbl_weather_partition.created_time is '创建时间';
comment on column tbl_weather_partition.weather is '天气情况';
comment on column tbl_weather_partition.temperature is '温度';
comment on column tbl_weather_partition.wind is '风力';
comment on column tbl_weather_partition.humidity is '湿度';
comment on column tbl_weather_partition.air_quality is '空气质量';

创建分区表

暂时只创建两个分区，作为示例

1
2
3
4
5
6
7
create table tbl_weather_partition_20180709 (
  check (created_time >= DATE '2018-07-09' and created_time < DATE '2018-07-10')
) inherits (tbl_weather_partition);

create table tbl_weather_partition_20180710 (
  check (created_time >= DATE '2018-07-10' and created_time < DATE '2018-07-11')
) inherits (tbl_weather_partition);

分区键上建立索引

分区用于查询，所以索引应该建立在分区表上，不能建立在主表上

1
2
3
create index tbl_weather_partition_20180709_created_time_index on tbl_weather_partition_20180709 (created_time);

create index tbl_weather_partition_20180709_city_code_index on tbl_weather_partition_20180709 (city_code);

创建触发器函数

对于开发人员来说，希望数据库是透明的，只管 insert into tbl_partition。对于数据插向哪个分区，则希望由DB决定。这点，ORACLE实现了，但是PG不行，需要前期人工处理下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE FUNCTION tbl_weather_partition_insert_trigger()
  RETURNS TRIGGER AS $$
BEGIN
  IF ( NEW.created_time >= DATE '2018-07-09' AND
       NEW.created_time < DATE '2018-07-10' ) THEN
    INSERT INTO tbl_weather_partition_20180709 VALUES (NEW.*);
  ELSIF ( NEW.created_time >= DATE '2018-07-10' AND
          NEW.created_time < DATE '2018-07-11' ) THEN
    INSERT INTO tbl_weather_partition_20180710 VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'Date out of range. Fix the tbl_weather_partition_insert_trigger() function!';
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

创建触发器

触发器的作用是当进行插入操作时触发tbl_weather_partition_insert_trigger函数，将数据插入到分区表中

1
2
3
create trigger insert_tbl_partition_trigger
  before insert on tbl_weather_partition
  for each row execute procedure tbl_weather_partition_insert_trigger();

测试

在主表中插入一条数据，在分区表中可实时查询到该数据

1
2
3
insert into tbl_weather_partition values ('1', 1, '2018-07-09 18:02:00', '多云', 32, 2, 80, 78);

select * from tbl_weather_partition_20180709;

更新或删除主表中的数据，则分区表中的数据也会同步更新或删除

1
2
3
update tbl_weather_partition set temperature = 35 where id = '1';

select * from tbl_weather_partition_20180709;