Cardinality Determines Where Indexes and Partitions Break Down
“An index or partition key on a column with low cardinality does not distribute data : it concentrates it, converting a theoretical distribution into a practical hotspot. ”
High-cardinality columns like user_id or timestamp make effective index and partition keys because each value maps to a small, selective subset of rows. Low-cardinality columns like status (3 values) or message_type (5 values) produce the opposite: indexes the query planner ignores because a sequential scan is faster, and partition keys that concentrate all writes into the one partition for the most frequent value. The failure is not visible at design time: it becomes visible when the most popular partition absorbs 90% of write volume and its neighbors sit idle.
Why It Matters
Index cardinality failures are quiet. An index on status = 'pending' with 40% of rows matching looks selective in a development database with 10k rows. In production with 50M rows, 20M rows match. PostgreSQL's query planner will correctly choose a sequential scan over the index for most queries: the index takes up storage space, generates write amplification on every insert and update, and is never used. No one notices until an index audit reveals idx_scan = 0 on a table that has absorbed 2 billion inserts.
Partition key cardinality failures are louder and more operationally damaging. A Kafka topic partitioned by user_tier (3 values: free, pro, enterprise) delivers all free-tier messages to partition 0, all pro messages to partition 1, and all enterprise messages to partition 2. If 80% of users are free tier, partition 0 absorbs 80% of write throughput. The partition 0 consumer is perpetually lagged. The partition 1 and 2 consumers are idle. The system's theoretical throughput: 3x parallelism: is unreachable because the partitioning key concentrates load rather than distributing it.
The correct cardinality decision has a different character for indexes versus partitions. For indexes: high cardinality is usually better because it makes the index selective. For partition keys: high cardinality is required to achieve distribution, but too-high cardinality with a poor hash function can lead to uneven distribution under certain access patterns. The universal rule is: validate the value distribution of any proposed index or partition key against production-representative data before committing to it.
Failure Modes
- ·Hot partition: all writes to message_type = 'chat' flooding a single partition while others sit idle
- ·Unused index: low-selectivity index on status column never chosen by query planner, generating write amplification with no read benefit
- ·Partition skew: hash partition key with good cardinality but skewed value distribution creating unequal partition sizes
- ·Index bloat on low-cardinality column: large index storing many pointers per value, slower than sequential scan for most queries
- ·Kafka consumer lag on hot partition while co-partitioned consumers on cold partitions are idle
Amplification Risks
- ⚡A hot Kafka partition growing at 10x the average partition rate will exhaust broker disk for that partition first
- ⚡Low-cardinality indexes compound write amplification (see write-amplification-compounds-with-indexes) without providing read benefits
- ⚡Partition skew prevents auto-scaling from providing relief: adding consumers does not help the hot partition
Temporal Behavior
- ⟳Hot partition lag grows monotonically when the consumer cannot keep up with the dominant partition's write rate
- ⟳Index effectiveness degrades over time if the value distribution of an indexed column shifts toward a few dominant values
- ⟳Partition skew worsens as the system scales: initial even distribution can become uneven as user behavior patterns emerge
Boundary Implications
- ◈Partition boundaries must align with actual data distribution boundaries, not assumed categorical boundaries
- ◈The hot partition is a failure isolation boundary breach: its throughput ceiling becomes the system's throughput ceiling
- ◈Index cardinality decisions at the schema boundary determine write I/O overhead for the lifetime of the schema
Topology
- ·Partition topology must reflect actual data distribution: theoretical partitions that receive no traffic are not parallelism
- ·Hot partitions become throughput bottlenecks in the topology graph: all write load converges on a single node
- ·Index topology on the datastore node must track actual cardinality of indexed columns, not assumed cardinality
Scaling
- ·Scaling out consumers or partitions does not resolve hot partition problems: additional partitions remain cold while the hot one overloads
- ·Repartitioning to fix a cardinality-induced hotspot requires a full data migration in most systems
- ·At scale, partition skew amplifies: a 90/5/5 distribution at 1M events/day becomes a critical bottleneck at 100M events/day
Resilience
- ·Even partition distribution is a resilience property: it ensures no single partition becomes a single point of throughput failure
- ·High-cardinality hash partition keys (user_id, UUID) are more resilient to hotspot formation than categorical keys
- ·Regular index cardinality audits prevent write amplification accumulation from unused indexes
Governance Implications
- ·Partition key selection must require cardinality analysis against production-representative data before schema definition
- ·Index additions must include a query planner analysis (EXPLAIN) verifying the index will be used before adding it
- ·Low-cardinality indexes must be audited quarterly using pg_stat_user_indexes for idx_scan counts
Evolution Implications
- ·Adding new enum values to a low-cardinality partition key column does not resolve hotspot problems: the distribution remains uneven
- ·Migrating from a low-cardinality partition key to a high-cardinality key requires full repartitioning
- ·As data volumes grow, index selectivity decreases for columns where the value distribution shifts toward a few dominant values
Mitigation Patterns
- →Choose Kafka partition keys with high cardinality and even distribution: user_id or UUID over categorical fields
- →Validate partition key distribution against production data before committing: count distinct values and their frequency
- →Use composite partition keys to increase effective cardinality when natural high-cardinality keys are unavailable
- →Run EXPLAIN before adding any index and verify the index is chosen by the query planner for the intended query
- →Audit pg_stat_user_indexes monthly for idx_scan = 0 and drop indexes that are never used
Cross-References