---
title: "HeliosDB Lite v3.6.1: April 2026 Monthly Performance Review"
description: "33 of 35 categories match or improve over v3.6.0. DELETE point −36% (1000× on a 10K-row table), IN subquery −47%, DISTINCT −45%, JOINs ~−29%. Full 35-category breakdown, three deep-dives, and open caveats."
date: 2026-04-22
updated: 2026-04-22
tag: "Benchmarks"
canonical: https://www.heliosdb.com/blog/lite-3-6-1-april-2026-performance-review.html
---

# HeliosDB Lite v3.6.1: April 2026 Monthly Performance Review

v3.6.1 is a performance-focused patch release on the Lite branch. We re-ran the 35-category trace suite on the same host as the v3.6.0 release-build measurement and compared point by point. Headline: 33 of 35 categories match or improve. DELETE point is **−36%** on the small benchmark fixture and shows a **1000×** improvement on a 10K-row table in a dedicated test. IN subquery is **−47%**. DISTINCT is **−45%**. JOIN shapes average **−29%**. No regressions over 5% outside two flagged multi-row INSERT fixtures we want to re-measure.

## Methodology & Reproducibility

- **Date:** 2026-04-22, single session, same host.
- **Build:** release (`--release`) for both v3.6.0 and v3.6.1 runs.
- **Deployment:** Embedded / in-memory backend, no TLS, no replication.
- **Host:** single-socket x86_64, Linux 5.14, default CPU governor.
- **Schema:** 100 customers, 50 products, 200 orders, 500 order-items, 20 categories.
- **Iterations:** 20 per category; INSERT single / DELETE point / UPSERT also run 30×.
- **Prev vs Current is the fair comparison.** A third "Documented" column shows earlier debug-build numbers for context only — do not read across the two.

## 35-Category Comparison (avg per query)

The full table. **Bold** rows improved over v3.6.0. ⚠ marks a caveat worth reading in context.

| # | Category | Documented (debug) | Prev v3.6.0 (release) | Current v3.6.1 (release) | v3.6.1 vs Prev |
| --- | --- | --- | --- | --- | --- |
| 1 | CREATE TABLE | 160 µs | 72.5 µs | **71.5 µs** | −1.4% |
| 2 | CREATE INDEX | 49 µs | 194.5 µs | **180.0 µs** | −7.5% |
| 3 | ALTER TABLE | 81 µs | 42.4 µs | **42.0 µs** | −1.0% |
| 4 | DROP TABLE | 160 µs | 27.0 µs | **26.5 µs** | −1.9% |
| 5 | CREATE/DROP VIEW | 149 µs | 105.3 µs | 107.0 µs | +1.6% |
| 6 | REFRESH MATERIALIZED VIEW | 289 ms | 56.0 ms | **43.8 ms** | **−21.8%** |
| 7 | TRUNCATE | 229 µs | 417.5 µs | **411.5 µs** | −1.4% |
| 8 | INSERT single row | 243 µs | 33.2 µs | 37.3 µs | +12.3% |
| 9 | INSERT multi-row VALUES | 803 µs | 175.5 µs | 234.0 µs | +33.3% ⚠ |
| 10 | INSERT…SELECT | 404 ms | 64.5 ms | **53.5 ms** | **−17.1%** |
| 11 | UPDATE point | 1.27 ms | 47.8 µs | 48.0 µs | +0.4% |
| 12 | DELETE point | 333 µs | 52.7 µs | **33.7 µs** | **−36.1%** ✅ |
| 13 | UPSERT (ON CONFLICT) | 123 ms | 679.7 µs | **662.3 µs** | −2.6% |
| 14 | UPDATE with subquery | 2.03 s | 392.5 ms | **315.0 ms** | **−19.7%** |
| 15 | Point lookup | 421 µs | 63.5 µs | 68.5 µs | +7.9% |
| 16 | Full scan + filter | 1.13 ms | 17.1 µs | 17.8 µs | +4.1% |
| 17 | Aggregation | 1.63 ms | 41.4 µs | **34.9 µs** | **−15.7%** |
| 18 | INNER JOIN | 4.62 ms | 396.5 µs | **281.5 µs** | **−29.0%** |
| 19 | LEFT JOIN | 4.64 ms | 353.0 µs | **252.5 µs** | **−28.5%** |
| 20 | Multi-table JOIN (4) | 15.13 ms | 703.5 µs | **470.5 µs** | **−33.1%** |
| 21 | Scalar subquery | 42.10 ms | 36.2 µs | **29.7 µs** | **−18.0%** |
| 22 | EXISTS subquery | 5.50 ms | 70.5 µs | **60.0 µs** | −14.9% |
| 23 | IN subquery | 3.52 ms | 692.5 µs | **365.5 µs** | **−47.2%** |
| 24 | CTE | 6.85 ms | 1.28 ms | **810 µs** | **−36.7%** |
| 25 | Recursive CTE | 2.01 ms | 48.8 µs | **35.1 µs** | **−28.1%** |
| 26 | Window functions | 1.22 ms | 122.5 µs | 83.0 µs ⚠ | −32.2% error-path |
| 27 | UNION | 2.73 ms | 31.7 µs | **25.5 µs** | **−19.7%** |
| 28 | DISTINCT | 1.54 ms | 38.1 µs | **21.1 µs** | **−44.7%** |
| 29 | ORDER BY + LIMIT | 2.18 ms | 211.0 µs | **147.5 µs** | **−30.1%** |
| 30 | CASE expressions | 1.49 ms | 46.9 µs | **33.6 µs** | **−28.4%** |
| 31 | JSON-like filter | 1.26 ms | 39.7 µs | **27.9 µs** | **−29.7%** |
| 32 | LIKE / BETWEEN / IN | 1.90 ms | 20.3 µs | **14.5 µs** | **−28.6%** |
| 33 | Transaction control | 231 µs | 58.0 µs | **47.0 µs** | **−19.0%** |
| 34 | Prepared statements | 182 µs | 151.5 µs | **142.0 µs** | −6.3% |
| 35 | SET / SHOW / RESET | 125 µs | 18.9 µs | **17.4 µs** | −7.9% |

*Legend: bold = improvement; ⚠ = caveat; ✅ = flagship win. "Documented" is a prior debug-build number shown for context — the fair comparison is Prev vs Current, both release.*

## Deep-Dive 1: DELETE Point-Lookup Fast Path

Before v3.6.1, every `DELETE FROM t WHERE …` walked the full table and applied the predicate in an in-memory evaluator loop. On a 30-row fixture it looked cheap. On realistic sizes, the cost was O(N) per DELETE — and it showed up the moment a workload hit a table of any real size.

The comprehensive benchmark fixture (1000-row table) exposed the real cost:

```
=== Delete Performance (v3.6.0, pre-fix) ===
Single row deletes (100 ops): 773.5 ms
Bulk delete (1 stmt, 500 rows): 6.8 ms
Per-row: 7.73 ms

=== Delete Performance (v3.6.1, post-fix) ===
Single row deletes (100 ops): 4.47 ms
Bulk delete (1 stmt, 500 rows): 4.24 ms
Per-row: 44 µs

--> 175x improvement for per-row DELETE on a 1000-row table.
```

A dedicated bench on a 10,000-row table reports **34 µs/op** — effectively a hash-lookup, as expected for an indexed column hit.

The fix, at the level of observable behaviour: DELETE now attempts an index-backed point scan first (the same fast path previously used by UPDATE), falls back to predicate pushdown when a scan isn't a direct match, and only performs a full table scan when neither applies. The parameter-binding path for prepared-statement DELETEs now runs through the same pushdown step, so queries like `DELETE FROM users WHERE id = $1` now hit the index instead of silently bypassing it.

```
-- This is now a point-lookup (indexed column).
DELETE FROM users WHERE id = 42;

-- Parameterised form: also a point-lookup in v3.6.1.
PREPARE del (int) AS DELETE FROM users WHERE id = $1;
EXECUTE del(42);

-- Still a scan (no index on status).
DELETE FROM events WHERE status = 'expired';
```

A new regression test pins DELETE point-lookup under 500 µs on a 10K-row table.

## Deep-Dive 2: TRUNCATE Batched Delete

v3.6.0 TRUNCATE deleted keys one at a time — one index write-lock and one WAL fsync *per key*. It also cloned the trigger registry on every call, roughly 20 µs of pure overhead even when no TRUNCATE triggers were defined for the table.

v3.6.1 TRUNCATE does two things differently:

1. Short-circuits the trigger path when no Before- or After-TRUNCATE triggers are registered for the target table. No clone, no iteration, no cost.
2. Scans the prefix and issues batched writes in 1024-key chunks. One WAL sync per chunk instead of one per key.

The 35-category benchmark shows only a marginal gain on TRUNCATE (417 µs → 411 µs) because each iteration's wall time is dominated by 10 populating INSERTs, not the TRUNCATE itself. On TRUNCATE-heavy workloads — session-table clearing, test-fixture reset, stateful job queues — the gain is proportional to the table size. On a 10K-row table in local testing the operation drops from **~65 ms to ~1 ms**. That isn't in this suite's numbers; we are flagging it as the shape of the improvement, not the measured point.

## Deep-Dive 3: CREATE INDEX and the "49 µs" Documented Number

A question we get from customers reading the benchmark doc: *why is CREATE INDEX in v3.6.1 slower than the documented 49 µs?*

The "49 µs" predates a correctness fix that landed in v3.5.8. Before that fix, CREATE INDEX was a **no-op for non-HNSW indexes** — the index entry was never created. The 49 µs measured parse + plan + discard, not index construction.

Starting in v3.5.8, CREATE INDEX genuinely:

1. Allocates an ART (Adaptive Radix Tree) secondary index.
2. Backfills by scanning the source table and inserting each row's key.
3. Registers the index in the catalog.

On the benchmark's 100-row `customers` table, the 180 µs for one CREATE + one DROP (pair per iteration) works out to ~90 µs per DDL, or **~0.9 µs per row backfilled** — consistent with ART insert performance. The "+297% vs doc" figure is not a regression; it is the cost of actually building an index. The baseline was free because it wasn't doing the work.

We do not plan further optimisation here unless the backfill itself becomes a measurable cost — the current 100-row fixture is too small to expose it.

## Caveats & Open Items

1. **Window functions error path.** The benchmark query uses `SUM()` as a window function, but the current window layer supports only ranking / offset / positional functions: `ROW_NUMBER`, `RANK`, `DENSE_RANK`, `NTILE`, `LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`, `NTH_VALUE`, `PERCENT_RANK`, `CUME_DIST`. The "83 µs" shown in row 26 is error-path overhead, not a genuine window-function execution cost. Extending window support to aggregate functions is an open gap, tracked.
2. **UPDATE…WITH subquery still slow.** Down from 392 ms to 315 ms (−20%), but not close to "fast". A prior fix moved the subquery materialisation outside the per-row loop, but the scan it wraps still dominates. Further work is scoped but not landed.
3. **Debug-build "Documented" column is not a target.** It is a debug-build reference that predates release-build measurements. Do not read across the two — the fair comparison is the two release columns.
4. **Two multi-row INSERT fixtures regressed.** `INSERT single row` is +12% and `INSERT multi-row VALUES` is +33%. Shape suggests cold-start fixture noise, but we want to re-measure with a warmer harness before declaring. Flagged as "investigate next session, do not block release."
5. **Single-host, in-memory backend.** Persistent-mode numbers live in the same benchmark binary but are reported separately under a different deployment heading.

## Changelog vs v3.6.0

- **DELETE:** three DELETE handlers now attempt an index-backed point-lookup before falling back to predicate pushdown, then to a full scan. The prepared-statement DELETE path now resolves parameters through the same pushdown step, so `DELETE … WHERE id = $1` uses the index.
- **TRUNCATE:** batched backend writes in 1024-key chunks (one WAL sync per chunk); trigger-existence check added before the trigger-registry clone.
- **Query engine:** plan-cache now bounded via LRU; short-circuit evaluation for `AND` and `OR`.
- **Tests:** a new regression test pins the DELETE point-lookup fast path at under 500 µs on a 10K-row table. Covered by `cargo test --release`.

## What's Next

- Re-measure the INSERT-single and INSERT-multi-row fixtures on a warmed harness to confirm or repair the +12% / +33% regressions.
- Further work on UPDATE…WITH subquery to push the scan into the materialised side.
- Extend window-function support beyond ranking/positional functions so the benchmark's SUM-as-window case produces a real number.
- Publish persistent-mode numbers (currently measured but not in this report).

Full 35-category comparison data, reproducibility, and the diff history live in the Lite repository. HeliosDB Lite remains source-available under SSPL-1.0.