Comment by mkoubaa
Comment by mkoubaa 2 days ago
Seems like it's asking to be forked
Comment by mkoubaa 2 days ago
Seems like it's asking to be forked
It has been forked at least once:
The real fork is DuckDB in a way, it has SQLite compatibility and so much more.
The SQLite team also has 2 branches that address concurrency that may someday merge to trunk, but by their very nature they are quite conservative and it may never happen unless they feel it passes muster.
https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co... https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html
As to the problem that prompted the article, there's another way of addressing the problem that is kind of a kludge but is guaranteed to work in scenarios like theirs: Have each thread in the parallel scan write to it's own temporary database and then bulk import them once the scan is done.
It's easy to get hung up on having "a database" but sharding to different files by use is trivial to do.
Another thing to bear in mind with a lot of SQLite use cases is that the data is effectively read only save for occasional updates. Read only databases are a lot easier to deal with regarding locking.
DuckDB is similar as an in process SQL database, but lacking btree-style ordered indexes makes it a poor performer in key lookups and order-by / range scans if your table is any size larger than trivial.
It’s the classic OLAP (DuckDB) vs OLTP (SQLite) trade off between the two. DuckDB is very good at many things but most applications that need a traditional SQL DB will probably not perform well if you swap it over to DuckDB.
Duckdb has optional adaptive radix tree indexing (https://duckdb.org/docs/stable/sql/indexes.html)
Oops, I stand corrected!
What I remember about our evaluation of DuckDB in 2024 concluded that (1) the major limitations were lack of range-scan and index-lookup performance (maybe w/ joins? or update where?), and (2) the DuckDB Node.js module segfaulted too much. Perhaps the engineers somehow missed the ART index it could also be the restriction that data fit in memory to create an index on it (our test dataset was about 50gb)
SQLite is fairly fork-resistant due to much of its test suite being proprietary: https://www.sqlite.org/testing.html