NoSQL decision boundaries — document, key-value, columnar, graph
When each store earns its ops cost — and the one boundary you actually crossed (pgvector).
Concept → your projects
The decision framework for "should this data leave Postgres":
| Store | Shape | Killer workload | Your verdict |
|---|---|---|---|
| Key-value (Redis/Dynamo) | opaque blobs by key | sub-ms reads, TTLs, ephemeral state | traverse: cache + queues (Redis) ✓ |
| Document (Mongo) | self-contained JSON docs | schema-flexible aggregates, one-document reads | not needed — Postgres JSONB covers zoth's policies |
| Wide-column (Cassandra/Scylla) | partition-key rows, LSM | massive append-only writes, sparse rows | not needed — submissions are write-moderate |
| Columnar (ClickHouse/Redshift) | column-organized, vectorized scans | analytics over billions of rows | not needed — stats at 100 users (Day 70) |
| Graph (Neo) | vertices + edges | multi-hop variable traversals | removed — Day 6/38 story |
| Vector (pgvector/Pinecone) | embeddings + ANN | semantic similarity | crossed — inside Postgres, via extension |
The two-layer framework to recite: (1) access pattern — what queries, at what rate; (2) ops envelope — what the budget can run. A store must win on (1) and fit (2). Every "we need Mongo" that fails (1) is fashion; every "we need it" that fails (2) is a staffing plan disguised as architecture.
Why Postgres JSONB beat a document DB for zoth's policies: the "document" (match_conditions/access_rules/approval_config) is read as part of a relational query (join with tenant, order by priority), validated at the service layer, and never stored independently of its row. JSONB gives document flexibility with transactional joins — the best of both for config-shaped data. The line where Mongo wins: the document is the unit of retrieval, schemas diverge per-customer, and you never join.
Redis in traverse — the KV verdict done right: cache (TTL'd blobs by key) + BullMQ (lists/streams as job storage) + rate-limit counters (future). Redis is ephemeral-by-policy (noeviction for queue keys — Day 46's nuance) — no data lives only in Redis (submissions commit to Postgres first). That's the discipline: KV as an acceleration layer, not a system of record.
Columnar — the analytics boundary (Day 70 preview): your stats endpoints aggregate thousands of rows; Postgres does that in memory, instantly. Columnar earns its keep when scans hit billions of rows and compression + vectorization give 10–100×. Discord's trillions of messages → ScyllaDB is the canonical scale story — worth citing as the "what the same data looks like at 10^6× your scale" anchor.
The vector boundary — the one you crossed, and where it ends: pgvector + HNSW serves zoth's RAG at thousands of docs. The boundary is crossed when: index size exceeds RAM (HNSW graph on disk = slow), recall requirements demand specialized indexes (IVF-PQ, DiskANN), or QPS competes with OLTP on the same instance. Then: external vector DB (or dedicated Postgres replica). Your honest sizing: 768-dim × thousands of docs ≈ tens of MB — years of headroom.
Practice drill
"Log analytics for 10k services, 1 TB/day": write path favors LSM/wide-column or ClickHouse directly (batched appends, no updates); query path (dashboards, ad-hoc filters over time ranges) favors columnar. Answer: ClickHouse/S3-partitioned Parquet. The insight to state: choose by the query, not the data's name ("logs").
Bulletproof your resume
Say these answers aloud before moving on
These are the follow-ups an interviewer will actually ask about your projects.
You removed Neo4j but added pgvector. What's the difference in reasoning?
Both decisions are 'what are the access paths?' The org-chain traversal is one fixed, bounded walk — a recursive CTE serves it, so the graph store lost on ops cost with no query-pattern win. Semantic similarity is a genuinely different access pattern (ANN over high-dimensional vectors) that B-trees cannot serve — pgvector won because it provides a NEW access path without a new database (extension, same transactional store, same backup story). The unifying rule: add capabilities, not databases; delete databases whose access paths you can express elsewhere.
When would traverse's submissions outgrow Postgres?
Write volume is the trigger: Postgres comfortably does tens of thousands of inserts/s with batching, and we're at ~0.1/s. At real scale (say Discord-message-class), the progression is: partition submissions by month (cheap retention + pruning), then move the analytics-facing copy to columnar (ClickHouse) via CDC, keeping Postgres as the transactional source of truth for the current window. The mistake to avoid: jumping to Cassandra because 'write-heavy' — submissions need secondary indexes, transactions with Solve/Revision, and relational joins; LSM stores tax exactly those.
Why is JSONB in Postgres often better than MongoDB even for document-shaped data?
If documents live inside a relational model — joined to tenants, ordered by priority, transactionally updated with other rows — JSONB keeps the flexibility while inheriting ACID, joins, and one ops story. Mongo wins when documents are standalone aggregates with divergent schemas per customer and no join semantics. My policies are exactly the former: config documents whose lifecycle is row-lifecycle. The decision line: does the document join?