Note that DuckDB can ingest SQLite tables. If you need OLAP on your SQLite system today instead of whenever this is stable, consider pairing up DuckDB. In the simplest case if you don’t need amazing latency and have small data (<1gb), I would copy the whole table from SQLite into an in-memory or temp file DuckDB, do your OLAP queries, then throw it away. For larger datasets, you can incrementally replicate the SQLite table into DuckDB by adding a logical clock column or something to the SQLite table, and then copy rows where logical_clock>last_duckdb_change before running your next DuckDB query.
We’re currently doing a bake-off between a few databases for small datasets (<10m rows) with dynamic schemas, and have pretty comparable read latencies between DuckDB and SQLite for our workflow, so you might be able to get away with switching wholesale from SQLite to DuckDB if you don’t do a lot of update.
DuckDB is great! But row-oriented storage is also great for many use cases. My goal is to provide a way for people to have access to the benefits of column-oriented storage without leaving behind the benefits of OLTP. Many people are already using sqlite and might not have the time/energy/budget to make a full switch to a new database. I also think there should be many options for embedded columnar storage, and right now there really aren't.
Absolutely, it's cool to see a real extension in this space, I didn't mean to diminish your work at all. If it were a few years more mature I'd try it out. I just happen to be messing around with DuckDB today for this exact use-case, but your thing is new, so I can't adopt it.
Unsure what the parent means by "ingest" SQLite tables (although i believe you can use COPY with sqlite tables in DuckDB), but you can interact with sqlite tables in DuckDB using the extension to attach to a sqlite db.
Yeah by ingest I mean COPY FROM or attach via the SQLite extension. Then you can `INSERT INTO duckdbTable (select * from sqliteDb.sqliteTable)` or similar. I don’t think duckdb aggregate on native SQLite format will have any advantage; I think the native duckdb format would be necessary to see a big advantage over doing the aggregate in SQLite directly based on my reading of the duckdb extension code.
We’re currently doing a bake-off between a few databases for small datasets (<10m rows) with dynamic schemas, and have pretty comparable read latencies between DuckDB and SQLite for our workflow, so you might be able to get away with switching wholesale from SQLite to DuckDB if you don’t do a lot of update.