分区表

什么样的表需要分区

1. 硬件不同,性能开始降级不同,大概表的大小和内存差不多的时候,开始分区
2. 更新和删除的行会导致最终需要清理的死元组,释放资源的时候,需要全表扫描

查看值在那个分区

-- 字段 tableoid::regclass
select *,tableoid::regclass from test_range_key;

范围分区

create table test_range(id int, date date) 
  partition by range ( date );

create table test_range_201801 partition of test_range(id primary key) 
  for values from ('2018-01-01') to ('2018-02-01');

create table test_range_201802 partition of test_range(id primary key) 
  for values from ('2018-02-01') to ('2018-03-01');

list分区

list不支持多列

test02=# create table test_list(id int, city text) partition by list(city);
CREATE TABLE
test02=# create table test_list_jn partition of test_list(id primary key) for values in ('济南');
CREATE TABLE
test02=# create table test_list_sjz partition of test_list(id primary key) for values in ('石家庄');
CREATE TABLE
test02=# \d+ test_list
                                 Table "public.test_list"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              | 
 city   | text    |           |          |         | extended |              | 
Partition key: LIST (city)
Partitions: test_list_jn FOR VALUES IN ('济南'),
            test_list_sjz FOR VALUES IN ('石家庄')

hash分区

索引增强(pg>=11)

1. 分区表可以有索引
2. 分区主表可以创建唯一约束，需要包含分区键

create table pk (id int primary key) partition by range(id);

create table pk1 partition of pk for values from (1) to (10);

数据分片

citusdata citusdata文档 citusdata-docker