作者
digoal
日期
2020-02-25
标签
PostgreSQL , 分区表转换
背景
某些情况下由于业务预估过多, 导致开发把所有表都建立成了分区表, 而实际上并不需要分区.
1、在高并发下分区表可能引入一定的优化器损耗,
2、分区多, 会导致会话relcache的内存增加, 长连接+高并发+未使用huge page 可能触发OOM
《PostgreSQL Huge Page 使用建议 - 大内存主机、实例注意》
《PostgreSQL relcache在长连接应用中的内存霸占"坑"》
《PostgreSQL 12 preview - 分区表性能提升百倍》
既然如此, 怎么快速把分区表切回非分区表?
注意: 本文不考虑字段采用serial 导致sequence和table挂钩、外键约束, 如果分区表包含serial字段, 序列属于partition table的情况, 删除old table会导致序列被删除, 并且新表的默认值也会被清理.
例子
create database db1;
```
create table tbl (id int, info text) partition by hash (id);
create table tbl0 partition of tbl for values with (modulus 2, remainder 0);
create table tbl1 partition of tbl for values with (modulus 2, remainder 1);
insert into tbl select generate_series(1,10000), md5(random()::text);
create table ttbl (id int, info text) partition by hash (id);
create table ttbl0 partition of ttbl for values with (modulus 2, remainder 0);
create table ttbl1 partition of ttbl for values with (modulus 2, remainder 1);
insert into ttbl select generate_series(1,10000), md5(random()::text);
create table "TTb" (id int, info text) partition by hash (id);
create table "TTb0" partition of "TTb" for values with (modulus 2, remainder 0);
create table "TTb1" partition of "TTb" for values with (modulus 2, remainder 1);
insert into "TTb" select generate_series(1,10000), md5(random()::text);
create schema s1;
set search_path=s1;
create table tbl (id int, info text) partition by hash (id);
create table tbl0 partition of tbl for values with (modulus 2, remainder 0);
create table tbl1 partition of tbl for values with (modulus 2, remainder 1);
insert into tbl select generate_series(1,10000), md5(random()::text);
create table ttbl (id int, info text) partition by hash (id);
create table ttbl0 partition of ttbl for values with (modulus 2, remainder 0);
create table ttbl1 partition of ttbl for values with (modulus 2, remainder 1);
insert into ttbl select generate_series(1,10000), md5(random()::text);
create table "TTb" (id int, info text) partition by hash (id);
create table "TTb0" partition of "TTb" for values with (modulus 2, remainder 0);
create table "TTb1" partition of "TTb" for values with (modulus 2, remainder 1);
insert into "TTb" select generate_series(1,10000), md5(random()::text);
create schema s2;
set search_path=s2;
create table tbl (id int, info text) partition by hash (id);
create table tbl0 partition of tbl for values with (modulus 2, remainder 0);
create table tbl1 partition of tbl for values with (modulus 2, remainder 1);
insert into tbl select generate_series(1,10000), md5(random()::text);
create table ttbl (id int, info text) partition by hash (id);
create table ttbl0 partition of ttbl for values with (modulus 2, remainder 0);
create table ttbl1 partition of ttbl for values with (modulus 2, remainder 1);
insert into ttbl select generate_series(1,10000), md5(random()::text);
create table "TTb" (id int, info text) partition by hash (id);
create table "TTb0" partition of "TTb" for values with (modulus 2, remainder 0);
create table "TTb1" partition of "TTb" for values with (modulus 2, remainder 1);
insert into "TTb" select generate_series(1,10000), md5(random()::text);
```
db1=# \dPt *.*
List of partitioned tables
Schema | Name | Owner | Parent name
--------+------+----------+-------------
public | TTb | postgres |
public | tbl | postgres |
public | ttbl | postgres |
s1 | TTb | postgres |
s1 | tbl | postgres |
s1 | ttbl | postgres |
s2 | TTb | postgres |
s2 | tbl | postgres |
s2 | ttbl | postgres |
(9 rows)
使用这个方法来切换表
begin;
create table new_tbl (like tbl including all);
insert into new_tbl select * from tbl;
alter table tbl rename to old_tbl;
alter table new_tbl rename to tbl;
drop table old_tbl; -- 如果有依赖, 如前描述 这个表有被依赖的对象时(例如sequence, foreign table), 会导致drop 失败
end;
一次性切换所有分区表, 如下
-- 防止锁等待, 等待直接回滚
-- 建议断开业务连接后操作
```
set lock_timeout ='5s';
do language plpgsql $$
declare
v_nsp name;
v_tbl name;
v_exchange1 name := 'new'||md5(random()::text);
v_exchange2 name := 'old'||md5(random()::text);
begin
for v_nsp,v_tbl in
SELECT n.nspname as "Schema",
c.relname as "Name"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid
WHERE c.relkind IN ('p','')
AND n.nspname !~ '^pg_toast'
ORDER BY "Schema", "Name"
LOOP
execute format('create table %I.%I (like %I.%I including all)', v_nsp, v_exchange1, v_nsp, v_tbl);
execute format('insert into %I.%I select * from %I.%I', v_nsp, v_exchange1, v_nsp, v_tbl);
execute format('alter table %I.%I rename to %I', v_nsp, v_tbl, v_exchange2);
execute format('alter table %I.%I rename to %I', v_nsp, v_exchange1, v_tbl);
-- 如果有依赖， 以下将报错， 导致整个事务回滚。
execute format('drop table %I.%I', v_nsp, v_exchange2);
END loop;
end;
$$;
```
```
db1=# \dPt .
List of partitioned tables
Schema | Name | Owner | Parent name
--------+------+-------+-------------
(0 rows)
```
如果分区表内的数据很多, 这个可能会执行比较久.
如果有依赖问题怎么办
先解决依赖, 然后再执行如上
例如解决sequence的依赖问题, 对每个分区表相关的sequence执行去除owner, 改成新表
alter sequence 序列 OWNED BY none;
以上do脚本改成如下
```
do language plpgsql $$
declare
v_tbl_oid oid;
v_nsp name;
v_tbl name;
v_exchange1 name := 'new'||md5(random()::text);
v_exchange2 name := 'old'||md5(random()::text);
v_seq_nsp name;
v_seq_name name;
begin
for v_tbl_oid,v_nsp,v_tbl in
SELECT c.oid, n.nspname as "Schema",
c.relname as "Name"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid
WHERE c.relkind IN ('p','')
AND n.nspname !~ '^pg_toast'
ORDER BY "Schema", "Name"
LOOP
execute format('create table %I.%I (like %I.%I including all)', v_nsp, v_exchange1, v_nsp, v_tbl);
execute format('insert into %I.%I select * from %I.%I', v_nsp, v_exchange1, v_nsp, v_tbl);
execute format('alter table %I.%I rename to %I', v_nsp, v_tbl, v_exchange2);
execute format('alter table %I.%I rename to %I', v_nsp, v_exchange1, v_tbl);
-- 如果有依赖， 以下将报错， 导致整个事务回滚。 -- 处理serial类型依赖, 设置owned by为none for v_seq_nsp, v_seq_name in select n.nspname as "Schema", c.relname as "Name" from pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace where c.oid in (select objid from pg_depend where refobjid=v_tbl_oid) and relkind='S' AND n.nspname !~ '^pg_toast' loop execute format('alter sequence %I.%I owned by none', v_seq_nsp, v_seq_name); end loop; execute format('drop table %I.%I', v_nsp, v_exchange2);复制
END loop;
end;
$$;
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
