Telemetry arrays that outgrow the 20 MB document
Fleet-monitoring SaaS on Couchbase 7.x. Bucket prod, scope iot, collection devices. One document per unit, key device::<serial>, holding metadata plus a readings array. 2,100 units report every 15 minutes; the ingest worker reads the document, appends a ~230-byte sample, and replaces it with a CAS check. After 60 days a typical document is 1.3 MB, about 40 units that were misconfigured to report every minute are already above 8 MB, and one is at 14 MB and still growing. A health-check worker also writes the same documents every 5 minutes to refresh lastSeen, firmware, and signalStrength. During network blips the ingest workers log bursts of CAS mismatch errors and retry in a tight loop, amplifying load. Two queries matter: the last 24 hours of samples for one device, and hourly averages across the whole fleet for the last 7 days. A proposal on the table is to enable compression and keep the current shape.
1. Diagnose the failure modes of the current document shape and propose a target model.
Read the worked answer
What is actually breaking
- Size: a unit writing one 230-byte sample per minute accumulates ~331 KB/day, so it crosses the 20 MB per-item ceiling in roughly two months. Compression buys weeks, not a design.
- Contention: read-modify-replace with CAS means every writer must win the whole document. Two writers plus retry loops produce
CasMismatchExceptionbursts, and each retry re-reads 1.3 MB. - Amplification: appending 230 bytes rewrites 1.3 MB, re-indexes it, and later compaction rewrites it again. Cost scales with document size, not with the data added.
Target model
device::<serial>- small, mutable metadata (lastSeen,firmware,signalStrength,reportIntervalSec). Health checks write only this, so they never collide with ingest.reading::<serial>::<yyyyMMddHH>- one document per device per hour holding that hour's samples. Immutable once the hour closes; the open bucket is appended to withmutateIn(...).arrayAppend(...), which is atomic server-side and avoids CAS entirely.- Optional
daily::<serial>::<yyyyMMdd>rollups written by the ingest worker so fleet aggregates never touch raw samples.
Every document is then bounded at tens of KB regardless of uptime, and the write path becomes an append instead of a read-modify-write. The cost is more keys and a client-side decision about which bucket to write - trivial next to a hard ceiling.
2. How do the two dashboard queries change, and what index do you create for each?
Read the worked answer
Last 24 hours for one device With hourly buckets the client knows the key pattern, so either:
SELECT * FROM readings USE KEYS [...]for the 24 keys (fastest, no index needed), or- store
serialandbucketStartin each document and queryWHERE serial = $s AND bucketStart >= $twithCREATE INDEX idx_serial_start ON readings(serial, bucketStart).
The composite index has the equality key first and the range key second, giving one contiguous range scan per device. Keeping the old array design and adding DISTINCT ARRAY r.ts FOR r IN readings END is possible in principle, but that index is nearly as large as the data, must be updated on every append, and still cannot answer the fleet-wide aggregation.
Hourly fleet averages for a week
Do not compute this from raw samples: 2,100 devices × 96 samples/day × 7 days ≈ 1.4 million samples, all of which N1QL would have to UNNEST and group. Write rollups at ingest time into daily::<serial>::<date> (per device per day: one small array of 24 hourly sums/counts), then the dashboard runs GROUP BY over 2,100 × 7 small documents. If per-hour resolution is required, use hourly rollups instead.
Principle: query shape drives the model - key-range scans for detail, pre-aggregated documents for analytics.
3. A colleague argues that switching to sub-document `arrayAppend` removes the need to remodel. Assess that position.
Read the worked answer
What the sub-document API does buy you: the append is applied server-side to the stored document, so the client sends a few hundred bytes and never transmits or CASes 1.3 MB. CAS mismatches against the health-check writer disappear because the two writers touch different paths. That is a real improvement to the ingest path.
What it does not buy you:
- The 20 MB item limit still applies. The misconfigured units still hit it, and now the append fails at the ceiling instead of a replace failing - equally fatal to ingestion.
- Reads still fetch the whole array, so the 24-hour dashboard read pulls 1.3 MB to display a few hundred samples.
- Compaction and indexing still churn a ~2.7 GB bucket of large items, so disk cost stays proportional to document size rather than to data added.
- Every append still bumps the document's CAS and metadata, so the item keeps moving through cache and the indexer.
Verdict: adopt arrayAppend as a stopgap this sprint, fix reportIntervalSec on the misconfigured units immediately, and migrate to hourly buckets before the next hardware refresh. The sub-document API makes the current shape survivable; it is not the target model, because it leaves both the ceiling and the read amplification in place.