Re:Searching for a better persistent cache
by legg0myegg0 on Tuesday July 27, 2021 @08:26PM
(#27979158)
I'm not sure if it would help in your case, but could you process all categories at once with a larger SQL query?
If so, DuckDB can process bulk queries about 20x faster than SQLite per CPU core because it is vectorized and column oriented. Then with multiple cores you can easily reach 100x SQLite speed. DuckDB has node bindings and is an in-process DB like SQLite.
If reading from disk is your bottleneck, I would recommend storing your data in compressed parquet files and reading them with DuckDB's parquet reader.
One drawback is that indexes are not persistent to the filesystem in DuckDB yet, but full table scans are much faster than SQLite since it is columnar.
If so, DuckDB can process bulk queries about 20x faster than SQLite per CPU core because it is vectorized and column oriented. Then with multiple cores you can easily reach 100x SQLite speed. DuckDB has node bindings and is an in-process DB like SQLite.
If reading from disk is your bottleneck, I would recommend storing your data in compressed parquet files and reading them with DuckDB's parquet reader.
One drawback is that indexes are not persistent to the filesystem in DuckDB yet, but full table scans are much faster than SQLite since it is columnar.
https://github.com/duckdb/duckdb/tree/master/tools/nodejs
https://duckdb.org/