Evaluate sqlite-zstd compression as alternative to gzip

[caufieldjh]

semantic-sql currently uses simple gzip compression. This is generally OK: it's universally recognized, most environments have the tools to decompress it, and provides a good lossless compression ratio.
For sqlite in particular, an alternative compression strategy like sqlite-zstd can provide similar compression ratios or better while supporting the usual CRUD operations without needing to decompress the whole database file.

See https://crates.io/crates/sqlite-zstd

[github-actions]

Research summary

Worth separating two different things that both go by "zstd" here, since they solve different problems:

1. sqlite-zstd (the phiresky/sqlite-zstd Rust crate linked in the issue)

This is row-level transparent compression for a live, writable SQLite database, not a whole-file compressor. It works by loading a compiled extension into the SQLite process, which:

• Renames your table to _zstd-suffixed storage and exposes a view with the original name (zstd_enable_transparent())
• Compresses individual column values (blob/text only) with an optional trained dictionary (zstd_train_dict())
• Does incremental background compression (zstd_incremental_maintenance())

Caveats that matter for us:

• The author's own README says "I wouldn't trust it with my data (yet)". Latest release is 0.3.5 (May 2025), ~1.7k stars, still has open correctness/edge-case issues (e.g. sqlite3_changes() returns 0 on affected queries, no support for attached DBs, partial DDL support).
• It requires every consumer to load a compiled Rust extension (.so/.dylib/.dll) via sqlite3_load_extension before the compression is transparent. semantic-sql databases are consumed directly by arbitrary tools (OAK, DataGrip, sqlite3 CLI, raw Python sqlite3/sqlalchemy, R, etc.) — none of these would see readable data without also shipping/loading this extension. That's a much bigger integration burden than gunzip.
• It's designed for databases that are actively written to (hence the CRUD focus, dictionary retraining, incremental maintenance). Our distributed .db files are build-once, read-only artifacts — most of what this tool optimizes for doesn't apply to our use case.

2. What might actually be wanted: swap the file-level compressor from gzip to zstd

If the goal is just "smaller/faster downloads of the distributed .db.gz," that's a much simpler, lower-risk change: use the plain zstd CLI/format instead of gzip on the finished .db file, same as today, just a different codec. Current gzip usage is here:

• Makefile:54-55 — stage/%.db.gz: STAMP → gzip -c db/$*.db > $@.tmp
• Makefile:243-244 — s3-deploy-% uploads stage/%.db.gz to s3://bbop-sqlite/
• src/semsql/builder/builder.py:87-104 — download_obo_sqlite() downloads the .gz and decompresses with Python's stdlib gzip module

zstd generally beats gzip on both axes at once (smaller output and much faster decompression, roughly an order of magnitude), especially at higher levels (-19ish) or with --long for large repetitive files like these triple stores. The tradeoffs:

• gzip/gunzip is in every base image and is Python stdlib (zero extra dependency for download_obo_sqlite). zstd decompression would need either shelling out to a zstd binary or adding the zstandard/pyzstd PyPI package as a dependency.
• Not every downstream consumer's tooling handles .zst as transparently as .gz yet, though this has improved a lot (most package managers ship zstd now).

Neither option gives the thing that's implicitly attractive about "no need to decompress the whole file" — that would require either the sqlite-zstd extension approach above (with its caveats) or a seekable/range-compressed format (e.g. zstd's "seekable format") paired with a custom SQLite VFS that does ranged reads against S3 — a substantially bigger project.

Suggested next step

A cheap, low-risk evaluation: benchmark gzip -9 vs zstd -19 --long (compressed size + decompression time) on a couple of representative built databases (e.g. a small one and something large like ncbitaxon.db or chebi.db), and if the win is meaningful, add stage/%.db.zst as an additional (not replacement, at least initially) S3 artifact alongside the existing .gz, plus a zstandard fallback path in download_obo_sqlite. That's independent of, and much lower-risk than, adopting sqlite-zstd proper.

Related

• Provide sqlite via a different URL #110 (vendor-neutral distribution URL) touches the same "how do we distribute .db files" surface area and could be considered together with any format change.
• Use https://s3.amazonaws.com/bbop-sqlite/hp.db.gz as an example S3 download, not https://s3.amazonaws.com/bbop-sqlite/hp.db #79, Update ontology builds in S3 bucket #76 also touch S3 distribution mechanics.

Happy to prototype the file-level zstd benchmark if useful — just say the word (e.g. @dragon-ai-agent please add a benchmark script comparing gzip -9 vs zstd -19 --long compression ratio and decompression speed on a sample built .db file, and report results without changing the default build).