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
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.
Defer sharing calculation (Support-enabled) for the load window, and turn the role hierarchy grant off for custom objects that do not need it.
Load parents before children, in Bulk API 2.0 jobs; use serial mode when the data is skewed, parallel otherwise.
Re-enable automation, then run the repair jobs: roll-up recalculation, Apex sharing recalculation, index requests to Support once the data is in.
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.
Storage: budget ~2 KB per record for data storage; files and attachments go to ContentVersion or stay outside the platform.
# Large data volumes — selectivity, skew, skinny tables, Big Objects, loads
Target: Spring '26. Sources: [Best Practices for Deployments with Large Data Volumes](https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_introduction.htm), [SOQL with Big Objects](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/big_object_querying.htm), [Async SOQL retirement](https://help.salesforce.com/s/articleView?id=000394892&type=1), [Bulk API 2.0](https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/bulk_api_2_0.htm).
## 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.