V4.2.0 Release Notes - Hologres - Alibaba Cloud Documentation Center

Query optimization and performance

Version 4.2 enhances query optimization by expanding QE V2 coverage to more scenarios, adding new plan rewrite rules for Common Table Expressions (CTEs), and optimizing the reuse of fragment instances and scan filters. These improvements significantly increase query throughput and filtering efficiency.

Enhanced support for Query Engine V2 (QE V2): The QE V2 execution engine, introduced in version 3.1, now supports more scenarios, including nested loop join, two-stage broadcast, lookup join, SQE (direct reads from external tables), and adaptive Bulkload Degree of Parallelism (DOP).
Runtime filter support for cross join: Aggregation results from scalar subqueries can now be pushed down to large probe-side tables for early filtering. The runtime filter directly eliminates non-matching rows and row groups during the scan phase, reducing unnecessary I/O.
In-file parallel scan: A single ORC file can now be split into multiple sub-splits based on split_size_mb and read concurrently by multiple NiagaraScan tasks. This eliminates the bottleneck of single-threaded scalar scans on large files. Controlled by the GUC parameter hg_experimental_enable_parallel_file_scan, disabled by default.
Vector TopK scan rewrite: This plan rewriting allows vector queries to benefit from large files without significantly affecting the performance of scalar queries.
Scan filter reuse: The scan operator now reuses filters to reduce memory allocation and computational overhead. At a 5% to 20% filter rate, performance improves by over 100%; at a 50% filter rate, by over 200%. Controlled by the GUC parameter hg_experimental_qe_v2_enable_reuse_filter, enabled by default.
Fragment instance reuse: After consuming a split, a fragment instance resets its state to consume the next split instead of being destroyed. In multi-file scenarios, this reduces memory costs to zero and lowers CPU cost to 41.8% of creating a new instance. Controlled by the GUC parameter hg_experimental_qe_v2_enable_reuse_fragment_instance, enabled by default.
Redundant project pushdown: Unneeded columns are pruned from intermediate operators earlier in the query plan, reducing data width and overall computational overhead. Controlled by the GUC parameter hg_experimental_enable_push_down_project, enabled by default.
Automatic CTE extraction for repeated patterns: When an SQL pattern is repeated beyond a threshold, the system automatically extracts it into a CTE to reduce redundant scanning. Controlled by the GUC parameter hg_enable_extract_common_plan_to_cte, enabled by default.
Aggregation pushdown into CTE producers: When the compression ratio of an aggregation above a CTE consumer exceeds a threshold, the aggregation is pushed down into the CTE producer to compress data earlier and reduce intermediate data volume. Controlled by the GUC parameter hg_experimental_enable_push_agg_into_cte_producer, enabled by default.
Simplified predicates pushed into CTEs: When a filter above a CTE consumer uses the same columns and the CTE count is below a threshold, the pushed-down predicate is simplified. Controlled by the GUC parameter hg_factorize_pushed_cte_predicates_threshold, with a default value of 4.
Equivalent column derivation for runtime filters: Equivalent columns can now be inferred from equality conditions, enabling additional conditions to generate runtime filters and improving filter effectiveness.
Shuffle pushdown and merge: When multiple layers of hash shuffles share the same prefix, only one shuffle is performed at the lowest layer with fewer hash keys, and redundant intermediate shuffles are removed. Controlled by the GUC parameter hg_experimental_enable_push_down_merge_shuffle, enabled by default.
New strategy to adjust scale_factor for join order: When the number of join operators meets or exceeds a threshold, the scale_factor is adjusted to produce a more optimal join order. Controlled by the GUC parameter hg_experimental_adjust_scale_factor_for_join_order, enabled by default.
Enhanced Hologres PG functions: Over 300 PQE functions, including common JSON and JSONB functions, have been migrated to a new execution framework and can now be pushed down to HQE, significantly improving query performance and stability.

Data ingestion and import

Optimized writes for Internal Stage: Stage directories are now automatically cleaned up when a database is dropped and recreated, eliminating the risk of storage redundancy. Pangu I/O throttling is also enhanced to enforce strict per-worker IOPS and throughput limits.
Paimon FileSystem Catalog support: Paimon tables can now be accessed through the Paimon FileSystem Catalog, simplifying data lake table management and supporting hybrid cloud scenarios such as data synchronization between on-premises data centers and the cloud.
Schema inference for external files: Hologres can now automatically infer the schema of external CSV and ORC files, reducing the configuration effort for external tables.
Read and write support for the Paimon blob type: With DLF, Hologres supports reading and writing the Paimon blob data type, which is mapped to the bytea type in the PostgreSQL ecosystem. This lets AI functions process multimodal data stored as Paimon blobs.
Merged query engine paths for data lake HiveQE and FSE: The query engine paths for HiveQE and FSE in data lake scenarios have been merged, improving query efficiency by over 30%.

Data processing and materialization

Dynamic Table incremental mode supports TopN data processing: You can use the ROW_NUMBER() OVER (...) window function to perform TopN filtering in the SQL definition of an incremental Dynamic Table. The refresh mode is automatically inferred as incremental mode.
Message merging for incremental Dynamic Table change consumption: When an incremental Dynamic Table consumes changes, it now supports merging positive and negative messages. This reduces unnecessary refresh overhead.
Cron job for scheduled tasks: You can now define custom scheduled tasks (cron jobs) using functions like hg_register_cron_job, hg_alter_cron_job, and hg_drop_cron_job. You can specify the schedule or interval, warehouse resources, and an automatic retry policy.

AI and multimodal search

AI function support for Model Studio: AI functions now integrate with Model Studio, supporting models such as LLM, LLM-VL, embedding, qwen-vl-ocr, and the qwen-mt series. This covers typical scenarios such as LLM inference, text embedding, visual understanding, OCR, and translation.
New AI function: AI_TRANSCRIBE: Adds the speech-to-text AI function AI_TRANSCRIBE(model, audio_file[, options]) to convert unstructured audio data into structured text.
Binary input support for the ai_embed function: The ai_embed function now accepts BYTEA content and Paimon blob data, enabling direct embedding on binary data for multimodal search and image similarity search.
Full-text search now supports fuzzy queries and search-time tokenizer parameters: Full-text search now includes fuzzy matching and supports the search_analyzer_params parameter for configuring the tokenizer at search time.
Optimized format and performance for full-text indexes: The index format now uses multiple segments instead of a single segment to support high-performance concurrent index access for large files. This change reduces the initial memory usage for building a full-text index. Search performance is improved for scenarios with low search term hit rates and is over three times faster in specific cases. The tokenization performance of the pinyin tokenizer and filter has also been improved, with throughput increases of 25% to 150%.

Data types and compatibility

Decimal 256 support: The precision range for the DECIMAL data type has been extended from 38 to 76 digits, now supporting DECIMAL(76, 38). This meets the high-precision computing needs of financial and accounting applications. It supports basic arithmetic functions, aggregate functions, and common expressions, and can be used in the incremental refresh of a Dynamic Table. It cannot be used as a distribution key, cluster key, or partition key.

Storage and engine

New Liquid Table allows changing the number of shards without interrupting reads or writes: You can create a Liquid Table and dynamically modify its number of shards by using ALTER TABLE. During this modification, write requests may experience increased latency but will not fail, while read latency is unaffected. You must delete all Liquid Tables before downgrading to V4.1.
Lazy-loading support for table-level snapshots: Snapshots that exceed the count limit are removed from memory and loaded from the meta tablet on demand. Snapshots can also be created for tables with streaming enabled.
Reduced disk files for Hologres tables: Hologres no longer creates empty files for each table, which improves FE startup speed.
Distributed ephemeral storage: The stage execution sink now supports writing to distributed ephemeral storage, improving the storage and management of intermediate results.
Partition-level locking for logically partitioned tables in serverless computing: Concurrent DML operations on multiple partitions in serverless computing scenarios now use partition-level locking instead of table-level locking, reducing lock contention between partitions.
Logically partitioned tables reach general availability: Logically partitioned tables have completed the beta phase and are now production-ready.

O&M and resource management

Relaxed HBO admission limits: The threshold for identifying a large query has been reduced from 20 seconds to 1 second, and the system will now always write statistics for these queries. This allows History-Based Optimization (HBO) to cover more queries and provide more precise optimizations.
Enhanced system tables for stage execution: New fields such as serverless allocated cores and stage count have been added to align with existing refresh activity capabilities, helping you understand and predict execution progress.

Default behavior changes

Change in the logic for the row_count field in table statistics
- What's changed: Hologres now calculates the row_count using statistics v2. This new method, which is based on file metadata, provides more stable counts in scenarios involving expired Time-to-Live (TTL) settings or frequent updates.
Auto split is now enabled by default for direct reads from MaxCompute via the common table path
- What's changed: Starting from version 4.2, the auto split feature is enabled by default for the common table path. This is controlled by the GUC parameter hg_experimental_enable_maxcompute_sdk_auto_split_size. You can disable it using this parameter if you encounter any issues.
Recycle bin operations now cancel running queries
- What's changed: In version 4.2, deleting an object in the recycle bin cancels any ongoing DML or DQL operations on that object.
- Impact: When you drop a table to the recycle bin, any active DML or DQL queries on that table are canceled and fail.
System table permission changes
- What's changed: Superusers no longer have DML permissions on any tables under the pg_catalog schema.
- Impact: Any scripts or tools that rely on DML operations against pg_catalog tables will fail.
Change in the minimum TTL constraint behavior
- What's changed: The new GUC parameter hg_time_to_live_in_days_min_value controls the minimum value of time_to_live_in_seconds. The default value is 36,500 days. This means that when you create a new table, the TTL cannot be set to less than 36,500 days (100 years).
Change in precision inference for numeric functions
- What's changed: Precision inference capabilities have been added to numeric-related functions in PQE. Precision is now managed by rounding instead of truncation.
Fast Rows is now enabled by default and its partition count is adjusted for external tables
- What's changed: For queries involving external tables that have no statistics, the system now performs an extra step to get Fast Rows by default before query execution.
Change in the default tokenizer for full-text search
- What's changed: When you create a new index for full-text search, the default tokenizer is now ik instead of jieba. This change improves performance and recall.