Skip to main content

Table Partitioning

  • 查询性能提升
    • 当主要查询的数据都在一个分片里时,索引加载到内存使用率更高
  • 当更新和查询单个分片时性能提升
    • 此时可能通过 seq scan 而不需要使用索引,导致随机访问
  • 通过操作分片表来批量操作数据,性能大幅度提高
  • 较少使用的分片可移到更便宜的存储上

当单表大小超过物理内存时,建议分表

tip
  • 分表索引不可以 CONCURRENTLY
    • 除非先 DETACH
  • 分表可以创建 CREATE INDEX ON ONLY
    • 创建后索引是无效的
    • 在单独分片上创建然后 ATTACH
    • 可以使用该方式来创建 UNIQUE 和 PRIMARY KEY
  • 有时候表继承比分片更好用
caution
  • 唯一 限制必须包含所有 partition key columns
  • 不能创建跨表 exclusion constraint
    • 不支持跨分片限制
  • 针对 INSERT 的 BEFORE ROW 触发器无法更改分片
  • 临时表和持久表不可以混用

分表策略

  • 范围 - 例如 时间字段
  • 列表 - 例如 年字段
  • 哈希 - 例如 UUID 字段

常见策略

  • 基于使用场景的冷热数据区分 - 例如 最近一周数据单独表
  • 基于数据本身的逻辑结构区分 - 例如 区域、年份
-- RANGE 分片
-- 创建虚拟表
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);

-- LIST 分片
CREATE TABLE cities (
city_id bigserial not null,
name text not null,
population bigint
) PARTITION BY LIST (left(lower(name), 1));
-- LIST 子表
CREATE TABLE cities_ab
PARTITION OF cities (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('a', 'b');
-- 默认分片
CREATE TABLE cities_partdef
PARTITION OF cities DEFAULT;

-- HASH 分片
CREATE TABLE orders (
order_id bigint not null,
cust_id bigint not null,
status text
) PARTITION BY HASH (order_id);
-- HASH 子表
CREATE TABLE orders_p1 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p3 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p4 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 可直接创建索引 - 当增加分片时会对分片加索引
CREATE INDEX ON measurement (logdate);

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
-- 可修改 TABLESPACE 选择不同存储介质
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01')
TABLESPACE fasttablespace;;

-- 分片表可以再分片
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
PARTITION BY RANGE (peaktemp);

-- 取保开启分片裁剪配置 - 默认开启
select * from pg_settings where name='enable_partition_pruning';

-- 可以直接删除分片表 - 会锁主表
DROP TABLE measurement_y2006m02;
-- 可以排除分片
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;


-- 单独创建分片,添加校验,导入数据,加入到分片
CREATE TABLE measurement_y2008m02
(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
TABLESPACE fasttablespace;
-- 提前创建好 CONSTRAINT - 否则 ATTACH 时还是会创建且需要 ACCESS EXCLUSIVE
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work
-- SHARE UPDATE EXCLUSIVE
-- ATTACH 后可以删除创建的 CHECK
-- 如果有 DEFAULT 也会检查,除非创建了排除 CHECK
ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );