Salesforce Large Data Volumes (LDV): Skinny Tables, PK Chunking, and Data Skew Explained

⚡ Key Points: 1-Minute Summary

• Data Skew: Having 10,000+ records assigned to a single owner, parent, or lookup target causes severe record locking and sharing recalculation storms.
• Skinny Tables: Specialized, pre-joined tables created by Salesforce Support to bypass complex database joins and dramatically speed up hot read paths.
• Custom Indexes: Applied selectively to high-traffic filters by Salesforce Support. Crucially, negative filters (e.g., !=) and leading wildcards cannot use indexes.
• PK Chunking: The secret weapon for extracting massive datasets via the Bulk API by slicing the export by Salesforce ID ranges.
• Query Plan Tool: The built-in Developer Console feature you must use to prove to Salesforce Support that a custom index is actually needed.

💬 In plain words: Once an object hits tens of millions of rows, normal Salesforce features start breaking. You must use specialized tools: Skinny Tables (pre-joined copies for fast reading), Custom Indexes (telling the database exactly how you filter data), PK Chunking (slicing massive data exports by ID ranges), and eliminating Data Skew (preventing 10,000+ child records from jamming up a single parent record lock).

🎬 Real-Life Example: The Search That Took Twenty Seconds

The Problem: Account search at a large enterprise crawled to twenty seconds once the org passed 30 million rows. The team asked Support for more hardware, then added a UI spinner so the wait looked intentional.
Why this is bad: The SOQL filter was Status != 'Inactive'. A negative filter cannot use a database index, so every single search ran a full table scan. No amount of hardware fixes an unindexable query.
The Fix: The developer rewrote the filter as a positive, indexed query: Status IN ('Active', 'Pending'). They checked the Query Plan tool and requested a custom index for the real query pattern. Search times dropped under a second.

🧠 Four LDV Tools to Memorize: Skinny tables read fast, custom indexes find fast, PK chunking slices big extracts, and data skew is the disease you must avoid.

🏗️ The Core Concept: Managing Large Data Volumes (LDV)

Large Data Volumes (LDV)—typically defined as tens of millions of records within a single object—turn ordinary features into system hazards. At this scale, query performance comes first. The Salesforce optimizer requires selective filters hitting either a standard index (Id, Name, external IDs, audit fields) or a custom index. Without selectivity, an unselective query performs a full table scan and times out.

• Skinny Tables: These are provisioned by Salesforce Support via a ticket. They are denormalized, invisible copies of frequently used columns that auto-sync and bypass complex database joins, optimizing read-heavy operations.
• PK Chunking: When extracting 100M rows, a single Bulk API query will fail. PK (Primary Key) Chunking automatically splits the extract into manageable chunks based on Salesforce Record ID ranges.
• Data Skew: This comes in three flavors, all of which create severe lock contention and sharing recalculation storms:
  • Ownership Skew: One user owning 10,000+ records.
  • Account Skew: One parent record holding 10,000+ child records.
  • Lookup Skew: Millions of child records pointing at a single lookup reference record.

To acquire custom indexes or skinny tables, you do not click a button in Setup. You must raise a case with Salesforce Support, provide the failing query, and use the Query Plan Tool in the Developer Console to prove that the optimizer is currently bypassing indexes.

🧭 360 Card — LDV: Indexes, Skinny Tables, Skew

Rule: Keep queries selective, keep the hot data tables small, and archive/move everything else off the primary object.
Gain: An object that timed out at 60 million rows will return data in milliseconds once the filter hits a proper index.
Price: Custom indexes and skinny tables require Salesforce Support intervention, meaning they are not immediate, self-serve fixes.
Limits: A standard index is only considered selective if it targets roughly 30% of the first 1 million rows. A custom index is much stricter (~10% limit). Negative filters and leading wildcards disable indexes entirely. Skew begins to cripple performance at around 10,000 children under one parent.
Mirror (Buying more storage): Buying more data storage doesn't make queries faster; it just gives you more room to fail.
At volume: Spread integration-created records across a pool of users at the top of the role hierarchy. Split monster accounts. If a lookup field is only used as a label, change it to a picklist—because picklists don't create database locks.

💬 Core Q&A

Q: A report or query on a 60M-row object times out constantly. Walk me through the optimization sequence.

🎯 Say this first: "Apply a selective filter first (indexed field), request a custom index if needed, use a skinny table for wide hot reads, use PK chunking for extracts, and always check for data skew."

A: You must troubleshoot LDV timeouts in this exact order:

• Step 1: The Query Plan. Run the SOQL query through the Query Plan tool. Think like the optimizer. Is the filter actually selective?
• Step 2: Restructure Filters. Remove unindexable operators. Avoid leading wildcards (LIKE '%text'), negative operators (!=), NULL checks on unindexed fields, and OR statements across multiple unindexed columns. Request a custom index on the primary discriminating field if required.
• Step 3: Reduce the Working Set. Archive old data continuously so the hot table shrinks. If the query still struggles with wide rows and joins, request a skinny table for the frequently accessed columns.
• Step 4: Pagination & Extraction. For UI code paths, implement LIMIT and keyset pagination. For data warehouse extracts, use Bulk API 2.0 with PK chunking.

Q: Compare Skinny Tables vs. Custom Indexes. Which do you ask Salesforce for?

A: Start with indexes. Ask for an index when the problem is finding the rows (your filter is selective but not currently indexed). Indexes are cheaper and less invasive. Ask for a skinny table when the problem is reading wide rows. A skinny table is a pre-joined, trimmed copy of hot fields used to bypass heavy database joins. Skinny tables are the bigger hammer and come with maintenance overhead (e.g., they don't automatically include soft-deleted records).

🔍 Scenario-Based Follow-Ups

Q: Explain each skew type's failure mode and fix.

🎯 Say this first: "Data skew occurs when 10,000+ child or owned records pile up under one parent or owner. It forces the database to serialize locks, turning parallel saves into a slow, single-file queue."

A: There are three specific types of skew to architect against:

• Ownership Skew: One user (often a default integration user) owns millions of records. The Failure: Any change to their role triggers a massive sharing recalculation storm. The Fix: Place skewed owners in an isolated role at the very top of the hierarchy, or spread record ownership across a pool of users.
• Account Skew: 10,000+ child records under a single Account. The Failure: Child DML operations take exclusive locks on the parent Account, causing parallel data loads to instantly deadlock. The Fix: Group data loads by parent, lower batch parallelism, and spread children across dummy "bucket" accounts.
• Lookup Skew: Millions of children pointing at one lookup reference record. The Failure: The exact same locking contention, but targeting the lookup record. The Fix: If the lookup is only decorative or used for categorization, replace it with a picklist. Picklists do not require database locks.

Q: Design the nightly extract of a 100M-row object to a data warehouse without breaking Salesforce or the warehouse.

A: Never run a full nightly extract on 100M rows. Prefer incremental extraction. Use Change Data Capture (CDC) or SystemModstamp delta queries to pull only the records that changed since the last run.

For the initial full-seed load, use the Bulk API 2.0 with PK chunking enabled. Run this off-peak. PK chunking automatically splits the massive query into smaller, digestible chunks (defaulting to 250k records). Ensure your extraction script is built to retry per chunk, so a single network timeout doesn't force you to restart all 100M rows. Finally, use an extraction user profile with the absolute minimum automation footprint to save processing time.