标签
PostgreSQL , enterprisedb , ppas , oracle
背景
PPAS 10以及以前的版本，对于Oracle分区表的使用，以及如何创建分区表的索引。
10 以及以前的版本，仅支持range, list分区。11开始支持HASH分区。
Oracle分区表语法
https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#i1006455
例子
CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
);
CREATE TABLE q1_sales_by_region
(deptno number,
deptname varchar2(20),
quarterly_sales number(10, 2),
state varchar2(2))
PARTITION BY LIST (state)
(PARTITION q1_northwest VALUES ('OR', 'WA'),
PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'),
PARTITION q1_southeast VALUES ('FL', 'GA'),
PARTITION q1_northcentral VALUES ('SD', 'WI'),
PARTITION q1_southcentral VALUES ('OK', 'TX'));
PPAS 分区表用法
注意两个相关参数
set default_with_oids = on; -- with oids（多一列），设置为OFF时不允许使用Oracle的创建分区表的语法。
set default_with_rowids = on; -- oid上增加一列UK索引。 如果业务上不需要使用rowid虚拟列，强烈建议设置为OFF。
语法与Oracle相似，前面两个Oracle中的创建分区表的SQL可以直接运行。
创建分区表索引
1、10以前的版本，不允许直接在表上创建
postgres=# \set VERBOSITY verbose
postgres=# create index idx_sales_1 on sales (prod_id);
ERROR: 42809: cannot create index on partitioned table "sales"
LOCATION: DefineIndex, indexcmds.c:396
只能在分区上创建索引。
如果分区很多，可以写成DO或者函数，简化整个过程。
需要用到inherit找到所有继承表。
postgres=# \d pg_inherits
Table "pg_catalog.pg_inherits"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
inhrelid | oid | | not null |
inhparent | oid | | not null |
inhseqno | integer | | not null |
Indexes:
"pg_inherits_relid_seqno_index" UNIQUE, btree (inhrelid, inhseqno)
"pg_inherits_parent_index" btree (inhparent)
例如要对sales的所有分区
do language plpgsql $$
declare
s name;
t name;
tbl oid := 'public.sales'::regclass;
col text := format('%I,%I', 'prod_id', 'quantity_sold');
o oid;
begin
for o in select inhrelid from pg_inherits where inhparent=tbl
loop
select nspname, relname into s,t from pg_class t1 join pg_namespace t2 on (t1.relnamespace=t2.oid) where t1.oid=o;
execute format('create index %s on %I.%I (%s)', 'md5'||md5(random()::text), s, t, col);
end loop;
end;
$$;
如下:
postgres=# \d+ sales
Table "public.sales"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
prod_id | numeric(6,0) | | | | main | |
cust_id | numeric | | | | main | |
time_id | timestamp without time zone | | | | plain | |
channel_id | character(1) | | | | extended | |
promo_id | numeric(6,0) | | | | main | |
quantity_sold | numeric(3,0) | | | | main | |
amount_sold | numeric(10,2) | | | | main | |
Partition key: RANGE (time_id) NULLS LAST
Partitions: sales_sales_q1_2006 FOR VALUES FROM (MINVALUE) TO ('01-APR-06 00:00:00'),
sales_sales_q2_2006 FOR VALUES FROM ('01-APR-06 00:00:00') TO ('01-JUL-06 00:00:00'),
sales_sales_q3_2006 FOR VALUES FROM ('01-JUL-06 00:00:00') TO ('01-OCT-06 00:00:00'),
sales_sales_q4_2006 FOR VALUES FROM ('01-OCT-06 00:00:00') TO ('01-JAN-07 00:00:00')
Has OIDs: yes
postgres=# \d sales_sales_q1_2006
Table "public.sales_sales_q1_2006"
Column | Type | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------
prod_id | numeric(6,0) | | |
cust_id | numeric | | |
time_id | timestamp without time zone | | |
channel_id | character(1) | | |
promo_id | numeric(6,0) | | |
quantity_sold | numeric(3,0) | | |
amount_sold | numeric(10,2) | | |
Partition of: sales FOR VALUES FROM (MINVALUE) TO ('01-APR-06 00:00:00')
Indexes:
"pg_oid_120027427_index" UNIQUE, btree (oid)
"md5193df902f78920ac4d636ebcab5d50b1" btree (prod_id, quantity_sold)
postgres=# \d sales_sales_q2_2006
Table "public.sales_sales_q2_2006"
Column | Type | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------
prod_id | numeric(6,0) | | |
cust_id | numeric | | |
time_id | timestamp without time zone | | |
channel_id | character(1) | | |
promo_id | numeric(6,0) | | |
quantity_sold | numeric(3,0) | | |
amount_sold | numeric(10,2) | | |
Partition of: sales FOR VALUES FROM ('01-APR-06 00:00:00') TO ('01-JUL-06 00:00:00')
Indexes:
"pg_oid_120027434_index" UNIQUE, btree (oid)
"md52c8ff555d00e2fd5245fafb3027a6d6d" btree (prod_id, quantity_sold)
将分区表创建索引的功能封装成函数
输入:
主表所在schema
主表名
索引字段
索引方法
表空间
是否需要不堵塞DML
函数如下
create or replace function create_index_on_partition_table (
ptblnsp name, -- 主表所在schema, 大小写敏感，推荐全部使用小写。
ptbl name, -- 主表名, 大小写敏感，推荐全部使用小写。
cols name[], -- 索引字段, 严格按顺序来创建，大小写敏感，推荐全部使用小写。
am name default 'btree', -- 索引方法
tbs name default 'pg_default' -- 表空间
) returns void as $$
declare
s name;
t name;
tbl oid := format('%I.%I', ptblnsp, ptbl)::regclass;
col text;
o oid;
begin
select string_agg(format('%I',x),', ') into col from unnest(cols) x;
for o in select inhrelid from pg_inherits where inhparent=tbl
loop
perform 1 from (select pg_get_indexdef(indexrelid) as def from pg_index where indrelid=o) t where substring(def, '\((.*)\)')=col limit 1;
if not found then -- 避免重复创建，例如新增了分区后，需要对新建分区添加索引，老分区已经添加就不需要再加了
select nspname, relname into s,t from pg_class t1 join pg_namespace t2 on (t1.relnamespace=t2.oid) where t1.oid=o;
execute format('create index %s on %I.%I (%s)', 'md5'||md5(random()::text), s, t, col);
end if;
end loop;
end;
$$ language plpgsql strict;
使用举例
CREATE TABLE salesabc
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, "QWWWuantity_sold" NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
);
创建分区索引
select create_index_on_partition_table('public','salesabc','{prod_id, QWWWuantity_sold,amount_sold}');
查看索引已正确创建
postgres=# select indexrelid::regclass,indrelid::Regclass,pg_get_indexdef(indexrelid) from pg_index where indrelid in (select inhrelid from pg_inherits where inhparent='public.salesabc'::regclass);
indexrelid | indrelid | pg_get_indexdef
-------------------------------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------
pg_oid_120027673_index | salesabc_sales_q1_2006 | CREATE UNIQUE INDEX pg_oid_120027673_index ON public.salesabc_sales_q1_2006 USING btree (oid)
pg_oid_120027680_index | salesabc_sales_q2_2006 | CREATE UNIQUE INDEX pg_oid_120027680_index ON public.salesabc_sales_q2_2006 USING btree (oid)
pg_oid_120027687_index | salesabc_sales_q3_2006 | CREATE UNIQUE INDEX pg_oid_120027687_index ON public.salesabc_sales_q3_2006 USING btree (oid)
pg_oid_120027694_index | salesabc_sales_q4_2006 | CREATE UNIQUE INDEX pg_oid_120027694_index ON public.salesabc_sales_q4_2006 USING btree (oid)
md56a2cbe5776d443387f068bbe539533e5 | salesabc_sales_q1_2006 | CREATE INDEX md56a2cbe5776d443387f068bbe539533e5 ON public.salesabc_sales_q1_2006 USING btree (prod_id, "QWWWuantity_sold", amount_sold)
md5e1c5c1645d5c9cd6500040d98b1ff39d | salesabc_sales_q2_2006 | CREATE INDEX md5e1c5c1645d5c9cd6500040d98b1ff39d ON public.salesabc_sales_q2_2006 USING btree (prod_id, "QWWWuantity_sold", amount_sold)
md519a145aefd180dd7f4a43e57f3254d61 | salesabc_sales_q3_2006 | CREATE INDEX md519a145aefd180dd7f4a43e57f3254d61 ON public.salesabc_sales_q3_2006 USING btree (prod_id, "QWWWuantity_sold", amount_sold)
md5402f9b0fb2919c8b4545033ac450a140 | salesabc_sales_q4_2006 | CREATE INDEX md5402f9b0fb2919c8b4545033ac450a140 ON public.salesabc_sales_q4_2006 USING btree (prod_id, "QWWWuantity_sold", amount_sold)
(8 rows)
Enterprisedb 11（POLARDDB PG， PPAS 11）都支持了直接对分区表创建索引，不需要以上繁琐的操作。
其他
1、不支持非默认ops的情况，如果有非默认OPS的话，改一下以上函数(使用非默认ops)。
2、如果需要支持并行创建，改一下以上函数(使用dblink异步任务，同时使用CONCURRENTLY关键字创建索引)。
3、如果需要开启异步任务，同时对多个分区创建，改一下以上函数(使用dblink异步任务)。
参考
《PostgreSQL 快速给指定表每个字段创建索引 - 2 (近乎完美)》
《PostgreSQL dblink异步调用实践,跑并行多任务 - 例如开N个并行后台任务创建索引, 开N个后台任务跑若干SQL》
《在PostgreSQL中跑后台长任务的方法 - 使用dblink异步接口》
社区版本分区表使用:
《PostgreSQL 9.x, 10, 11 hash分区表 用法举例》
《PostgreSQL native partition 分区表性能优化之 - 动态SQL+服务端绑定变量》
《PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化》
《PostgreSQL 11 preview - 分区表 增强 汇总》