It is strange to hear about innovation in DuckDB - I see that they are gradually re-implementing the stuff already existing in ClickHouse. Sometimes they do a better job at promoting it.
I kinda wish for a database with ClickHouse storage and DuckDB optimizer. At least my experience with ClickHouse is that MergeTree is incredibly good at what it does, but the optimizer hurts.
[1]: https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.h...
My impression of ClickHouse was that it was more like postgresql in that regard, i.e. OLAP : OLTP as ClickHouse : Postgres as DuckDB : SQLite.
clickhouse-local may have closed the gap on that though. Can you embed it in Python as a library?
https://github.com/chdb-io/chdb
pip install chdb