What question did this study set out to answer?

This research aims to formalize the conditions where random primary key insertions degrade database performance.

March 17, 20260 citationsOpen Access

When UUIDs Break Your Database - A Formal Threshold Model for B-Tree Performance Degradation Under Random Primary Keys in OLTP Systems

Key Points

This research aims to formalize the conditions where random primary key insertions degrade database performance.
Developed a model based on B-Tree mechanics and IO cost functions.
Introduced the Buffer Saturation Ratio (BSR) as a performance threshold indicator.
Derived six key equations from first principles to predict performance decline.
Validated findings with benchmark data and a real-world latency incident.
Identified a performance inflection point at BSR = 1.0, where write latency shifts substantially.
Demonstrated that Write Amplification Factor (WAF) increases logarithmically across secondary indexes.
Produced a decision table linking BSR ranges to key strategy choices (BigInt, UUIDv7, ULID).

Abstract

UUIDv4 primary keys are widespread in production OLTP databases, yet their performance cost at scale is not well understood. This paper formalises the conditions under which random primary key insertion shifts write latency from CPU-bound to IO-bound operation. We introduce the Buffer Saturation Ratio (BSR), a dimensionless index equal to the ratio of buffer pool memory to total index size. When BSR drops below 1. 0, each insert must read a page from disk before writing. We derive the model from B-Tree page-split mechanics, LRU eviction probability under uniform random key distribution, and the two-phase IO cost function of InnoDB. Six equations are derived from first principles, culminating in Equation 6, the threshold row count formula N* = Bₚool x f / (Kₛize + Pₒverhead), which predicts the exact row count at which a given schema will cross the performance cliff. Validation draws on published benchmark data from Percona, EDB, and Cybertec PostgreSQL, and on a documented production logistics incident involving latency growth from 2ms to 200ms. All four sources show a latency inflection point near BSR = 1. 0. A Write Amplification Factor (WAF) derivation shows O (log N) growth compounding across secondary indexes. The paper closes with a practitioner decision table mapping BSR ranges to concrete key strategy choices across BigInt, UUIDv7, and ULID.

AI에게 질문

Bookmark

View Full Paper