Hard Problems

Problem

Paginated queries timing out. Team had exhausted index tuning. Every optimization lever was maxed. Root issue: join happened before pagination. Materializing millions of rows, then limiting to 100, is cardinality inversion.

Solution

Reverse operation order: LIMIT from primary table first, then join only for those 100 rows. Changed cardinality from millions-in-join to 100-in-join.

Result

Response time: 3+ minutes → under 500ms (360x faster). Key insight: separates query tuning from query architecture.

Problem

Post-cutover response time spiked from seconds to 5+. Root cause: MySQL LOAD DATA holds locks for the entire operation. With 6 parallel threads per client × 100+ clients, hundreds of concurrent lock holders. Everything downstream waited for locks.

Solution

Reduce parallelism from 6 → 3 concurrent load threads. Released locks sooner. Allowed downstream queries to proceed.

Result

Response time: returned to baseline. CEO co-owned the recovery communication. Key insight: production scale ≠ staging scale.

Problem

Reco (old system) precalculated every 24 hours. If product went out of stock at hour 7, it appeared in recommendations for 17 more hours. Merchants saw broken recommendations, lost trust. CSMs complained (not in sprint planning). It was a churn driver.

Solution

Webhook → MySQL landing → Debezium → Pub/Sub. Real-time messages when product updates. Consuming services listen, update blocks in real-time. Max 5-minute lag instead of 24 hours.

Result

Complaints and churn disappeared. CSMs now escalate directly to you (organizational credibility shift). Key insight: listen to customers, not just tickets.

Problem

MySQL views for normalized serving. Theory: one view, consuming services query it. Reality: views materialize the entire dataset before applying filters. COUNT(*) on one client read millions of rows. Queries: 30+ seconds.

Two-Phase Approach

Result

Phase 2 achieved same latency as Phase 1 with different approach. Proved design was correct. Became the platform architecture. Key insight: normalize in consumers, not database.

Problem

Consuming service response time spiked. Certain queries slow, others normal. Root: query filtered on MongoDB field without index. Only intermittent because only certain queries hit the unindexed field.

Solution

Result

Monitoring now prevents similar regressions. Key insight: process gaps matter as much as technical fixes.

Problem

Team member optimized recommendation sorting: listen to real-time product changes, update ClickHouse on every change. Didn't understand ClickHouse's memory constraints. With millions of products and real-time traffic, database ran out of memory. Silent failure: reports looked correct but were wrong.

Solution

Result

Christmas sales unblocked. Process improved. Team member learned. Key insight: crisis management + mentorship under pressure.

First Incident

No explicit ORDER BY in segment API. BigQuery returns results in non-guaranteed order. Emails sent to wrong audience. Fixed with explicit ordering.

Second Incident (BQ → ClickHouse Migration)

Copied query from BigQuery to ClickHouse. Didn't realize ClickHouse's ORDER BY semantics differ (NULL handling, tie-breaking). Same problem: ordering changed. Same customer churned twice.

Systemic Fix

Three layers: (1) Producer maintains API contract and ordering guarantee. (2) Consumers validate incoming data matches expected ordering before sending emails. (3) Continuous QA validation across all services.

Result

Ordering validated continuously. Key insight: recognize when problems repeat, build systems to prevent it.

Problem

Segment comparison: identify new/removed/unchanged members. On BigQuery: full outer join (expensive but works). Migrated to ClickHouse. Hit memory limits: comparing 100M members requires O(n²) memory. Scaled up RAM. System showed success. But results were silently wrong.

Root Cause

Trying to move BigQuery's unlimited-memory join model into ClickHouse's tight-memory budget. Incompatibility, not a tuning problem.

Solution: Batched Hashing with cityHash64

Instead of joining all members: (1) Use cityHash64 to partition both old and new segments deterministically. (2) Same email always falls into same bucket in both datasets. (3) Compare bucket-by-bucket via hashes. (4) Any hash mismatch detects changes in that bucket. Avoids O(n²) memory explosion entirely.

Result

Memory: unlimited → 16GB fixed. Cost: ₹3+ lakhs → _₹50K (6x reduction). Correctness: now produces correct results. Key insight: recognize incompatibility, solve algorithmically instead of throwing resources.