Hologres supports Grand Unified Configuration (GUC) parameters to control query behavior, connection management, performance, and security at the session or database level.
Limits
GUC parameters do not apply to system tables.
GUC parameter reference
Parameters are grouped by function. The Level column indicates where each parameter can be configured: Session (takes effect immediately for the current connection) or Database (takes effect after reconnecting).
Auto-analyze
These parameters control the auto-analyze feature, which automatically collects statistics to keep query plans accurate.
| Parameter | Description | Default | Level |
|---|---|---|---|
hg_enable_start_auto_analyze_worker | Enables or disables auto-analyze. | on (Hologres V1.1+) | Session / Database |
hg_auto_check_table_changes_interval | Interval at which Hologres checks internal tables for changes and triggers auto-analyze. | 10min | Session / Database |
hg_auto_check_foreign_table_changes_interval | Interval at which Hologres checks foreign tables for changes and triggers auto-analyze. | 4h | Session / Database |
hg_auto_analyze_max_sample_row_count | Maximum number of rows sampled per auto-analyze run. | 16777216 | Session / Database |
hg_fixed_api_modify_max_delay_interval | Maximum delay before auto-analyze picks up changes made through fixed APIs. | 3day | Session / Database |
MaxCompute foreign table query
These parameters tune how Hologres queries MaxCompute foreign tables. For tuning guidance, see Optimize query performance for MaxCompute foreign tables.
| Parameter | Description | Default | Valid values | Level |
|---|---|---|---|---|
hg_foreign_table_max_partition_limit | Maximum number of partitions hit per query. A value of 0 means no limit. | 512 (before V3.0.7); 0 (V3.0.7+) | 0–1024 | Session / Database |
hg_experimental_query_batch_size | Number of rows fetched per batch when scanning a MaxCompute table. | 8192 | — | Session / Database |
hg_foreign_table_split_size | Data split size (in MB) for parallel reads. Avoid setting an excessively large value. | 64 | — | Session / Database |
hg_foreign_table_executor_max_dop | Maximum degree of parallelism (DOP) for query execution. | Number of CPU cores (max 128) | — | Session / Database |
hg_foreign_table_executor_dml_max_dop | Maximum DOP for DML operations on foreign tables. | 32 | — | Session / Database |
hg_enable_access_odps_orc_via_holo | Enables reading MaxCompute ORC files through the Hologres native reader. | on (Hologres V1.1+) | — | Session / Database |
Result cache
| Parameter | Description | Default | Level |
|---|---|---|---|
hg_experimental_enable_result_cache | Enables result caching for identical queries. Disable only when stale cache results cause issues. | on | Session / Database |
Internal table query optimization
These parameters tune the query optimizer for internal tables. For details, see Optimize query performance.
| Parameter | Description | Default | Level |
|---|---|---|---|
optimizer_join_order | Controls how the optimizer searches for the optimal join order. Set to query to use the query-specified order. | exhaustive | Session |
optimizer_force_multistage_agg | Forces multi-stage aggregation. Enable for queries with high-cardinality GROUP BY that show poor performance. | off | Session |
Security and encryption
| Parameter | Description | Default | Level |
|---|---|---|---|
hg_anon_enable | Enables data masking. Configure at the database level so the setting applies to all sessions. | off | Database (recommended) |
hg_experimental_encryption_options | Enables and configures data encryption at rest. Configure at the database level. | off | Database (recommended) |
Query and connection timeouts
Configure idle_session_timeout at the database level. The default value of 0 disables automatic idle connection release, which can exhaust the connection limit and cause connection leaks.
| Parameter | Description | Default | Level |
|---|---|---|---|
statement_timeout | Cancels any active query that runs longer than the specified duration. Value is in milliseconds; 0 disables the timeout. For details, see Manage queries. | 8h | Session (recommended) |
idle_in_transaction_session_timeout | Terminates sessions that are idle within an open transaction for longer than the specified duration. Value is in milliseconds; 0 disables the timeout. Configure at the database level to prevent transaction leaks from locking the database. For details, see Manage queries. | 10min | Database (recommended) |
idle_session_timeout | Releases idle connections that have been inactive for longer than the specified duration. Value is in milliseconds; 0 disables automatic release. For details, see Manage connections. | 0 (disabled) | Database (recommended) |
Data type conversion
| Parameter | Description | Default | Level |
|---|---|---|---|
hg_experimental_functions_use_pg_implementation | Switches the specified conversion function (to_char, to_date, or to_timestamp) to the PostgreSQL implementation, which supports the year range 0000–9999. The default Hologres implementation supports 1925–2282. Supported in Hologres V1.1.31+. For details, see Data type conversion function. | — | Session / Database |
Example: To extend the year range for to_char:
set hg_experimental_functions_use_pg_implementation = 'to_char';Aggregate functions
| Parameter | Description | Default | Valid values | Level |
|---|---|---|---|---|
hg_experimental_approx_count_distinct_precision | Controls the precision (and memory usage) of the APPROX_COUNT_DISTINCT function. Higher values reduce error margin but increase memory. | 17 | 12–20 | Session / Database |
Time zone
| Parameter | Description | Default | Level |
|---|---|---|---|
timezone | Sets the time zone for the session or database. | GMT-8:00 | Session / Database |
Table operations
| Parameter | Description | Default | Level |
|---|---|---|---|
hg_experimental_enable_create_table_like_properties | When enabled, CREATE TABLE LIKE copies both the table schema and table properties (primary key, index). When disabled, only the schema is copied. | off | Session / Database |
hg_experimental_affect_row_multiple_times_keep_first | Sets the INSERT ON CONFLICT conflict resolution policy to keep the first occurrence when a batch contains duplicate primary key values. | off | Session / Database |
hg_experimental_affect_row_multiple_times_keep_last | Sets the INSERT ON CONFLICT resolution policy to keep the last occurrence when a batch contains duplicate primary key values. | off | Session / Database |
Replication and monitoring
| Parameter | Description | Default | Level |
|---|---|---|---|
hg_experimental_enable_read_replica | Enables shard-level replication. | on | Session / Database |
hg_experimental_display_query_id | Displays the query ID in the NOTICE field on the client. Works for HoloWeb and PostgreSQL clients. For JDBC, retrieve the query ID with statement.getWarnings(). | off | Session / Database |
Check the current value of a GUC parameter
Run SHOW to check the current or default value of a parameter:
-- Check whether auto-analyze is enabled
SHOW hg_enable_start_auto_analyze_worker;
-- Check the MaxCompute partition limit
SHOW hg_foreign_table_max_partition_limit;Configure GUC parameters
Configure GUC parameters at the session level or database level depending on the parameter's scope and your use case. Not all parameters need to be set at the database level.
Session level
The SET statement configures a parameter for the current connection only. The setting is discarded when the connection closes. Use session-level configuration when the behavior should apply to a specific query or workload, not globally.
Syntax:
set <GUC_name> = <value>;Examples:
-- Enable auto-analyze for this session
set hg_enable_start_auto_analyze_worker = on;
-- Limit MaxCompute partition hits to 1024 for this session
set hg_foreign_table_max_partition_limit = 1024;Database level
The ALTER DATABASE statement configures a parameter for all future connections to the specified database. The setting takes effect after you close and reopen the connection. When you create a new database, configure its GUC parameters explicitly — they are not inherited automatically.
Syntax:
alter database <db_name> set <GUC_name> = <value>;Examples:
-- Enable auto-analyze for all connections to testdb
alter database testdb set hg_enable_start_auto_analyze_worker = on;
-- Limit MaxCompute partition hits to 1024 for all connections to testdb
alter database testdb set hg_foreign_table_max_partition_limit = 1024;
-- Enable data masking for all connections to a database
alter database <db_name> set hg_anon_enable = on;
-- Enable data encryption for all connections to a database
alter database <db_name> set hg_experimental_encryption_options='AES256,623c26ee-xxxx-xxxx-xxxx-91d323cc4855,AliyunHologresEncryptionDefaultRole,187xxxxxxxxxxxxx';
-- Release idle connections after 10 minutes (600,000 ms) of inactivity
alter database <db_name> SET idle_session_timeout = 600000;