postgresql 表分区

postgresql 表分区

背景

一个大表查询性能往往不高, 其原因在于数据加载 data load,扫描数据 table scans ,内存交换 memory swap 等等操作的时间成本会随着数据量的增加而上升.

从 PG 11 开始, 支持较为简洁的声明表分区. 为提高大表性能提供了一种解决方案.

什么是表分区

表分区(Partitioning) 指的是: 将一个逻辑上的大表拆分成多个物理上的小表. 是一种单体数据库表的水平分表.

表分区能带来以下好处:

• 在特定情形下, 查询性能的大幅度提升. 尤其是在绝大多数查询集中于某个分区上时.
• 当查询或者更新一个大百分比的单一分区时, 性能可能通过分区的顺序扫描替代索引扫描来提高.
• 批量加载或者删除可以通过添加或者减少分区来完成. 这比直接 delete 要快得多.
• 极少使用的数据可以迁移到更加便宜的存储介质中

通常只有当一个表非常大(超过数据库服务器的物理内存)时，这些好处才有价值, 但表从分区中获益的确切时间取决于应用程序和使用场景。

如何进行表分区

pg 提供以下表分区的形式

• Range 范围分区
  • 通过值的范围来进行分区, 例如时间, 数字等. 不过要注意数据范围是左闭右开. 例如:[10, 100)表示从 10 到 99.
• List 列表分区
  • 通过固定列表里的值进行分区
• Hash 哈希分区
  • 根据 hash 算法进行分区
• Multi-level 多级分区
  • 分区的分区, 可以将上述 3 种分区方式混合使用. 例如: 先按照时间分区, 再按照状态再次分区.

-- 查看表 分区信息
select
	d.relname, c.relname
from
	pg_class c
	join pg_inherits i on i.inhrelid = c. oid
	join pg_class d on d.oid = i.inhparent
where
	d.relname = '表名';

Range 范围分区

-- 创建分区表, 并以列 arr 进行分区
CREATE TABLE range_customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY RANGE(arr);
-- 分区表
CREATE TABLE cust_arr_small PARTITION OF range_customers FOR VALUES FROM (MINVALUE) TO (25);
CREATE TABLE cust_arr_medium PARTITION OF range_customers FOR VALUES FROM (25) TO (75);
CREATE TABLE cust_arr_large PARTITION OF range_customers FOR VALUES FROM