# 实时数仓及HStore表使用最佳实践 #### 实时数仓场景介绍 随着智能数据时代深化演进，企业数据生态系统呈现三个显著特征：数据规模的海量化扩张、数据类型的多样化发展（涵盖结构化、半结构化和非结构化形态）、以及业务场景的复杂度持续提升，为应对这些挑战，实时数仓技术应运而生。实时数仓在大规模数据查询和分析能力基础上，提供**高并发、高性能、低时延、低成本** 的事务处理能力，通过**[HStore表]**的使用发挥显著性能，其在互联网、物联网及传统行业数字化转型中发挥着关键作用，典型应用场景包括： - **用户行为智能分析**：通过实时采集网页浏览日志构建用户全生命周期画像，支持多维度行为路径分析。基于实时数仓的OLAP联机分析能力，可秒级计算用户留存率、转化漏斗等核心指标，助力精细化运营决策。 - **实时风险控制中枢**：在互联网金融、电商交易等场景中，依托毫秒级响应的实时数据处理能力，构建风险特征计算引擎。通过关联用户多源行为数据，动态识别异常模式，实现百毫秒级欺诈交易拦截，有效保障业务安全。 - **工业物联智能运维**：针对电网、制造等传统行业，实时数仓可集成海量设备传感器数据（包括振动、温度等时序数据流），结合设备维修日志等半结构化数据，构建预测性维护模型。通过实时趋势分析，实现设备健康状态动态监测与故障预警，将传统被动式运维转变为智能预防模式。 实时数仓支持两种高效数据入库模式：**直通模式** 和**缓冲模式**。 表1入库模式 | **入库模式** | **入库形式** | **入库方法** | **特点** | **适合场景** | |:---|:---|:---|:---|:---| | 直通模式 | SQL | 将CDC（Change Data Capture，变更数据捕获）数据解析成Insert/Delete/Update，传入DWS。 | 1. 表结构与远端完全一致。 2. 部署简单，数据链路短，时延低。 | - 变更数据量不大。 - 对同步实时性要求严格。 | | 缓冲模式 | 微批数据 | 将大批量的小事务通过缓冲方式转换为微批数据，既能充分利用批量导入高性能的优点，又能确保数据在较短时间内同步到目标端。 | 1. 支持扩展字段，供后续ETL业务使用。 2. 保留操作历史，业务有更多操作空间。 3. 通过扩展字段保留源端操作信息，支持抹平和不抹平方式，轻松保留操作历史。 4. 接入灵活配置，可根据实际业务量进行多实例部署。 5. 轻量化进程，灵活部署。 | - 变更数据量大。 - 对实时性有一定要求但又不是很严格。 - 要求保留源端操作历史信息。 | #### 普通列存表存储机制 在DWS中，列存表以压缩单元（CU）为最小存储单位，每列默认以60000行当作一个CU进行存储。由于CU采用追加写模式，更新和删除操作不会修改原有CU。CU一旦生成，其数据便不可更改，无论插入1条还是60,000条数据，都会生成一个新的完整CU。 因此使用过程中会产生如下问题： 1. 删除操作：仅在字典中将旧数据标记为作废，并未释放空间。 2. 更新操作：标记旧数据删除后，将新记录写入新的CU。 3. 空间问题：频繁更新/删除会导致表空间膨胀和大量存储空间无法有效利用。 #### HStore表的优势 HStore表采用附加delta表的形式，可以实现高效的存储与更新平衡，具体表现在以下几个方面： 表2HStore表优势 | 维度 | 优势 | |:---|:---| | 批量数据处理 | - 批量插入数据直接写入CU。 - 保持与传统列存一致的压缩效率。 | | 增量数据处理 | - 更新的列数据和小批量插入先序列化压缩。 - 通过后台定期MERGE操作整合到主表CU。 | | 存储效率 | - 最大程度降低磁盘占用空间。 - 保持列存格式的高压缩率特性。 | | 性能表现 | - 支持高并发更新操作入库。 - 提供卓越的查询响应速度。 | | 适用场景 | - 需要实时数据入库和实时查询。 - 要求具备传统TP事务处理能力。 - 高并发更新与查询并重的混合负载。 | ![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/caution_3.0-zh-cn.png) 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. 批量入库： - 单次入库量超过100万/DN且数据无重复时，考虑使用MERGE INTO。 - 常规场景推荐使用UPSERT。 - **点查建议** （推荐使用HStore_opt表） 1. 在等值过滤条件使用最多且distinct值分布相对均匀的一个列上创建二级分区（distinct值的分布过于倾斜或者个数太少的列不要创建二级分区）。 2. 除了二级分区之外的等值过滤列，如果过滤条件涉及的列在查询中基本固定，使用cbtree索引，创建索引的列数不要超过5列。 3. 除了二级分区之外的等值过滤列，如果过滤条件涉及的列在不同查询中变化，使用GIN索引，创建索引的列数不要超过5列。 4. 所有涉及等值过滤的字符串列，都可以建表时指定bitmap索引，不限列数，后续不可修改。 5. 时间范围过滤的列，指定为分区列。 6. 点查返回数据量超过10万/dn的场景，索引扫描很可能不如非索引扫描，建议使用guc参数enable_seqscan对比测试下性能，灵活选择。 - **索引相关** 1. 索引会占用额外存储空间。 2. 对性能提升有要求时创建索引。 3. 需要执行UPSERT操作时使用索引。 4. 有唯一性或接近唯一的点查需求使用索引。 - **MERGE相关** 1. 入库速度控制： - 入库速度不得超过MERGE处理能力。 - 通过控制入库并发防止Delta表膨胀。 2. 空间复用问题： - Delta表空间复用受oldestXmin影响。 - 长时间运行的事务可能导致空间复用延迟和膨胀。 #### 实时数仓规格选型 在[创建集群](https://support.huaweicloud.com/mgtg-dws/dws_01_0019.html)界面，选择**存算一体1:4云盘** 规格，这部分规格创建的DWS集群具备实时数仓能力，实时数仓规格及对应的业务场景请参见[表3]。 表3存算一体1:4云盘规格 | **规格名称** | **CPU** **架构** | **vCPU** | **内存（GB）** | **单节点存储容量** | **步长**（GB） | 带宽 | DN数量 | **使用场景** | |:---|:---|:---|:---|:---|:---|:---|:---|:---| | dwsx2.h.xlarge.4.c7 | X86 | 4 | 16 | 20GB \~ 2000GB | 20 | 1.5 Gbps | 1 | DWS的入门规格，一般用于测试、学习环境或者小型分析系统。 | | dwsk2.h.xlarge.4.kc1 | ARM | 4 | 16 | 20GB \~ 2000GB | 20 | 1 Gbps | 1 | DWS的入门规格，一般用于测试、学习环境或者小型分析系统。 | | dwsk2.h.xlarge.kc2 | ARM | 4 | 16 | 20GB \~ 2000GB | 20 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 1 | DWS的入门规格，一般用于测试、学习环境或者小型分析系统。 | | dwsx2.h.xlarge.4.c7n | X86 | 4 | 16 | 20GB \~ 2000GB | 20 | 2 Gbps | 1 | DWS的入门规格，一般用于测试、学习环境或者小型分析系统。 | | dwsx2.h.2xlarge.4.c6 | X86 | 8 | 32 | 100GB \~ 4000GB | 100 | 2 Gbps | 1 | 适用于中小企业规模企业内部数据仓库构建和报表分析。 | | dwsx2.h.2xlarge.4.c7 | X86 | 8 | 32 | 100GB \~ 4000GB | 100 | 2 Gbps | 1 | 适用于中小企业规模企业内部数据仓库构建和报表分析。 | | dwsk2.h.2xlarge.4.kc1 | ARM | 8 | 32 | 100GB \~ 4000GB | 100 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 1 | 适用于中小企业规模企业内部数据仓库构建和报表分析。 | | dwsk2.h.2xlarge.kc2 | ARM | 8 | 32 | 100GB \~ 4000GB | 100 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 1 | 适用于中小企业规模企业内部数据仓库构建和报表分析。 | | dwsx2.h.2xlarge.4.c7n | X86 | 8 | 32 | 100GB \~ 4000GB | 100 | 3 Gbps | 1 | 适用于中小企业规模企业内部数据仓库构建和报表分析。 | | dwsx2.h.4xlarge.4.c7 | X86 | 16 | 64 | 100GB \~ 8000GB | 100 | 4 Gbps | 1 | 推荐在生产环境下使用，适用于绝大部分企业大数据量OLAP分析系统，BI报表，可视化大屏等场景。 | | dwsk2.h.4xlarge.4.kc1 | ARM | 16 | 64 | 100GB \~ 8000GB | 100 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 1 | 推荐在生产环境下使用，适用于绝大部分企业大数据量OLAP分析系统，BI报表，可视化大屏等场景。 | | dwsk2.h.4xlarge.kc2 | ARM | 16 | 64 | 100GB \~ 8000GB | 100 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 1 | 推荐在生产环境下使用，适用于绝大部分企业大数据量OLAP分析系统，BI报表，可视化大屏等场景。 | | dwsx2.h.4xlarge.4.c7n | X86 | 16 | 64 | 100GB \~ 8000GB | 100 | 4 Gbps | 1 | 推荐在生产环境下使用，适用于绝大部分企业大数据量OLAP分析系统，BI报表，可视化大屏等场景。 | | dwsx2.h.8xlarge.4.c7 | X86 | 32 | 128 | 100GB \~ 16000GB | 100 | 7.5 Gbps | 2 | 推荐在生产环境下使用，适用于绝大部分企业大数据量OLAP分析系统，BI报表，可视化大屏等场景。 | | dwsk2.h.8xlarge.4.kc1 | ARM | 32 | 128 | 100GB \~ 16000GB | 100 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 2 | 推荐在生产环境下使用，适用于绝大部分企业大数据量OLAP分析系统，BI报表，可视化大屏等场景。 | | dwsk2.h.8xlarge.kc2 | ARM | 32 | 128 | 100GB \~ 16000GB | 100 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 2 | 推荐在生产环境下使用，适用于绝大部分企业大数据量OLAP分析系统，BI报表，可视化大屏等场景。 | | dwsx2.h.8xlarge.4.c7n | X86 | 32 | 128 | 100GB \~ 16000GB | 100 | 8 Gbps | 2 | 推荐在生产环境下使用，适用于绝大部分企业大数据量OLAP分析系统，BI报表，可视化大屏等场景。 | | dwsk2.h.12xlarge.4.kc1 | ARM | 48 | 192 | 100GB \~ 24000GB | 100 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 4 | 有着极致的性能，适用于高吞吐数仓加工，高并发在线查询生产环境。 | | dwsk2.h.12xlarge.kc2 | ARM | 48 | 192 | 100GB \~ 24000GB | 100 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 4 | 有着极致的性能，适用于高吞吐数仓加工，高并发在线查询生产环境。 | | dwsx2.h.16xlarge.4.c7 | X86 | 64 | 256 | 100GB \~ 32000GB | 100 | 12 Gbps | 4 | 有着极致的性能，适用于高吞吐数仓加工，高并发在线查询生产环境。 | | dwsx2.h.16xlarge.4.c7n | X86 | 64 | 256 | 100GB \~ 32000GB | 100 | 16 Gbps | 4 | 有着极致的性能，适用于高吞吐数仓加工，高并发在线查询生产环境。 | | dwsk2.h.16xlarge | ARM | 64 | 256 | 100GB \~ 32000GB | 100 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 4 | 有着极致的性能，适用于高吞吐数仓加工，高并发在线查询生产环境。 | | dwsk2.h.24xlarge | ARM | 96 | 384 | 100GB \~ 48000GB | 100 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 4 | 有着极致的性能，适用于高吞吐数仓加工，高并发在线查询生产环境。 | | dwsk2.h.32xlarge | ARM | 128 | 512 | 100GB \~ 64000GB | 100 | **min (350, 120 + 0.5 × 容量) MiB/s**，公式中容量单位为GiB | 4 | 有着极致的性能，适用于高吞吐数仓加工，高并发在线查询生产环境。 | #### 实时数仓GUC参数最佳配置 创建完实时数仓后，请按[表4]配置GUC参数相应推荐值，配置方法参考以下步骤。 表4实时数仓GUC参数优化表 | 参数名 | 描述 | 参数类型 | 取值范围 | 实时数仓推荐值 | |:---|:---|:---|:---|:---| | enable_codegen | 标识是否允许开启代码生成优化，目前代码生成使用的是LLVM优化。 | USERSET | - on表示允许开启代码生成优化。 - off表示不允许开启代码生成优化。 | off | | enable_numa_bind | 设置是否开启NUMA绑定功能，该参数仅9.1.0.100及以上集群支持。 | SIGHUP | - on表示开启NUMA绑定。 - off表示关闭NUMA绑定。 | DN取值设置为on，CN取值设置为off。 | | abnormal_check_general_task | cm_agent定期清理CN空闲连接的时间间隔。 | CM参数 | 非负整型，单位为秒，默认为60。 | 3600 | 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，减少反复建立连接的开销。 ![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/caution_3.0-zh-cn.png) 默认值60s的定期清理时间间隔，对毫秒级业务性能影响较大，单个线程重新创建的开销大约需要300ms，有毫秒级性能敏感场景建议调大。如果清理时间间隔内清理连接较慢，会导致占用内存较高。 需修改**abnormal_check_general_task**取值，联系技术支持人员修改。 #### 实时数仓HStore表最佳配置参数及创建示例 实时数仓场景下，推荐表的类型为：**hstore_opt** ，在使用HStore表前，请参见[表5]设置以下与HStore_opt表相关的参数。 表5HStore表相关参数 | 是否必选 | **参数名称** | **参数说明** | **推荐值** | **是否重启集群**生效 | |:---|:---|:---|:---|:---| | **必选** | autovacuum | 控制是否打开autovacuum，默认值on。 该参数需联系技术支持人员修改。 | on | 否 | | **必选** | autovacuum_max_workers | 设置能同时运行的自动清理线程的最大数量。其中0表示不会自动进行autovacuum。 | - 9.1.0.xxx及以下集群中该参数默认值为6。 - 9.1.1及以上集群中该参数默认值为2。 | 否 | | **必选** | autovacuum_max_workers_hstore | 设置hstore表automerge工作线程的数量，autovacuum_max_workers的取值一定大于autovacuum_max_workers_hstore取值，建议autovacuum_max_workers=6，autovacuum_max_workers_hstore=3。 | 3 | 否 | | **必选** | enable_col_index_vacuum | 控制是否开启索引清理，避免更新入库后索引持续膨胀与入库性能劣化，该参数仅8.2.1.100及以上集群支持。 该参数需联系技术支持人员修改。 | on | 否 | | **可选** | autovacuum_naptime | 控制autovacuum的轮询间隔。 | 20s | 否 | | **可选** | colvacuum_threshold_scale_factor | 控制列存vacuum重写中，重写文件最低dead tuple的比例值。当文件中的dead tuple占(all_tuple - null_tuple)的比例大于此值，该文件才会被重写，取值范围： - -2，表示不会执行vacuum重写，也不会执行vacuum清理。 - -1，表示不会执行vacuum重写，只会执行vacuum清理。 - 0\~100，表示dead tuple的比例值。 该参数需联系技术支持人员修改。 | 70 | 否 | | **可选** | col_min_file_size | 用于控制可以清理文件的阈值，超过128MB即可以触发清理，默认是1GB才能触发，在有频繁更新或者回滚的场景下建议使用。 | 1GB | 否 | | **可选** | autovacuum_compaction_rows_limit | 控制后台小CU合并与0CU清理，0表示只清理0CU，不处理小CU。 | 2500 | 否 | | **可选** | autovacuum_compaction_time_limit | 控制后台0CU清理的触发频率，单位分钟。 该参数需联系技术支持人员修改。 | 1 | 否 | | **可选** | autovacuum_merge_cu_limit | 控制automerge一个事务处理的CU个数，0代表尽可能多地在一个事务内处理所有待merge的CU。 该参数需联系技术支持人员修改。 | 10 | 否 | 1. 配置GUC参数。 1. 在DWS控制台，选择"集群 \> 集群列表"。 2. 在集群列表中找到所需要的集群，单击集群名称，进入"集群详情"页面。 3. **修改必选参数** ：单击"**参数修改** "页签，分别搜索**autovacuum_max_workers** 、**autovacuum_max_workers_hstore** ，参见[表5]设置为**推荐值** ，单击"保存"。 ![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/note_3.0-zh-cn.png) autovacuum、enable_col_index_vacuum默认值已为推荐值，无需单独设置。 4. **修改可选参数** ：搜索**autovacuum_naptime** 、**cost_model_version** ，参见[表5]设置为**推荐值** ，单击"保存"。 ![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/note_3.0-zh-cn.png) 其他可选参数，请联系技术支持人员修改。 2. 使用SQL编辑器连接DWS集群，创建HStore表，以下为建表示例。 **请根据业务数据特征选择合理的分布键、分区键。** 详情参见[DWS总体开发设计规范](https://support.huaweicloud.com/devg-dws/dws_04_0075.html)。 ``` 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. 当出现异常数据处理时，进行分区调整。 ``` ALTER TABLE public.hstore_opt_table_demo ADD PARTITION pmax VALUES LESS THAN (maxvalue); ``` 4. HStore表的其他使用建议和性能对比，请参见[HStore表相比于普通行、列存表性能对比实践]。 #### HStore表相比于普通行、列存表性能对比实践 1. **并发更新实践** 在普通列存表上插入一批数据后，开启两个会话，其中会话1删除某一条数据，不结束事务： ``` 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锁问题： ``` BEGIN; BEGIN DELETE col where a = 2; ``` 使用HStore表重复上面实验，能够观察到会话2直接执行成功，不会锁等待。 ``` BEGIN; BEGIN DELETE hs where a = 2; DELETE 1 ``` 2. **压缩效率实践** 构建一张有三百万数据的数据表data。 ``` 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。 ``` 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表，观察大小为3.5MB。 ``` 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左右。 ``` 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表的第四列，耗时300毫秒左右。 ``` 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。 ``` 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_hstore_opt=off%' or op like '%enable_hstore_opt=false%') ; ``` 先将2、3 都执行完毕后， 再执行2生成的建表语句，3生成的导入数据语句，每个表依次执行。 2. 生成带有enable_hstore_opt参数的建表语句。 ``` 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, c.reloptions::text as op from pg_class c join pg_namespace n on c.relnamespace = n.oid where c.relkind ='r' and c.oid >16384 and n.nspname ='public' and ( c.reloptions::text like'%enable_hstore_opt=off%' or c.reloptions::text like'%enable_hstore_opt=false%' ) ) t; ``` 3. 生成导入数据。表名替换，删除旧表的语句。 ``` select 'start transaction; ' || 'lock table ' || full_tbname || ' in EXCLUSIVE mode; ' || 'insert into ' || full_tbname || '_opt select * from ' || full_tbname || '; ' || 'alter table ' || full_tbname || ' rename to ' || quote_ident(relname || '_bk') || '; ' || 'alter table ' || full_tbname || '_opt rename to ' || quote_ident(relname) || '; ' || 'commit; ' || 'drop table ' || full_tbname || '_bk;' from ( select quote_ident(n.nspname) || '.' || quote_ident(c.relname) as full_tbname, c.relname as relname, c.reloptions from pg_class c join pg_namespace n on c.relnamespace = n.oid where c.relkind = 'r' and c.oid > 16384 and n.nspname = 'public' and ( c.reloptions @> array['enable_hstore_opt=off'] or c.reloptions @> array['enable_hstore_opt=false'] ) ) t; ```