r/SQL • u/ShannonBase • 7d ago

MySQL Why heatwave sets this limitation?

/r/mysql/comments/1x18rf9/why_heatwave_sets_this_limitation/
2 Upvotes

75% Upvoted

3 comments sorted by

1

u/Liubov_Skky 7d ago

Heatwave isn't a single db, it's a bolted-on in-memory cluster running next to innodb. if you open a transaction with uncommitted inserts, the heatwave nodes can't see them yet. trying to sync uncommitted states to a distributed olap node in real-time is a consistency nightmare. So mysql just bails) drops the offload, and runs the query locally on innodb.

it needs autocommit=1 just to guarantee the data is fully flushed to disk. if you want htap without fighting transaction limits, look at mariadb with the duckdb engine. it runs analytics natively inside the same server, no weird cloud offloading or sync problems

1

u/ShannonBase 6d ago

Does MariaDB with DuckDB support long-running transaction statements? The OLAP side can observe uncommitted OLTP changes within a single transaction. This poses a data freshness challenge. Under eventual consistency, using committed data is acceptable; however, if real-time fresh changes must be visible, what can MariaDB do?

1

u/Liubov_Skky 6d ago

No, of course, no database is going to magically let a columnar olap engine read uncommitted memory state from an oltp engine without completely destroying performance, mariadb doesn't do that either. But with duckdb you can just do a standard cross-engine join between your hot innodb tables (for the fresh, uncommitted stuff) and your massive duckdb tables (for the heavy historical aggregations) in a single query.

and so you don't get blocked by arbitrary autocommit rules, and you don't have to ship your data over the network to a separate cloud cluster just to run a dashboard.