文档首页/ 数据仓库服务 GaussDB(DWS)/ 最佳实践/ 数据开发/ HStore表使用优秀实践
更新时间：2024-10-10 GMT+08:00
查看PDF
分享

HStore表使用优秀实践

基本原理

在GaussDB(DWS)中，CU是列存表存储数据的最小单元。列存表每列默认存储60000行数据为一个CU，CU生成后数据固定不可更改。无论是向列存表中插入1条还是60000条数据，都只会生成一个CU，在多次插入少量数据时，由于不能有效利用列存压缩能力，从而导致数据膨胀影响查询性能和磁盘使用率。

由于CU文件数据不能更改只能追加写，对CU中的数据做更新或删除都不会真正更改这个CU。删除是将老数据在字典中标记为作废，更新是标记老数据删除后，再写入一条新记录到新CU。在对列存表进行多次更新/删除操作，会导致列存表空间膨胀，大量空间无法有效利用。

列存Delta表解决了小批量入库产生的小CU问题，但无法解决同一个CU上的并发更新产生的锁冲突问题。而实时入库的场景下，需要将insert+upsert+update操作实时并发入库，数据来源于上游的其他数据库或者应用，同时要求入库后的数据要能及时查询，且对于查询的效率要求很高。

HStore表则采用附加delta表的形式，批量插入的数据会直接写入CU，具有与列存一致的压缩优势，而被更新的列、小批量插入的数据会序列化后压缩，同时定期merge到主表CU。

使用场景

GaussDB(DWS)中的HStore表， 在使用列存储格式尽量降低磁盘占用的同时，支持高并发的更新操作入库以及高性能的查询效率。因此对于实时入库和实时查询有较强诉求，以及要求具备处理传统TP事务能力的场景建议使用HStore表。

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

HStore表的创建与相关视图

创建HStore表，需要指定enable_hstore表级参数： 
1
 
 CREATE TABLE test1 (i int,j text) with (orientation = column,enable_hstore=on);
创建HStore_opt表，需要指定enable_hstore_opt表级参数： 
1
 
 CREATE TABLE test2 (i int,j text) with (orientation = column,enable_hstore_opt=on);

通过视图观察Delta表的类型元组数量以及Delta表的膨胀情况：

1
 
 SELECT * FROM pgxc_get_hstore_delta_info('tableName');

通过函数对Delta表做轻量清理以及全量清理。

  • 轻量Merge满6万的I记录以及CU上的删除信息，持有四级锁不阻塞业务增删改查，但空间不会还给系统。 
    1
    		 
     select hstore_light_merge('tableName');
  • 全量Merge所有记录，然后truncate清空Delta表返还空间给系统，不过持有八级锁会阻塞业务。 
    1
    		 
     select hstore_full_merge('tableName');

往HStore表中批量插入一百条数据，可以看到生成了一条类型是I的记录(n_i_tup 为1)。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
 
 CREATE TABLE data(a int primary key, b int);
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "data_pkey" for table "data"
 CREATE TABLE

 INSERT INTO data values(generate_series(1,100),1);
 INSERT 0 100

 CREATE TABLE hs(a int primary key, b int)with(orientation=column, enable_hstore=on);
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "hs_pkey" for table "hs"
 CREATE TABLE

 INSERT INTO hs SELECT * FROM data;
 INSERT 0 100

 SELECT * FROM pgxc_get_hstore_delta_info('hs'); 
  node_name |      part_name      | live_tup | n_i_type | n_d_type | n_x_type | n_u_type | n_m_type | data_size
 -----------+---------------------+----------+----------+----------+----------+----------+----------+-----------
  dn_1      | non partition table |        1 |        1 |        0 |        0 |        0 |        0 |      8192
 (1 row)

执行hstore_full_merge后可以看到Delta表上没有元组（live_tup为0），并且Delta表的空间大小data_size是0。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
SELECT hstore_full_merge('hs');
  hstore_full_merge
 -------------------
                  1
 (1 row)
 ​
SELECT * FROM pgxc_get_hstore_delta_info('hs'); 
  node_name |      part_name      | live_tup | n_i_type | n_d_type | n_x_type | n_u_type | n_m_type | data_size
 -----------+---------------------+----------+----------+----------+----------+----------+----------+-----------
  dn_1      | non partition table |        0 |        0 |        0 |        0 |        0 |        0 |         0
 (1 row)

执行删除，可以看到Delta表上有一条类型是D的记录（n_d_tup为1）。

1
2
3
4
5
6
7
 
 DELETE hs where a = 1;
 DELETE 1
SELECT * FROM pgxc_get_hstore_delta_info('hs'); 
  node_name |      part_name      | live_tup | n_i_type | n_d_type | n_x_type | n_u_type | n_m_type | data_size
 -----------+---------------------+----------+----------+----------+----------+----------+----------+-----------
  dn_1      | non partition table |        1 |        0 |        1 |        0 |        0 |        0 |      8192
 (1 row)

使用实践

当需要使用HStore表时，需要同步修改以下几个参数默认值，否则会导致HStore表性能严重劣化。

推荐的参数修改配置是：autovacuum_max_workers_hstore=3，autovacuum_max_workers=6，autovacuum=true，enable_col_index_vacuum=on。

  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
    		 
     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)

    批量导入到列存表，观察大小为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=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 analye                                                               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表的第四列，耗时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表的使用要求与建议

  • 参数设置

    依赖后台常驻线程对HStore表进行MERGE清理操作，才能保证查询性能与压缩效率，使用HStore表务必设置相关GUC参数，推荐的参数值如下：

    autovacuum_max_workers_hstore=3

    autovacuum_max_workers=6

    autovacuum=true

    enable_col_index_vacuum=on

  • 入库建议（推荐使用HStore_opt表）
    HStore_opt表入库建议：
    1. update入库性能差，建议修改为upsert；
    2. delete入库，确定计划走索引扫描即可，用JDBC batch方式入库最佳；
    3. upsert入库，无并发冲突下开启enable_hstore_nonconflict_upsert_optimization，其他场景都关闭；enable_hstore_nonconflict_upsert_optimization即可，会自动选择最优路径；
    4. merge into入库只有在单次入库数据量超过100W/dn，且无并发数据保证无重复的情况下，建议使用。
  • 点查建议（推荐使用HStore_opt表）

    HStore_opt表点查建议：

    1. 在等值过滤条件使用最多且distinct值分布相对均匀的一个列上创建二级分区（distinct值的分布过于倾斜或者个数太少的列不要创建二级分区）；
    2. 除了二级分区之外的等值过滤列，如果过滤条件涉及的列在查询中基本固定，使用cbtree索引，创建索引的列数不要超过5列；
    3. 除了二级分区之外的等值过滤列，如果过滤条件涉及的列在不同查询中变化，使用gin索引，创建索引的列数不要超过5列；
    4. 所有涉及等值过滤的字符串列，都可以建表时指定bitmap索引，不限列数，后续不可修改；
    5. 时间范围过滤的列，指定为分区列；
    6. 点查返回数据量超过10W/dn的场景，索引扫描很可能不如非索引扫描，建议使用guc参数enable_seqscan对比测试下性能，灵活选择。
  • 索引相关

    索引会占用额外的空间，同时带来的点查性能提升有限，所以HStore表只建议在需要做Upsert或者有点查（这里指唯一性与接近唯一的点查） 的诉求下创建一个主键或者btree索引。

  • MERGE相关

    由于HStore表依赖后台autovacuum来将操作MERGE到主表，所以入库速度不能超过MERGE速度，否则会导致delta表的膨胀，可以通过控制入库的并发来控制入库速度。同时由于Delta表本身的空间复用受oldestXmin的影响，如果有老事务存在可能会导致Delta空间复用不及时而产生膨胀。

父主题： 数据开发