hckrnws

The DuckDB-as-a-storage-engine approach is clever because it lets you keep your existing MySQL connections, tooling, and replication topology while routing analytical queries to a columnar engine underneath. That's a much easier sell operationally than standing up a separate analytics database and building a sync pipeline. The real question is how they handle consistency between the InnoDB and DuckDB copies of the same data, that's where every hybrid OLTP/OLAP system either shines or quietly loses rows.

On this page, we introduce how to implement a read-only Columnar Store (DuckDB) node leveraging the MySQL binlog mechanism. https://github.com/alibaba/AliSQL/blob/master/wiki/duckdb/du... In this implementation, we have performed extensive optimizations for binlog batch transmission, write operations, and more.

Nice question! We did spend a lot of time considering the issue of data consistency.

In the MySQL replication, GTID is crucial for ensuring that no transaction is missed or replayed repeatedly. We handle this in two scenarios (depending on whether binlog is enabled):

    - log_bin is OFF: We ensure that transaction in DuckDB are committed before the GTID is written to disk (in the mysql.gtid_executed table). Furthermore, after a crash recovery, we perform idempotent writes to DuckDB for a period of time (the principle is similar to upsert or delete+insert). Therefore, at any given moment after a crash recovery, we can guarantee that the data in DuckDB is consistent with the primary database.
    - log_bin is ON: Unlike the previous scenario, we no longer rely on the `mysql.gtid_executed` table; we directly use the Binlog for GTID persistence. However, a new problem arises: Binlog persistence occurs before the Storage Engine commits. Therefore, we created a table in DuckDB to record the valid Binlog position. If the DuckDB transaction fails to commit, the Binlog will be truncated to the last valid position. This ensures that the data in DuckDB is consistent with the contents of the Binlog.

How I see SQL databases evolving over the next 10 years:

    1. integrate an off the shelf OLAP engine
       forward OLAP queries to it
       deal with continued issues keeping the two datasets in sync
    2. rebase OLTP and OLAP engines to use a unified storage layer
       storage layer supports both page-aligned row-oriented files and column-oriented files and remote files
       still have data and semantic inconsistencies due to running two engines
    3. merge the engines
       policy to automatically archive old records to a compressed column-oriented file format
       option to move archived record files to remote object storage, fetch on demand
       queries seamlessly integrate data from freshly updated records and archived records
       only noticeable difference is queries for very old records seem to take a few seconds longer to get the results back

Curious how it stacks up to pg_duckdb. (pg_duckdb seems pretty clean, due to Postres' powerful extension mechanisms)

[flagged]

I feel this analysis is unfair to PostgreSQL. PG is highly extensible, allowing you to extend write-ahead logs, transaction subsystem, foreign data wrappers (FDW), indexes, types, replication, others.

I understand that MySQL follows a specific pluggable storage architecture. I also understand that the direct equivalent in PG appears to be table access methods (TAM). However, you don't need to use TAM to build this - I'd argue FDWs are much more suitable.

Also, I think this design assumes that you'd swap PG's storage engine and replicate data to DuckDB through logical replication. The explanation then notes deficiencies in PG's logical replication.

I don't think this is the only possible design. pg_lake provides a solid open source implementation on how else you could build this solution, if you're familiar with PG: https://github.com/Snowflake-Labs/pg_lake

All up, I feel this explanation is written from a MySQL-first perspective. "We built this valuable solution for MySQL. We're very familiar with MySQL's internals and we don't think those internals hold for PostgreSQL."

I agree with the solution's value and how it integrates with MySQL. I just think someone knowledgeable about PostgreSQL would have built things in a different way.

Actually, that’s not the case. I also support PostgreSQL products in my professional work. However, specifically regarding this issue—as I mentioned in my article—it is simply easier to integrate DuckDB by leveraging MySQL's binlog and its pluggable storage engine architecture.

Thanks for providing this from PG perspective. Also wonder if storage engine such as OrioleDB would be better suited for FDWs to handle consistency between copies of the same data between DuckDB?

The only concern I have about OrioleDB is how long it's taking to get to GA.

Anyone using it in prod even with the beta status?

It looks like you pasted the output from LLM verbatim, the first line is a bit confusing. It's a pity because the answer itself is meaningful.

I think we can give them a pass for this one. I think they are one of the developers and I suspect English may not be their first language, so they asked an LLM to help translate for them. If they don't understand English, I can see why they might have accidentally included that first line.

I guess they don't speak English at all, but they could clearly improve their prompting skills :)

So you pasted someone's comment in an LLM and posted the output here. Cool. Not really.

He's Chinese and if you had looked into his comment history you'd know this is not someone who uses LLMs for karma farming and looking at his blog he has a long history of posting about database topics going back before there was GPT.

Should I ever participate in a Chinese speaking forum, I'd certainly use an LLM for translation as well.

Looks to me like they're using an LLM for _translation_, not for generating a response. The model output even says "Here's the _translation_" (emphasis mine).

Does this feed DuckDb continuously data from transactional workloads, akin to what SAP hana does? If so that would be huge - people spend lots of time trying to stitch transactional data to warehouses using Kafka/debezium.

BTW, Would be great to hear apavlo’s opinion on this.

HTAP is here! It seems like these hybrid databases are slowly gaining adoption which is really cool to see.

The most interesting part of this is the improvements to transaction handling that it seems they've made in https://github.com/alibaba/AliSQL/blob/master/wiki/duckdb/du... (its also a good high level breakdown of MySQL internals too). Ensuring that the sync between the primary tables and the analytical ones are fast and most importantly, transactional, is awesome to see.

I don't think this is meaningfully HTAP, it's gluing together two completely different databases under a single interface. As far as I can tell, it doesn't provide transactional or consistency guarantees different than what you'd get with something like Materialize.

This isn't new either, people have been building OLAP storage engines into MySQL/Postgres for years, e.g., pg_ducklake and timescale.

Genuinely curious in what situation would you actually want transactional consistency in the same session as you are doing analytical or vector retrieval style use cases?

I might make the argument that paying the tax of delivering what you're arguing for has so many significant downsides in the end you'd have something you wouldn't really want anyway

having an embedded column database for analytics in your traditional db is a massive win for productivity + operations simplicity.

at the moment I use PG + Tiger Data - couldn't find a mysql equivalent

so this as one.

Mariadb has a columnar engine already (though I did not use it myself) https://mariadb.com/docs/analytics/mariadb-columnstore/colum... and is mostly mysql compatible.

For about a year releases include a vector storage type, so it will be interesting to see it compared in performance with what Alibaba did.

Just wanted to plug that out. Given how often Postgres is plugged on HN, I think people ignore how versatile mariadb is.

MariaDB also has MariaDB Exa, which is a real HTAP solution using Exasol for the analytical workloads: https://mariadb.com/products/exa/

This ColumnStore is very simple and just do table scans sequentially on every query. It doesn't support indexes and unique constraints. It is almost an append-only serialization file format, but with some columnar concepts.

Clickhouse supports MySQL protocol natively, and can also wrap/import MySQL tables. Okay so you need two connections but it works pretty well.

It even supported running as a MySQL Replica at some point.

"MaterializedMySQL"

Sadly that feature seems to have been thrown out, probably due to complexity.

https://github.com/ClickHouse/ClickHouse/discussions/44887#d...

https://www.percona.com/blog/complete-walkthrough-mysql-to-c...

https://github.com/ClickHouse/ClickHouse/pull/73879

Mostly due to support, at least on the PG side.

They bought peerdb and offer it as clickhouse pipes so I suspect the incentive to support that feature is pretty low

Can tiger data be used just as a simple column store?

All I want is effectively what clickhouse does in PG. I have a single table that I need fast counts on and clickhouse can do the counts fast but I have to go through the entire sync/replication to do that.

A quick scan of TimeSeries always seemed like it was really only best setup for that and to use it another way would be a bit of a struggle.

in a way -- materialized views --

but Tiger Data is more optimized for TimeSeries data - https://www.tigerdata.com/docs/use-timescale/latest/hypercor...

I do wish too there was an embedded click house like db in Postgres

One option is TiDB. It has support for columnar data alongside row based data. However, it is MySQL compatible, but not based on MySQL code so not quite what you asked for.

Yes, TiDB has columnar data and also Vector support. All open source and MySQL compatible.

MariaDB has supported columnar tables for a bit https://mariadb.com/resources/blog/see-columnar-storage-for-...

I don't think MariaDB ColumnStore has any kind of advantage. It is just an append-only storage format with some columnar concepts.

https://vettabase.com/mariadb-columnstore-sql-limitations/#I...

How easy will this be to combine with https://github.com/mysql/mysql-operator for deployment?

We havn't try that before, maybe I will try to combine with mysql-operator later..

On a drive-by-glance it looks like if you had a tighter integrated version of PSQL FDW for DuckDB and Vector Storage - meets Vespa. I find it interesting they went with extending MySQL instead of FDW route on PSQL?

probably they had millions of lines of code already using mysql

the commits history looks a bit weird, 2 commits in 2022, 1 in 2024 and 2025, and 5 in 2026 (one is "First commit, Support DuckDB Engine")

Just guessing, but it probably wasn't planned as open source.

The real version control history might be full of useless internal Jira ticket references, confidential information about products, in Mandarin, not even in git... there's a thousand reasons to surface only a minimal fake git version history, hand-crafted from major releases.

Wonder how DuckDB compares here to what TiDB did using Clickhouse instead

I’m quite certain that if DuckDB had been open-sourced and reached stability around 2020, TiDB would have definitely chosen DuckDB instead of ClickHouse.

Interesting. I'd think they serve different purposes

FoundationDB Record layer doesn't get much attention here but I have found that all my use cases are satisfied by it.

And I get the benefit of resiliency and DR for free.

If you are a developing for My SQL and you are using Java/kotlin/closure/scala consider this as well.

I get the feeling that Oracle is abandonning MySQL.

Let's all hope Ali will pick it up :)

I'm fully invested on Postgres though.

[flagged]

In almost no situation is laughing at what someone says appropriate, also not here.

[flagged]

Quickly becoming my least-favorite account. If you’re going to have a schtick, have a schtick. Write your comments in and old timey voice or iambic pentameter or whatever, include a signature, ascii art, lean into being annoying.

I hope the poor devs that built this wernt subjected to the brutal 996 culture (9am-9pm, 6 days per week)