Hybrid DML

What is Hybrid DML

Hybrid DML is a new write engine developed by Hologres for INSERT, INSERT ON CONFLICT, UPDATE, and DELETE operations. It consolidates write logic that was previously scattered across multiple operators into a single intelligent operator (DataSetHybridDMLNode) that adapts its join and deduplication strategy to the actual data volume at runtime, eliminating out-of-memory (OOM) failures when loading large tables.

In short:

• Legacy DML: the query optimizer (QO) decides the join algorithm up front and the execution engine (SE) follows a fixed plan. When the data volume exceeds the optimizer's estimate, the query is prone to OOM.

• Hybrid DML: the execution engine selects the join strategy adaptively based on real-time data volume, ensuring that the query never runs out of memory -- it may run slower, but it never fails.

Key benefits

Improved stability

With the legacy DML engine, when both the source (for example, a MaxCompute foreign table) and the target table are large, the Hash Join hash table can exceed the memory limit and cause the query -- or even the worker -- to OOM.

Hybrid DML embeds an adaptive decision engine (Game Machine) that monitors memory usage in real time as data is written batch by batch:

• Small data volume: uses Hash Join for the best performance.

• Medium data volume: automatically swaps the Hash Join build side, placing the smaller dataset in the hash table.

• Large data volume: automatically falls back to Sort-Merge Join, which spills to disk and guarantees no OOM.

Performance gains

Enable and disable

No SQL changes are required. Continue using standard INSERT, UPDATE, and DELETE statements. Hybrid DML is toggled with a GUC parameter.

-- Enable Hybrid DML (on by default in Hologres V3.1 and later)
SET hg_experimental_enable_hybrid_dml = on;

-- Disable Hybrid DML and fall back to the legacy DML engine
SET hg_experimental_enable_hybrid_dml = off;

Examples

-- Make sure Hybrid DML is enabled
SET hg_experimental_enable_hybrid_dml = on;

-- Plain load
INSERT INTO holo_table SELECT * FROM mc_table;

-- Upsert on primary key
INSERT INTO holo_table
SELECT * FROM source_table
ON CONFLICT (pk_col) DO UPDATE SET
    col1 = EXCLUDED.col1,
    col2 = EXCLUDED.col2;

-- Ignore conflicts
INSERT INTO holo_table
SELECT * FROM source_table
ON CONFLICT (pk_col) DO NOTHING;

-- Load from a Paimon foreign table
INSERT INTO holo_table SELECT * FROM paimon_table;

Core GUC parameters

Values supported by hg_experimental_hybrid_dml_join_mode:

Defaults by version

Feature support matrix

Verify whether Hybrid DML is in use

Use EXPLAIN to inspect the execution plan:

EXPLAIN INSERT INTO holo_table SELECT * FROM source_table;

• Hybrid DML plan: contains a DataSetHybridDMLNode node.

• Legacy DML plan: contains DataSetInsertNode, DataSetDeleteNode, DataSetUpdateNode, or DataSetMergeNode.

For a more detailed plan in Protobuf format:

EXPLAIN (FORMAT PB) INSERT INTO holo_table SELECT * FROM source_table;

Adaptive join decision flow

The adaptive decision engine in Hybrid DML makes its choice in real time as data is written. The flow is as follows:

Key decision points:

1. Source data smaller than target: buffer the source in a Buffer Table. After end-of-stream (EOS), build the hash table from the source and probe the target -- RIGHT OUTER JOIN.

2. Target table smaller than source: build the hash table by scanning the target, then probe with the source -- LEFT OUTER JOIN.

3. Memory insufficient: fall back to Sort-Merge Join. Each side is sorted independently, then merged. The operator spills to disk, so it never OOMs.

Runtime Filter acceleration

When Hash Join is selected and the source data is the build side, Hybrid DML generates Runtime Filters automatically:

• Rowgroup Filter: converts the source primary key values into an IN-list filter and pushes it down to the target table's storage layer, skipping irrelevant row groups.

• Bloom Filter: builds a Bloom Filter on the primary key column to prune non-matching rows early when scanning the target table.

Default trigger thresholds:

• Target table contains at least 10,000 rows.

• Source row count is less than 0.3 times the target row count.

Blind Merge optimization

An INSERT ON CONFLICT DO UPDATE statement triggers Blind Merge and skips the join with the target table entirely when all of the following conditions are met:

1. The UPDATE SET clause covers every non-primary-key column.

2. Primary key columns appear in the SET clause only as identity expressions (for example, pk = EXCLUDED.pk).

3. No subqueries or other complex expressions are used.

The reasoning behind Blind Merge: when every column will be overwritten anyway, the engine can skip reading the existing values from the target table and write the new row directly.

Functional limits

Performance expectations

Compatibility

• No impact on other features: Hybrid DML and the legacy DML engine are fully isolated. Turning off the GUC parameter reverts to legacy behavior.

• No breaking changes: SQL syntax is unchanged and the feature is transparent to applications.

• Forward compatible: workloads that ran on earlier versions continue to behave the same way.

Reading the execution plan

EXPLAIN INSERT INTO target_table
SELECT * FROM source_table
ON CONFLICT (pk) DO UPDATE SET col1 = EXCLUDED.col1;

Hybrid DML plan (example):

Insert on target_table
  ->  DataSetHybridDMLNode       -- Hybrid DML operator
        Join Strategy: Auto       -- Adaptive join
        Dedup Action: Throw       -- Source-side dedup policy
        Memory Quota: 2048 MB     -- Memory quota
        Runtime Filter: Enabled   -- Runtime Filter on
        ->  Seq Scan on source_table

Legacy DML plan (for comparison):

Insert on target_table
  ->  DataSetInsertNode          -- Legacy Insert operator
        ->  Hash Left Join       -- Fixed join strategy
              ->  Seq Scan on source_table
              ->  Seq Scan on target_table

Key differences:

• Hybrid DML produces a single-layer operator with the join strategy embedded inside.

• Legacy DML uses a standalone Join operator whose strategy is locked in by the optimizer.

Inspect the actual join strategy

Use EXPLAIN (FORMAT PB) to view the detailed Protobuf plan, which includes:

• The join strategy actually selected (join_strategy field).

• The source-side deduplication action (source_deduplicate.action field).

• Runtime Filter settings (runtime_filter_options field).

• Memory quota (memory_quota field).

Day-to-day usage

1. Keep the defaults: Hologres V3.1 and later enable Hybrid DML by default. Keep join_mode set to auto; no tuning is required for the vast majority of workloads.

2. Large-table loads need no extra work: you no longer have to estimate data volumes to pick a join strategy.

3. Dynamic Table workloads: incremental refresh uses Hybrid DML by default; no additional configuration required.

Performance tuning

Fallback procedure

If you run into a Hybrid DML issue, you can revert at any time:

-- Session-level fallback
SET hg_experimental_enable_hybrid_dml = off;

-- Database-level fallback (requires superuser privileges; takes effect for new connections)
ALTER DATABASE dbname SET hg_experimental_enable_hybrid_dml = off;

After the fallback, all DML statements run on the legacy DML engine. No SQL changes are required.