backend/docs/adr/0002-why-five-databases.md

ADR-0002: Use 5 specialized databases instead of one general-purpose store

• Status: Accepted
• Date: 2026-06-21
• Decider: @cryptorugmunch

Context

RMI stores data across multiple categories:

Category	Volume	Access pattern	Query shape
User data, wallets, alerts (relational)	252K rows	CRUD + joins	SQL
Cache, rate limits, labels (key-value)	82K keys	O(1) get/set	key lookup
Analytics, cost tracking, legacy logs (columnar)	83K rows (will grow)	Aggregate scans	SQL OLAP
Wallets, tokens, transfers (graph)	20K nodes	Multi-hop traversal	Cypher
Embeddings, semantic search (vector)	75 vectors	k-NN similarity	HNSW

A single Postgres would not serve all five patterns efficiently:

• Graph queries in Postgres require recursive CTEs that scale O(n²)
• Vector search in Postgres uses pgvector but is 10× slower than Qdrant
• OLAP scans in Postgres lock transactional tables
• Cache in Postgres requires manual eviction policies

Decision

Use five specialized databases:

1. Postgres 16 — source of truth (relational data)
2. Redis 7.2 — cache, rate limits, queues
3. ClickHouse — OLAP, cost tracking, deprecation logs
4. Neo4j 5 — graph queries (cross-chain wallet flows)
5. Qdrant — vector search (RAG semantic retrieval)

Plus DuckDB for local analytics on the laptop (single-file, no server).

Alternatives Considered

• All Postgres (with extensions): pgvector + recursive CTEs + pg_partman. Rejected — graph perf unacceptable, OLAP competes with transactional load.
• Single MongoDB: Rejected — weak analytics, no vector search, no graph.
• DynamoDB + S3 + Neptune: Rejected — vendor lock-in, high cost, no vector.
• Drop Neo4j, use ClickHouse: Possible. Rejected for now — graph queries on 84K nodes are O(seconds), not O(minutes).

Consequences

• Positive: Each DB does one job well. Optimized for its access pattern. Can scale independently.
• Negative:
  • 5 things to back up, monitor, patch, version
  • New engineer onboarding: must learn 5 DBs
  • Cross-DB queries require application-level joins
  • 5 different query languages (SQL, Redis commands, SQL/CH, Cypher, REST)
• Mitigations:
  • DataBus facade unifies access (app/databus/core.py)
  • Container memory limits set on all 5 DB containers
  • Each DB has a runbook in docs/runbooks/

Re-evaluation triggers

• Neo4j license change (currently community edition, free)
• Postgres + pgvector catching up to Qdrant perf (current gap: ~10×)
• ClickHouse memory pressure requiring Memgraph substitution