Ticking Big Data support

[stonesmi]

Design: In-Memory First, Optional Valkey Backend

1. Goals

• Primary: Keep the existing in-memory implementation as the default and fully functional on its own.
• Optional: Allow Valkey to be used as an alternative backend for specific datasets/grids.
• Explicit configuration only: No auto-routing, no implicit hybrid mode.
• No cross-backend joins: Any join must be executed entirely within a single backend (in-memory or Valkey).
• Single ingestion entrypoint into Valkey so consumer nodes can scale horizontally.

---

2. Operating Modes

2.1 Backend modes (per dataset / per grid)

Each dataset (or grid) is configured with one of:

• in_memory
  • Data stored and queried entirely in the application’s in-memory structures.
  • No dependency on Valkey.
• valkey
  • Data stored and queried entirely in Valkey.
  • Application nodes treat Valkey as the authoritative store for that dataset.

There is no auto mode and no mixing of backends for a single logical dataset or join.

2.2 Join constraints

• Only one-to-many left outer joins are supported.
• All tables participating in a join must:
  • Use the same backend (in_memory or valkey).
  • Be configured consistently at deployment time.
• No hybrid joins:
  • No joining an in-memory parent to Valkey children or vice versa.

---

3. Data Access Layer (DAL)

3.1 Purpose

Provide a single abstraction for grids to query data, regardless of backend.

3.2 Interface (conceptual)

• queryViewport(request) → ViewportResult
  • Inputs:
    • Dataset identifier
    • Sort specification
    • Filter specification
    • Join specification (if any)
    • Viewport/window (offset or cursor, size)
  • Output:
    • Rows
    • Total count
    • Paging/cursor info

3.3 Backend-specific engines

• InMemoryEngine

  • Uses existing in-memory collections and logic.
  • Implements sort, filter, join, and viewport in process.

• ValkeyEngine

  • Uses Valkey data structures and indexes.
  • Implements sort, filter, join, and viewport via Valkey queries.

3.4 Explicit routing

• DAL looks up the configured backend for the dataset:
  • If in_memory → call InMemoryEngine.
  • If valkey → call ValkeyEngine.
• No dynamic switching, no auto-detection.

---

4. Ingestion Architecture

4.1 Single ingestion entrypoint

Introduce an Ingestion Service that is the only writer to Valkey.

• Receives all upstream data (from producers, Kafka, etc.).
• Normalizes and validates records.
• Writes to:
  • Valkey for datasets configured as valkey.
  • Event stream / message bus for all datasets.

4.2 In-memory population

• Consumer nodes subscribe to the event stream.
• For datasets configured as in_memory:
  • Nodes update their local in-memory structures.
• For datasets configured as valkey:
  • Nodes may keep minimal caches (optional), but Valkey is authoritative.

4.3 Valkey optionality

• If Valkey is not deployed:
  • Ingestion Service simply does not write to Valkey.
  • Event stream still feeds in-memory datasets.
• If Valkey is deployed:
  • Ingestion Service writes to Valkey for valkey datasets.
  • In-memory datasets continue to be fed via the event stream.

---

5. Consumer Nodes and Scaling

5.1 Behavior

• Consumer nodes are responsible for:
  • Handling grid requests.
  • Calling the DAL with dataset ID and query parameters.
  • Rendering results.

5.2 Scaling with Valkey

• For datasets configured as valkey:
  • Consumer nodes are effectively stateless for those datasets.
  • Any node can serve any grid backed by Valkey.
  • Horizontal scaling is straightforward (add more consumer nodes).

5.3 Scaling without Valkey

• For datasets configured as in_memory:
  • Each consumer node maintains its own in-memory copy.
  • Scaling is limited by per-node memory and ingestion fan-out.
  • Still fully functional without Valkey.

---

6. Logic Migration to Valkey (Per Dataset)

For any dataset you choose to move from in-memory to Valkey:

6.1 Data modeling

• Map existing in-memory structures to Valkey data structures:
  • Base rows → hashes/documents keyed by primary ID.
  • One-to-many relationships → child rows with parent ID, and/or parent→child ID collections.
• Ensure all sortable, filterable, and joinable fields are indexed in Valkey.

6.2 Sorting

• Replace in-memory sort logic with Valkey-side sort queries.
• DAL’s ValkeyEngine:
  • Accepts sort spec.
  • Issues appropriate Valkey queries.
  • Returns sorted rows to the grid.

6.3 Filtering

• Replace in-memory filter logic with Valkey-side filter queries.
• DAL’s ValkeyEngine:
  • Accepts filter spec.
  • Executes indexed queries in Valkey.
  • Returns filtered rows.

6.4 Joins (one-to-many left)

• Implement join-friendly structures in Valkey:
  • Child rows keyed by child ID with parent ID field.
  • Optional parent→child ID collections for fast lookup.
• DAL’s ValkeyEngine:
  • Executes parent query (with sort/filter).
  • Fetches children for each parent.
  • Applies left outer semantics.

6.5 Viewport / windowing

• Implement windowed queries in Valkey:
  • Return only the requested slice (offset/cursor, size).
• Remove in-memory paging logic for datasets moved to valkey.

---

7. Configuration and Deployment

7.1 Dataset configuration

TODO

Non-Functional Requirements (NFR) Specification

Overview

Context: High-performance data system handling 100M+ rows.
Key Constraints: 5-Second SLA, P50 = 1 Second, Ad-Hoc Sorts/Filters, One-to-Many Left Joins.

---

1. Performance

NFR1 — Ad-Hoc Query Latency SLA

• P50: ≤ 1 second
• P95: ≤ 3 seconds
• P99: ≤ 5 seconds
• Applies to sorts, filters, and one-to-many left outer joins.

NFR2 — Sort Performance

• Supports numeric and text columns.
• Single column sorts.
• Must meet P50/P95/P99 latency targets.

NFR3 — Filter Performance

• Numeric ranges, text search, categorical filters.
• Compound predicates.
• Must meet P50/P95/P99 latency targets.

NFR4 — Join Performance

• One-to-many left outer joins only.
• Supports text join keys.
• Must meet P50/P95/P99 latency targets.

NFR5 — Data Update to Viewport Latency

• ≤ 500–1,000 ms end-to-end.

NFR6 — Index Update Performance

• Must keep pace with ingestion.
• No query backlog.
• Index freshness must be maintained for all sortable, filterable, and joinable fields.

---

2. Scalability

NFR7 — Horizontal Scalability

• Supports 100M → 1B+ rows.
• Maintains latency SLAs under scale.
• Scales with query concurrency and ingestion rate.

NFR8 — Storage & Index Scalability

• Supports 50+ columns per table.
• Indexes on all sortable/filterable/joinable fields.
• Predictable memory and storage growth.

---

3. Reliability & Fault Tolerance

NFR9 — High Availability

• 99.9% uptime.
• Automatic failover.
• Failover impact ≤ 5 seconds.

NFR10 — Data Durability

• Safe recovery after crashes or restarts.

---

4. Observability & Maintainability

NFR11 — Query Observability

• Metrics for P50/P95/P99 latency.
• Sort, filter, and join latency.
• Index update time.
• Query concurrency.
• Resource utilization.

NFR12 — Index & Join Health Monitoring

• Detect slow index updates.
• Detect stale or inconsistent indexes.
• Detect join hotspots.
• Detect skewed data distributions.
• Detect inefficient query plans.
• Detect partition or shard imbalance.

NFR13 — Configurability

• Tunable indexing strategies.
• Tunable join strategies.
• Memory limits, query timeouts, and cache sizes.
• Pre-fetch windows and concurrency limits.

---

5. Security

NFR14 — Access Control

• Authentication and authorization.
• Row-level restrictions.
• Secure handling of join operations involving sensitive data.

NFR15 — Audit Logging

• Logs for queries, sorts, filters, joins.
• Logs for data access.
• Logs for administrative actions.

---

6. Usability

NFR16 — Perceived Performance

• UI remains responsive even at 5-second backend latency.
• Skeleton loading appears instantly.
• Virtualized rendering prevents DOM overload.
• Partial results allowed.
• No UI lockups.