Designing for Large Data Volumes in Salesforce: What Actually Works

Orgs do not fail at large data volumes suddenly. They degrade: a report that took seconds takes minutes, a nightly batch starts finishing at 9 a.m., a list view times out for exactly one team. By the time someone calls it an "LDV problem", the data model decisions causing it are years old. Having worked on orgs with tens of millions of records in banking and insurance, here is what actually moves the needle, roughly in the order I apply it.

First, understand what gets slow and why

Salesforce is a multitenant database. Every query you run competes for shared resources, and the query optimizer protects the platform by refusing to do certain expensive things at scale. The practical consequence is one word: selectivity.

A filter is selective when an index can satisfy it and the result set is a small fraction of the table. The optimizer's thresholds matter here: for a standard index, the filter must return fewer than 30% of the first million records and 15% after that, capped at one million; for custom indexes it is 10%, capped at about 333,000. Blow past those and the platform full-scans, and full scans on 40 million rows are where report timeouts come from.

So the first diagnostic is always the same: take the slow query or report filter, run it through Query Plan in the Developer Console (or the newer query analysis tooling), and check whether the leading filter is indexed and selective.

Indexes: the cheap wins

Standard indexed fields include Id, Name, OwnerId, CreatedDate, SystemModstamp, lookup fields and external ID fields. Beyond those:

• Marking a field as an external ID creates a custom index. This is the quiet reason upserts on external IDs perform well.
• You can request custom indexes from Salesforce Support on most field types once you demonstrate the query pattern.
• Two-column indexes exist for combined filters and are worth asking for when a compound filter is your hot path.

Watch the anti-patterns that make an index useless: leading wildcards (LIKE '%foo'), negative operators (!=, NOT IN), comparing to null in older patterns, and formula fields that are not deterministic. Rewriting a filter from "everything except closed" to "status in these five open values" has fixed more than one production report for me.

Data skew: the silent killer

Skew is when too many child records point at one parent, or too many records share one owner. The classic cases:

• Account skew. The infamous "Miscellaneous" account with 300,000 contacts under it. Every update to a child can lock the parent, so parallel loads start deadlocking, and sharing recalculation on that account takes forever.
• Ownership skew. A single integration user owning two million records. When that user changes role or a sharing rule touches them, the recalculation is massive. If you must have a mass-owner user, park it in an isolated role at the top of the hierarchy and keep it out of sharing rules.
• Lookup skew. Same story with any lookup pointing at a small set of "category" records.

The fix is structural: distribute children across more parents (bucketing), avoid catch-all parents, and design load jobs to group records by parent so a single parent is never hammered from multiple parallel batches.

Skinny tables, and when to actually use them

A skinny table is a Salesforce-managed physical table containing a subset of columns from a standard or custom object, kept in sync automatically. Reports and list views hitting only those columns avoid the joins between the standard fields table and the custom fields table, which can be a dramatic speedup on wide objects with tens of millions of rows.

Reality check before you request one from Support: they hold at most 100 columns, they do not include soft-deleted rows, they are per-object and per-org (sandboxes need their own), and every schema change to included fields needs coordination. I treat skinny tables as a last-mile optimization after indexes, filter rewrites and archiving, not as a first response.

Divide the data: archiving and Big Objects

The cheapest query is the one that never scans old data. Past a certain point the real question is not "how do we query 80 million rows faster" but "why are 60 million closed cases from 2019 still in the transactional object?"

The options, in the order I usually evaluate them:

• Delete what has no retention requirement. Radical, unpopular in meetings, frequently correct.
• Big Objects for data you must keep queryable inside Salesforce. They store billions of rows cheaply, but be clear about the trade: you query them by their composite index (or with async SOQL), no triggers, no standard reports, limited field types. They are an archive, not a second transactional object.
• External storage (a warehouse or data lake) with Salesforce Connect external objects when users still need to see the history on the record page. With zero-copy patterns and Data Cloud maturing, keeping cold data outside the org and surfacing it on demand keeps getting easier to justify.

Whichever you pick, the archiving job itself is an LDV exercise: batch Apex or Bulk API deletes, watched for locking, with the recycle bin emptied (hard delete) so the rows actually leave the indexes.

Loading at scale without breaking the org

Big loads fail for predictable reasons, so plan for them:

• Bypass automation during loads. Six triggers and four flows per record times five million records is not a data load, it is a stress test. A trigger framework with a metadata-driven bypass switch turns this from a code change into a checkbox.
• Defer sharing calculations on huge ownership or hierarchy changes, then recalculate once at the end.
• Load in parallel, but sort by parent. Parallel Bulk API batches touching the same parent account will lock each other. Sorting input files by the lookup value is a boring fix that works.
• Use upsert with external IDs so the job can be rerun after a partial failure without creating duplicates.

Keep the UX honest

Some LDV pain is really UX design pain. A list view over "all cases ever" will never be fast, and it also is not useful. Scope views and reports by time or team, teach dashboards to run on summarized data, and consider a reporting snapshot or a warehouse for the "all history" questions. Users do not need 40 million rows on screen. They need the 200 that matter today.

A short checklist

• Slow query? Check Query Plan, fix selectivity, request indexes.
• Children per parent under 10,000, no catch-all parents, integration owners isolated in the role hierarchy.
• Data past its useful life archived to Big Objects or external storage, on a schedule, automatically.
• Loads run with automation bypassed, sorted by parent, upserting on external IDs.
• Skinny tables only after everything above, for the specific reports that still hurt.