When SQL Runs on MPP: Greenplum and Cloudberry

The finale of the whole SQL series. The previous 11 posts all ran on single-node PostgreSQL; this one puts the same SQL onto MPP (Massively Parallel Processing) — Greenplum, and its open-source successor Apache Cloudberry (incubating at Apache). The good news: because Cloudberry descends from Greenplum and Greenplum descends from PostgreSQL, the syntax barely changes, and everything in the previous 11 posts applies directly; the bad news: you gain one thing the single-node era never made you think about — which node the data lives on. And that decision governs the speed of every query you run.

From one node to MPP: a coordinator plus a pile of segments

The MPP architecture is intuitive: one coordinator (the brain) receives queries, plans and dispatches; below it a pile of segments (each really an independent PostgreSQL instance) each holds a share of the data and processes its own share in parallel. How a table is spread across the segments is decided by the distribution key:

The power of parallelism rests on “every node has about the same amount of work”. So the distribution key’s worst enemy is data skew — if you pick a column with concentrated values (say “country”, where 80% of rows are one country) as the key, that whole country crowds into one segment, the others sit idle while it works itself to death, and parallelism is written off.

Pick the wrong distribution key and data “moves between nodes”

Beyond skew, the distribution key has a second, more hidden duty: deciding whether a join / group by has to move data between nodes. When you join two tables, if “the data to be matched” happens to be on the same segment, each node can join locally; if it’s scattered across segments, the data has to be moved together over the network first — MPP calls that movement a Motion, and it’s the MPP version of the Spark shuffle:

There are several kinds of Motion, matching exactly the moves you learned in Spark: Redistribute Motion (both tables re-hashed and spread by the join key, = shuffle join), Broadcast Motion (a small table copied to every segment, = broadcast join), Gather Motion (collecting each segment’s results back to the coordinator). And in the execution plan these Motions are listed plainly — seeing a big table Redistributed is, like seeing Seq Scan in PG or Exchange in Spark, the signal to stop and ask “can this movement be avoided”.

Principles for choosing a distribution key

Putting it together, choosing a distribution key has two goals, and they sometimes fight:

• Spread evenly (avoid skew): pick a high-cardinality, evenly distributed column (user_id, order_id), not one with concentrated values (country, status, boolean).
• Match locally (avoid motion): pick the column most often used to join as the key. Especially for big-table-to-big-table joins, give both tables the same join key as their distribution key — then data for the same key is on the same segment by nature, the join moves nothing, and that’s the single most important move for MPP performance.

When you truly can’t have both (the join key happens to be badly skewed), you trade off, or use Broadcast to copy the small table away. The moment you write DISTRIBUTED BY on a table, you’ve decided the fate of a whole pile of future queries — the biggest way MPP differs from a single node.

Reflections

Every MPP difficulty comes back to one sentence: move less data

Distribution key, motion, skew — a pile of terms, but underneath only one thing: where the data is, and whether it has to move. That’s fundamentally the same physics as the Spark shuffle — put “data that will be joined / grouped together” together and nothing moves; put it wrong and it has to cross the network, and network movement is always the most expensive step in distributed computing. Coming to MPP after learning the Spark shuffle is nearly painless: new names (Exchange → Motion, broadcast join → Broadcast Motion), identical reasoning. The physics of performance is shared across engines — which is why I keep saying that learning one distributed engine thoroughly gets you most of the way with the others.

The difficulty of distributed isn’t syntax, it’s “location”

The biggest takeaway of this post: from a single node to MPP, the SQL barely changes, but you gain a whole extra layer to think about — the “location” of data. On one node you only worry about “how to write the query”; on MPP you first worry about “how the data is distributed, where the computation happens, whether it has to move”. And that decision is fixed at table creation; get it wrong and everything after is slow. It confirms something for me: the real difficulty of distributed systems is never the API, it’s location and movement — where data lives, where it’s computed, when it has to cross a boundary. That’s exactly the central proposition of the separation of compute and storage post I’ve been writing and DDIA’s partitioning chapter; MPP is just one very concrete miniature of it.

The whole series is really about one thing: don’t be fooled by “it runs”

Writing the last post and looking back at these twelve, the shared theme is really one sentence: SQL is very easy to “get running”, but “running right, running fast” requires seeing through the mechanism underneath. Processing order, how JOIN matches, NULL’s three-valued logic, how windows compute, how indexes look up, how to read EXPLAIN, how MVCC isolates, how MPP distributes — every post takes apart something “you thought you understood, but could merely use”. Only after seeing through the mechanism do you go from “can write SQL” to “understand SQL”. That’s what this series’ name means — SQL: I thought I knew it; and having read this round, I hope you, like me, really know it a little better.