更新时间：2025-08-22 GMT+08:00
DWS HStore表使用优秀实践
列存表存储机制
在DWS中，列存表以压缩单元（CU）为最小存储单位，每列默认以60000行当做一个CU进行存储。由于CU采用追加写模式，更新和删除操作不会修改原有CU。CU一旦生成，其数据便不可更改，无论插入1条还是60,000条数据，都会生成一个新的完整CU。
因此使用过程中会产生如下问题：
- 删除操作：仅在字典中将旧数据标记为作废，并未释放空间。
- 更新操作：标记旧数据删除后，将新记录写入新的CU。
- 空间问题：频繁更新/删除会导致表空间膨胀和大量存储空间无法有效利用。
HStore表的优势
HStore表采用附加delta表的形式，可以实现高效的存储与更新平衡，具体表现在以下几个方面：
- 批量数据处理：
- 批量插入数据直接写入CU
- 保持与传统列存一致的压缩效率
- 增量数据处理：
- 更新的列数据和小批量插入先序列化压缩
- 通过后台定期MERGE操作整合到主表CU
- 存储效率：
- 最大程度降低磁盘占用空间
- 保持列存格式的高压缩率特性
- 性能表现：
- 支持高并发更新操作入库
- 提供卓越的查询响应速度
- 适用场景
- 需要实时数据入库和实时查询
- 要求具备传统TP事务处理能力
- 高并发更新与查询并重的混合负载
DWS在9.1.0版本对HStore表做了优化，为保持前向兼容，保留了老的HStore表，优化后的HStore表为HStore_opt表。除了微批copy无更新入库性能要求高的场景外，HStore表的场景都可以使用HStore_opt表代替，性能更优。
使用建议
- 参数设置
为确保HStore表的查询性能与存储效率，推荐参数设置：
1 2 3 4
autovacuum_max_workers_hstore=3 # 专用于HStore的MERGE线程数 autovacuum_max_workers=6 # 系统总autovacuum线程数 autovacuum=true # 启用自动清理 enable_col_index_vacuum=on # 启用列索引清理
- 入库建议（推荐使用HStore_opt表）
- 点查建议（推荐使用HStore_opt表）
- 在等值过滤条件使用最多且distinct值分布相对均匀的一个列上创建二级分区（distinct值的分布过于倾斜或者个数太少的列不要创建二级分区）；
- 除了二级分区之外的等值过滤列，如果过滤条件涉及的列在查询中基本固定，使用cbtree索引，创建索引的列数不要超过5列；
- 除了二级分区之外的等值过滤列，如果过滤条件涉及的列在不同查询中变化，使用GIN索引，创建索引的列数不要超过5列；
- 所有涉及等值过滤的字符串列，都可以建表时指定bitmap索引，不限列数，后续不可修改；
- 时间范围过滤的列，指定为分区列；
- 点查返回数据量超过10W/dn的场景，索引扫描很可能不如非索引扫描，建议使用guc参数enable_seqscan对比测试下性能，灵活选择。
- 索引相关
- 索引会占用额外存储空间
- 对性能提升有要求时创建索引
- 需要执行UPSERT操作时使用索引
- 有唯一性或接近唯一的点查需求使用索引。
- MERGE相关
- 入库速度控制：
- 入库速度不得超过MERGE处理能力
- 通过控制入库并发防止Delta表膨胀
- 空间复用问题：
- Delta表空间复用受oldestXmin影响
- 长时间运行的事务可能导致空间复用延迟和膨胀
- 入库速度控制：
性能对比
- 并发更新实践
在列存表上插入一批数据后，开启两个会话，其中会话1删除某一条数据，然后不结束事务：
1 2 3 4 5 6 7 8 9 10 11
CREATE TABLE col(a int , b int)with(orientation=column); CREATE TABLE INSERT INTO col select * from data; INSERT 0 100 BEGIN; BEGIN DELETE col where a = 1; DELETE 1
会话2删除另一条数据，可以看到会话2需要等待会话1，会话1提交后会话2才能继续执行，这就复现了列存的CU锁问题：1 2 3
BEGIN; BEGIN DELETE col where a = 2;
使用HStore表重复上面实验，能够观察到会话2直接执行成功，不会锁等待。1 2 3 4
BEGIN; BEGIN DELETE hs where a = 2; DELETE 1
- 压缩效率实践
构建一张有三百万数据的数据表data。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
CREATE TABLE data( a int, b bigint, c varchar(10), d varchar(10)); CREATE TABLE INSERT INTO data values(generate_series(1,100),1,'asdfasdf','gergqer'); INSERT 0 100 INSERT INTO data select * from data; INSERT 0 100 INSERT INTO data select * from data; INSERT 0 200 ---循环插入，直到数据量达到三百万 INSERT INTO data select * from data; INSERT 0 1638400 select count(*) from data; count --------- 3276800 (1 row)
批量导入到行存表，观察大小为223MB。
1 2 3 4 5 6 7 8 9
CREATE TABLE row (like data including all); CREATE TABLE INSERT INTO row select * from data; INSERT 0 3276800 select pg_size_pretty(pg_relation_size('row')); pg_size_pretty ---------------- 223 MB (1 row)
批量导入到HStore_opt表，观察大小为3.5MB。
1 2 3 4 5 6 7 8 9
CREATE TABLE hs(a int, b bigint, c varchar(10),d varchar(10))with(orientation= column, enable_hstore_opt=on); CREATE TABLE INSERT INTO hs select * from data; INSERT 0 3276800 select pg_size_pretty(pg_relation_size('hs')); pg_size_pretty ---------------- 3568 KB (1 row)
由于表结构比较简单，数据也都是重复数据，所以HStore表的压缩效果很好，一般情况下HStore表相比行存会有3~5倍的压缩。
- 批量查询性能实践
依旧使用上面创建的表，查询行存表的第四列，耗时在4s左右。
1 2 3 4 5 6 7
explain analyze select d from data; explain analyze QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- id | operation | A-time | A-rows | E-rows | Peak Memory | E-memory | A-width | E-width | E-costs ----+------------------------------+----------------------+---------+---------+--------------+----------+---------+---------+---------- 1 | -> Streaming (type: GATHER) | 4337.881 | 3276800 | 3276800 | 32KB | | | 8 | 61891.00 2 | -> Seq Scan on data | [1571.995, 1571.995] | 3276800 | 3276800 | [32KB, 32KB] | 1MB | | 8 | 61266.00
查询HStore_opt表的第四列，耗时300毫秒左右。1 2 3 4 5 6 7 8
explain analyze select d from hs; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- id | operation | A-time | A-rows | E-rows | Peak Memory | E-memory | A-width | E-width | E-costs ----+----------------------------------------+--------------------+---------+---------+----------------+----------+---------+---------+---------- 1 | -> Row Adapter | 335.280 | 3276800 | 3276800 | 24KB | | | 8 | 15561.80 2 | -> Vector Streaming (type: GATHER) | 111.492 | 3276800 | 3276800 | 96KB | | | 8 | 15561.80 3 | -> CStore Scan on hs | [111.116, 111.116] | 3276800 | 3276800 | [254KB, 254KB] | 1MB | | 8 | 14936.80
此处只验证了批量查询场景，该场景下列存表以及HStore表相比行存表都有很好的查询性能。
HStore整改实践
列存delta表在使用时，存在来不及merge等问题（由于磁盘带宽以及列存delta没有定时merge机制），这会导致delta表膨胀以及查询性能和并发更新入库性能变差。
HStore在并发入库、高性能查询、异步merge机制等方面相比列存Delta都做了大量优化，能全面替代列存Delta。
如下为普通列存、行存表等整改成HStore的参考案例：
- 查询delta表清单，nspname需要换成操作的目标namespace。
1select n.nspname||'.'||c.relname as tbname,reloptions::text as op from pg_class c,pg_namespace n where c.relnamespace = n.oid and c.relkind = 'r' and c.oid > 16384 and n.nspname ='public' and (op like '%enable_delta=on%' or op like '%enable_delta=true%') and op not like '%enable_hstore_opt%';
先将2、3 都执行完毕后， 再执行2生成的建表语句，3生成的导数语句，每个表依次执行。
- 生成enable_hstore_opt建表语句。
1 2
select 'create table if not exists '|| tbname ||'_opt (like '|| tbname ||' INCLUDING all EXCLUDING reloptions) with(orientation=column,enable_hstore_opt=on);' from( select n.nspname||'.'||c.relname as tbname,reloptions::text as op from pg_class c,pg_namespace n where c.relnamespace = n.oid and c.relkind = 'r' and c.oid > 16384 and n.nspname ='public' and (op like '%enable_delta=on%' or op like '%enable_delta=true%') and op not like '%enable_hstore_opt%');
- 生成导数据。表名替换，删除旧表的语句。
1 2 3 4 5 6 7 8
select 'start transaction; lock table '|| tbname ||' in EXCLUSIVE mode; insert into '|| tbname ||'_opt select * from '|| tbname ||'; alter table '|| tbname ||' rename to '|| tbname ||'_bk; alter table '|| tbname ||'_opt rename to '|| tbname ||'; commit; drop table '|| tbname ||'_bk;' from(select n.nspname||'.'||c.relname as tbname,reloptions::text as op from pg_class c,pg_namespace n where c.relnamespace = n.oid and c.relkind = 'r' and c.oid > 16384 and n.nspname ='public' and (op like '%enable_delta=on%' or op like '%enable_delta=true%') and op not like '%enable_hstore_opt%');
