A 4 TB events table serves three query patterns: 80% ask for one tenant over the last 7 days, 15% look up a single event by id, and 5% scan one product across all history. The table currently has no partitioning. Which layout should the team adopt?
Show the full answer Hide the answer
The deciding property
The predicate that appears in the most expensive queries, and its cardinality. Date appears in 80% of queries, has low cardinality when truncated to a day, and grows predictably. Tenant appears in the same 80% but has thousands of distinct values, so it is a clustering key, not a partition key.
That split is the whole answer: partition on the low-cardinality, always-filtered dimension; sort or cluster within the partition on the high-cardinality one.
Why this option
Partitioning by day turns the 7-day query into 7 partitions out of roughly 1,000, a 99% reduction before anything else happens. Clustering by tenant id inside each day means the tenant's rows sit in a small number of adjacent files, so file-level min/max statistics let the engine skip most of what remains. The single-event lookup is served by the same statistics narrowing to a few files, which is slower than an index and fast enough at 15% of a read workload.
What would flip the decision
| If this changes | Choose | Because |
|---|---|---|
| Point lookups become 60% of traffic with a latency SLO | A key-value or operational store for that access path | Statistics-based skipping is not an index and never will be |
| One tenant is 40% of the data | Partition by date and isolate that tenant | Clustering cannot fix a partition that a single tenant dominates |
| Queries stop filtering by date | Cluster or partition by tenant | Partitioning on a column nobody filters on costs maintenance and buys nothing |
| Daily volume falls to a few MB | Partition by month | Daily partitions on small volume recreate the small-file problem |
When not to partition at all
Under roughly 100 GB, on an engine that scans columns quickly, partitioning buys little and costs maintenance: every partition is metadata, and a table that is scanned whole gains nothing from being divided. Sort the data on the common predicate and stop.
Why the other options fail
- Partition by tenant id creates thousands of partitions, most of them tiny. Metadata and small-file overhead grow with partition count, and the 5% product scan now touches every one of them. This is the most common over-partitioning mistake, and it feels right because tenant is the thing the business cares about.
- Partition by event date and tenant id multiplies partition count by tenant count: 1,000 days × 3,000 tenants is millions of directories holding kilobytes each. It is the same mistake as above with a date in front of it.
- Add a secondary index on event id optimises the 15% and leaves the 80% untouched. Some engines do not support it; where they do, the index is maintained on every write and the dominant query pattern is still a full scan.