行存改为列存表的步骤

行存改为列存表的步骤

场景:因大数据量场景,test表原先设计为行存表,随着表数据量增长,行存查询性能较差需要将该表改为列存表

--行存表表结构
CREATE  TABLE test1 (	id varchar	name varchar)WITH (orientation=row, compression=high)DISTRIBUTE BY HASH(id);
--查行存表权限
SELECT relname,relacl from pg_class where relname='test1'
--统计行存表数据量
select count(1) from test1;
212862656
--新建列存表
CREATE  TABLE test2 (	id varchar	name varchar)WITH (orientation=column, compression=high)DISTRIBUTE BY HASH(id);
--给列存表权限
GRANT select on test2 to yangpg;
--行存表数据写入列存表
insert into test2  select * from test1
--查询列存表是否正常写入
select * from test2;
--行存表重命令
alter table test1 rename to test1_bak;
--列存表重命令
alter table test2 rename to test1;
--查询列存表权限
SELECT relname,relacl from pg_class where relname='test1'