Comparison
PartitioningvsPartitioning strategy
Partitioning
you split the giant table into monthly chunks so the query touches one of them and dropping old data is a DDL statement instead of a delete.
Splitting one logical table into physical pieces by a key, usually range or hash, inside a single database. The wins are partition pruning at query time and cheap bulk deletion by dropping a partition. It only helps queries that filter on the partition key; everything else now scans every partition.
Full entry →Partitioning strategy
you partitioned by customer id, there are two million customers, and now every query lists two million tiny folders.
The choice of what a table is physically split by, which sets how much data a filtered query can avoid reading. It is the highest-leverage physical decision in a warehouse and the hardest to change later, because changing it rewrites the table. The two failure modes are opposite and equally common: too coarse and nothing is pruned, too fine and you have the small files problem plus metadata that dwarfs the data.
Full entry →