Mastering High-Cardinality Primary Keys
In this lesson, you will learn how to structure primary keys for massive, unique datasets to prevent index fragmentation and maintain high-performance lookups.
High cardinality refers to columns with a massive number of unique values, such as UUIDs, timestamps at microsecond resolution, or hashed sensor IDs. When these values are used as primary keys in traditional B-Tree indexes, they present a specific design challenge: random insertion patterns.
The Problem with Monotonic or Random Keys
If you use a natural, high-cardinality key like a UUID v4, the index values are distributed completely randomly across the keyspace. When a new record arrives, the database engine must perform a random disk I/O to fetch the corresponding index page, modify it, and write it back. As your dataset grows beyond RAM capacity, this leads to frequent page splits and massive performance degradation due to heavy random I/O.
The Solution: Key Salting and Prefixing
To handle this, we employ Key Salting or Prefixing. By prepending a "bucket" or "shard" identifier to your high-cardinality ID, you force related data to reside in the same physical index block or partition.
Instead of using the raw UUID as the primary key:
key: 550e8400-e29b-41d4-a716-446655440000
You transform it into a composite key:
key: <bucket_id>:<uuid>
-- Example of a salted primary key structure
CREATE TABLE sensor_readings (
bucket_id INT, -- Added prefix for locality
reading_id UUID, -- High-cardinality value
payload JSONB,
PRIMARY KEY (bucket_id, reading_id)
);
By adding a bucket_id (e.g., a modulus of the hash of the UUID), you group insertions into specific ranges. This makes the index writes "near-sequential" for that bucket, significantly reducing page splits and improving cache hit rates. Even if the underlying data is sparse or highly unique, you have effectively "binned" the randomness to align with the database's page-based storage architecture.
Key Takeaway: To optimize high-cardinality primary keys, augment your identifiers with a deterministic prefix to transform random-access patterns into localized, range-friendly write operations.