DuckDB Salvage Analysis: Final Embedding Campaign 20260629T002747¶

Archive status — 2026-10-07¶

This is a historical report. Measurements and decisions describe the original run and revision; recommendations and commands are not current operating instructions. Path corrections below change navigation, not measurements. The original ignored report remains unchanged at .artifacts/analysis/2026-06-29-final-embedding-campaign-20260629T002747-duckdb-salvage-analysis.md (SHA-256 2f3cdfac16de536a3be9b4e6852ce3104ce65775b4473c4f5f94bc56b7150680).

Some original evidence is unavailable in this checkout. The missing paths are listed below; those observations have not been independently revalidated. Do not treat this archive as a fully reproducible current benchmark.

See the report archive index for coverage and remaining gaps.

Artifact or input reference	Current availability
`.artifacts/final-embedding-model-campaign/20260629T002747`	unavailable historical path
`benchmarks/embedding/model-candidates.json`	available
`benchmarks/embedding/uv-backed-repos.local.json`	available

Resolved directory moves:

benchmarks/embedding-model-candidates.json → benchmarks/embedding/model-candidates.json.
benchmarks/uv-backed-repos.local.json → benchmarks/embedding/uv-backed-repos.local.json.

Scope¶

Campaign:

.artifacts/final-embedding-model-campaign/20260629T002747
Command shape: final embedding model campaign with --backend both
Repositories: codira, fontshow, chatops, sanikey
Models: 6 embedding candidates from benchmarks/embedding/model-candidates.json
Runs: 5
Warmup: 1

This campaign is directly usable for backend comparison because SQLite and DuckDB phases were generated by the same campaign runner and use the same repository/model matrix.

Parity Checks¶

Campaign backend mode: both
Concrete backends: sqlite, duckdb
Git commit in campaign plans: ed59156b6fa34298bbe833368c9eb93fb980b508
DuckDB backend package: codira-backend-duckdb 1.49.3
SQLite backend package: codira-backend-sqlite 1.45.0
DuckDB profiling was active: 24 duckdb-profile.json files were emitted, one per DuckDB model/repository phase.

Headline Timings¶

Hyperfine wall-clock medians across the 24 model/repository pairs:

Command	SQLite median	DuckDB median	DuckDB / SQLite
`index --full`	2.429 s	4.962 s	2.04x
incremental `index`	0.368 s	0.434 s	1.18x
`ctx`	3.331 s	3.659 s	1.10x
`emb`	2.941 s	3.471 s	1.18x
`sym`	0.266 s	0.315 s	1.19x
`calls`	0.265 s	0.308 s	1.16x
`cov`	0.230 s	0.231 s	1.00x
`symlist`	0.270 s	0.321 s	1.19x
`audit`	0.267 s	0.320 s	1.20x
`help`	0.166 s	0.166 s	1.00x

The residual problem is therefore specifically full-index write-path behavior. Interactive/query overhead is now mostly in the 1.1x-1.2x band, which is not the strategic blocker.

Paired Full-Index Differences¶

The full-index wall-clock delta is not uniform. Median difference is about 1.98 s; mean difference is about 2.37 s.

Worst paired deltas:

Repo	Model	SQLite	DuckDB	Delta	Ratio
`fontshow`	`nomic-embed-text-v1.5-onnx`	4.652 s	11.317 s	6.665 s	2.43x
`codira`	`nomic-embed-text-v1.5-onnx`	6.058 s	11.264 s	5.207 s	1.86x
`fontshow`	`jina-embeddings-v2-base-code-onnx`	4.361 s	7.524 s	3.164 s	1.73x
`fontshow`	`current-minilm-sentence-transformers`	3.253 s	6.221 s	2.969 s	1.91x

Small repositories have worse ratios because DuckDB pays a relatively fixed write/session/index cost even when there is little data.

Internal Phase Timings¶

Codira phase timings show that non-backend phases are essentially equal:

Phase	SQLite mean	DuckDB mean	DuckDB / SQLite
parsing	0.805 s	0.813 s	1.01x
discovery	0.017 s	0.017 s	1.01x
filtering	0.010 s	0.010 s	1.03x
metadata	0.007 s	0.007 s	1.02x
scan_state	0.029 s	0.029 s	1.01x
indexing	0.431 s	1.071 s	2.48x

The internal indexing phase isolates the backend write path. It confirms the same conclusion as Hyperfine: DuckDB is losing in persistence, not parsing, scanning, model inference, or command startup.

Indexing median by repo:

Repo	SQLite	DuckDB	DuckDB / SQLite
`chatops`	0.198 s	0.526 s	2.66x
`codira`	0.884 s	1.743 s	1.97x
`fontshow`	0.513 s	1.594 s	3.11x
`sanikey`	0.125 s	0.419 s	3.35x

DuckDB Profile Evidence¶

The 24 DuckDB profile files aggregate to these top spans. These spans are nested, so they should not be summed as wall time. They identify hot code paths.

Span	Total seconds	Calls	Rows
`persist.store_analysis`	56.998	4,866	4,866
`sql.select`	48.627	5,082	0
`embeddings.flush_pending_rows`	27.235	24	66,510
`vector_store.store_vectors`	20.849	24	66,510
`sql.create_index`	13.715	1,128	0
`prepare.create_schema_indexes`	13.705	24	0
`session.commit_transaction`	13.414	48	0
`duckdb.raw_commit`	13.412	48	0
`flush.structural_rows`	4.623	48	104,172
`rebuild.derived_indexes`	3.367	24	0
`flush.reference_scan_rows`	2.554	48	1,227,894
`flush.relationship_rows`	2.106	48	222,672

The strongest signal is persist.store_analysis, which runs once per file. It dominates the profile and is coupled with thousands of sql.select calls.

Per-repo median selected spans:

Repo	`persist.store_analysis`	`sql.select`	`embeddings.flush_pending_rows`	`vector_store.store_vectors`	`create_schema_indexes`	`commit_transaction`
`codira`	3.399 s	2.665 s	1.558 s	1.173 s	0.898 s	0.794 s
`fontshow`	3.072 s	2.620 s	1.531 s	1.180 s	0.793 s	0.775 s
`chatops`	0.968 s	0.847 s	0.663 s	0.496 s	0.238 s	0.388 s
`sanikey`	0.812 s	0.736 s	0.662 s	0.492 s	0.215 s	0.211 s

Root Cause¶

The remaining DuckDB slowdown is not because DuckDB is intrinsically slow. It is because the plugin is still architected like a row-oriented transactional backend with some bulk inserts grafted onto it.

The key anti-pattern is:

for each analyzed file:
    persist_store_analysis(file)
    allocate ids with SELECT MAX(id) on first use per table
    build per-file module/class/function/documentation bookkeeping
later:
    bulk flush accumulated rows

That keeps too much of the write plan in Python and sends DuckDB thousands of small persistence decisions and SELECTs before reaching the bulk-load parts. DuckDB is strongest when given columnar relations and set-oriented SQL plans. The current plugin uses Arrow and CSV in some flush paths, but the full index is still organized around per-file mutation semantics.

The second important issue is duplication around embeddings/vector storage:

embeddings.flush_pending_rows is materializing 66,510 rows per full matrix.
vector_store.store_vectors is also materializing 66,510 rows.
For DuckDB, this means a DuckDB structural backend plus a separate DuckDB vector-store path are both doing work. This is technically modular, but not native for a DuckDB-optimized full-index build.

The third issue is fixed overhead:

1,128 index creation calls across 24 profiles.
48 commits across 24 profiles.
Fixed index/commit costs dominate small repositories.

The recent lifecycle fix helped, but it does not change the basic write model. The full-index path is still not DuckDB-native.

Salvage Decision¶

DuckDB is salvageable only if we stop iterating on the current persistence shape.

Continuing to tune the current plugin incrementally is not worth it. The data shows the bottleneck is architectural:

per-file persistence remains the hottest path;
vector persistence is duplicated across structural and vector-store layers;
index/commit overhead is still large enough to dominate small repos;
current bulk paths are partial, not end-to-end.

Recommendation:

Do not spend more time on micro-optimizations of the current code path.
Either build a DuckDB-native v2 write path with an explicit acceptance gate, or deprecate DuckDB as a production backend and keep SQLite as default.

The plugin is worth one final, radical salvage attempt if the goal is to keep a DuckDB backend for analytical/vector-heavy future work. It is not worth further incremental work if the only goal is to beat SQLite on current small-repo Codira benchmarks.

Resolutive Fix Plan¶

1. Add a DuckDB-native full-index builder¶

Implement a separate full-index path for DuckDB instead of routing full rebuilds through the same per-file persistence method.

Design:

Analyzer output is collected into in-memory columnar row buffers by table.
IDs are assigned in one deterministic planning pass, not through per-file SQL lookups.
All structural tables are loaded from Arrow or Parquet relations in table order.
Derived relation tables are created with CREATE TABLE AS SELECT or INSERT INTO ... SELECT, not Python loops.
Indexes are created only after all table loads finish.
Full rebuild writes into a fresh DuckDB database or staging schema, then atomically swaps the result into place.

Target native constructs:

CREATE TABLE AS SELECT
INSERT INTO ... SELECT FROM read_parquet(...)
Arrow relation registration only for large whole-table batches, not per-file operations
COPY/Parquet staging for very large relation tables such as reference scan rows
one transaction for structural load plus one final checkpoint/swap

Acceptance:

DuckDB index --full median <= 1.15x SQLite on this 4-repo matrix.
Small-repo fixed overhead no worse than 1.25x SQLite.
No regression in sym, calls, audit, ctx, or emb.

2. Collapse DuckDB structural and vector writes for full index¶

For DuckDB backend plus DuckDB vector store, avoid treating the vector store as a separate database write path during full-index builds.

Options:

Preferred: one DuckDB file owns both structural and vector tables for DuckDB backend runs.
Acceptable transitional path: pass the active DuckDB connection into the DuckDB vector store during indexing so vector writes share the same native session and staged transaction.

This removes the current double-DuckDB shape:

structural DuckDB backend writes embeddings metadata
separate DuckDB vector store writes materialized vectors

Acceptance:

vector_store.store_vectors is no longer a separate 0.5-1.2s median cost in DuckDB profile output.
Full-index DuckDB profile shows vector rows loaded as part of the same whole-table load plan.

3. Remove per-file `persist.store_analysis` from full-index hot path¶

Keep _store_analysis only for incremental updates or compatibility tests. Full rebuilds should not call it per file.

Implementation sketch:

Add a full-index planning object that takes all FileAnalysis records.
Build deterministic maps:
file path -> file id
module stable identity -> module id
class stable identity -> class id
function stable identity -> function id
Emit final table row arrays directly.
Resolve call/reference/documentation rows against those maps in memory.
Flush each final table once.

Acceptance:

DuckDB profile no longer has thousands of persist.store_analysis calls.
sql.select call count during full index drops from about 5,082 across the matrix to near schema/index bookkeeping only.

4. Make index creation coarse-grained and measurable¶

Current profile shows 1,128 sql.create_index calls across the matrix. That cost is real and fixed.

Actions:

Review every DuckDB index for query evidence.
Drop indexes that do not affect current sym, calls, refs, audit, ctx, emb, symlist, or cov commands.
Prefer fewer composite indexes matching actual query predicates.
Keep a dedicated test that asserts the index set is intentional, not just inherited from SQLite.

Acceptance:

prepare.create_schema_indexes median falls below 0.25s on codira and fontshow.
Query medians remain within 1.1x SQLite or improve.

5. Set a hard go/no-go gate¶

Run the same campaign after the rewrite:

uv run python -m scripts.run_final_embedding_model_campaign --runs 5 --warmup 1 --manifest benchmarks/embedding/uv-backed-repos.local.json --model-manifest benchmarks/embedding/model-candidates.json --backend both

Go criteria:

index --full: DuckDB median <= 1.15x SQLite.
incremental index: DuckDB median <= 1.10x SQLite.
ctx, emb, sym, calls, audit: DuckDB median <= 1.15x SQLite.
No memory spiral.
DuckDB profile confirms no per-file persistence hotspot.

No-go criteria:

If a DuckDB-native v2 full-index path still cannot get below 1.25x SQLite on full index, stop investing in DuckDB as a primary Codira backend.
Keep DuckDB only as an experimental/analytical backend or remove it from the recommended first-party bundle.

Non-Regression Tests¶

Required tests for the rewrite:

Full-index DuckDB path does not call _store_analysis per file.
Full-index DuckDB profile contains one table-load span per structural table.
codira.analyzers remains absent from core imports.
SQLite and DuckDB produce equivalent symbol, call, ref, audit, documentation, and embedding query results for a mixed Python/C/C++/Bash/Markdown fixture.
DuckDB index metadata, runtime inventory, and embedding backend state match SQLite semantics.
DuckDB vector rows are query-compatible with existing emb and ctx.
Custom Semgrep rule prevents reintroducing DuckDB executemany and can be extended to flag per-row or per-file DuckDB persistence loops in full-index paths.

Documentation Updates¶

Update:

docs/architecture/storage-backends.md: document DuckDB-native full-index architecture and the no per-file persistence rule.
docs/plugins/backends.md: explain when DuckDB is recommended and what workload it targets.
docs/process/duckdb-slowdown-fix-2026-06-25.md or a successor note: record the v2 rewrite decision and go/no-go gate.
AGENTS.md: no change needed unless module ownership changes beyond the backend/vector-store package boundary.

Bottom Line¶

DuckDB is not currently competitive with SQLite for Codira full-index workloads: median full index is still about 2.04x slower.

The plugin is salvageable only with a DuckDB-native full-index rewrite. The current implementation has already received enough incremental tuning to show its ceiling. If the v2 rewrite is not acceptable in scope, the pragmatic decision is to stop trying to make DuckDB the default-quality backend and keep SQLite as the production backend.

DuckDB Salvage Analysis: Final Embedding Campaign 20260629T002747¶

Archive status — 2026-10-07¶

Scope¶

Parity Checks¶

Headline Timings¶

Paired Full-Index Differences¶

Internal Phase Timings¶

DuckDB Profile Evidence¶

Root Cause¶

Salvage Decision¶

Resolutive Fix Plan¶

1. Add a DuckDB-native full-index builder¶

2. Collapse DuckDB structural and vector writes for full index¶

3. Remove per-file persist.store_analysis from full-index hot path¶

4. Make index creation coarse-grained and measurable¶

5. Set a hard go/no-go gate¶

Non-Regression Tests¶

Documentation Updates¶

Bottom Line¶

3. Remove per-file `persist.store_analysis` from full-index hot path¶