The Junction Object That Outgrew Its Parent
Six years of growth left Application__c, the junction between Candidate__c and Position__c, at 14.2 million rows and adding 250,000 a month. Recruiters report that list views of open applications time out. The "General Pool" position holds 3.1 million of those applications, and a nightly Batch Apex reassignment job on that position fails with UNABLE_TO_LOCK_ROW. That job now runs 70 minutes for 400,000 rows. Application__c carries 6 roll-up summary fields to Position__c and 22 custom fields, and three integrations query it by Status__c plus a CreatedDate range. Position__c has 40,000 rows and is the master in the master-detail relationship.
1. Separate the symptoms. Which failure is a data-volume problem and which is a data-model problem, and why?
Read the worked answer
They are different bugs that happen to share an object.
List view timeouts = query selectivity on a large data volume. A list view is a SOQL query plus sharing evaluation. Status__c is low cardinality and unindexed; CreatedDate is indexed but only helps if the range is actually bounded. A filter of Status__c = 'Open' with no date bound forces a scan across 14.2M rows and the UI gives up. Nothing is corrupted - the query is just unwinnable.
UNABLE_TO_LOCK_ROW = parent skew, a data-model problem. With a master-detail and 6 roll-up summaries, every child DML recalculates the parent's rollups and takes a lock on that Position row. Three million children hang off one parent, so concurrent batches queue on the same row and time out. The same object with the same volume spread across 40,000 evenly sized parents would not lock.
The 70-minute runtime is plain volume plus work per row - 400,000 rows at batch size 200 is 2,000 batches, each paying rollup recalculation.
2. Propose the redesign. Say what stays on Application__c, what leaves it, and name one thing you would deliberately not do.
Read the worked answer
Keep hot data. Applications created or touched in the last 18-24 months - roughly 2M rows - stay where recruiters and integrations can query them normally.
Archive the cold tail to a Big Object. Big Objects take the other ~12M rows, are queryable only through Async SOQL, and support compliance and trend reporting. Do not try to recreate the same schema there.
Break the skew, don't paper over it. Either split the General Pool into N bucket positions (round-robin on a hash field) so child DML hits many parent rows instead of one, or - if the rollups are genuinely unneeded for pooled records - change the relationship to a lookup and drop the rollup summaries. Splitting is less invasive; dropping rollups is cleaner if finance agrees.
Restore selectivity. Request a custom index on Status__c via a Support case; be ready for a refusal because low-cardinality fields make poor indexes. In parallel, rewrite every list view, report, and integration query to include a bounded CreatedDate range, and request a skinny table for the read-heavy reporting workload.
Deliberately not do: add retry logic and larger batch sizes to the lock failures. That converts a fast failure into a long, slow one and hides the model problem for another year.