How does storage index pruning interact with CPU cache locality?
When these two mechanisms interact correctly, they create a "Performance Flywheel" that allows a single server to handle workloads that would otherwise require a dozen.
In high-performance storage (like Oracle Exadata or modern DPUs), a Storage Index is a memory-resident map that tracks the Minimum and Maximum values for columns within a specific region of the disk (usually a 1MB "Storage Region").
The Pruning Action: When a SQL query asks for WHERE ID = 500, the storage layer checks the Index. If a 1MB chunk has a range of ID [600-1000], the storage layer prunes that entire chunk. It is never read, never decrypted, and never sent over the wire.
The 1M TPS Impact: This eliminates "I/O Noise." Only the 1MB chunks that could contain the data are moved toward the CPU.
The true magic happens when the "Survivor" data (the data that wasn't pruned) hits the CPU. This is where Cache Locality takes over.
Without pruning, a large scan floods the CPU's L3 Cache with useless data blocks. This is known as Cache Pollution.
The Problem: The CPU spends all its time evicting useful session data to make room for a "Cold Scan."
The Interaction: Because Pruning removes 90% of the data at the source, only the relevant 10% enters the L3 cache. This allows the CPU to maintain Cache Residency for the query's working set, meaning subsequent joins or aggregations happen at the speed of silicon, not the speed of RAM.
Storage Indexes often work on "Striped" or "Columnar" data.
The Interaction: When a pruned stream of columnar data arrives at the CPU, it is highly dense. One 64-byte Cache Line might contain 8 or 16 actual values. Because the "junk" rows were pruned earlier, every byte pulled into the L1 Cache is likely to be used immediately. This results in a near-perfect IPC (Instructions Per Cycle) rate.
| Stage | Action | Hardware Impact |
| Storage Layer | Index Pruning | Reduces PCIe/Fabric bandwidth consumption. |
| Memory Controller | Prefetching | Pruning tells the controller exactly which blocks to "warm up." |
| L3 Cache | Resident survivor set | Higher "Hit Rate" because non-matching data was never loaded. |
| Execution Core | Vector Processing | Data is "Cache-aligned," enabling AVX-512/SIMD speed. |
At 1M TPS, the interaction between pruning and locality changes the fundamental math of your database:
Lower Thermal Throttling: Since the CPU isn't wasting cycles sifting through non-matching data, it runs cooler. This prevents Turbo Boost from dropping, keeping your transaction speeds deterministic.
Deterministic P99s: Pruning ensures that even a "Massive" query only touches a predictable amount of data. This keeps the CPU's Memory Bus available for concurrent OLTP transactions, preventing the "Long Tail" latency that occurs when a background scan chokes the system.
| Scenario | Data Moved to CPU | CPU Cache State | 1M TPS Stability |
| No Pruning | 100% of Table | Polluted (Thrashing) | Low (Jitter) |
| With Pruning | 1–10% of Table | Warm (High Hit Rate) | Maximum |
Storage Index Pruning is the "Filter" and CPU Cache Locality is the "Funnel." By pruning data early, you ensure the CPU's high-speed caches are filled only with "Gold," not "Gravel." In a 1M TPS architecture, this synergy is the difference between a system that scales linearly and one that hits a "performance wall" as data volume grows.