Skinny tables & indexing

Skinny tables & indexing

When an object grows large, two Salesforce features become key levers for keeping reads fast: custom indexes and skinny tables. Both attack the same enemy — the full table scan — but from different angles, and an architect must know when each applies and what each costs.

A custom index makes a specific field efficient to filter on. Standard indexes already cover Id, Name, owner, lookups, external Id, unique fields, and audit fields. For any other field you frequently filter or sort by on a large object, a custom index (created by Salesforce support, or automatically when you mark a field External Id or Unique) lets the optimizer use that field selectively instead of scanning every row. Indexes are most valuable on high-cardinality fields — many distinct values — because a filter that matches only a small slice of rows is what selectivity requires. Indexing a two-value checkbox helps little.

A skinny table is a behind-the-scenes copy of an object's most-used fields, kept automatically in sync by Salesforce, that avoids the join between the base table and its "detail" table that standard objects normally require. Because reports, list views, and queries against the skinny table read fewer, denormalised columns, they run noticeably faster on very large objects. Skinny tables can include fields from the base object and its standard companions, and they are also enabled through Salesforce support.

The important constraints: skinny tables are limited to a set number of columns, support only certain field types (no long text, no formulas that reference other objects), are not automatically copied to sandboxes, and must be requested from Salesforce rather than self-served. They shine for large, read-heavy objects with stable, well-known query patterns.

Reach for a custom index when:  a filter/sort field is high-cardinality
                                and queried often but isn't standard-indexed.
Reach for a skinny table when:  a large object is read-heavy with a stable
                                set of frequently-selected columns.

Both are tools of last resort after good selective design — you cannot index your way out of a fundamentally non-selective query. As an exercise, identify the three most-queried fields on your largest object and decide which would benefit from a custom index and whether a skinny table fits the read pattern. Next we address the other half of LDV: getting old data out of the way through archiving.