Sorry you hit that! This is actually already working on version 1.2.2. Could you install that version? That should get you going for the moment! We will dig into what you ran into.
IN list filter predicate pushdown is much improved in DuckDB 1.2, coming in about a week! I am not sure if it applies to Arrow yet or not. Disclaimer: I work at MotherDuck and DuckDB Labs
Hello! I would recommend trying out DuckDB's SQLite attach feature! You can read or write data, and even make schema changes, all with DuckDB's engine and syntax. The storage then uses SQLite, which is row oriented!
Howdy! I work at MotherDuck and DuckDB Labs (part time as a blogger). At MotherDuck, we have both client side and server side compute! So the initial reduction from PB/TB to GB/MB can happen server side, and the results can be sliced and diced at top speed in your browser!
Your blog does a great job contrasting the two use cases. I don't think too much has changed on your main use case, however here are a few ideas to test out!
DuckDB can read SQLite files now! So if you like DuckDB syntax or query optimization, but want to use the SQLite format / indexes, that may work well.
Since DuckDB is columnar (and compressed), it frequently needs to read a big chunk of rows (~100K) just to get 1 row out and decompressed. Mind trying to store your data uncompressed? Might help in your case! (PRAGMA force_compression='uncompressed')
We heard your feedback! Backward compatibility was just implemented! Version 0.9 is actually fully readable by 0.10. With version 1.0 coming in a few months, this will be readable for several years' worth of version updates.
Great question! For analytical queries, DuckDB is ~100x faster than SQLite. It also has great connectivity with Parquet, Pandas, Arrow, Postgres, and more! Plus, the syntax follows Postgres closely, so you have strong data typing and true timestamps, etc.
All that said - SQLite is still faster for transactions. Now DuckDB can actually read and write from/to SQLite, so you can use SQLite for OLTP and DuckDB for OLAP.
Starting in this release, the DuckDB team invested significantly in adding memory safety throughout catalog operations. There is more on the roadmap, but I would expect this release and all following to have improved stability!
That said, at my primary company, we have used it in production for years now with great success!