h5i-db / Cookbook / Fundamentals / Maintenance: verify, compact, vacuum

Maintenance: verify, compact, vacuum

A versioned database makes an unusual bargain. It never overwrites data, so it accumulates manifests, segments and history. That is the feature. It also means three maintenance questions need honest answers.

  1. Is the data intact? verify() walks the manifest checksum chain, and verify(deep=True) re-checksums every stored byte.
  2. Why did queries get slower? Streaming ingestion leaves many small Parquet segments. compact() merges them into one, as a new version.
  3. Where did my disk go, and what can I get back? vacuum() reclaims unreferenced objects only. We test what that means for old versions rather than assume it.
import time
from pathlib import Path

import pandas as pd

import h5i_db
from h5i_db import count_star
import cookbook_utils as cu

db = h5i_db.Database(cu.fresh_db("00_maintenance"), create=True)


def du_mb(path: str) -> float:
    """Directory size in MiB - the du -s of this recipe."""
    return sum(f.stat().st_size for f in Path(path).rglob("*") if f.is_file()) / 2**20


def bench(fn, repeat: int = 3) -> float:
    """Min-of-N wall time: the standard cheap timing harness."""
    times = []
    for _ in range(repeat):
        t0 = time.perf_counter()
        fn()
        times.append(time.perf_counter() - t0)
    return min(times)

1. The data#

A week of ticks from cu.make_trades, one row per print. We are going to mistreat it deliberately, appending it in 150 tiny commits the way an unbatched feed writer would.

column type meaning
ts timestamp[us, tz=UTC] trade timestamp, ascending
symbol string ticker
price float64 trade price
size int64 shares traded
exchange string reporting venue
side string B buyer-initiated, S seller-initiated 
trades = cu.make_trades(symbols=["AAPL", "MSFT", "NVDA"], days=5, trades_per_day=20_000, seed=7)
print(f"{trades.num_rows:,} rows x {trades.num_columns} columns")
trades.to_pandas().head()
output
313,164 rows x 6 columns
ts symbol price size exchange side
0 2026-06-01 13:30:00.013859+00:00 NVDA 319.29 1 NASDAQ B
1 2026-06-01 13:30:00.101693+00:00 NVDA 319.21 200 IEX S
2 2026-06-01 13:30:00.171748+00:00 MSFT 362.93 1 NYSE S
3 2026-06-01 13:30:00.199372+00:00 MSFT 363.06 1 IEX B
4 2026-06-01 13:30:00.247497+00:00 MSFT 362.93 100 BATS S

2. Simulate an unbatched writer: 150 tiny commits#

Every commit writes a manifest and at least one segment. 150 appends of about 2,000 rows each leave the table with 150 small Parquet files. Each one is a file open, a footer parse and a merge step for every query that follows.

db.create_table("trades", trades.schema, time_column="ts", sort_key=["ts", "symbol"])

N = 150
n = len(trades)
step = n // N
for i in range(N):
    db.append("trades", trades.slice(i * step, step if i < N - 1 else n - (N - 1) * step))

head = db.versions("trades")[-1]
print(f"{head['rows']:,} rows across {head['segments']} segments, "
      f"{head['bytes'] / 2**20:.1f} MiB of live data, db dir = {du_mb(db.path):.1f} MiB")
output
313,164 rows across 150 segments, 2.4 MiB of live data, db dir = 11.3 MiB

3. verify(): the checksum chain#

Shallow verify checks the table's manifest chain. Each manifest commits to its predecessor and to every segment it references, so a truncated file, a lost commit or a tampered manifest breaks the chain. It reads metadata only (bytes_checked: 0), which makes it cheap enough to run on every open.

deep=True additionally re-reads and re-checksums every segment byte. That is the periodic bit-rot audit, priced accordingly.

print("shallow:", db.verify("trades"))
print("deep   :", db.verify("trades", deep=True))
output
shallow: {'table': 'trades', 'head_sequence': 150, 'manifests_checked': 151, 'segments_checked': 150, 'bytes_checked': 0, 'problems': []}
deep   : {'table': 'trades', 'head_sequence': 150, 'manifests_checked': 151, 'segments_checked': 11325, 'bytes_checked': 190082365, 'problems': []}

An empty problems list from a deep verify is an attestation you can hand to anyone: the bytes on disk are exactly the bytes every commit signed for.

4. compact(): undo the segment sprawl#

Time a representative aggregation against the 150-segment table, compact, then time it again. Compaction rewrites the same rows into one well-sized segment and commits the result as a new version. History is untouched, so this is safe to run whenever ingestion leaves debris.

QUERY = """
    SELECT time_bucket('5m', ts) AS bar, symbol,
           vwap(price, size) AS vwap, sum(size) AS volume
    FROM trades GROUP BY bar, symbol ORDER BY bar, symbol
"""

t_before = bench(lambda: db.sql(QUERY).to_arrow())

commit = db.compact("trades", note="merge 150 streaming appends")
print({k: commit[k] for k in ("sequence", "op", "segments_total", "segments_added")})

t_after = bench(lambda: db.sql(QUERY).to_arrow())
print(f"\n5m-bar rollup: {t_before * 1e3:.1f} ms on 150 segments "
      f"-> {t_after * 1e3:.1f} ms on 1 segment  ({t_before / t_after:.1f}x)")
output
{'sequence': 151, 'op': 'compact', 'segments_total': 1, 'segments_added': 1}

5m-bar rollup: 21.6 ms on 150 segments -> 14.8 ms on 1 segment  (1.5x)

Two honest observations.

The speedup here is real but modest. Per-segment overhead scales with segment count, so a day of per-tick commits, meaning tens of thousands of segments, hurts far more than our 150.

And the directory grew. The 150 old segments are still referenced by versions 1 to 150, and the compacted table carries one more copy of every row. Version history is a storage bargain, and compaction does not break it:

print(f"db dir after compact: {du_mb(db.path):.1f} MiB")
v75 = db.read("trades", version=75)
print(f"version 75 still readable: {len(v75):,} rows (head has {head['rows']:,})")
output
db dir after compact: 13.1 MiB
version 75 still readable: 156,525 rows (head has 313,164)

5. vacuum(): what it does and does not reclaim#

The natural fear is that vacuum trades history for disk. Test that instead of assuming it. Below is a dry run (apply=False) with grace_seconds=0, the most aggressive setting, right after compacting.

db.vacuum("trades", grace_seconds=0, apply=False)
output
{'scanned_objects': 306,
 'candidates': [],
 'candidate_bytes': 0,
 'deleted': 0,
 'dry_run': True}

Zero candidates. Every one of those 151 segments is referenced by some version's manifest, and vacuum only ever collects unreferenced objects: staging files from discarded mutation plans, debris from interrupted ingests.

In this build vacuum never prunes version history. Every version stays readable no matter how aggressively you vacuum. Retention is a policy decision it refuses to make implicitly, which is the correct default for anything auditors may ask about.

So let us manufacture the garbage vacuum does exist for. Stage a replace plan, which writes its repaired segment to storage immediately, then discard it. The staged segment is now referenced by nothing.

lo = int(pd.Timestamp("2026-06-01 15:00:00", tz="UTC").value // 1000)
hi = int(pd.Timestamp("2026-06-01 16:00:00", tz="UTC").value // 1000)
window = db.read("trades", time_start=lo, time_end=hi)

plan = db.plan_replace_range("trades", lo, hi, data=window, note="staged then abandoned")
plan.discard()

print("dry run, default grace (1h):", db.vacuum("trades", apply=False))
print("dry run, grace_seconds=0   :", db.vacuum("trades", grace_seconds=0, apply=False))
output
dry run, default grace (1h): {'scanned_objects': 307, 'candidates': [], 'candidate_bytes': 0, 'deleted': 0, 'dry_run': True}
dry run, grace_seconds=0   : {'scanned_objects': 307, 'candidates': ['tables/a8a1921a-b1e0-4c8e-8d77-0d1d39842168/segments/b47f87e5-af76-42b6-bc45-f3fae558e4b9.parquet'], 'candidate_bytes': 1973203, 'deleted': 0, 'dry_run': True}

The default one-hour grace period hides the orphan. Vacuum will not touch young files, because a file that looks unreferenced right now might belong to a commit another process is seconds away from publishing.

Only grace_seconds=0 exposes the discarded plan's segment as a candidate, and it is safe here because nothing else is writing. Reclaim it for real:

size_before = du_mb(db.path)
result = db.vacuum("trades", grace_seconds=0, apply=True)
print(result)
print(f"db dir: {size_before:.2f} MiB -> {du_mb(db.path):.2f} MiB")
output
{'scanned_objects': 307, 'candidates': ['tables/a8a1921a-b1e0-4c8e-8d77-0d1d39842168/segments/b47f87e5-af76-42b6-bc45-f3fae558e4b9.parquet'], 'candidate_bytes': 1973203, 'deleted': 1, 'dry_run': False}
db dir: 15.01 MiB -> 13.13 MiB
# The load-bearing claim, checked: vacuum deleted the orphan and nothing else.
versions = db.versions("trades")
for v in versions:  # every version's manifest still resolves and reads
    db.read("trades", version=v["sequence"], limit=5)
assert db.vacuum("trades", grace_seconds=0, apply=False)["candidates"] == []
print(f"all {len(versions)} versions still readable after vacuum; "
      f"deep verify clean: {db.verify('trades', deep=True)['problems'] == []}")
output
all 152 versions still readable after vacuum; deep verify clean: True

6. Snapshots: named, checksummed pins#

Since vacuum never eats history here, snapshots are not a defensive necessity. Their maintenance role is different.

snapshot(name) records the head version of chosen tables plus the manifest checksum under a durable name. That gives you a read point humans can cite, such as "the EOD cut", and an integrity anchor you can re-verify later.

Pending mutation plans get the same courtesy: their staged segments are protected from vacuum until they are applied, discarded or expired, on a 7-day TTL.

snap = db.snapshot("eod-2026-06-05", tables=["trades"], note="post-compact EOD cut")
entry = next(iter(snap["entries"].values()))
print(f"snapshot {snap['name']!r} pins {entry['table_name']} @ v{entry['sequence']}")
print(f"manifest checksum: {entry['manifest_checksum'][:16]}…")

db.table("trades", snapshot="eod-2026-06-05").select(count_star().alias("rows")).to_pandas()
output
snapshot 'eod-2026-06-05' pins trades @ v151
manifest checksum: f0e614c8df0e9ebd…
rows
0 313164

A maintenance cadence that works#

Takeaways#

db.close()