CSE Roadmap

Specialist tier · about 2 hours · 5 parts

Part 4 of 5 · 2 min read

Scatter-gather queries

A document-partitioned secondary index answers a query by asking every partition and combining whatever comes back.

Scatter-gather queries

Think of a researcher who needs every book on a subject. They call every single branch library one by one, asking each to check its own local catalog, then combine all of the answers into one list.

In plain words: a scatter-gather query is a query that must be sent to every partition, since no single partition can be known in advance to hold all, or even any, of the matching rows, with the results from every partition then combined into a final answer. Document-partitioned secondary indexes make every query on that index a scatter-gather query, since each partition only knows about its own rows. Scatter-gather queries scale poorly as the number of partitions grows, since the query's total latency depends on the slowest partition's response, and every partition must do at least some work even if it holds no matching rows.

A worked example. A document-partitioned index on order date, queried for "March 1st," sends the same query to all 10 partitions, waits for every one of them to respond, and merges their individual results into one combined list, even though several of those partitions may have no matching rows.

The key lines. No new SQL; a scatter-gather query is executed by the database's query coordinator issuing the same underlying query to every partition.

Scatter-gather query

A scatter-gather query is a query sent to every partition because no single partition is known in advance to hold the matching rows, with each partition's results combined into one final answer.

Example: calling every branch library to ask about a subject, then combining every branch's answer, is a scatter-gather query.

Remember this

Document-partitioned indexes trade expensive scatter-gather reads for cheap, local writes. Term-partitioned indexes trade that back: cheap, targeted reads for writes that may touch a separate partition.

Quick notes

  • A row's primary partition is decided by its primary partitioning key; a secondary index has no such natural grouping.
  • A document-partitioned, or local, secondary index is cheap to write but requires a scatter-gather query to read.
  • A term-partitioned, or global, secondary index is cheap to read but can make a single row's write touch another partition.
  • A scatter-gather query is sent to every partition and merges whatever each one returns.
  • The right secondary-indexing approach depends on whether the workload favors cheap writes or cheap targeted reads.