feat(kv): high-throughput bulk-insert path for the KV store (COPY-based)

[StefanSteiner]

Summary

The KV store's write path (set / set_batch) tops out around 1,000–2,000 inserts/sec because every key goes through an UPDATE-then-conditional-INSERT upsert over the extended-query (text SQL) protocol. For workloads that need to load millions of new KV entries quickly, this is the bottleneck. The repo already has a binary-COPY Inserter that hits 22M+ rows/sec, so a bulk-insert path for the KV store is feasible — but it interacts with a correctness constraint that shapes the whole design.

This issue captures the design so it can be prioritized if real use cases surface. Not scheduled work today.

Background: why set_batch is slow

• set performs a manual upsert: it always issues UPDATE {t} SET value = $3 WHERE store_name = $1 AND key = $2, and only when the UPDATE affects 0 rows issues a guarded INSERT INTO {t} (store_name, key, value) SELECT $1, $2, $3 WHERE NOT EXISTS (SELECT 1 FROM {t} WHERE store_name = $4 AND key = $5) (hyperdb-api/src/kv_store.rs:187-208). So a new key costs 2 round-trips.
• set_batch wraps all per-key upserts in one BEGIN/COMMIT (kv_store.rs:398-416), which helps, but it's still 1–2 text-SQL statements per key. That per-row overhead is the ceiling.

The correctness constraint (this is the crux)

The backing table _hyperdb_kv_store(store_name TEXT NOT NULL, key TEXT NOT NULL, value TEXT) has no PRIMARY KEY, no UNIQUE, no index — Hyper rejects all of them (0A000: Index support is disabled). Uniqueness of (store_name, key) is therefore an application-side invariant, enforced only by the UPDATE-then-conditional-INSERT ... WHERE NOT EXISTS guard (kv_store.rs:62-75 DDL, :187-208 guard).

A raw binary-COPY bypasses that guard entirely (COPY is pure append-only — no UPDATE, no conflict handling). If duplicate (store_name, key) rows ever land in the table, the read side silently returns wrong results:

Method	Behavior under duplicate keys
get / get_as	returns one arbitrary value (no LIMIT/ORDER BY/aggregate)
size()	inflated — COUNT(*) counts physical rows, not distinct keys
keys()	returns duplicate keys (no DISTINCT)
pop()	returns one value, but its DELETE removes all duplicate rows for that key — silently drops the others
exists()	still correct (only checks for ≥1 row)

So the design question is not throughput — it's who guarantees the keys are new and unique. Also note: COPY is TCP-only (inserter.rs:135-139), not available over gRPC.

Proposed options

The two options below are complementary, not mutually exclusive — a real use case can decide which to prioritize (or both).

Option A — load_new: maximally fast, caller guarantees uniqueness

Add an append-only bulk method that writes straight through the Inserter. Inserter::from_table(conn, KV_TABLE) (inserter.rs:192) picks up the existing 3-column schema from the catalog — no new DDL, no schema change — and add_str covers all three TEXT columns.

/// Appends entries via the binary COPY path (orders of magnitude faster than `set_batch`).
///
/// **Caller contract:** every key MUST be new and unique within this store — both
/// absent from the store already AND distinct within `entries`. This path does NO
/// upsert and NO duplicate check; violating the contract inserts duplicate rows,
/// which silently corrupt `get`/`size`/`keys`/`pop`. Use `set_batch` if keys may collide.
pub fn load_new(&self, entries: &[(&str, &str)]) -> Result<u64>

• Internally: Inserter::from_table → per entry add_str(store_name); add_str(key); add_str(value); end_row() → execute().
• Name deliberately signals append-only / new-keys-only semantics at the call site (not set_batch_fast).
• Sync + async twins (async via AsyncArrowInserter / the async COPY path).
• Trade-off: fastest possible, but a contract violation is silent corruption — squarely the caller's responsibility.

Option B — safe bulk merge: fast, dedup-preserving, no caller contract

Keep the uniqueness invariant intact by staging + merging:

1. COPY the batch into a temporary staging table via the Inserter.
2. INSERT INTO _hyperdb_kv_store (store_name, key, value) SELECT ... FROM stage s WHERE NOT EXISTS (SELECT 1 FROM _hyperdb_kv_store t WHERE t.store_name = s.store_name AND t.key = s.key) — and dedup within the staging batch itself (e.g. DISTINCT ON).

• Trade-off: one extra pass so not raw-COPY speed, but far faster than millions of per-key upserts, and it cannot create duplicates. A good default helper for callers who can't promise clean keys.
• Could be surfaced as a helper API alongside load_new.

Recommendation / decision guidance

• One-shot bulk load of millions of unique rows you'll query with SQL → arguably skip the KV store entirely: create a normal typed table and use the Inserter directly. Full COPY throughput, real column types, no store_name overhead, and no uniqueness trap (you're not pretending it's a KV store). The KV store's value is the handle semantics (get/set/pop/exists over a named store) — a bulk analytical dump uses none of that.
• Data must remain a live KV store you'll get/pop against afterward → load_new (Option A) if the caller can guarantee new/unique keys, or the safe merge (Option B) otherwise.

Scope notes

• No schema change and no change to existing methods required for either option.
• TCP-only (COPY constraint) — document it; gRPC connections can't use this path.
• Natural fit for a post-M2 milestone; prioritize when concrete use cases appear.

Filed from an investigation of the KV write path, the COPY Inserter API, and the KV read-side invariants. File:line references are against main at time of filing.