# 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.
