Streamlining data flow for 230 million leads

Context

Elite IT ran a lead distribution platform for the US real estate market. Leads arrived continuously from multiple sources, had to be stored and replicated, and then distributed to thousands of agents, each with their own filters and entitlements.

The working set was 230M+ active leads. Each lead touched 11 related tables, which puts the total at over 2.5 billion records that had to stay consistent with each other.

This is the system I wrote about in The hardest systems are the boring ones. It is a CRUD application. That description is accurate and completely useless.

The problem

The headline symptom was query latency. Agent-facing queries that filtered across several of the 11 tables were taking up to 15 minutes. At that point the feature does not exist, whatever the UI says.

Underneath it were three compounding issues:

Fan-out on write. A single incoming lead touched 11 tables. Ingestion throughput and query performance were fighting over the same resources.
No room to change the schema. With 2.5B rows, an ALTER TABLE on a hot table is an outage. Whatever we did had to work within the existing shape, or be achievable as a background migration measured in days.
Distribution is a read amplifier. Thousands of agents each running their own filtered view meant the read pattern was wide, unpredictable, and impossible to cache naively.

Constraints

Zero data loss. These are commercial leads with money attached to them.
No maintenance window worth the name. The platform was in active use.
The fix had to hold at the next order of magnitude, not just the current one.

Approach

Partitioning before indexing. The instinct at this scale is to reach for more indexes. That was the wrong first move, because index maintenance cost was already part of the write problem. Partitioning came first, so that the majority of queries could be answered without touching the majority of the data. Get the partition key right and the index strategy gets simpler. Get it wrong and no amount of indexing saves you.

Index design driven by the actual query shapes. Not by the columns that looked important, but by the filter combinations agents genuinely used. At this scale every index you add is paid for on every write, forever, so the ones that were not carrying their weight were as much of a problem as the ones that were missing.

Caching the distribution layer. Redis in front of the read path for the filtered views that thousands of agents hit repeatedly, which took sustained load off the primary rather than just making individual queries faster.

Separating the ingestion path from the query path so that a burst of incoming leads could not degrade agent-facing reads.

The migration

Part of the work was a bulk migration of 500K+ records into AWS RDS, with Python-based schema conversion between the source and target shapes. Requirements were zero data loss and no extended downtime, which meant it ran as a reconcilable, resumable process with verification at each stage rather than a single cutover.

The deployment path

The delivery pipeline mattered more than usual here, because the cost of a bad deploy against this dataset was high:

CI/CD on AWS CodePipeline with ECS Fargate
Docker image size reduced 86%, deploy time down 70%
Blue-green deployments with health checks and auto-scaling, holding 99.9% uptime

Faster, smaller deploys are a correctness feature when rollback speed is your main safety net.

Outcome

Measure	Result
Active leads	230M+
Total records maintained	2.5B+
Query time	15 minutes to milliseconds, 99.99% reduction
Records migrated	500K+, zero data loss
Docker image size	86% smaller
Deploy time	70% faster
Uptime	99.9%

The platform went on to support two SaaS products, Reprosify.com and MuzzBizz, serving 10K+ monthly active users.

What I took from it

The decisions that mattered were all made before the code. Partition key, index strategy, and which of the 11 tables could tolerate eventual consistency. Those are cheap to decide and enormously expensive to revisit.

Reversibility is the real constraint at scale, not performance. Performance problems announce themselves and can be attacked. An architecture you cannot change quietly shapes every subsequent decision for years.

Query time is a product feature. Fifteen minutes and fifty milliseconds are not two points on the same scale. One of them is a feature that exists and one is a feature that does not.