Azure Synapse Analytics — Part 2: Internals — MPP, Distributions & Columnstore

maang.io System Design Series

What is this?

Back to the warehouse. In Part 1 the foreman split every question across a crew, each worker owning some of the 60 numbered bins. Now walk onto the floor and watch what actually happens when an order comes in. Two things decide whether the crew finishes in three seconds or three minutes. First: how the records are stored inside each bin — are they loose sheets you flip one at a time, or pre-sorted into tidy column-wise stacks you can scan in a blur? Second — and this is the big one: does any worker have to physically carry records over to another worker's desk before they can answer? That carrying — one worker walking boxes across the floor to another — is the slowest thing that happens in the building, and a great foreman arranges the bins so it almost never needs to.

Those two questions are the whole of Synapse internals: columnstore storage (how a bin holds data) and data movement (when the crew has to shuffle records between distributions). Get columnstore and you understand why scans are fast; get data movement and you understand why your query is slow.

The one-line idea: Every table is 60 shards of columnar, compressed storage. A query is a distributed plan run 60-ways in parallel. The dominant cost is not CPU or disk — it's the Data Movement Service shuffling rows between distributions to line up a join or aggregation. Distribution strategy exists for exactly one purpose: to make the answer computable without moving data.

Let's open a bin, then follow a query end to end.