Optimization Roadmap: Timeseries Query Engine + Multi-Model Considerations

[emanzx]

NodeDB Optimization Opportunities

NodeDB is not a timeseries-only DB. It's a hybrid multi-model database.
Optimizations must account for all workloads sharing the same process.

Current Architecture

One NodeDB binary handles:
├── Timeseries  (columnar memtable, ILP ingest, TS partitions)
├── Documents   (CRDT-backed, Loro)
├── Graph       (node/edge traversal)
├── Vector      (HNSW embeddings)
├── Spatial     (H3 hexagonal index)
├── KV          (key-value with WAL)
└── Event Plane (CDC, triggers, WASM UDFs)

All sharing the same memory, same WAL, same SPSC bridge, same Data Plane cores.

Memory Profile (observed during 10M benchmark)

Operation	RSS
Ingest (steady state)	~150MB
After query battery	1,976MB - 4,974MB

The 150MB idle footprint is excellent. The query spike is the optimization target.

---

Optimization 1: Streaming Aggregation

Impact: Query memory 5GB → ~500MB

Currently loads all partitions into memory for a GROUP BY. At 100M rows that could be 10-20GB.

Current:  load ALL partitions → aggregate → output         (O(all data) memory)
Better:   load partition 1 → partial aggregate → drop →
          load partition 2 → merge → ... → output          (O(1 partition) memory)

This matters even more for multi-model — if a vector search is running alongside a timeseries GROUP BY, both competing for RAM.

---

Optimization 2: Time Range Partition Pruning

Impact: 2-10x faster for dashboard queries

Each partition has min_ts / max_ts in metadata. A query like:

SELECT time_bucket('5 min', timestamp) AS b, COUNT(*)
FROM dns_bench
WHERE timestamp > NOW() - INTERVAL '1 hour'
GROUP BY b ORDER BY b

Should only read 1-2 partitions, not all 20. Most real-world dashboard queries have a time range filter. Currently all partitions are scanned regardless.

---

Optimization 3: Column Projection Pushdown

Impact: Less disk I/O, less memory

If a query only needs qtype and COUNT(*), don't read client_ip, qname, elapsed_ms columns from disk. Each column is stored as a separate .col file — only open and decompress the columns referenced in the query.

The PR #10 mentioned projection pushdown but the 2-5GB RSS during queries suggests it's loading more than necessary.

---

Optimization 4: Parallel Partition Scan

Impact: 2-3x faster queries

Currently timeseries queries appear to run on a single Data Plane core. Partition scans could be parallelized across the 3 Data Plane cores — each core scans different partitions, merge results.

Current:  Core 0 scans partition 1, 2, 3, ... 20 (serial)
Better:   Core 0 scans 1-7, Core 1 scans 8-14, Core 2 scans 15-20 (parallel)
          → merge partial accumulators

---

Optimization 5: Tiered Memory Budgets

Impact: Multi-model stability

Since multiple engines share one process, a memory budget system would prevent one workload from starving others:

Total budget: 6GB
├── Timeseries queries: max 2GB
├── Vector search (HNSW): max 1GB
├── Document ops: max 512MB
├── Graph traversal: max 512MB
└── OS/runtime: 2GB

Without this, a 100M-row GROUP BY could starve a concurrent vector search or document query.

---

Optimization 6: OS Page Cache Management

Impact: Better multi-model coexistence

NodeDB reads partition files via sequential I/O. After a query, the OS page cache holds decompressed data. If a document or vector workload then needs memory, the OS evicts those pages — next timeseries query reads from disk again.

Options:

• madvise(MADV_SEQUENTIAL) for scan queries
• madvise(MADV_DONTNEED) after aggregation to release pages proactively
• Let the multi-model scheduler hint which workload is priority

---

Optimization 7: Ingest Throughput — Batch SPSC Dispatch

Impact: Potentially 85K/s → 600K+/s

Currently each ILP batch goes through SPSC one-at-a-time. Batching multiple ILP lines into a single SPSC message would reduce dispatch overhead. In earlier tests with larger batches (before WAL fix), we saw 685K/s.

---

Optimization 8: Materialized Aggregates

Impact: Instant dashboard queries

The continuous aggregate feature in the codebase (nodedb/src/engine/timeseries/continuous_aggregate) could pre-compute common GROUP BY and time_bucket results on flush:

CREATE CONTINUOUS AGGREGATE hourly_dns AS
  SELECT time_bucket('1 hour', timestamp) AS bucket, qtype, COUNT(*)
  FROM dns_bench
  GROUP BY bucket, qtype;

Dashboard queries would read pre-computed results instead of scanning raw data.

---

Optimization 9: WAL Isolation for Multi-Model

Impact: Workload isolation

The WAL is shared across all engines. A heavy timeseries ingest (100M rows) generates massive WAL segments that slow down WAL replay for KV and document operations. Per-engine or per-collection WAL segments would isolate workloads.

---

Optimization 10: WASM UDF Edge Computation

Impact: Unique competitive advantage

The Event Plane with WASM UDFs opens a unique optimization: push aggregation logic into UDFs that run at ingest time. Instead of storing 100M raw rows and aggregating at query time, compute the aggregates during ingest.

No other timeseries DB has this capability. This could make certain dashboard queries effectively zero-latency.

---

Priority Matrix

Priority	Optimization	Impact	Effort	Why First
1	Streaming aggregation	Query memory 5GB → ~500MB	Medium	Blocks 100M+ queries today
2	Time range partition pruning	2-10x faster dashboards	Low	Most real queries have WHERE time > X
3	Column projection pushdown	Less disk I/O, less memory	Low	GROUP BY qtype shouldn't read elapsed_ms
4	Parallel partition scan	2-3x faster queries	Medium	Use all 3 Data Plane cores
5	Tiered memory budgets	Multi-model stability	High	Prevents one engine starving others
6	OS page cache management	Better coexistence	Low	Quick madvise hints
7	Batch SPSC dispatch	85K → 600K+ ingest	Medium	Already proven possible
8	Materialized aggregates	Instant dashboards	High	Feature already in codebase
9	WAL isolation	Workload isolation	High	Matters at multi-model scale
10	WASM edge computation	Unique differentiator	High	Long-term competitive moat

The first 3 would close most of the gap with ClickHouse while keeping NodeDB's multi-model advantage.

[farhan-syah]

Most of the items are already done at 8b3efc7 point