Data Architecture & Performance

An index cannot rescue an unbounded Salesforce data model

JSBC Labs19 October 20268 min read

An index is a route, not a reduction

A list view times out after years of growth. The immediate request is predictable: index the status field. Support adds the index, performance improves briefly, and six months later the page is slow again. The field was never selective because almost every active record has the same status. The index provided a faster route to an enormous result set; it did not reduce the work.

Salesforce documents that its query optimizer evaluates an index against record-distribution thresholds before deciding whether the index is faster than another plan. An indexed field is therefore not a performance guarantee. The JSBC Labs position is that query performance starts with bounded business scope and data lifecycle. Indexes should accelerate that design, not substitute for it.

Design around the workload

Before tuning SOQL, name the workload. A service agent opening twenty recent cases, a nightly reconciliation reading changed records, an executive report spanning five years and a legal export of every record are different products. They need different latency targets, freshness, filters, access patterns and processing mechanisms.

For each workload, record expected object volume, daily growth, result size, concurrency and acceptable response time. Identify the dimensions users actually know at query time: business unit, region, owner, lifecycle state, effective date or external partition. A query cannot be selective if the product asks users to search an unlimited population without supplying a meaningful boundary.

Selectivity changes as the business grows

A filter that returns two thousand rows from one hundred thousand can behave very differently when it returns two million from fifty million. The code and index may be unchanged, yet the distribution has crossed the optimizer’s useful threshold. That is why a query that succeeds in a small sandbox can fail under production cardinality and skew.

Measure value distribution, not only total record count. Salesforce’s large-data-volume guidance shows how aggregate queries can reveal whether filter values are selective and notes that deleted records can be included or excluded when gathering statistics. Track the high-volume values and their growth. ‘Open’, ‘Active’ and ‘Not processed’ frequently become the least selective values precisely because they describe the organisation’s working set.

Make the business supply a narrow key

Efficient interfaces guide users towards selective criteria. Default a work queue to a recent date range and relevant team. Require an exact customer identifier before showing deep history. Separate ‘my active work’ from enterprise search. If free-text discovery is the real task, use SOSL or an appropriate search service instead of stretching SOQL into a wildcard scan.

Salesforce recommends focused queries, selective filters and specific search terms. Its guidance also warns about negative filters, null filters on some field types and cross-object formula filters. These patterns describe what records are not wanted rather than providing an efficient route to what is wanted. Store a positive, durable classification when the business repeatedly needs it—for example, Eligible_For_Renewal__c backed by governed update logic.

Use Query Plan as release evidence

Do not approve a high-volume query because it looks reasonable. Salesforce’s Query Plan tool exposes available operations and their cost, helping teams see which plan the optimizer prefers. Capture the plan against representative volume and data distribution before release, then retain the query, assumptions and result with the performance evidence.

A good plan is not permanent certification. Data distribution, sharing, retention and user behaviour change. Recheck important queries after bulk loads, mergers, ownership changes and material growth. Set application telemetry around duration, rows returned and failure rate so production identifies degradation before users normalise it as ‘Salesforce being slow’.

Request an index only after proving the filter

Salesforce maintains indexes on several common fields and supports custom indexes. Its documentation states that the optimizer still evaluates whether an available index is useful. Some data types and non-deterministic formulas cannot provide the index path teams expect. A request for a custom index should therefore include the target query, production distribution, query plan and forecast growth.

Review combined filters as a system. An OR can prevent index use when the branches are too broad, and all fields in the OR must be indexed for an index to be used. Sometimes two focused queries combined in application logic outperform one broad expression. The correct answer depends on the workload, permissions and cardinality—not on a blanket rule that more indexes make every query faster.

Bound the active dataset

If every completed transaction remains operationally active forever, each query, report, sharing calculation, backup and sandbox refresh inherits the cost. Salesforce’s large-data-volume best practices explicitly recommend reducing active data and archiving or discarding data as it arrives to prevent performance degrading over time.

Define retention by business purpose. Keep the records required for current service inside the operational model; move cold history to an archive object, external store or analytical platform with governed retrieval. Preserve legal holds, lineage and authorised access. Archiving is not deletion by another name, and it is not a backup. It is a deliberate change in the data’s operating tier and access contract.

Partition according to real boundaries

A partition helps only when the workload can identify it. Region, legal entity, fiscal period or customer cohort can narrow queries when those dimensions match how people work. An arbitrary shard key that no user or integration knows simply moves complexity into every request.

Store the partition key directly where it must drive access; avoid calculating it through a cross-object relationship at query time. Keep ownership and parent-child concentration in view because data skew and sharing joins can change the cost of retrieval. A good partition reduces both the candidate rows and the permission work required to return them.

Separate interactive access from bulk extraction

A screen should not retrieve millions of records, and a large export should not pretend to be a screen request. Design interactive paths for bounded results and fast feedback. Use asynchronous or bulk mechanisms for reconciliation, migration, archival and analytical extraction, with checkpoints, resumability and operational ownership.

Salesforce recommends PK chunking for Bulk API queries on tables above ten million records or when a bulk query consistently times out. Chunking improves the mechanics of a legitimate bulk workload; it does not make an unbounded synchronous query acceptable. Select the access pattern that matches the volume and user expectation, then protect each path with its own limits.

Put growth into the definition of done

Performance testing with a few thousand evenly distributed records proves almost nothing about a design expected to hold tens of millions. Salesforce Well-Architected recommends production-scale volume testing with realistic skew, relationship depth and record counts. Seed the dominant values, deep parents, sharing patterns and deleted-record conditions that will shape the query plan.

For every critical search, report or selector, define the workload, maximum active population, required partition, expected cardinality, query-plan evidence, retention rule and owner. Revalidate at growth thresholds rather than waiting for a timeout. The durable principle is straightforward: narrow the question, bound the active data, choose the right access path and then use an index. An index can accelerate good data architecture; it cannot rescue an operating model that refuses to decide what belongs in scope.