Modeling for the queries you actually run

Overview

An identity services provider had modeled Cassandra tables the way it would have modeled relational ones, then added secondary indexes to cover the queries the primary key did not serve. Some partitions had grown into the gigabytes, and index-backed queries fanned out to every node in the ring. AceMQ was engaged to redesign the model.

Challenge

Cassandra rewards modeling around query patterns and punishes modeling around entities. The redesign had to bound partition growth, keep the platform's dominant queries served by a single replica set, and be adoptable incrementally — the provider could not stop writes to rebuild tables wholesale.

Environment

Apache Cassandra on Kubernetes backing identity verification and credential lifecycle services.

Approach

AceMQ inventoried every query the application issued and worked backward to the tables required to serve them, accepting denormalization where it removed fan-out. Partition sizing was projected against several years of growth rather than current volume, and the migration was designed to run with dual writes so cutover carried no window.

Solution

• Inventoried actual application query patterns and derived the required table designs from them
• Introduced time and hash bucketing to bound partition growth against multi-year projections
• Replaced secondary index queries with purpose-built denormalized tables to eliminate cluster-wide fan-out
• Chose clustering keys and ordering to serve range scans and pagination without in-memory sorting
• Designed a dual-write and backfill migration path so the cutover required no write freeze
• Set partition size and row count guardrails with monitoring to catch regressions in future development

Outcome

Technologies