Docs / design notes
On engine boundaries, transaction boundaries, and what a bolted-on OLAP engine structurally cannot do.
Every few weeks someone asks me the same question, and it is a fair one:
DuckDB is fast, permissively licensed, embeddable, and already has a vectorized executor, a compressed columnar format, morsel-driven parallelism, and a mature optimizer. Alibaba shipped it inside AliSQL. There is
pg_duckdbfor Postgres and MyDuck for MySQL. Why are you writing an entire columnar engine, an entire vectorized executor, and an entire optimizer integration by hand?
It is the highest-leverage question anyone can ask about ShannonBase, so it deserves a real answer rather than a slogan. This post is that answer. I will make the case for the DuckDB route as strongly as I can, then explain the specific architectural property it cannot deliver — and why that property is the whole product for us.
Let me not strawman it.
If your goal is “make analytical queries on MySQL data 100x faster with the smallest possible engineering team,” embedding DuckDB is close to the optimal move. You inherit, for free:
Alibaba’s RDS MySQL DuckDB analytical read-only instance is the most serious production instance of this design. Their published architecture is clean: the instance is a read-only replica that follows the primary over binlog; InnoDB retains only metadata and system tables while all user data lives in DuckDB; a query is parsed by the MySQL server layer, handed to DuckDB for execution, and the result set is converted back into MySQL wire protocol. They report roughly 200x speedup on TPC-H SF100 with storage footprint around 20% of the row-store primary.
Those are excellent numbers, and they are excellent numbers achieved by a small fraction of the engineering effort that building an engine from scratch requires. If ShannonBase’s mission were “MySQL, but TPC-H is fast,” I would have taken this route, and I would tell you to take it too.
That is not the mission. The rest of this post is about why.
Integrating DuckDB into MySQL does not give you one database with two storage layouts. It gives you two databases that agree to cooperate at a well-defined boundary. DuckDB brings its own storage manager, its own catalog, its own transaction manager and MVCC implementation, its own type system, its own parser, and its own optimizer. Every one of those is now duplicated in your process.
Duplication is not automatically bad. It becomes bad in specific, predictable places, and every team that has walked this path has hit the same four.
DuckDB does not implement two-phase commit. This is not an oversight — it is a reasonable design choice for an embedded analytics engine — but it means you cannot use XA to make binlog GTID position and DuckDB’s committed state atomically consistent, and you cannot make a DDL statement atomic across InnoDB’s data dictionary and DuckDB’s catalog.
Alibaba’s engineers describe exactly how they worked around it: they reworked the commit and binlog-apply paths to make replay idempotent, so a crash-restart converges to a consistent state by replaying rather than by rolling back a prepared transaction.
That is solid engineering. It is also a downgrade in the strength of your correctness argument. With 2PC, consistency is a property you prove once at the protocol level. With idempotent replay, consistency is a property you must maintain across every interleaving of DML replay, DDL replay, checkpoint, and crash — forever, for every new feature. The invariant moves from “the protocol guarantees it” to “we tested it.”
There is a second consequence. DuckDB is optimized for large transactions and performs poorly on high-frequency small ones, which produces severe replication lag under an OLTP write pattern. The published fix is batched replay: accumulate many binlog transactions and apply them as one. It works — they report keeping up with sysbench load — but note what batching means semantically. Visibility of committed TP data on the AP side is now deliberately deferred to a batch boundary. You have traded freshness for throughput, and you had no choice, because the engine underneath you was never designed for a stream of 200-byte updates.
MySQL’s observable semantics are not the SQL standard. They are the
SQL standard plus thirty years of specific behavior:
utf8mb4_0900_ai_ci collation ordering, DECIMAL
arithmetic with MySQL’s exact scale-propagation rules, zero dates and
NO_ZERO_IN_DATE, ENUM/SET ordinal
comparison, implicit string-to-number coercion, sql_mode
variations, the ONLY_FULL_GROUP_BY boundary cases, spatial
reference system handling, JSON path semantics.
To run MySQL SQL on DuckDB, all of that must be re-implemented in DuckDB’s dialect. The AliSQL team extended DuckDB’s parser to accept MySQL-specific syntax and rewrote or added a large number of functions to match MySQL behavior, then validated with roughly 170,000 SQL statements in an automated compatibility harness, reporting about 99% compatibility.
99% is a genuinely impressive number. It is also, in an architecture with no fallback path, a hard 99%. The remaining 1% is not “slower” — it is “does not run.” And compatibility here is a treadmill, not a milestone: every MySQL point release and every new DuckDB version can move a semantic edge in the translation layer.
Contrast the secondary-engine model. In ShannonBase, parsing, type
resolution, collation, sql_mode, and function semantics all
happen in the MySQL server layer, exactly once, using MySQL’s own
code. Rapid never sees a LIKE expression — it sees an
already-resolved Item tree with MySQL’s semantics baked
in. Compatibility is not something I test my way toward; it is a structural
property.
I want to be honest about the flip side: the compatibility cost
does not disappear, it relocates. It moves from “semantic
translation” to “engine integration,” and it shows up as a
different class of bug. Real examples from my own commit history:
ENUM predicate pushdown breaking because the code consulted
field->type() instead of field->real_type();
filesort returning wrong rows on wide varlen columns because
m_last_returned_rowid wasn’t assigned in
populate_row_from_chunks; the hypergraph optimizer wrapping an
already-complete aggregation in a spurious two-phase plan; a
STREAM AccessPath falling through to the default branch in
translate_access_path. None of these are semantic translation
bugs. They are all “my engine did not honor a contract the server
layer assumed.” Different failure mode, comparable total cost.
This is the difference that matters most for query capability.
In the DuckDB-as-replacement-engine design, the MySQL optimizer is essentially bypassed. A statement is routed to DuckDB and DuckDB plans it end to end. That gives you DuckDB’s excellent plans — and an all-or-nothing execution model. A query either runs entirely on the columnar engine or it does not run there at all. There is no per-operator decision, no partial offload, no fallback.
In the secondary-engine design, the columnar engine participates in a plan that the MySQL optimizer owns. The cost model can decide, for this query and increasingly for this operator, whether the row store or the column store is cheaper, and can compose them. A large scan and aggregate goes to Rapid; a point lookup on a secondary index stays in InnoDB; a query that Rapid cannot support degrades to a correct row-store plan instead of failing.
That composability is what makes the next section possible at all.
Some MySQL DDL has no DuckDB equivalent — reordering columns, for instance. AliSQL handles this with a Copy DDL mechanism: build a replacement table and swap it, parallelized across threads to cut the cost. Again: correct, pragmatic, and a permanent maintenance surface. Every DDL feature MySQL adds is a potential new divergence between two catalogs that must be kept in lockstep across an asynchronous replication channel.
Everything above is a cost argument, and cost arguments alone would not justify writing an engine. The actual justification is capability. There are three things ShannonBase is built to do that the integration architecture cannot do — not “does not do yet,” but cannot, without dismantling the boundary that made it cheap.
Rapid/IMCS is a fractured mirror maintained in the same process, fed by a dual channel: redo log application plus direct DML notification. Column store visibility is keyed on the same InnoDB transaction IDs as the row store. An analytical query executes against the same MVCC snapshot as the transactional statement that preceded it in the same session — including reading the session’s own uncommitted writes.
The replica architecture cannot offer this at any price. Its AP data lives on a different instance, behind an asynchronous channel, deliberately batched. “Write, then immediately analyze, in one transaction” is outside its universe. For a reporting workload that is fine. For an operational workload where a decision is made from an aggregate and then written back, it is not.
This is the part that made the decision for me.
ShannonBase runs ONNX Runtime and LightGBM inference inside the
kernel, and exposes vector search over an ART-indexed structure as a
first-class access path. The design goal is that an inference or an
embedding-similarity search is an operator in the execution plan, consuming
the same columnar buffers that a SUM would consume, under the
same snapshot, inside the same transaction.
Put that requirement against the replica architecture and it dissolves:
And the agent layer makes it sharper still. ShannonBase’s embedded
agent runtime (JerryScript, with an in-process SQL bridge via
execute_sql_internal) executes a ReAct loop where the model
reads, proposes, waits for human approval, and writes. That loop is only
coherent if reads and writes share a transaction manager. Bolt an OLAP
replica onto the side and the agent is reading a different database than
the one it is about to modify — which is precisely the failure mode
the whole design exists to eliminate.
If you want a one-sentence version of this post: the DuckDB integration route optimizes the analytical query; ShannonBase optimizes the transaction that contains the analytical query. Those lead to different architectures, and only one of them has room for an AI operator in the plan.
Covered above, but it compounds with the previous two. When an agent
generates SQL, “99% of statements run” is a materially worse
guarantee than it sounds, because the agent will generate the unusual 1%
— odd casts, GROUP BY shapes, date arithmetic —
far more often than a hand-written application will.
I am not going to pretend this is free. It is the most expensive decision in the project.
Writing a columnar engine means owning every bug in it. From my own
history: an off-by-one in ring buffer sequence release that livelocked
after 262,144 entries; a vectorized scan snapshot’s
VarlenReference discarded during BLOB resolution;
CU::read passing normalized length as buffer size and silently
truncating TEXT; a freelist bucket migration bug in the varlen data pool; a
lock gap in ARTIterator::find_position_ge() that released the
tree mutex before traversal; SIMD aggregate overflow in the decimal path.
DuckDB’s team fixed the equivalent class of bug years ago, and I paid
for each of mine in production incidents and debugging weeks.
There are two places where the integration route is simply better, and I would tell an honest evaluator so:
Resource isolation. A separate read-only instance gives you physical TP/AP isolation for free. My populate threads, background worker pool, and SIMD aggregation contend with OLTP for CPU and memory in the same process. I can mitigate this with CPU quotas on the populate/compact thread groups, a separate memory arena for Rapid with admission control — but the ceiling of a shared-process design is real and lower.
Dataset size. Rapid/IMCS is memory-resident. DuckDB is disk-based with strong compression and spills gracefully; SF100 on a 32-core / 128 GB box is comfortable for it. Above the memory line, the integration route wins outright today. Closing this requires cold-partition spill with zone-map skipping over existing chunk metadata, and that work is ahead of me, not behind me.
I will also say the thing that follows from all of this: racing a mature vectorized engine on pure scan-and-aggregate throughput is not a winnable long-term strategy for a small team. DuckDB’s executor will keep getting faster on a budget I do not have. If ShannonBase’s pitch were a TPC-H number, the DuckDB route would beat me on cost and eventually on performance. The pitch is not a TPC-H number.
I do not think MySQL + DuckDB is the wrong answer. I think it is the right answer to a different question. Here is the rule I would give someone choosing between them:
Choose the DuckDB integration route if your analytics are reporting-shaped — dashboards, BI, batch aggregation — and seconds-to-minutes of staleness is acceptable; if your datasets exceed memory; if you need hard TP/AP isolation; and if your compatibility surface is a fixed set of hand-written queries you can validate once.
Choose a native secondary-engine route if analytical results must be consistent with the transaction that produced them; if you need per-query or per-operator planning with a correct fallback rather than all-or-nothing routing; if arbitrary generated SQL must work with full MySQL semantics; or if you intend to put inference, embedding, or vector search into the execution plan rather than beside the database.
ShannonBase exists because the second column is our workload. The engine is expensive because the boundary I refused to accept is exactly the boundary that made the cheap version cheap.
ShannonBase is an open-source, MySQL 8.4-compatible HTAP database with native AI and agent capabilities. Technical details of the AliSQL DuckDB integration referenced here are drawn from Alibaba Cloud’s published documentation and engineering write-ups on the RDS MySQL DuckDB analytical instance.