实时数仓及HStore表使用最佳实践

实时数仓场景介绍

随着智能数据时代深化演进，企业数据生态系统呈现三个显著特征：数据规模的海量化扩张、数据类型的多样化发展（涵盖结构化、半结构化和非结构化形态）、以及业务场景的复杂度持续提升，为应对这些挑战，实时数仓技术应运而生。实时数仓在大规模数据查询和分析能力基础上，提供高并发、高性能、低时延、低成本的事务处理能力，通过HStore表的使用发挥显著性能，其在互联网、物联网及传统行业数字化转型中发挥着关键作用，典型应用场景包括：

• 用户行为智能分析：通过实时采集网页浏览日志构建用户全生命周期画像，支持多维度行为路径分析。基于实时数仓的OLAP联机分析能力，可秒级计算用户留存率、转化漏斗等核心指标，助力精细化运营决策。
• 实时风险控制中枢：在互联网金融、电商交易等场景中，依托毫秒级响应的实时数据处理能力，构建风险特征计算引擎。通过关联用户多源行为数据，动态识别异常模式，实现百毫秒级欺诈交易拦截，有效保障业务安全。
• 工业物联智能运维：针对电网、制造等传统行业，实时数仓可集成海量设备传感器数据（包括振动、温度等时序数据流），结合设备维修日志等半结构化数据，构建预测性维护模型。通过实时趋势分析，实现设备健康状态动态监测与故障预警，将传统被动式运维转变为智能预防模式。

实时数仓支持两种高效数据入库模式：直通模式和缓冲模式。

普通列存表存储机制

在DWS中，列存表以压缩单元（CU）为最小存储单位，每列默认以60000行当做一个CU进行存储。由于CU采用追加写模式，更新和删除操作不会修改原有CU。CU一旦生成，其数据便不可更改，无论插入1条还是60,000条数据，都会生成一个新的完整CU。

HStore表的优势

HStore表采用附加delta表的形式，可以实现高效的存储与更新平衡，具体表现在以下几个方面：

DWS在9.1.0版本对HStore表做了优化，为保持前向兼容，保留了老的HStore表，优化后的HStore表为HStore_opt表。除了微批copy无更新入库性能要求高的场景外，HStore表的场景都可以使用HStore_opt表代替，性能更优。

HStore表使用建议

• 参数设置

  为确保HStore表的查询性能与存储效率，推荐参数参见表5。

• 入库建议（推荐使用HStore_opt表）
  1. 更新操作：

     避免直接使用UPDATE，改用UPSERT方式。

  2. 删除操作：
     • 确保执行计划走索引扫描。
     • 采用JDBC batch方式入库效率最佳。
  3. 批量入库：
     • 单次入库量超过100W/DN且数据无重复时，考虑使用MERGE INTO。
     • 常规场景推荐使用UPSERT。
• 点查建议（推荐使用HStore_opt表）
  1. 在等值过滤条件使用最多且distinct值分布相对均匀的一个列上创建二级分区（distinct值的分布过于倾斜或者个数太少的列不要创建二级分区）。
  2. 除了二级分区之外的等值过滤列，如果过滤条件涉及的列在查询中基本固定，使用cbtree索引，创建索引的列数不要超过5列。
  3. 除了二级分区之外的等值过滤列，如果过滤条件涉及的列在不同查询中变化，使用GIN索引，创建索引的列数不要超过5列。
  4. 所有涉及等值过滤的字符串列，都可以建表时指定bitmap索引，不限列数，后续不可修改。
  5. 时间范围过滤的列，指定为分区列。
  6. 点查返回数据量超过10W/dn的场景，索引扫描很可能不如非索引扫描，建议使用guc参数enable_seqscan对比测试下性能，灵活选择。
• 索引相关
  1. 索引会占用额外存储空间。
  2. 对性能提升有要求时创建索引。
  3. 需要执行UPSERT操作时使用索引。
  4. 有唯一性或接近唯一的点查需求使用索引。

• MERGE相关
  1. 入库速度控制：
     • 入库速度不得超过MERGE处理能力。
     • 通过控制入库并发防止Delta表膨胀。
  2. 空间复用问题：
     • Delta表空间复用受oldestXmin影响。
     • 长时间运行的事务可能导致空间复用延迟和膨胀。

实时数仓规格选项

在创建集群界面，选择存算一体1:4云盘规格，这部分规格创建的DWS集群具备实时数仓能力，实时数仓规格及对应的业务场景请参见表3。

实时数仓GUC参数最佳配置

创建完实时数仓后，请按表4配置GUC参数相应推荐值，配置方法参考以下步骤。

1. 修改enable_codegen为off，减少短查询动态生产执行代码时申请内存的开销。
   1. 在DWS控制台，选择“专属集群 > 集群列表”。
   2. 在集群列表中找到所需要的集群，单击集群名称，进入“集群详情”页面。
   3. 单击“参数修改”页签，搜索到enable_codegen，修改为off，然后单击“保存”。

2. 修改DN上的NUMA为on，CN上NUMA为off，numa进程绑定可减少跨numa访问进程的开销。

   需修改enable_numa_bind取值，联系技术支持人员修改。

3. 修改CM清理空闲连接的时间abnormal_check_general_task取值，从默认值60修改为3600，减少反复建立连接的开销。
   

   默认值60s的定期清理时间间隔，对毫秒级业务性能影响较大，单个线程重新创建的开销大约需要300ms，有毫秒级性能敏感场景建议调大。如果清理时间间隔内清理连接较慢，会导致占用内存较高。

   需修改abnormal_check_general_task取值，联系技术支持人员修改。

实时数仓hstore_opt表最佳配置参数及创建示例

实时数仓场景下，推荐表的类型为：hstore_opt，在使用hstore_opt表前，请参见表5设置以下与HStore_opt表相关的参数。

1. 配置GUC参数。
   1. 在DWS控制台，选择“专属集群 > 集群列表”。
   2. 在集群列表中找到所需要的集群，单击集群名称，进入“集群详情”页面。
   3. 修改必选参数：单击“参数修改”页签，分别搜索autovacuum_max_workers、autovacuum_max_workers_hstore，参见表5设置为推荐值，单击“保存”。
      

      autovacuum、enable_col_index_vacuum默认值已为推荐值，无需单独设置。

   4. 修改可选参数：搜索autovacuum_naptime、cost_model_version，参见表5设置为推荐值，单击“保存”。
      

      其他可选参数，请联系技术支持人员修改。

2. 使用SQL编辑器连接DWS集群，创建HStore_opt表，以下为建表示例。

   请根据业务数据特征选择合理的分布键、分区键。详情参见DWS总体开发设计规范。

   1 2 3 4 5 6 7 8 9 10 11 12 13

   CREATE table public.hstore_opt_table_demo( t_code character varying(20), t_gisid character varying(800), t_datatime timestamp(6) without time zone, t_gmid character varying(64) ) WITH (orientation=column, enable_hstore_opt=on) -- 创建表时，默认使用该配置。 DISTRIBUTE BY hash (t_gmid) --分布键，选择主键或者关联字段 PARTITION BY range(t_datatime) -- 分区键 ( partition p2024_1 start('2024-01-01') end ('2024-06-01') every (interval '1 month'), partition p2024_7 start('2024-06-01') end ('2024-12-31') every (interval '1 month') );

3. 当出现异常数据处理时，进行分区调整。

   1	ALTER TABLE public.hstore_opt_table_demo ADD PARTITION pmax VALUES LESS THAN (maxvalue);

4. hstore_opt表的其他使用建议和性能对比，请参见HStore表相比于普通行、列存表性能对比实践。

HStore表相比于普通行、列存表性能对比实践

1. 并发更新实践
   在普通列存表上插入一批数据后，开启两个会话，其中会话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

2. 压缩效率实践
   构建一张有三百万数据的数据表data。

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

   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 10 11

   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 10 11 12

   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倍的压缩。

3. 批量查询性能实践
   依旧使用上面创建的表，查询行存表的第四列，耗时在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的参考案例：

1. 查询delta表清单，nspname需要换成操作的目标namespace。

   1	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%';

   先将2、3 都执行完毕后， 再执行2生成的建表语句，3生成的导数语句，每个表依次执行。

2. 生成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%');

3. 生成导数据。表名替换，删除旧表的语句。

   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%');