Org Skills

Large data volumes — selectivity, skew, skinny tables, Big Objects, loads

Supporting material for salesforce-data-model. Agents load it on demand; it ships inside the skill folder.

RawSource

Target: Spring '26. Sources: Best Practices for Deployments with Large Data Volumes, SOQL with Big Objects, Async SOQL retirement, Bulk API 2.0.

1. Selectivity — the one rule

A query on a large object must filter on an indexed field, and the filter must be selective enough that the optimiser uses the index instead of a table scan.

Indexed by default: Id, Name, OwnerId, CreatedDate, SystemModStamp, RecordTypeId, Email on Contact/Lead, master-detail and lookup fields, unique fields and external Ids. Custom indexes on other fields are created by Salesforce Support.

What defeats the index:

Pattern Why Instead
WHERE Name LIKE '%acme%' Leading wildcard SOSL, or a formula-free normalised field with a trailing wildcard
WHERE Status__c != 'Closed' / NOT IN Negative filters are not selective Positive IN list of the statuses you want
WHERE Total__c > 0 on a formula field Formulas are not indexed Store the value in a real field maintained by automation
WHERE CreatedDate = LAST_N_DAYS:365 on 40 M rows Selective by field, not by row count Narrower window, or a chunked Id > walk / Apex Cursor
null checks on a large optional field Nulls are not indexed unless the custom index is built for them Ask Support for a null-inclusive custom index, or use a sentinel value

Thresholds to remember: the optimiser considers an index usable roughly below 30 % of the first million rows and 15 % beyond that, capped at one million rows returned; a query returning more than that is a batch job, not a query.

2. Skew

Skew Threshold Symptom Fix
Ownership skew > ~10 000 records owned by one user Sharing recalculation storms; slow role changes Spread integration-created records over a pool of users; put the integration user outside the role hierarchy
Lookup / account data skew > ~10 000 children under one parent UNABLE_TO_LOCK_ROW under parallel loads Bucket parents; load children grouped by parent in serial mode
Parent record locking Master-detail updates lock the parent Bulk failures at random Sort the load file by parent Id so one batch touches one parent

3. Speeding up reads

  • Skinny tables (enabled by Salesforce Support) denormalise the hot fields of one object, including some formula fields, for list views and reports. Limits: under 100 columns, no fields from other objects, kept in sync automatically, and they must be re-created in a sandbox refresh.
  • Custom indexes via Support for fields you filter on constantly; two-column indexes exist for the common "filter + sort" pair.
  • Divisions for very large orgs with naturally partitioned data (rare; ask before proposing).
  • Report and list-view hygiene: filter on indexed fields first, avoid formula filters, set a date bound on every report over an LDV object.

4. Archiving to Big Objects

Use a Big Object for append-only history that must remain queryable but takes no part in sharing, automation or reporting-by-filter.

  • Define the object and its composite index in metadata (<indexes> in .object-meta.xml). The index fields, their order and their count are fixed at creation and cannot be changed — a wrong index means recreating the object and reloading it.
  • Query with SOQL that filters index fields left to right, with equality on every field before the last; otherwise the query is rejected. Anything larger goes through Batch Apex (Database.QueryLocator over the Big Object) or a Bulk API 2.0 query job. Async SOQL was retired in Summer '23 — do not propose it.
  • Insert with Database.insertImmediate from Apex or Bulk API 2.0; there is no trigger, no roll-up, no sharing and no LIMIT-free browsing.
  • Pair every archive job with a delete job: Database.emptyRecycleBin after a hard delete, or Bulk API 2.0 hardDelete so storage is actually released.
  • Alternative: export to the data lake and keep an aggregate summary object in Salesforce. Cheaper for anything that is analysed rather than looked up.

5. Loading and migrating volume

  1. Prepare: external Id on every object, load file sorted by parent, duplicate rules and validation narrowed for the window, automation bypassed through User.Ignore_Validation_Rules__c on the load user and the per-handler bypass in each trigger.
  2. Defer sharing calculation (Support-enabled) for the load window, and turn the role hierarchy grant off for custom objects that do not need it.
  3. Load parents before children, in Bulk API 2.0 jobs; use serial mode when the data is skewed, parallel otherwise.
  4. Re-enable automation, then run the repair jobs: roll-up recalculation, Apex sharing recalculation, index requests to Support once the data is in.
  5. Measure automation cost per record in a Full sandbox at production volume before the production window — a flow costing 30 ms per record is 100 minutes for 200 000 records.
  6. Storage: budget ~2 KB per record for data storage; files and attachments go to ContentVersion or stay outside the platform.