Quivio — Multi-Tenant SaaS CRM at Scale

The problem

Quivio’s customer-segmentation API powered live marketing campaigns, so it had to answer “which customers match these 65+ filter attributes right now?” against millions of rows. On the original architecture the write path (event ingestion) and read path (segmentation queries) shared one schema, one set of indexes, and one connection pool.

At low volume this was fine. At scale, three things broke at once:

• Read/write contention — write locks blocked segmentation reads; segmentation queries fought the write-optimised indexes.
• Latency — the segmentation API took up to 30 seconds, far too slow for real-time campaign triggers.
• A 6,634-line monolith made every deploy high-risk and coupled unrelated domains together.

Architecture

The fix was to separate concerns: a resilient event-ingestion pipeline feeding a purpose-built read model, with the two scaling independently.

flowchart TD
  A[Customer Events] -->|32 partitions| B[Azure Event Hub]
  B -->|BlobCheckpointStore| C[Ingestion Workers]
  C -->|Kubernetes HPA 10-20 pods| D[Processing Services]
  D -->|Statement Trigger| E[(PostgreSQL Write Model)]
  E -->|Atomic REFRESH CONCURRENTLY| F[(Materialized Views + GIN)]
  G[Segmentation API] -->|Index-only Scan| F
  F -->|Sub-100ms Response| G

The write path ingests from a 32-partition Event Hub with BlobCheckpointStore for zero-downtime partition rebalancing, processed by Kubernetes workers that autoscale 10–20 pods on load. The read path is a set of GIN-indexed Materialized Views refreshed atomically, so segmentation queries never touch the hot write tables.

Key decisions & trade-offs

Materialized Views over a distributed cache. Redis offered faster raw reads, but the 65+ combinatorial filter attributes would have required storing millions of key-sets in RAM, with complex TTL/eviction sync. PostgreSQL’s GIN indexing gave clean array-matching inside the transactional heap — automatic invalidation via triggers, no separate cache to keep coherent.

Migration-aware refresh. Daily bulk imports (1M+ rows) would have hammered the views. A debounced, window-based suppression strategy kept refreshes stable under heavy load instead of thrashing.

Monolith → 6 focused services. Decomposed the 6,634-line service along domain boundaries (not tech layers), raising test coverage to 150+ unit tests and cutting deployment blast radius.

Results

• Segmentation API: 30s → 100ms (300x) via covering GIN indexes on materialized views.
• Sustained 1M+ events / 5 minutes with 10x capacity headroom and zero-downtime rebalancing.
• 65% memory reduction and a 94.7% cache-hit rate from LRU-based tenant connection pooling with reference counting.
• Monolith decomposed into 6 services with 150+ tests — deploys went from risky to routine.