LanceDB Optimize
Compact and prune all LanceDB tables to merge accumulated fragment files,
reduce open file descriptors, and improve query performance.
When To Use
- Periodic maintenance (recommended weekly or after heavy write activity).
- After "Too many open files" (os error 24) errors.
- After bulk imports, batch embeds, or large cleanup operations.
- Before backups to minimize storage footprint.
Database Roots
All paths are relative to DB_ROOT (default: /mnt/wsl/data4tb/static-flow-data).
| DB |
Path |
Tables |
| Content |
$DB_ROOT/lancedb |
api_behavior_events, article_request_ai_run_chunks, article_request_ai_runs, article_requests, article_views, articles, images, interactive_assets, interactive_page_locales, interactive_pages, llm_gateway_keys, llm_gateway_usage_events, llm_gateway_runtime_config, taxonomies |
| Comments |
$DB_ROOT/lancedb-comments |
comment_ai_run_chunks, comment_ai_runs, comment_audit_logs, comment_published, comment_tasks |
| Music |
$DB_ROOT/lancedb-music |
music_comments, music_plays, music_wish_ai_run_chunks, music_wish_ai_runs, music_wishes, songs |
Preconditions
- Resolve CLI in this order:
./target/release/sf-cli./target/debug/sf-cli../target/release/sf-clisf-cli from PATH
- Verify CLI works:
<cli> --help
- Build if needed:
cargo build -p sf-cli --release
- If the checkout is newer than the chosen binary, rebuild before use.
- Do not prefer legacy
./bin/sf-cli snapshots for storage-format-sensitive writes.
- Verify DB paths exist.
Execution Workflow
Step 1: Pre-check — Count Fragments
For each DB root and each table, inspect fragment counts via Lance metadata
instead of counting files under data/:
<cli> db --db-path <db_path> audit-storage --table <table>
Report a summary table of fragment counts. Tables with <= 3 fragments can be
skipped (already compact).
Step 2: Optimize All Tables
For each table that needs compaction:
<cli> db --db-path <db_path> optimize <table> --all --prune-now
--all: full optimization (compact fragments + rebuild indexes)--prune-now: immediately remove old versions (older_than=0, delete_unverified=true)- On the current StaticFlow fork, blob v2 tables such as
songs, images,
and interactive_assets are expected to compact normally.
Run tables within the same DB sequentially (they share the same lock).
Different DBs can run in parallel.
Step 3: Post-check — Verify
- Re-check fragment counts with
audit-storage.
- Verify row counts match pre-optimization counts:
<cli> db --db-path <db_path> count-rows <table>
- Report before/after comparison.
Selective Optimization
To optimize only specific DBs or tables:
# Single table
<cli> db --db-path /mnt/wsl/data4tb/static-flow-data/lancedb optimize api_behavior_events --all --prune-now
# All tables in one DB
for t in <table_list>; do
<cli> db --db-path <db_path> optimize "$t" --all --prune-now
done
Safety Notes
- Optimization is non-destructive: it merges fragments and removes old versions,
but never deletes current data.
- The backend should ideally not be writing heavily during optimization to avoid
lock contention. Light writes (normal API traffic) are fine.
- If optimization fails mid-way, the table remains valid — just not fully compacted.
Re-run to complete.
1---2name: lancedb-optimize3description: Compacts and prunes LanceDB tables in StaticFlow databases to merge fragment files, reduce open file descriptors, and reclaim storage.4---56# LanceDB Optimize78Compact and prune all LanceDB tables to merge accumulated fragment files,9reduce open file descriptors, and improve query performance.1011## When To Use121. Periodic maintenance (recommended weekly or after heavy write activity).132. After "Too many open files" (os error 24) errors.143. After bulk imports, batch embeds, or large cleanup operations.154. Before backups to minimize storage footprint.1617## Database Roots1819All paths are relative to `DB_ROOT` (default: `/mnt/wsl/data4tb/static-flow-data`).2021| DB | Path | Tables |22|----|------|--------|23| Content | `$DB_ROOT/lancedb` | `api_behavior_events`, `article_request_ai_run_chunks`, `article_request_ai_runs`, `article_requests`, `article_views`, `articles`, `images`, `interactive_assets`, `interactive_page_locales`, `interactive_pages`, `llm_gateway_keys`, `llm_gateway_usage_events`, `llm_gateway_runtime_config`, `taxonomies` |24| Comments | `$DB_ROOT/lancedb-comments` | `comment_ai_run_chunks`, `comment_ai_runs`, `comment_audit_logs`, `comment_published`, `comment_tasks` |25| Music | `$DB_ROOT/lancedb-music` | `music_comments`, `music_plays`, `music_wish_ai_run_chunks`, `music_wish_ai_runs`, `music_wishes`, `songs` |2627## Preconditions281. Resolve CLI in this order:29 - `./target/release/sf-cli`30 - `./target/debug/sf-cli`31 - `../target/release/sf-cli`32 - `sf-cli` from `PATH`332. Verify CLI works: `<cli> --help`34 - Build if needed: `cargo build -p sf-cli --release`353. If the checkout is newer than the chosen binary, rebuild before use.364. Do not prefer legacy `./bin/sf-cli` snapshots for storage-format-sensitive writes.375. Verify DB paths exist.3839## Execution Workflow4041### Step 1: Pre-check — Count Fragments4243For each DB root and each table, inspect fragment counts via Lance metadata44instead of counting files under `data/`:4546```bash47<cli> db --db-path <db_path> audit-storage --table <table>48```4950Report a summary table of fragment counts. Tables with <= 3 fragments can be51skipped (already compact).5253### Step 2: Optimize All Tables5455For each table that needs compaction:5657```bash58<cli> db --db-path <db_path> optimize <table> --all --prune-now59```6061- `--all`: full optimization (compact fragments + rebuild indexes)62- `--prune-now`: immediately remove old versions (older_than=0, delete_unverified=true)63- On the current StaticFlow fork, blob v2 tables such as `songs`, `images`,64 and `interactive_assets` are expected to compact normally.6566Run tables within the same DB sequentially (they share the same lock).67Different DBs can run in parallel.6869### Step 3: Post-check — Verify70711. Re-check fragment counts with `audit-storage`.722. Verify row counts match pre-optimization counts:73 ```bash74 <cli> db --db-path <db_path> count-rows <table>75 ```763. Report before/after comparison.7778## Selective Optimization7980To optimize only specific DBs or tables:8182```bash83# Single table84<cli> db --db-path /mnt/wsl/data4tb/static-flow-data/lancedb optimize api_behavior_events --all --prune-now8586# All tables in one DB87for t in <table_list>; do88 <cli> db --db-path <db_path> optimize "$t" --all --prune-now89done90```9192## Safety Notes93- Optimization is non-destructive: it merges fragments and removes old versions,94 but never deletes current data.95- The backend should ideally not be writing heavily during optimization to avoid96 lock contention. Light writes (normal API traffic) are fine.97- If optimization fails mid-way, the table remains valid — just not fully compacted.98 Re-run to complete.
zero-yx