DWS行存表切换hstore表最佳实践

注意：830和910内核hstore能力比较成熟，建议升级830和910后再使用hstore

1、行存表切换hstore

修改GUC参数

select name,setting from pg_settings where name in ('enable_hstore_table','enable_hstore_lightupdate','autovacuum_naptime','autovacuum_max_workers_hstore','autovacuum_max_workers','autovacuum','enable_hstore_merge_keepgtm','colvacuum_threshold_scale_factor');
​
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "colvacuum_threshold_scale_factor=50"

创建列存表

-- 原表表结构留档，供回退使用
select * from pg_get_tabledef('schema_name.table_name'); 
​
-- 创建hstore表
create table schema_name.table_name_hstore (like schema_name.table_name including all excluding storage excluding reloptions) with(orientation=column,enable_hstore=true,compression=low);
​
-- 删除hstore表索引
drop index schema_name.index_name_hstore;

新表授权

-- hstore表授权
SELECT 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || '_hstore TO ' || grantee || ';' FROM information_schema.table_privileges WHERE concat(table_schema,'.',table_name)='schema_name.table_name';
​
-- 检查hstore表权限
select grantee as user, concat(table_schema,'.',table_name) as table_name, string_agg(privilege_type,',') as privileges
from information_schema.table_privileges
where concat(table_schema,'.',table_name)='schema_name.table_name'
group by grantee,concat(table_schema,'.',table_name);

切换hstore表

-- 开启事务
START TRANSACTION; 
-- 使用2个线程导数，可根据资源使用情况调整
set query_dop=2;
-- 设置锁超时时间为0，避免锁超时导致事务回滚
set lockwait_timeout = 0
-- 原表加7级锁
LOCK TABLE schema_name.table_name IN EXCLUSIVE MODE;
-- hstore表导数
insert into schema_name.table_name_hstore select * from schema_name.table_name;
-- hstore表酌情添加合适索引
CREATE INDEX schema_name.index_name_hstore ON schema_name.table_name_hstore USING btree (?, ?) LOCAL;
-- 校验数据行数，可省略
SELECT COUNT(1) FROM schema_name.table_name; 
SELECT COUNT(1) FROM schema_name.table_name_hstore; 
-- 原表重命名
alter table schema_name.table_name rename to schema_name.table_name_row;
-- hstore表重命名成原表
alter table schema_name.table_name_hstore rename to schema_name.table_name;
-- hstore表收集统计信息
analyze schema_name.table_name;
-- 提交
COMMIT;

删除老表

-- hstore表稳定运行一段时间后，删除行存表
drop table schema_name.table_name_row;

2、回滚方案

start transaction;
LOCK TABLE schema_name.table_name in exclusive mode;
set query_dop = 2;
create table schema_name.table_name_new like schema_name.table_name_row including all;
drop index schema_name.index_name_new;
insert into schema_name.table_name_new select * from schema_name.table_name;
CREATE INDEX schema_name.index_name_new ON schema_name.table_name_new USING btree (?, ?) LOCAL;
select count(*) from schema_name.table_name;
select count(*) from schema_name.table_name_new;
alter table schema_name.table_name rename to schema_name.table_name_hstore;
alter table schema_name.table_name_new rename to schema_name.table_name;
analyze schema_name.table_name;
COMMIT;
​
drop table schema_name.table_name_hstore;