Comment by munro | Hacker News Reader

Comment by munro | Hacker News Reader

Cool! This is the first time hearing about DuckDB, exciting as I heavily use SQLite. And these benchmarks are showing it's 6-15 times faster than sql.js (SQLite) [1], along with another small benchmark I found [2]. I usually just slather on indexes in SQLite tho, so indexed queries may not stand up as well; and might not be as fast when it's on storage, as this is comparing in memory performance (I think?), but I'll give it a spin!

Gonna throw this out there: main thing I'm looking for from an embedded DB is better on disk compression; I've been toying with RocksDB, but it's hard to tune optimally & it's really too low level for my needs.

[1] > ipython

    import numpy as np
    duckdb = [0.855, 0.179, 0.151, 0.197, 0.086, 0.319, 0.236, 0.351, 0.276, 0.194, 0.086, 0.137, 0.377]
    sqlite = [8.441, 1.758, 0.384, 1.965, 1.294, 2.677, 4.126, 1.238, 1.080, 5.887, 1.194, 0.453, 1.272]
    print((np.quantile(sqlite, q=[0.1, 0.9]) / np.quantile(duckdb, q=[0.1, 0.9])).round())

[2] https://uwekorn.com/2019/10/19/taking-duckdb-for-a-spin.html

Do you need compression to get more bandwidth or are you trying to save money on storage costs?

DuckDB is a column based db, so you are going to see a throughput increase for queries that only use a handful of columns.

Saving money definitely, but also delaying having to scale the hardware as long as possible. I'm at 3.5 TiB NVMe (raid 1), and it would cost another $52 USD/mo to add an additional raid of 1.5 TiB NVMe @ Hetzner, not cool. Generally I'm seeing around 6:1 compression, so going from 3.5 TiB to 21 TiB is a big deal for me.

I'm manually doing zlib compression for large text columns when there's an obvious opportunity, basically DIY toast [1]. Doing that allowed one SQLite DB to go from about 205 GiB to 35 GiB. And I haven't really felt any performance impact when working with the data; but definitely feel the coding overhead. And there's still so much missed opportunity for compression.

Largest RocksDB is +1 GiB/day (poorly tuned with zlib compression). I just couldn't use SQLite for that one, lots of small rows, but they compress extremely well. I never wrapped up the compression experiments on that, but look at some rough notes snappy was 430G, and lz4 level 6 was 86G. Unfortunately using RocksDB has made coding more difficult.

I think one day I'm just going to snap and build a ZipFS-like extension [2], until them I'm just trying to keep an eye out, and putting out this call for help. :3

[1] https://www.postgresql.org/docs/9.5/storage-toast.html

[2] https://www.sqlite.org/zipvfs/doc/trunk/www/index.wiki

For my bachelor thesis, I used Postgres on a compressed btrfs partition. For my text-heavy dataset, this gave excellent results without compromising on ergonomics.

As the implementation is block-based it is also faster than the naive approach of just zipping your data files.

That is really cool, I tried btrfs a couple times and I had weirdness, sorry for being unspecific, not trying to do a drive by hit job on btrfs, but I moved on.

That said, for a specific case like this, I think this could be really compelling, esp going from compressed source material, to a postgres instance running on loopback block device with btrfs on top, could be really amazing for text analysis like you said. You could actually see performance improvements due to this. 4GB/s for PCIe 4, then due to massive core counts and large L3 caches, you could probably boost that to 20GB/s after decompression.

If you can use compression algorithms that allow for regex search in the compression domain, it could be an effective search speed of 10s to hundreds of GB/s.

I am going to have to play with btrfs again.

I believe compression is still a work in progress in duckdb. My current use is with parquet files which compress well but don't lend themselves to updates like a db.

Reply on news.ycombinator.com