3 ms·

For numeric values only, any advantage of using sqlite over let's say parquet with Apache Arrow/duckdb?

avereveard 2y ago
SQLite let's you decide when and if vacuuming after partial row modifications. Does arrow allows that I don't know but it's most likely the best SQLite feature you can open a massive database and do one value change really really fast without having to write and compact the entire thing all the times.
- andyferris 2y ago
  Arrow isn’t great at random insertion and deletion, as each column is a contiguous array. If it’s on disk those arrays are probably packed tightly together, so you can’t even append rows. (I have an idea to implement an LSM tree where each layer is an arrow file, which should allow for faster mutations while maintaining a lot of the benefits of arrow. But I haven’t got around to it).
CJefferson 2y ago
Given SQLite is already distributed on basically every OS,is amazingly rock solid, I’d ask instead any advantage to using Apache Arrow/duckdb? I don’t have great experience with either.
- jitl 2y ago
  DuckDB can have massive performance advantage over SQLite for things like aggregates on wide tables or or filters on unindexed columns, with advantage growing with the size of the table. We tried a few head to heads between the two and while we ultimately went with SQLite, we may go back and double write everything to DuckDB to get better performance for those kinds of queries on the same data set. Duckdb can also read / import SQLite natively so we could ingest lazily from sqlite disk -> duckdb. Biggest issue with duckdb is some memory leaks and crashes for the nodejs driver. Seems not production ready.
  - datadrivenangel 2y ago
    They did just release DuckDB version 1.0, so it should be getting much more reliable and stable.
    - jitl 2y ago
      They said 1.0 means “we aren’t going to break the disk format backwards compatibility anymore” other than that, it’s just a number and doesn’t have any power to make the software more reliable. It really depends on their priorities how much they’ll actually stabilize my use-case. I’ll probably check back in a year and see if things improved.
prirun 2y ago
SQLite encodes integers in a variable-length format, so it can be very space-efficient compared to always storing integers in 4 or 8 bytes.

SQLite let's you decide when and if vacuuming after partial row modifications. Does arrow allows that I don't know but it's most likely the best SQLite feature you can open a massive database and do one value change really really fast without having to write and compact the entire thing all the times.

andyferris 2y ago
Arrow isn’t great at random insertion and deletion, as each column is a contiguous array. If it’s on disk those arrays are probably packed tightly together, so you can’t even append rows. (I have an idea to implement an LSM tree where each layer is an arrow file, which should allow for faster mutations while maintaining a lot of the benefits of arrow. But I haven’t got around to it).

CJefferson 2y ago

Given SQLite is already distributed on basically every OS,is amazingly rock solid, I’d ask instead any advantage to using Apache Arrow/duckdb? I don’t have great experience with either.

jitl 2y ago
DuckDB can have massive performance advantage over SQLite for things like aggregates on wide tables or or filters on unindexed columns, with advantage growing with the size of the table. We tried a few head to heads between the two and while we ultimately went with SQLite, we may go back and double write everything to DuckDB to get better performance for those kinds of queries on the same data set. Duckdb can also read / import SQLite natively so we could ingest lazily from sqlite disk -> duckdb. Biggest issue with duckdb is some memory leaks and crashes for the nodejs driver. Seems not production ready.
- datadrivenangel 2y ago
  They did just release DuckDB version 1.0, so it should be getting much more reliable and stable.
  - jitl 2y ago
    They said 1.0 means “we aren’t going to break the disk format backwards compatibility anymore” other than that, it’s just a number and doesn’t have any power to make the software more reliable. It really depends on their priorities how much they’ll actually stabilize my use-case. I’ll probably check back in a year and see if things improved.

prirun 2y ago

SQLite encodes integers in a variable-length format, so it can be very space-efficient compared to always storing integers in 4 or 8 bytes.