I built stanchion to scratch my own itch. I have personal applications running on SQLite where I want to store log and metric data (basically forever) but don't want to add complexity by bringing in a new database or whole separate server. Some of these applications are running on a Raspberry Pi, where storage and compute are limited, but I still want data warehouse like capabilities.
I envision stanchion being used in similar scenarios: on phones and resource-limited devices in applications that are already using SQLite. I know that there are alternatives like DuckDB (which is very cool), but I want stanchion to be "good enough" that it is useful without having to add whole new database technology.
If you think stanchion may be a good fit for your use case and you are interested in contributing, please test it and provide feedback by opening issues for any bugs, difficulties, or missing features you would need! Ideas are also welcome in this thread or as a github issue. Of course stars are always appreciated as well. The CONTRIBUTING doc in the repository has more details.
- Dan
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.
https://duckdb.org/2024/01/26/multi-database-support-in-duck...
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.
So i would think we'd see similar speedups for sqlite even without copying any data
EDIT: nvm, the github stats seem to be completely wrong? It shows 96% C to me...
Found it, I believe this is the result of including code from SQLite in the repo
SQLite is limited to its own format but can write.
So very different use cases.
The column-oriented data is stored in large BLOBs inside of regular SQLite tables. It uses the SQLite incremental BLOB I/O API [0] to incrementally read/write data in a column oriented way.
However, this project (and other SQLite extensions) will eventually hit a limit with SQLite's virtual table API. When you create a virtual table, you can perform a number of optimizations on queries. For examples, SQLite will tell your virtual table implementation the WHERE clauses that appear on the virtual table, any ORDER BYs, which columns are SELECT'ed, and other limited information. This allows extension developers to do things like predicate + projection pushdowns to make queries faster.
Unfortunately, it doesn't offer many ways to make analytical queries faster. For example, no matter what you do, a `SELECT COUNT(*) FROM my_vtab` will always iterate through every single row in your virtual table to determine a count. There's no "shortcut" to provide top-level counts. Same with other aggregate functions like SUM() or AVERAGE(), SQLite will perform full scans and do calculations themselves.
So for this project, while column-oriented datasets could make analytical queries like that much faster, the SQLite API does limit you quite a bunch. I'm sure there are workarounds around this (by custom UDFs or exposing other query systems), but would be hefty to add.
That being said, I still love this project! Really would love to see if there's any size benefit to this, and will definitely contribute more when I get a chance. Great job Dan!
You are right that there are limitations. The sqlite virtual table API is very clearly designed with row-oriented access in mind. However, there are still ways that stanchion makes analytical queries faster: lazily loading segments (which are the BLOBs that contain the actual values) so data that is not accessed is skipped, using encodings like bit packing to reduce the size of data that needs to be traversed on disk, and allowing users to specify a clustered index to ensure records are ordered in a way that allows querying to minimize data access.
One area that I want to explore more deeply is the xFindFunction callback in the virtual table API. It allows the a virtual table to "overload" a function with its own implementation. I believe there are some opportunities to work around some of the limitations you are describing, but I'm not even sure at this point if they can apply to aggregate functions.
This is all theoretical until there are solid benchmarks, which is something that I want to add in the near term. And if you know of any workarounds to the limitations that you think may be useful, I am all ears!
Very interested to see how xFindFunction works for you. One limitation I've found is that you don't know if a user uses a xFindFunction inside of xBestIndex (ie at query time), unless 1) it's part of a WHERE clause and 2) only two arguments are provided, the first being a column value and the 2nd any literal. I've found this limiting in the past, only having 1 argument to work with in that narrow case. But I'm sure there's clever tricks there!
One trick I've noticed: You can kindof detect a COUNT(*) with the `colUsed` field in xBestIndex. In that case, `colUsed` will be 0 (ie 0 columns are requested), so you can use that as a signal to just iterate over N times instead of accessing the underlying data. Still slow, but you can probably do something like ~1 million/sec, but better than accessing the data that many times!
[0] https://www.sqlite.org/vtab.html#order_by_and_orderbyconsume...
this is similar to how citus and hydra columnar engines for postgres work - it seems to be a fairly successful use-case.
That said, I just googled it and it turns out I'm being a bit dramatic— it's actually not super hard to dynamically link Python to a custom-built sqlite: https://charlesleifer.com/blog/compiling-sqlite-for-use-with...
Popular curated extension collections like sqlean (https://github.com/nalgeon/sqlean) seem like they'll have a shelf life of many years.
A couple of questions:
* I’m curious what the difficulties were in the implementation. I suspect it is quite a challenge to implement this support in the current SQLite architecture, and would curious to know which parts were tricky and any design trade-off you were faced with.
* Aside from ease-of-use (install extension, no need for a separate analytical database system), I wonder if there are additional benefits users can anticipate resulting from a single system architecture vs running an embedded OLAP store like DuckDB or clickhouse-local / chdb side-by-side with SQLite? Do you anticipate performance or resource efficiency gains, for instance?
* I am also curious, what the main difficulty with bringing in a separate analytical database is, assuming it natively integrates with SQLite. I may be biased, but I doubt anything can approach the performance of native column-oriented systems, so I'm curious what the tipping point might be for using this extension vs using an embedded OLAP store in practice.
Btw, would love for you or someone in the community to benchmark Stanchion in ClickBench and submit results! (https://github.com/ClickHouse/ClickBench/)
Disclaimer: I work on ClickHouse.
* SQLite has rock solid support for transactionally persisting data to disk, which is a difficult and complex thing that stanchion gets almost for free. But the downside is fitting the architecture of stanchion into sqlite's way of doing things. So that is a long way of saying yes, the biggest difficulty was first learning how sqlite works and the implementing features to work with sqlite.
* It's a good question and certainly an area worth exploring. I don't know the answer, but doing, for instance, a comparison of power consumption and performance on a phone for the same use case between sqlite+stanchion and chdb running side-by-side with sqlite would be very interesting. To spitball some ideas of areas that may benefit stanchion: caching (both code and data, application caching and hardware caching), data sharing (no need for decoding and re-encoding either within the host application or between databases), and unified transactions (this one is a stretch). As you mention, chDB (and also DuckDB) benefit from having an architecture designed for analytics.
* As mentioned elsewhere in these comments, the sqlite virtual table system does have some limitations, so I think you're right when it comes to query performance. However, those limitations are limited to the way sqlite queries data in virtual tables, so I think stanchion can be competitive on data size on disk and potentially on insert performance.
I do plan to run and publish some benchmarks of stanchion against chDB and DuckDB in the near term. So far, I haven't focused on performance with stanchion, but that will be more of a focus going forward. Plus it's good to measure first and use that to track improvements. Stay tuned!
1: https://cldellow.com/2018/06/22/sqlite-parquet-vtable.html
I don’t really see a use case for your product based on the pitch page, just a lot of marketing speak that (to an engineer) seems like an apples and oranges comparison (eg. You’re running a local db, could be in memory even, and benchmarking it against Postgres, which is an incredibly flexible and comprehensive piece of software with multitenancy, transactions, replication, schema management, query planning, and a full on webserver? How is that the same product? Why not benchmark this against Excel? Cuz I’ve already got excel installed and I can open a CSV and grep for a string and count the matches in like 5 seconds. That seems to be what you’re competing against.
If you open source the code and make it a general purpose library (and not windows only) and market it toward developers, you might get some more interest.
It is not just an in-memory DB. All its data is persisted on disk and I made sure whenever I did a benchmark on any query; that I was doing an apples-to-apples comparison.
Even though the beta consists of a Windows executable; there is nothing Windows specific about it. It is built to be cross-platform and it has been tested on Linux. With my small team, we just maintain a single platform at the moment for simplicity sake.
We have not yet open-sourced the code as you pointed out; but we have considered it. But if everyone just wants to dismiss it out of hand because it fails to check some box and just focuses on what it can't do, instead of what it does well; then there might not be any point in doing so.
SQLite does not seem to care about the bogus characters so I would be really interested in using Stanchion and SQLite to see if it speeds up my exploration.
Also, I do not know how I have never known about SQLite extensions before now. I feel like I just found out that Narwhals are actually real.
https://github.com/sqlite/sqlite/tree/master/ext/lsm1
https://www.charlesleifer.com/blog/lsm-key-value-storage-in-...
* custom functions, including aggregate functions, window functions, and scalar functions: https://sqlite.org/appfunc.html
* virtual tables: https://sqlite.org/appfunc.html (This is how stanchion and other extensions like FTS and sqlite-vss are implemented)
* table valued functions (also implemented through the virtual table mechanism)
* virtual file systems: https://www.sqlite.org/vfs.html
It's really impressive how extensible SQLite is, and it's the power of that extensibility that makes stanchion possible.
[1] https://github.com/dgllghr/stanchion#data-storage-internals
My understanding is that Arrow has really focused on an in-memory format. It is binary, so it can be written to disk, but they are seemingly just scratching the surface on compression. Compression is a big advantage of columnar storage because really cool compression schemes like bit packing, run-length encoding, dictionary compression, etc. can be used to significantly reduce the size of data on disk.
Arrow interop especially is a great idea, though, regardless of the storage format. And if they are developing a storage-friendly version of arrow, I would certainly consider it.
Any timeline for that?
I realise some people view public domain as legally problematic. I think the best answer for that is public-domain equivalent licenses such as 0BSD [0] or MIT-0 [1] – technically still copyrighted, but effectively not, since they let you do anything under zero conditions. (There are other, possibly more well-known options such as Unlicense or WTFPL or CC0; however, those tend to upset lawyers more than 0BSD and MIT-0 do.)
Of course, it is your work, and you are free to license it however you like. Still, some potential users are going to be put off by the licensing.
If anything I'd ask for an exception to allow statically linking unmodified builds (e.g. my use case, WASM, requires static linking all extensions).
Or a dual MPL+LGPL license.
I do have one qualm, though. SQLite is written in C, and if I were writing plugins for it, I would seriously consider myself bound to using C for them, regardless of the merits of the language. I can easily imagine myself trying to build an extended SQLite library where one plugin is written in Zig, another in Rust, and perhaps a third in Hare. A fourth plugin might be written in C, but be built using Meson. Yet another plugin written in C is built with Bazel. And here I come, writing my plugin in Chibi Scheme!
Eventually, the toolchain needed to build the library and all its plugins overflows.
I would strongly recommend that people who write plugins for programs or libraries give serious consideration to using the programming language(s) and build system(s) of the parent software.
