zstd is the go-to compression format these days. It's even supported in low-level software such as many linux filesystems.
I don't know much about duckdb but it looks like it supports zstd too: https://duckdb.org/docs/lts/data/json/loading_json
How did I miss zstd?
Here are my benchmarks for 2.3 GB of jsonl, on a laptop. Compressed size, compress time, decompress time; using defaults.
gzip 7.3% 21s 9s
bzip2 4.6% 251s 50s
bzip3 3.3% 82s 69s
zstd 6.9% 2s 3s
lzma 4.7% 51s 3sall hail zstd, the one format to rule them all
Not really, it's a popular dictionary-based compression format.
The thing zstd got really right is fast decompression. For write-once read-never data like backups lzma (aka xz/7zip/lzip) is great. But it takes forever to decompress. On zstd I can get good compression while decompressing the file only marginally slower than reading the uncompressed file from SSD
Writing your files directly into a compressed stream and decompressing on the fly has become almost a standard workflow for any files I'm going to read and write sequentially anyways. No need for the data to ever exist uncompressed on the file system. Previous formats never did that for me because they either had too much overhead or too little gain, often both