修改表-官网手册
表分区-官网手册

概述

分区是指将一个逻辑上大的表拆分为较小的物理块。分区可以提供以下好处：

• 在某些情况下，尤其是当表中大多数被频繁访问的行位于单个分区或少量分区中时，查询性能可以得到显着提高。分区替代了索引的前几列，从而减小了索引的大小，并使索引中频繁使用的部分更有可能装入内存。

• 当查询或更新访问单个分区的很大一部分时，可以通过对该分区进行顺序扫描而不是使用索引和分散在整个表中的随机访问读取来提高性能。

• 如果计划将这种需求计划到分区设计中，则可以通过添加或删除分区来完成批量加载和删除。做ALTER TABLE DETACH PARTITION或采用滴的个体分区DROP TABLE远快于批量操作。这些命令还完全避免了VACUUM由于批量操作引起的开销DELETE。

很少使用的数据可以迁移到更便宜，更慢的存储介质上。

通常只有在表很大的情况下，这些好处才是值得的。表可以从分区中受益的确切时间取决于应用程序，尽管经验法则是表的大小应超过数据库服务器的物理内存。

PostgreSQL为以下形式的分区提供内置支持：

• 范围分区
  该表被划分为由键列或列集定义的“ 范围 ”，分配给不同分区的值的范围之间没有重叠。例如，可以按日期范围或特定业务对象的标识符范围进行分区。

• 列表分区
  通过显式列出哪些键值出现在每个分区中来对表进行分区。

• 哈希分区
  通过为每个分区指定模数和余数来对表进行分区。每个分区将保留行，分区键的哈希值除以指定的模数将产生指定的余数。

如果您的应用程序需要使用上面未列出的其他分区形式，UNION ALL则可以使用替代方法，例如继承和视图。此类方法提供了灵活性，但没有内置声明性分区的某些性能优势。

分区

list 分区

partition by list

CREATE TABLE "table_name" (
	"id" bigserial not null,
	"str1" varchar(255),
	"country" varchar(2)
	"int1" int8,
	"int2" int8 DEFAULT 0,
	"money1" numeric(10,2),
	"created_at" timestamp(0),
	"updated_at" timestamp(0)
)partition by list( country )
WITH (OIDS=FALSE);

-- 注释
COMMENT ON COLUMN "table_name"."country" IS 'country comment xxx';

-- Partitions SQL
create table table_name_us partition of table_name for values in ('US');
create table table_name_ca partition of table_name for values in ('CA');
create table table_name_default partition of table_name default; 

“created_at” timestamp(0) DEFAULT CURRENT_TIMESTAMP(0) NOT NULL,

range 分区

注意前后时间范围分配衔接

分区依据在partition by range,

CREATE TABLE "table_name" (
	"id" bigserial not null,
	"report_date" date,
	"created_at" timestamp(0),
	"updated_at" timestamp(0)
)partition by range(report_date)
WITH (OIDS=FALSE);

COMMENT ON COLUMN "table_name"."report_date" IS 'xxxxx';


-- 再按日期分区
create table table_name_201803_default partition of table_name default;
	
create table table_name_201803_before partition of table_name 
	for values from (MINVALUE) to ('2018-03-01');
	
create table table_name_201803 partition of table_name 
	for values from ('2018-03-01') to ('2018-04-01');
	
create table table_name_201803_after partition of table_name  
  	for values from ('2018-04-01') to (MAXVALUE)
  	WITH (parallel_workers = 4)
    TABLESPACE fasttablespace;

在上面的示例中，我们每个月都会创建一个新分区，因此编写一个脚本自动生成所需的DDL可能是明智的。

确保在中未禁用enable_partition_pruning配置参数postgresql.conf。如果是这样，查询将不会根据需要进行优化。

启用修剪, 自动判断是否需要排除对应的分区表, 而不是全部扫描, 因为该分区不能包含满足查询WHERE条件

hash

散列类型分区根据分区键的散列值分配行。

create table table_name(
	id bigserial ,
	country varchar(255),
	asin varchar(24)
) partition by hash(asin);

CREATE TABLE asin_0 PARTITION OF table_name FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE asin_1 PARTITION OF table_name FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE asin_2 PARTITION OF table_name FOR VALUES WITH (MODULUS 3, REMAINDER 2);

分区维护

卸载分区

通常，最初定义表时建立的分区集并不打算保持静态。通常希望删除数据的旧分区，并定期为新数据添加新分区。分区的最重要优点之一就是，它允许通过操纵分区结构而不是物理地移动大量数据来几乎立即执行此原本很痛苦的任务。

删除旧数据的最简单选择是删除不再需要的分区：

ALTER TABLE table_name 
	DETACH PARTITION table_name_201803 ;
-- 删除表不是必须的, 一般是迁移到更廉价的存储介质, 或导出转换数据	
DROP TABLE table_name_201803 ;

创建分区

创建表并且附加到分区

create table  table_name_201802  partition of table_name 
    for values from ('2008-02-01') to ('2008-03-01')
    tablespace fasttablespace;

-- 默认分区
create table table_name_default partition of table_name default;

附加分区

把已经存在的表(可能有数据)附加到分区表

alter table table_name ATTACH partition table_name_201802  
    for values from ('2008-02-01') to ('2008-03-01' );

添加索引

在分区表的键列以及您可能需要的任何其他索引上创建索引。（并非严格要求键索引，但是在大多数情况下它很有用。）这将在每个分区上自动创建一个索引，以后创建或附加的任何分区也将包含该索引。

create index on table_name (report_date);
-- 唯一键可以直接加在主表,所有子表生效
alter table table_name add unique (asin, report_date);

非继承表创建的分区, 目前不支持设置添加主键索引

enable_partitionwise_aggregate

默认情况下，此选项已关闭。可设置分区高级聚合

SET enable_partitionwise_aggregate=on;
-- 测试语句
explain select country, count(*) from test1 group by country

小案例

-- 创建list分区主表
create table test1(
	id bigserial ,
	country varchar(255),
	asin varchar(24)
) partition by list(country);

-- 创建子表
create table test_us partition of test1 for values in ('US');
create table test_it partition of test1 for values in ('IT');
create table test_default partition of test1 default;

-- 添加唯一索引, 一般结合分区键
ALTER TABLE  test1 ADD UNIQUE (asin, country);

-- 测试唯一索引在子表是否生效
insert into test1 VALUES(1, 'US', 'ASIN1');
insert into test1 VALUES(1, 'US', 'ASIN1'); -- 此处应该报错,违反唯一约束

-- 测试分区键之间的数据转移(PG11后支持)
-- 数据由test_us 转移到了 test_it
select *  from test_us;
update test1 set country='IT' where id=1;
select *  from test_it;
-- 测试默认分区数据转移
update test1 set country='ABC' where id=1;
select *  from test_default;

-- 卸载分区子表
alter table test1 detach partition test_us;
alter table test1 detach partition test_it;
alter table test1 detach partition test_default;
-
- 删除分区子表
drop table test_us;
drop table test_it;
drop table test_default;

-- 删除分区主表
drop table test1;

PG 嵌套分区

嵌套分区可以结合list和range的方式，根据业务需要提供灵活的玩法

-- 多级分区
-- 创建list分区主表
create table test2(
	id bigserial ,
	country varchar(255),
	asin varchar(24),
	logdate date not null
) partition by list(country);
create table test_zh partition of test2 for values in ('ZH') partition by range (logdate);
create table  test_zh_before  partition of test_zh 
    for values from (MINVALUE) to ('2022-04-30');
create table  test_zh_202205  partition of test_zh 
    for values from ('2022-05-01') to ('2022-05-31');

-- 默认分区插入一条数据，test_zh_202205分区插入一条数据
insert into test2 VALUES(1, 'ZH', 'ASIN1','2022-05-02');
insert into test2 VALUES(2, 'ZH', 'ASIN2','2022-04-02');

select * from test2;

SELECT * from test_zh_before;
SELECT * from test_zh_202205;
alter table test1 detach partition test_zh;
drop table test_zh;
alter table test_zh detach partition test_zh_before;
drop table test_zh_before