Indexing Compression Diff

Indexing, Compression & Diff

Tier: Intermediate Commands covered: index, diff, blake3, extsort, extdedup, snappy

Note

Per-command flag reference lives in /docs/help/. This page is the workflow layer — when to reach for each command and how they compose.

These are the performance primitives. None of them transform data — they make subsequent commands faster, smaller, or auditable. extsort / extdedup / snappy are also covered in their respective category pages; this page collects them for the "performance / ops" mindset.

Quick decision table

If you want to…	Use	Notes
Make count, sample, slice, stats, frequency, split, schema faster	index	One-time cost; speeds up 9 commands by 2-9×
Auto-index files over a size threshold	QSV_AUTOINDEX_SIZE env var	See Environment Variables
Get more accurate sniff type/date inference on a large file	index, then sniff	Distributed sample across the whole file, not just the first rows
Compute a cryptographic fingerprint of a file	blake3	Cache keys, pipeline gating, integrity checks
Diff two CSVs ludicrously fast	diff	1M × 9 cols in <600 ms with a primary key
Sort a file > RAM	extsort	See Aggregation & Statistics
Dedup a file > RAM	extdedup	See Aggregation & Statistics
Stream-compress / decompress Snappy	snappy	Most qsv commands auto-handle .sz; this is the explicit interface
Remove qsv-generated cache files	clean	Dry-run by default; --stale for changed/orphaned caches only

index

Builds a <file>.idx sidecar that lets random-access commands (and multithreaded ones) skip parsing rows they don't need. The headline numbers from the README:

• 14 seconds to index the 15 GB / 28 M-row NYC 311 dataset
• After indexing: count, sample, slice are instantaneous
• frequency, split, stats, schema become multithreaded with an index (the 🏎️ symbol in the README legend)
• luau gets random-access mode with an index (otherwise sequential only)
• sniff draws a distributed sample with an index (otherwise it samples only the first --sample rows) — see below

The index is automatically used by any command that benefits. If the source file changes, the index goes stale — re-run qsv index. If QSV_AUTOINDEX_SIZE is set, qsv auto-creates / refreshes the index for files larger than the threshold.

Note that sniff's gain is inference accuracy, not raw speed. With an index it samples across the whole file — the first & last 5 rows, 5 rows each around the 25th/50th/75th percentiles, and the rest random up to the --sample budget — instead of just the first --sample rows. This catches column types and dates that only appear late in a file (and likewise improves stats --infer-dates sniff). It falls back to the contiguous first-N sample when the file is not indexed.

Example: one-time index of the NYC 311 full export

qsv index nyc311-full.csv     # ~14s for 15 GB
ls -lh nyc311-full.csv*
# 15G  nyc311-full.csv
# 27M  nyc311-full.csv.idx

Example: index a CSV via shell, then enjoy the speedup

qsv index NYC_311_SR_2010-2020-sample-1M.csv
qsv count NYC_311_SR_2010-2020-sample-1M.csv      # instant
qsv slice -i 500000 NYC_311_SR_2010-2020-sample-1M.csv | qsv flatten  # parses 1 row, not 500k

Example: turn on auto-indexing for any file > 50 MB

export QSV_AUTOINDEX_SIZE=50000000
# Now qsv index runs automatically on big files when needed

See also: /docs/help/index.md, Environment Variables — QSV_AUTOINDEX_SIZE, Performance Tuning, Stats Cache & Caching.

clean

New in 22.0.1. qsv scatters auto-regenerable caches next to your source files — a .idx index, a stats cache, a frequency cache. That's exactly what makes the "smart" commands fast, and exactly what you don't want to ship inside a data package. clean removes them.

clean is a dry-run by default. It lists what it would remove and the bytes it would reclaim, and deletes nothing until you pass --force:

# preview (default) — nothing is deleted
qsv clean

# actually delete
qsv clean --force

--dry-run/-n wins over --force if you pass both, so a preview flag can never be defeated by a stale --force left in a script.

What it will and won't touch

Only files that are verifiably qsv-generated are removed — never matched on filename alone:

Family	Removed	How it's verified
Index	<file>.idx	Must pass a csv-index size-multiple-of-8 magic check and have a sibling source file
Stats cache	<stem>.stats.csv, .stats.weighted.csv, .stats.csv.json, .stats.csv.data.jsonl	Anchored on the .stats.csv.json args sidecar — so your own hand-written report.stats.csv survives
Frequency cache	<stem>.freq.csv.data.jsonl	Validated by its first-line qsv_version/arg_input metadata

Outputs from schema, validate and moarstats are your results, not caches — they are never touched unless you name them explicitly with --schema, --validate, --moarstats, or --all.

Example: clear only what's gone stale, across a tree

--stale narrows the sweep to caches whose source file is newer than the cache, or whose source is gone entirely — the orphans that accumulate after a pipeline rerun:

qsv clean --stale --recursive --force ./data

Example: strip everything associated with one file before packaging

qsv clean --all --force data.csv

See also: /docs/help/clean.md, Stats Cache & Caching → Cache-busting cheatsheet, index.

diff

The headline number: 1M × 9 columns in under 600 ms. diff is not a line-level differ — it's a primary-key-aware differ. Each row in left/right is identified by one or more primary key columns; the differ classifies each row as added, removed, or modified.

Primary-key uniqueness is required. Use qsv extdedup --select keycol data.csv --no-output to check.

Example: weekly regulatory CSV diff

qsv diff --key id last_week.csv this_week.csv > delta.csv

Output columns include diffresult (Add / Remove / Modify), the primary key, and a side-by-side view of changed fields.

Example: focus the diff on only the columns that actually changed

qsv diff --key id --drop-equal-columns last_week.csv this_week.csv > delta.csv

--drop-equal-columns drops entire columns from the diff result that have no differences anywhere. A column is kept if it is a key column, if it differs in any modified row, or if it has a non-empty value in any added or deleted row — otherwise it is dropped. It pairs with --drop-equal-fields (which blanks out equal field values within modified rows): combine the two to keep only differing columns and, within them, only differing values.

Example: diff with different delimiters on each side

qsv diff --delimiter-left '\t' --delimiter-right ',' \
  --key 'BBL' \
  last.tsv this.csv

Example: handle differently-structured headers

qsv diff --no-headers-right \
  --key 1 \
  last.csv right_no_headers.csv

Example: gate a CI job on no-changes

qsv diff --key id reference.csv candidate.csv \
  | qsv count
# Exits with non-zero count = the diff is non-empty

See also: /docs/help/diff.md, extdedup — for verifying primary-key uniqueness, blake3 — for fingerprint-level "did anything change at all" checks, Recipe: Diff & Audit.

blake3

Multithreaded, mmap-backed BLAKE3 hashing. Functionally similar to b3sum but bundled into qsv. Supports keyed hashing, key derivation, variable-length output, and checksum verification.

The CSV-relevant uses are cache keys ("did the input change between runs?"), pipeline gating ("only run downstream if upstream changed"), and integrity checks ("is this file the same as the one signed off last week?").

Example: fingerprint a CSV for a pipeline-gating cache key

KEY=$(qsv blake3 input.csv | awk '{print $1}')
echo "Cache key: $KEY"

Example: verify a downloaded .zip matches an expected hash

echo "abc123...  downloaded.zip" | qsv blake3 --check -

Example: short hash for use as a column in a CSV

qsv blake3 -l 8 input.csv

-l N truncates the output to N bytes before hex encoding.

Example: keyed BLAKE3 for keyed-hash-based pseudonymization

echo -n 'my-32-byte-secret-key-............' \
  | qsv blake3 --keyed sensitive.csv

See also: /docs/help/blake3.md, pseudo — for reversible pseudonymization, diff — for row-level diff after a hash-level detection, BLAKE3 spec.

extsort (cross-reference)

External merge-sort for files > RAM. Multithreaded. See Aggregation & Statistics → extsort for the full treatment.

qsv extsort --select 'Created Date' nyc311-full.csv > nyc311-by-date.csv

extdedup (cross-reference)

On-disk hash-table dedup. Preserves input order (unlike dedup). See Aggregation & Statistics → extdedup.

qsv extdedup --select session_id huge_clickstream.csv > unique.csv

snappy (cross-reference)

Streaming Snappy compression. Multithreaded compression is 5-6× faster than the auto-compression most qsv commands do on .sz output. See Conversion & I/O → snappy.

qsv snappy compress nyc311-full.csv > nyc311-full.csv.sz

Transparent input decompression

As of v21.1.0, compressed inputs just work — qsv detects and decompresses them automatically, with no pre-processing step:

• Local .zip inputs (and lookup tables) — the first tabular entry is extracted transparently.
• Compressed remote sources (.gz / .zlib / .zst) for lookup tables and get / dc: ingests — streamed and decompressed on the fly.

.sz (Snappy) was already auto-handled (see snappy above); this extends the same convenience to zip and the common remote codecs.

Zip handling is now consistent across commands. The "extract all entries" path (cat, sqlp, to, validate, scoresql) and the reader-level "first tabular entry" path (every other command) now share a single zip module with one selection rule. Entries are returned tabular-first (CSV/TSV/TAB/SSV before other supported formats), so a single-input command and a Config-only command (stats, count, …) pick the same entry from a mixed multi-entry zip. A zip with no supported entry now errors clearly instead of failing downstream with a vague message.

See Conversion & I/O → special-format handling for the full format detection / error-surfacing behavior.

See also

• Command Reference (index)
• Performance Tuning — when to index, how multithreading works
• Environment Variables — QSV_AUTOINDEX_SIZE, cache config
• Stats Cache & Caching
• Aggregation & Statistics — extsort / extdedup
• Conversion & I/O — snappy deep-dive
• Cookbook → Diff & Audit
• Cookbook → Larger-than-RAM CSV