Skip to main content

DuckDB analytics in AliSQL

AliSQL integrates DuckDB as a native analytical storage engine behind the MySQL protocol and SQL interface. The current AliSQL 8.0.44-2 feature release includes DuckDB 1.4.4.

Why DuckDB​

DuckDB provides columnar storage and vectorized execution suited to scans, joins, and aggregations. AliSQL uses those capabilities without asking applications to adopt a different database protocol or analytical query endpoint.

InnoDB remains the transactional engine. DuckDB is intended for analytical tables and replicas.

AliSQL and DuckDB analytics architecture showing an InnoDB transaction primary, Row-based Binlog replication, a DuckDB analytics node, the query path, business value, and representative APIs

Integration architecture​

AliSQL provides integration logic around DuckDB for:

  • supported MySQL syntax normalization and function mapping;
  • MySQL-to-DuckDB data type and result conversion;
  • native and Copy DDL execution;
  • InnoDB-to-DuckDB table conversion;
  • Row-based Binlog replication, batching, GTID handling, and idempotent recovery;
  • resource controls for query and replication threads.

Query path​

The server parses the request and performs MySQL-side processing, then passes supported work to the DuckDB engine. AliSQL normalizes selected MySQL syntax and maps supported functions and types before converting the DuckDB result into MySQL-facing values. This adaptation is why compatibility must be evaluated at the SQL and data-type level, not only at the wire protocol.

Enable the engine​

duckdb_mode is read-only after startup:

[mysqld]
duckdb_mode=ON
duckdb_memory_limit=2147483648
duckdb_threads=0
duckdb_temp_directory=/path/to/duckdb-tmp

Create or convert a table with MySQL-facing SQL:

CREATE TABLE sales (
    id BIGINT PRIMARY KEY,
    region VARCHAR(32),
    amount DECIMAL(18,2)
) ENGINE=DuckDB;

ALTER TABLE existing_innodb_table ENGINE=DuckDB;

Review the resource controls before loading data:

VariableDefaultOperational meaning
duckdb_modeNONEStatic startup switch; use ON to enable the engine
duckdb_require_primary_keyONRequired for a replicated DuckDB node; logical keys are not physically enforced as DuckDB indexes
duckdb_memory_limit0Automatic, typically about 80% of physical memory; size it with the InnoDB buffer pool
duckdb_threads0Automatic total DuckDB worker count
duckdb_temp_directoryemptyStatic location for temporary spill files
duckdb_max_temp_directory_size0Automatic, typically about 90% of available disk space
duckdb_max_threads_per_query1000000Per-user-query worker ceiling; set a practical value below the total pool

The automatic memory and temporary-space defaults are not production sizing recommendations. Set explicit budgets that leave headroom for InnoDB, the server layer, the operating system, and concurrent queries.

Analytical replica​

A dedicated DuckDB node can consume row-based binlogs from an InnoDB primary. This separates analytical resource usage from latency-sensitive transactional traffic.

DuckDB does not expose the MySQL two-phase commit interface. AliSQL therefore supplies a dedicated replay, commit, and recovery protocol. Batch apply can improve throughput for workloads composed of many small source transactions.

Performance reference​

The published TPC-H SF100 reference uses a 32-vCPU, 128 GB ECS instance with a 500 GB ESSD PL1 disk. Several completed queries are reported as more than 200× faster than InnoDB in that specific environment.

Treat benchmark results as directional

The source material does not record every variable needed for reproducibility, including all cache state and run-count details. Timeout and out-of-memory results are censored outcomes. Do not present the aggregate as a universal speedup or performance guarantee.

Operational boundaries​

  • Keep latency-sensitive transactional writes on InnoDB.
  • MySQL syntax compatibility is broad but not complete.
  • Enable duckdb_require_primary_key for replicated analytical tables.
  • DuckDB does not physically enforce MySQL primary and unique indexes; source data must preserve uniqueness.
  • Use the supported Row-based Binlog replication path rather than bypassing AliSQL recovery logic.
  • duckdb_use_direct_io is experimental and is not recommended for production.

Primary references​