3 ms·
A lot of recent tooling around SQLite has opened up some new possibilities. The biggest one I can think of is Litestream which has allowed SQLite to have distri
by iknownothow 4y ago
A lot of recent tooling around SQLite has opened up some new possibilities. The biggest one I can think of is Litestream which has allowed SQLite to have distributed reads. This makes SQLite a strong contender for serverless and extremely cheap OLAP. There have been a lot of posts about serverless Postgres as well lately in the form of neon.tech.
If SQLite had better performance for OLAP queries, there is no way I wouldn't explore it as an option for work because it's so darn easy to use. The operational or hosting or cloud costs of other OLAP database is insanely high in comparison.
P.S. It's my personal opinion and experience that most companies have only about 1TB or less of analytics data. Large OLAP databases are just a waste for such small data but currently apart from DuckDB there aren't any cheap competitors for these small use cases. Postgres and SQLite are too slow (without some additional engineering).
- chrisjc 4y agoI've been thinking about very similar needs, but perhaps accomplished in a somewhat different way. It would be really neat if SQLite or DuckDB implemented a way to create (register) read-only external tables (like Apache Iceberg). That way your large OLAP database (Snowflake, etc) could manage (DDL & CRUD) the external table, while SQLite and DuckDB would consume from it and automatically get all of the updates/deltas to the external table. clicks --> Iceberg bits --> Snowflake/MPP <--> table --> DuckDB pieces --> on S3 Snowflake could take care of all the analytical heavy lifting continually preparing/updating/unloading data to the Iceberg table in an analytics ready/friendly form (lags, leads, etc built into data). DuckDB could watch for changes on the Iceberg table (manifest) and update its internal representation of the external table as it changes. DuckDB can already read parquet files out on S3 so it should be somewhat trivial to implement? DuckDB could then handle all of the lightweight slicing/dicing of the analytics data; projections, counts, pagination, etc Here's an example of Snowflake managing an Iceberg table: https://www.youtube.com/watch?v=Kz5cWY_vRwU https://www.youtube.com/watch?v=Kz5cWY_vRwU
- 1egg0myegg0 4y agoDuckDB can actually read SQLite or Postgres directly! In the SQLite case, something like Litestream plus DuckDB could work really well! Also, with Pyarrow's help, DuckDB can already do this with Delta tables! https://github.com/duckdblabs/sqlite_scanner https://github.com/duckdblabs/sqlite_scanner https://github.com/duckdblabs/postgresscanner https://github.com/duckdblabs/postgresscanner