What's New
Function Overview
Product Bulletin
- Product Notice
- Version Support Notes
Service Overview
- GaussDB(DWS) Infographics
- What Is GaussDB(DWS)?
- Data Warehouse Types
- Data Warehouse Flavors
- Advantages
- Application Scenarios
- Functions
- Concepts
- Related Services
- Security
- GaussDB(DWS) Permissions Management
- GaussDB(DWS) Access
- Restrictions
- Technical Support
- Service Quotas
- GaussDB(DWS) Technical Specifications
Billing
- GaussDB(DWS) Billing Overview
- Billing Modes
- Item
- Billing Examples
- Billing Mode Change
- Renewal
  - Overview
  - Manual Renewal
- Bills
- Arrears
- Stopping Billing
- Cost Management
- Billing FAQs
Getting Started
- Checkpoint Vehicle Analysis
- Supply Chain Requirement Analysis of a Company
- Operations Status Analysis of a Retail Department Store
- Creating a Time Series Table
- Best Practices of Hot and Cold Data Management
- Best Practices for Automatic Partition Management
- Creating a Cluster and Connecting to It
- Using CDM to Migrate MySQL Data to the GaussDB(DWS) Cluster
- Using DLI Flink Jobs to Write Kafka Data to GaussDB(DWS) in Real Time
- Basic SQL Operations
- Database Quick Start
- Getting Started with Common Practices
User Guide
- Using GaussDB(DWS)
- Preparations
- Creating a GaussDB(DWS) Cluster
- Connecting to a GaussDB(DWS) Cluster
- Creating a GaussDB(DWS) Database and User
- Migrating Service Data to a GaussDB(DWS) Cluster
  - Migrating Data to a GaussDB(DWS) Cluster Using GDS-Kafka
  - Data Source Management
- GaussDB(DWS) Cluster Data Security and Encryption
- GaussDB(DWS) Cluster Management
- GaussDB(DWS) Cluster O&M
Best Practices
- Import and Export
- Data Migration
- Data Analytics
- Decoupled Storage and Compute
  - DWS 3.0 Decoupled Storage and Compute Usage Suggestions and Performance Optimization
- Data Development
- Database Management
- Performance Tuning
  - Optimizing Table Structure Design to Enhance GaussDB(DWS) Query Performance
  - Analyzing SQL Statements That Are Being Executed to Handle GaussDB(DWS) Performance Issues
- Cluster Management
  - Binding Different Resource Pools to Two Types of Jobs to Balance Load for GaussDB(DWS)
  - Scaling Options for GaussDB(DWS) with a Coupled Storage-Compute Architecture
Data Migration and Synchronization
- Data Migration to GaussDB(DWS)
- Importing Data
- Full Database Migration
  - Using CDM to Migrate Data to GaussDB(DWS)
  - Using DSC to Migrate SQL Scripts
- Real-time Import
  - Using DRS to Import Data to GaussDB(DWS)
  - Real-time Data Import From Kafka GaussDB(DWS)
- Metadata Migration
  - Using gs_dump and gs_dumpall to Export Metadata
  - Using gs_restore to Import Data
- Exporting Data
- Other Operations
Developer Guide
- Standard data warehouse (9.1.0.x)
- Standard data warehouse (8.1.3.x)
- Hybrid Data Warehouse (9.1.0.x）
- Hybrid Data Warehouse (8.1.3.x）
- Historical Versions
SQL Syntax Reference
- SQL Syntax Reference (9.1.0.x)
- SQL Syntax Reference (8.1.3.x)
- Historical Versions
Performance Tuning
- Overview of Query Performance Optimization
- Query Execution Process
- SQL Execution Plan
- SQL Optimization Guide
- Optimization Cases
- SQL Execution Troubleshooting
- query_band Load Identification
- Common Performance Parameter Optimization Design
Tool Guide
- Overview
- Downloading Related Tools
- gsql
- Data Studio
- GDS
- DSC
- DataCheck
- DWS-Connector
- Server Tool
API Reference
- Before You Start
- API Overview
- Calling APIs
- Getting Started
- API Description
- Application Cases
  - Using Postman to Call the API for Creating a Cluster
  - Using Postman to Call the API for Creating a Snapshot
- Introduction
- Appendix
SDK Reference
- SDK Overview
FAQs
- Top FAQs
- Product Consulting
- Database Connections
- Data Migration
- Database Usage
- Cluster Management
- Account Permissions
- Database Performance
- Backup and Restoration
  1. Why Does It Take a Long Time to Create an Automated Snapshot in GaussDB(DWS)?
  2. Does a GaussDB(DWS) Snapshot Have the Same Function as an EVS Snapshot?
Troubleshooting
- Database Connections
- JDBC/ODBC
- Data Import and Export
- Database Parameter Modification
- Account/Permission/Password
- Cluster Performance
- Cluster Exceptions
  - The Disk Usage Alarm Is Frequently Generated
- Database Use
Videos
Performance White Paper
- Overview
- Test Result
  - TPC-H Single Query Test
  - TPC-DS Single Query Test
- Test Methods
- Appendixes
  - TPC-H Test Sets
  - TPC-DS Test Sets
Technical White Paper
- GaussDB(DWS)
- Platforms and Technical Specifications Supported by GaussDB(DWS)
  - Technical Specifications
- GaussDB(DWS) Core Technologies
- GaussDB(DWS) Tools
  - Client Tools
  - Database Monitoring Tool
- External APIs
Error Code Reference
- 8.2.0 and earlier versions
  - Management Console Error Code
  - Data Warehouse Service Error Codes
- 8.2.1 or later versions
Glossary
More Documents
- User Guide
- API Reference (ME-Abu Dhabi Region)
- Developer Guide (ME-Abu Dhabi Region)
- SQL Syntax Reference (ME-Abu Dhabi Region)
- Tool Guide (ME-Abu Dhabi Region)
- Error Code Reference (ME-Abu Dhabi Region)
  - Management Console Error Code
- User Guide (Paris Region)
- API Reference (Paris Region)
- Developer Guide (Paris Region)
- SQL Syntax Reference (Paris Region)
- Tool Guide (Paris Region)
- Error Code Reference (Paris Region)
  - Management Console Error Code
- User Guide (Kuala Lumpur Region)
- API Reference (Kuala Lumpur Region)
- Developer Guide (Kuala Lumpur Region)
- SQL Syntax Reference (Kuala Lumpur Region)
- Tool Guide (Kuala Lumpur Region)
- Error Code Reference (Kuala Lumpur Region)
  - Management Console Error Code
General Reference
- Glossary
- Service Level Agreement
- White Papers
- Endpoints
- Permissions

On this page

autovacuum
autovacuum_compaction_rows_limit
autovacuum_compaction_time_limit
autovacuum_max_workers
autovacuum_max_workers_hstore
hstore_buffer_size
gtm_option
defer_xid_cleanup_time
enable_hstore_keyby_upsert
autovacuum_asyncsort_rows_limit
autovacuum_asyncsort_size_limit
autovacuum_asyncsort_time_limit
enable_hstore_binlog_table
enable_generate_binlog
binlog_consume_timeout
enable_hstoreopt_auto_bitmap
enable_cu_predicate_pushdown
enable_hstoreopt_insert_sort

Show all

Help Center/ GaussDB(DWS)/ Developer Guide/ Hybrid Data Warehouse (9.1.0.x）/ Hybrid Data Warehouse GUC Parameters

Hybrid Data Warehouse GUC Parameters

Updated on 2025-01-07 GMT+08:00

View PDF

autovacuum

Parameter description: Specifies whether to start the automatic cleanup process (autovacuum).

Type: SIGHUP

Value range: Boolean

on indicates the database automatic cleanup process is enabled.
off indicates that the database automatic cleanup process is disabled.

Default value: on

autovacuum_compaction_rows_limit

Parameter description: Specifies the threshold of a small CU. A CU whose number of live tuples is less than the value of this parameter is considered as a small CU. This parameter is supported only by clusters of version 8.2.1.300 or later.

Type: USERSET

Value range: an integer ranging from -1 to 5000

Default value: 2500

NOTICE:

If the version is earlier than 9.1.0.100, do not set this parameter. Otherwise, duplicate primary key data may occur.

NOTE:

If the version is earlier than 9.1.0.100, value –1 indicates that the 0 CU switch is disabled.
In version 9.1.0.100, the default value of this parameter is 0.
In 9.1.0.200 and later versions, the default value of this parameter is 2500.
You are advised not to modify this parameter. If you do need to modify this parameter, contact technical support.

autovacuum_compaction_time_limit

Parameter description: Specifies the interval for clearing small CUs. Small CUs are merged at a specified interval. This parameter is supported only by clusters of version 8.2.1.300 or later.

Type: SIGHUP

Value range: an integer ranging from 0 to 10080. The unit is minute.

Default value: 0

autovacuum_max_workers

Parameter description: Specifies the maximum number of autovacuum worker threads that can run at the same time. The upper limit of this parameter is related to the values of max_connections and job_queue_processes.

Type: SIGHUP

Value range: an integer

The minimum value is 0, indicating that autovacuum is not automatically performed.
The theoretical maximum value is 262143, and the actual maximum value dynamically changes. Formula: 262143 - max_inner_tool_connections - max_connections - job_queue_processes - auxiliary threads - Number of autovacuum launcher threads - 1. The number of auxiliary threads and the number of autovacuum launcher threads are specified by two macros. Their default values in the current version are 20 and 2, respectively.

Default value: 4

autovacuum_max_workers_hstore

Parameter description: Specifies the maximum number of concurrent automatic cleanup threads used for hstore tables in autovacuum_max_workers.

Type: SIGHUP

Value range: an integer

Default value: 1

NOTE:

To use HStore tables, set the following parameters, or the HStore performance will deteriorate severely. The recommended settings are as follows:

autovacuum_max_workers_hstore=3, autovacuum_max_workers=6, autovacuum=true

hstore_buffer_size

Parameter description: Specifies the number of HStore CU slots. The slots are used to store the update chain of each CU, which significantly improves the update and query efficiency.

To prevent excessive memory usage, the system calculates a slot value based on the memory size, compares the slot value with the value of this parameter, and uses the smaller value of the two.

Type: POSTMASTER

Value range: an integer ranging from 100 to 100000

Default value: 100000

gtm_option

Parameter description: Specifies the GTM running mode in GaussDB(DWS). This parameter is supported by version 8.2.1 or later clusters.

GTM mode: In this mode, the GTM manages running transactions and allocates XIDs and CSNs in a unified manner.
GTM-Lite mode: The GTM is only responsible for XID allocation and CSN update, and is no longer responsible for global transaction management. The GTM-Lite mode applies to TP scenarios with high concurrency and short queries. It can improve query performance while ensuring transaction consistency.
GTM-Free mode: Distributed transactions support only external write consistency and do not support external read consistency. This mode does not take effect in hybrid data warehouses

Type: POSTMASTER

Value range: enumerated values

gtm or 0: The GTM mode is enabled.
gtm-lite or 1: The GTM-Lite mode is enabled.
gtm-free or 2: The GTM-Free mode starts.

Default value: gtm

NOTICE:

Both GaussDB(DWS) and GTM instances have the gtm_option parameter with the same meaning. For GTM and GTM-Lite, the same mode must be set in GaussDB(DWS) and GTM. Otherwise, service errors may occur.
The GTM-Free mode can be enabled by setting enable_gtm_free to on or gtm_option to gtm-free.
To set the non-GTM-Free modes, set enable_gtm_free to off.
The GTM-Free mode takes effect only in hybrid cloud and ESL scenarios.

defer_xid_cleanup_time

Parameter description: Specifies the global OldestXmin maintenance period in GTM-Lite mode in the hybrid data warehouse. In each maintenance period, the CCN or FCN collects and delivers the values of global OldestXmin. This parameter is supported by version 8.2.1 or later clusters.

This parameter takes effect only in GTM-Lite mode. You are advised not to modify this parameter.

Type: SIGHUP

Value range: an integer ranging from 1 to INT_MAX. The unit is ms.

Default value: 5,000.

enable_hstore_keyby_upsert

Parameter description: Specifies whether to enable batch upsert optimization for hstore tables, which can boost performance significantly if the front end guarantees that no two upsert operations affect the same row or column at the same time. You should turn on this parameter for such scenarios. This parameter is supported only in cluster 8.3.0 and later versions.

Type: USERSET

Value range: Boolean

on indicates that upsert optimization is enabled for hstore tables.

off indicates that the upsert process optimization on the hstore table is disabled and the old process is used.

Default value: off

autovacuum_asyncsort_rows_limit

Parameter description: This parameter specifies the row threshold for automatic asynchronous sorting. This parameter is supported only by clusters of version 9.1.0 or later.

Type: SIGHUP

Value range: an integer ranging from 120000 to 4200000

Default value: 600000

autovacuum_asyncsort_size_limit

Parameter description: Specifies the space usage threshold for automatic asynchronous sorting. This parameter is supported only by clusters of version 9.1.0 or later.

Type: SIGHUP

Value range: an integer between 1048576 and 104857600. The unit is KB.

Default value: 10485760 (10 GB)

autovacuum_asyncsort_time_limit

Parameter description: This parameter specifies the interval for automatic asynchronous sorting. Asynchronous sorting is triggered again only when the time elapsed since the last asynchronous sorting exceeds the value of autovacuum_asyncsort_time_limit. This parameter is supported only by clusters of version 9.1.0 or later.

Type: SIGHUP

Value range: an integer ranging from 0 to 10080. The unit is minute.

Default value: 10

enable_hstore_binlog_table

Parameter description: This parameter specifies whether binlog tables can be created.

Type: SIGHUP

Value range: Boolean

on indicates that binlog tables can be created.
off indicates that binlog tables cannot be created.

Default value: off

enable_generate_binlog

Parameter description: Specifies whether binlogs are generated for DML operations on binlog tables in the current session. This parameter is supported only by clusters of version 9.1.0.200 or later.

Type: USERSET

Value range: Boolean

on indicates that binlogs are generated.
off indicates that binlogs are not generated.

Default value: on

binlog_consume_timeout

Parameter description: This parameter specifies the duration for cyclically determining whether all binlog records are consumed during binlog table scaling or VACUUM FULL operations. This parameter is supported only by 8.3.0.100 and later versions. Unit: second

Type: SIGHUP

Value range: an integer ranging from 0 to 86400

Default value: 3600

enable_hstoreopt_auto_bitmap

Parameter description: This parameter determines whether to automatically set bitmap columns by default when creating HStore Opt tables. This parameter is supported only by version 9.1.0.100 or later.

Type: SIGHUP

Value range: Boolean

on indicates that the bitmap columns option is set by default.
off indicates that the bitmap columns option is not set by default.

Default value: off

enable_cu_predicate_pushdown

Parameter description:

Function overview: This function is used to control whether to enable filter pushdown. Enabling this will enhance query performance, particularly when working with the bitmap_columns column and PCK sorting column. It applies to specific WHERE, IS NULL, and IN conditions. This parameter is supported only in 9.1.0.200 and later versions.
Supported column types:
- Integer type: INT2, INT4, and INT8
- Date and time type: DATE, TIMESTAMP, and TIMESTAMPTZ
- String types: VARCHAR and TEXT
- Numeral type: NUMERIC (a maximum of 19 characters)
Query conditions: This function supports multiple WHERE expressions, including:
- IN expression: matches multiple values.
- IS NULL / IS NOT NULL condition: checks whether the column value is null.
- Comparison expressions: greater than (>), less than (<), equal to (=), and not equal to (<>), which is used for range query and exact match.

Type: USERSET

Value range: Boolean

on indicates that filter pushdown is enabled.
off indicates that filter pushdown is disabled.

Default value: on

enable_hstoreopt_insert_sort

Parameter description: This parameter determines whether to enable sorting (including VACUUM FULL) for importing data to the HStore Opt table.

This parameter is supported only by version 9.1.0.100 or later.

Type: SIGHUP

Value range: Boolean

on indicates that sorting during import is enabled.
off indicates that sorting during import (including VACUUM FULL) is disabled when asynchronous sorting is enabled (the value of autovacuum_asyncsort_time_limit is greater than 0). When asynchronous sorting is disabled, sorting during import (including VACUUM FULL) is still enabled.

Default value: on

Previous topic: Hybrid Data Warehouse Functions

Next topic: Hybrid Data Warehouse Binlog