When MySQL meets DuckDB: Best practices for columnar engines on MySQL

This presentation introduces AliSQL’s evolution through open-source collaboration and focuses on the architecture, core technologies, and real-world applications of AliSQL DuckDB, an integrated database solution that combines MySQL-compatible transaction processing with high-performance analytical capabilities.

The presentation begins with an overview of AliSQL and its long-term commitment to the open-source database ecosystem. Built on top of open-source MySQL, AliSQL continues to benefit from community standards and innovation while contributing production-validated features, bug fixes, and patches back to the MySQL, MariaDB, and DuckDB communities. This open-source strategy reflects AliSQL’s development philosophy: originating from open source, evolving through large-scale production scenarios, and giving proven innovations back to the broader ecosystem.

The main part of the presentation explains why traditional database architectures are evolving from separate OLTP and OLAP systems toward integrated HTAP platforms. Conventional architectures usually require transactional data to be transferred through ETL pipelines into a separate analytical database or data warehouse. Such architectures introduce additional latency, operational complexity, infrastructure cost, and data consistency challenges. AliSQL DuckDB addresses these problems by integrating the InnoDB row-oriented transactional engine and the DuckDB column-oriented analytical engine within a unified MySQL-compatible system.

The presentation provides a detailed explanation of the AliSQL DuckDB architecture. Transactional queries continue to run on InnoDB, while analytical queries can be automatically routed to DuckDB through intelligent workload routing. Data and schema changes are synchronized through native MySQL Binlog replication, allowing analytical data to remain continuously updated without requiring an external data synchronization platform. This architecture supports multiple deployment models, including DuckDB analytical primary instances, analytical read-only instances, standalone analytical clusters, cascading replication, and multi-source data aggregation.

A major focus of the presentation is MySQL compatibility. AliSQL DuckDB is designed to preserve the existing MySQL user experience, including the MySQL protocol, client ecosystem, SQL syntax, functions, data types, implicit type conversions, and result-set behavior. The system uses the MySQL parser to generate an abstract syntax tree and then performs SQL normalization and dialect conversion before executing the query in DuckDB. For complex MySQL functions, AliSQL registers and reuses native MySQL implementations as DuckDB user-defined functions. Through parser extensions, function implementations, compatibility fixes, and large-scale SQL testing, the platform achieves compatibility with approximately 99.99% of tested MySQL statements, allowing most existing applications and analytical SQL workloads to migrate without modification.

The presentation also examines the performance foundations of AliSQL DuckDB. DuckDB stores data in a column-oriented format and executes queries using a vectorized execution engine. Data is processed in batches through DataChunks, reducing function-call overhead, improving CPU cache locality, and enabling SIMD acceleration. Query execution is divided into pipelines that can be scheduled across multiple threads, providing efficient intra-query parallelism without excessive thread communication. In addition, DuckDB’s optimizer uses accurate column-level statistics, column pruning, predicate pushdown, late materialization, and advanced join-order optimization algorithms to improve complex multi-table analytical queries.

Several data synchronization and ingestion optimizations are discussed in detail. AliSQL DuckDB accumulates multiple small replication transactions into larger batches, significantly reducing commit overhead, WAL flush frequency, and interaction with the storage engine. The system maintains GTID information and Relay Log positions to preserve replication correctness and support crash recovery. For schema changes, AliSQL DuckDB distinguishes between directly supported instant DDL operations and copy-based DDL operations that require rebuilding a table. Bulk import paths use append-oriented processing, transaction batching, RowGroup linking, and reduced WAL overhead to achieve high ingestion throughput. In demonstrated scenarios, full data imports can reach approximately two million rows per second, while incremental synchronization can exceed 300,000 rows per second.

The presentation further explains how AliSQL DuckDB reduces storage cost through adaptive column compression. During checkpoint processing, the engine evaluates multiple compression algorithms for each column and selects an appropriate encoding based on the data distribution and type. Supported techniques include run-length encoding, bit packing, dictionary and FSST compression, floating-point compression, constant encoding, and bitmap-based representations. In TPC-H SF100 tests, the DuckDB-based storage format reduced the data footprint from approximately 168 GB in InnoDB to around 26 GB, representing a compression ratio of approximately 6.5 times and an 84% reduction in storage usage.

Production readiness is another important topic. The presentation covers crash-safe data consistency, backup and recovery capabilities, replication position management, high availability, and integration with the broader Alibaba Cloud RDS disaster recovery architecture. These capabilities are essential for transforming an embedded analytical engine into a production-grade cloud database service that can support business-critical workloads.

Finally, the presentation introduces several representative application scenarios. In HTAP acceleration scenarios, applications can use InnoDB for high-concurrency transactions and DuckDB for complex analytical queries within the same database service. In data aggregation scenarios, data from dozens of MySQL instances can be consolidated into a single AliSQL DuckDB instance for near-real-time multi-table analysis, reducing query latency and operational overhead. In data archiving scenarios, historical data can be moved to compressed columnar storage while remaining accessible through familiar MySQL protocols and SQL syntax. The presentation also explores observability and auditing scenarios, such as collecting traces, token consumption, model usage, and security events from AI agent applications through an AliSQL DuckDB-based monitoring platform.

Overall, this presentation demonstrates how AliSQL combines open-source innovation, MySQL ecosystem compatibility, columnar storage, vectorized execution, optimized replication, adaptive compression, and cloud-native reliability to provide an integrated transactional and analytical database platform. It highlights both the engineering challenges of deeply integrating MySQL and DuckDB and the practical business value delivered through higher analytical performance, simplified architectures, lower storage costs, reduced operational complexity, and faster access to real-time data.