The short answer
Table partitioning divides table data into smaller logical subsets based on a boundary column, typically a date. In Synapse Dedicated SQL Pool, table partitioning must be designed with extreme care due to the underlying 60-distribution architecture.
The 60-distribution multiplier
When you create a partitioned table in Synapse, the partitioning is applied independently across each of the 60 distributions.
If you a table by day for one full year (365 days): 60 distributions * 365 daily partitions = 21,900 separate physical storage partitions.
Table Partitions +-------------------------------------------------+ | Distribution 1: 365 daily rowgroups | | Distribution 2: 365 daily rowgroups | | ... | | Distribution 60: 365 daily rowgroups | | Total Physical Segments: 21,900 segments! | +-------------------------------------------------+
Remember that each columnstore rowgroup needs roughly 1 million rows to achieve optimal compression and segment elimination. To fill 21,900 physical rowgroups with 1 million rows each, your table would need 21.9 billion rows.
If your table only contains 50 million rows, daily partitioning will create thousands of tiny, fragmented rowgroups with only a few hundred rows each. This ruins compression ratios and causes queries to scan thousands of tiny files, running slower than an unpartitioned table.
Practical partitioning rule
- Do not partition tables with fewer than 100 million rows.
- Partition by month or week rather than by day to avoid partition explosion.
- Partition primarily when your pipeline requires fast partition switching (sliding window metadata swaps to drop old data instantly) rather than purely for query filtering.