Katkat
Back

Optimize User-and-Region Queries on a Million-Row Table

J.P. MorganSoftware Engineer II · Onsite
MediumSoftware Engineer IIOnsiteSystem Design

Last updated

Evaluate and design database access patterns, indexing strategies, and scaling techniques (partitioning, sharding, replication) for a financial user-profile system with high regional skew and frequent lookups.

Scenario

You are designing the data access tier for a global corporate banking portal. The core entity is a user_profiles dataset currently containing 5 million user records, growing by 50,000 records each month. Queries frequently need to fetch user profiles by (user_id, region) or look up all active users within a given region.

Your task is to present an end-to-end database design and optimization roadmap that addresses current bottlenecks and anticipates future scale.

Requirements

Functional Requirements

  • Point lookups: Fetch full profile given a user_id and region (e.g., login authentication and entitlement checks).
  • Regional scans: Fetch or aggregate users belonging to a specific region with optional status filtering.
  • Updates: Users update preference and compliance fields; roughly 500 writes/sec peak across all regions.

Non-Functional Requirements & Scale

  • Initial dataset: 5 million rows (~5 GB data volume).
  • Read throughput: 10,000 queries per second (QPS) peak (90% point lookups, 10% regional batch scans).
  • Latency: p99 read latency < 10 ms for point lookups.
  • Skew: 65% of queries and data belong to region = 'NA', 25% to region = 'EMEA', and 10% to region = 'APAC'.
  • High availability with read-after-write consistency for profile updates.

What Your Solution Should Cover

  1. Index Strategy: Define the exact composite B-tree index column ordering ((user_id, region) vs (region, user_id)) and justify your choice based on access patterns and selectivity.
  2. Partitioning: Evaluate table partitioning (e.g., list/hash by region or range). At what dataset size does partitioning become necessary versus index-only optimizations?
  3. Horizontal Scaling: Compare application-level sharding vs. read replicas. How do you handle regional skew without creating write or memory hotspots?
  4. Trade-offs: Discuss operational overhead, index maintenance costs during high-throughput writes, and consistency models when replicating across geographic regions.

Hints

Hint 1

Consider column cardinality and whether the primary key already includes user_id; if user_id is globally unique, the role of region in the index changes.

Hint 2

A million rows (and even 5 million rows) fits entirely in buffer pool memory for modern database instances, making partitioning premature for pure query latency.

Hint 3

Evaluate whether read replicas with regional routing or a distributed cache (e.g., Redis) resolves read hotspots more simply than horizontal sharding.

Follow-up questions

  • If user_id is a globally unique UUID, what are the trade-offs of including region in the primary index versus keeping it as a standard secondary index?
  • How does the 65% traffic skew toward North America affect your decision to shard by region versus sharding by a hash of user_id?
  • If regulatory mandates (e.g., GDPR) require data residency within the specific geographic boundary, how does your architecture evolve from a centralized database with replicas to multi-region primary clusters?