Organising for query efficiency.
WHAT PARTITIONING DOES
Divides data so queries read only relevant sections.
WHAT TO PARTITION BY
The column most queries filter on, usually a date.
WHY DATE USUALLY
Most analytical queries are bounded by time.
WHAT PARTITION PRUNING IS
The engine skipping partitions that cannot match.
WHAT PREVENTS IT
Filtering on a derived value rather than the partition column Type mismatches Functions applied to the partition column
WHAT OVER-PARTITIONING CAUSES
Very many small files, and metadata overhead exceeding the benefit.
WHAT UNDER-PARTITIONING CAUSES
Scanning far more than needed.
WHAT TO AIM FOR
Partitions large enough to be efficient, small enough to prune usefully.
WHAT CLUSTERING OR SORTING PROVIDES
Ordering within partitions, so statistics allow skipping further.
WHAT TO CLUSTER BY
High-cardinality columns frequently filtered on.
WHAT COMPACTION DOES
Merges small files into larger ones.
WHY IT IS NECESSARY
Incremental loads produce small files continuously.
WHAT TO SCHEDULE
Compaction, regularly.
WHAT TO MONITOR
File counts and sizes per table.
