Repository navigation
No SQL/HTTP way to trigger index compaction (Java API only) — client-server users can't settle after bulk load #5144
Description
Summary
Index compaction can only be triggered through the Java API (Index.compact() / LSMSparseVectorIndex.compact()). There is no SQL statement or HTTP endpoint for it, so client-server users cannot force a compaction after a bulk load — they wait for background compaction on its own schedule. Embedded users can (and benefit measurably).
Measured effect
1M sparse vectors (30k dims), controlled benchmark, blocking compact() via the Java API right after bulk ingest:
| query p50 | query p95 | |
|---|---|---|
| before compaction | 9.5 ms | 12.6 ms |
| after compaction (~2 s) | 7.0 ms | 8.8 ms |
(A no-compaction control run showed no warming effect — the ~26% gain is compaction, not cache.)
This creates an asymmetry between deployment modes for the common load-then-serve pattern: every comparable system exposes a client-reachable equivalent (Elasticsearch _forcemerge, Milvus flush+load), and ArcadeDB has the mechanism — just no SQL/HTTP surface for it.
Suggested shape
Something in the spirit of the existing REBUILD INDEX:
COMPACT INDEX `Doc[tokens,weights]`or an HTTP admin endpoint. Blocking or async-with-status both work for the bulk-load use case.
Activity
paper-icde: FIX broken adapter class structure; add nocompact ablatio…
tae898 commentedon Jul 9, 2026
Some measured numbers from a controlled benchmark, in case they help prioritize.
What compaction is worth. 1M sparse vectors (30k dims), bulk load then query. Calling
compact()through the Java API right after ingest costs about 2 seconds and gives:That is roughly 26% off query latency. A no-compaction control run showed no warming effect, so the gain is compaction, not cache.
Who can get it. Only embedded users, since
compact()is Java-API only. A client-server user doing the very common "bulk load, then serve queries" pattern has no way to reach it, short ofREBUILD INDEX, which re-indexes everything (minutes at 10M) rather than merging segments (about 2 seconds).Comparison context. Among the engines I benchmark against, ArcadeDB is the only one whose settle step is not client-reachable:
_forcemergeflush+loadSide effect worth noting. Because embedded can compact and server cannot, an embedded-vs-server comparison of the same engine mixes transport cost with settle-step cost. I had to add a deliberately non-compacting embedded variant to separate the two. A client-reachable trigger would remove that confound.
Happy to re-measure and report once a trigger exists, in whatever form makes sense (
COMPACT INDEX, an HTTP admin endpoint, blocking or async with status).tae898 commentedon Jul 9, 2026
One sequencing note: see #5189.
Sparse-vector compaction currently writes the whole merged segment inside a single transaction, and the WAL caps a transaction buffer at 2 GB, so
compact()throws once the segment grows past that. Embedded users can hit it today. Server users cannot, only because this issue leaves them with no trigger.If a SQL/HTTP trigger lands before #5189 is addressed, server users would gain a button that fails at scale (I see it at 10M sparse vectors with FP32 weights; INT8 reaches the same ceiling on a larger corpus). Fixing #5189 first, or together, would avoid that.
feat(#5144): add COMPACT INDEX SQL statement to trigger index compact…
lvca commentedon Jul 10, 2026
Added a
COMPACT INDEXSQL command so you can trigger a compaction from a client (SQL or the HTTP/commandendpoint), without needing the embedded Java API:It runs synchronously and works for both regular indexes and sparse-vector indexes, so you can settle an index right after a bulk load. It reuses the same compaction the engine already runs in the background, and requires the same privilege as
REBUILD INDEX.Note on the sequencing you raised: the sparse-vector 2 GB limit is tracked and fixed under #5189, so
COMPACT INDEXon large sparse indexes is safe.Merged to
main, will be in the next release. Thanks for the detailed benchmark, @tae898.lvca commentedon Jul 10, 2026
Please @tae898 let me know how it works, together with #5189 ;-)
paper-icde: 26.8.1 re-campaign — memory anon working set, server COMP…
tae898 commentedon Jul 11, 2026
Confirmed working in my benchmarks. I now run COMPACT INDEX over HTTP /command as the server's post-build settle step for a sparse index; 8/8 reps green at the 10M-doc tier (recall@10 ~0.993). Thanks.
lvca commentedon Jul 11, 2026
That was a great idea, thank you!
feat(#5144): add COMPACT INDEX SQL statement to trigger index compact…