3 ms·

If i am ever in a situation where i am considering SQLite, i will try to use DuckDB instead. It's a similar weight (the all-inclusive JARs are 6.9 MB for SQLite

by twic 6y ago

If i am ever in a situation where i am considering SQLite, i will try to use DuckDB instead. It's a similar weight (the all-inclusive JARs are 6.9 MB for SQLite and 8.7 for DuckDB), it also stores a database in a single file, it's different in many ways, but for me, the killer feature is that it uses the PostgreSQL SQL syntax (and parser). This should make it very easy to migrate from DuckDB to PostgreSQL if that becomes necessary. I currently work on a project based around SQLite, which suffers from that fact. Migrating it to PostgreSQL would be great, but would involve a lot of tedious work.

gravypod 6y ago
Something I've used similarly in these situations is LevelDB or RocksDB if you just need a really small key value store. It's a really tiny library that gets you really fast write performance and some light atomic operations.
sradman 6y ago
It is my understanding that DuckDB is an embedded in-memory OLAP ColumnStore designed to be primarily used with R/Python data frames. I noticed that they added JDBC support. Does this mean they've added persistence and other OLTP functionality? I thought their "SQLite for Analytics" tagline was confusing and I'm certainly questioning my recollection now.
- twic 6y ago
  As far as i know, DuckDB has always supported files, and the full SQL standard you would need for OLTP, and Pandas is just one use case. People do seem to have a wide range of impressions of what DuckDB is for, so i think it's fair to conclude that their messaging has not been very effective!
  - sradman 6y ago
    The problem is my recall and laziness. I didn't look into the details of what parts of HyPer were borrowed and I missed the adoption of the DataBlocks [1] storage format. DuckDB is designed to be an embedded HTAP (Hybrid Transactional/Analytical Processing) engine. I think the project is immature and the documentation is sparse. I expect to see detailed documentation on default file locations and the recovery process for an OLTP/HTAP engine and I didn't see anything like that in the DuckDB docs. My bad, I should have checked my assumptions. I would not adopt DuckDB as an OLTP replacement for either SQLite or PostgreSQL without a strong OLAP component and some serious performance/load/integrity testing, but it sounds like you are confident in its abilities. Thanks for sharing your experience. [1] https://db.in.tum.de/downloads/publications/datablocks.pdf https://db.in.tum.de/downloads/publications/datablocks.pdf
Chris2048 6y ago
> the all-inclusive JARs are 6.9 MB for SQLite and 8.7 for DuckDB As neither seems to be a JAVA program, What does this refer to? A jar with the DB bindings, or with binary code for the relevant DB compiled into the jar? Or do you mean using a Java lib that can read the relevant DB format without needing a DB service?
- twic 6y ago
  Both SQLite and DuckDB are available as JARs containing native libraries for the database, for some range of platforms, and Java code which unpacks the library at runtime and uses it. org.duckdb:duckdb_jdbc:0.2.2 contains: libduckdb_java.so_linux_amd64 libduckdb_java.so_osx_amd64 libduckdb_java.so_windows_amd64 org.xerial:sqlite-jdbc:3.32.3.2 contains: org/sqlite/native/DragonFlyBSD/x86_64/libsqlitejdbc.so org/sqlite/native/FreeBSD/x86_64/libsqlitejdbc.so org/sqlite/native/Linux/aarch64/libsqlitejdbc.so org/sqlite/native/Linux-Alpine/x86_64/libsqlitejdbc.so org/sqlite/native/Linux/android-arm/libsqlitejdbc.so org/sqlite/native/Linux/arm/libsqlitejdbc.so org/sqlite/native/Linux/armv6/libsqlitejdbc.so org/sqlite/native/Linux/armv7/libsqlitejdbc.so org/sqlite/native/Linux/ppc64/libsqlitejdbc.so org/sqlite/native/Linux/x86_64/libsqlitejdbc.so org/sqlite/native/Linux/x86/libsqlitejdbc.so org/sqlite/native/Mac/x86_64/libsqlitejdbc.jnilib org/sqlite/native/Windows/x86_64/sqlitejdbc.dll org/sqlite/native/Windows/x86/sqlitejdbc.dll So that SQLite JAR is smaller, but covers a much wider range of platforms!

by twic 6y ago

gravypod 6y ago

Something I've used similarly in these situations is LevelDB or RocksDB if you just need a really small key value store. It's a really tiny library that gets you really fast write performance and some light atomic operations.

sradman 6y ago

It is my understanding that DuckDB is an embedded in-memory OLAP ColumnStore designed to be primarily used with R/Python data frames. I noticed that they added JDBC support. Does this mean they've added persistence and other OLTP functionality? I thought their "SQLite for Analytics" tagline was confusing and I'm certainly questioning my recollection now.

twic 6y ago
As far as i know, DuckDB has always supported files, and the full SQL standard you would need for OLTP, and Pandas is just one use case. People do seem to have a wide range of impressions of what DuckDB is for, so i think it's fair to conclude that their messaging has not been very effective!
- sradman 6y ago
  The problem is my recall and laziness. I didn't look into the details of what parts of HyPer were borrowed and I missed the adoption of the DataBlocks [1] storage format. DuckDB is designed to be an embedded HTAP (Hybrid Transactional/Analytical Processing) engine. I think the project is immature and the documentation is sparse. I expect to see detailed documentation on default file locations and the recovery process for an OLTP/HTAP engine and I didn't see anything like that in the DuckDB docs. My bad, I should have checked my assumptions. I would not adopt DuckDB as an OLTP replacement for either SQLite or PostgreSQL without a strong OLAP component and some serious performance/load/integrity testing, but it sounds like you are confident in its abilities. Thanks for sharing your experience. [1] https://db.in.tum.de/downloads/publications/datablocks.pdf https://db.in.tum.de/downloads/publications/datablocks.pdf

Chris2048 6y ago

> the all-inclusive JARs are 6.9 MB for SQLite and 8.7 for DuckDB As neither seems to be a JAVA program, What does this refer to? A jar with the DB bindings, or with binary code for the relevant DB compiled into the jar? Or do you mean using a Java lib that can read the relevant DB format without needing a DB service?

twic 6y ago
Both SQLite and DuckDB are available as JARs containing native libraries for the database, for some range of platforms, and Java code which unpacks the library at runtime and uses it. org.duckdb:duckdb_jdbc:0.2.2 contains: libduckdb_java.so_linux_amd64 libduckdb_java.so_osx_amd64 libduckdb_java.so_windows_amd64 org.xerial:sqlite-jdbc:3.32.3.2 contains: org/sqlite/native/DragonFlyBSD/x86_64/libsqlitejdbc.so org/sqlite/native/FreeBSD/x86_64/libsqlitejdbc.so org/sqlite/native/Linux/aarch64/libsqlitejdbc.so org/sqlite/native/Linux-Alpine/x86_64/libsqlitejdbc.so org/sqlite/native/Linux/android-arm/libsqlitejdbc.so org/sqlite/native/Linux/arm/libsqlitejdbc.so org/sqlite/native/Linux/armv6/libsqlitejdbc.so org/sqlite/native/Linux/armv7/libsqlitejdbc.so org/sqlite/native/Linux/ppc64/libsqlitejdbc.so org/sqlite/native/Linux/x86_64/libsqlitejdbc.so org/sqlite/native/Linux/x86/libsqlitejdbc.so org/sqlite/native/Mac/x86_64/libsqlitejdbc.jnilib org/sqlite/native/Windows/x86_64/sqlitejdbc.dll org/sqlite/native/Windows/x86/sqlitejdbc.dll So that SQLite JAR is smaller, but covers a much wider range of platforms!