Anurag’s Read

The lifecycle of a sharded Postgres query.

A clear walk through how a Postgres-compatible router authenticates, parses, plans and joins across shards. It shows why the shard key choice decides join cost.
Score
8.7
Verdict
Must read
Blog and date
PlanetScale,
Original length and level
23 min, advanced
Topics
sharding, query planning, postgres, distributed systems
Read the original

Context

Neki makes a sharded Postgres cluster look like one server to the application. The example has customers and orders tables on four shards. Both tables are hashed by their own id, so a customer's orders sit on other shards.

Architecture

The client connects to a router, which completes TLS and SCRAM authentication and reports ReadyForQuery. The router accepts simple and extended protocol messages and parses the SQL into an AST. It checks for a cached plan, then plans using cached schema and topology.

The plan has one route node per table and a join in the router. Route nodes send Parse, Bind, Execute and Sync messages inside protobuf requests to a sidecar next to each Postgres. The sidecar runs the query on a pooled connection and streams rows back, and the router joins and returns them.

Mechanisms

M1

Planning inputs

Topology is a JSON file in etcd that lists shard groups, hash indexes and key ranges. The example uses xxhash on id with four ranges. Schema comes from the authoritative shard and refreshes periodically, not per query.

M2

Join choice

A nested loop would run a scatter query for every customer. A hash join loads customers into a router hash table, then scatters one orders query. The planner builds from customers because 100,000 rows need less memory than about 300,000 orders. If the table exceeds its memory budget, both inputs partition by join key and join one partition at a time.

M3

Sidecar pooling

Each Postgres connection is a backend process that uses memory even when idle. The sidecar lets many routers share warm connections. It resets session settings and checks the role before each use, which prevents role poisoning.

M4

Aggregates

For AVG across shards, the router asks for SUM and COUNT per shard, combines them and divides. Averaging shard averages gives wrong answers.

Design decisions

No.ChoseInstead ofWhy
D1Hash join in the routerNested loop joinThe nested loop needs thousands of shard queries, while the hash join needs 8.
D2Sidecar with pooled gRPC streamsDirect router connections to PostgresDirect connections waste Postgres memory and do not share well across many routers.
D3Copy encoded values without decodingThis saves CPU on the return path.

Key numbers

  • Four shards, 100,000 customers and 1,000,000 orders in the example.
  • About 300,000 orders match the default date estimate.
  • Hash join needs 8 shard queries and moves about 400,000 rows.
  • Better key design needs 4 requests.
  • Parser is about 18,000 lines of Go.

Failures and limits

  • Caches of schema and topology can be stale between refreshes.
  • Memory pressure in the router hash table forces a spill to disk.
  • The post names failure handling as a goal but gives few details.

Lessons you can use

  • Shard key choice decides which joins stay local.
  • Count the network requests a plan needs, not only the rows.
  • Pool connections close to the database, because each connection costs memory.
  • Rewrite aggregates so partial results combine correctly.

Glossary

Scatter routeA query sent to every shard because it has no shard key filter.
SidecarA helper process that runs next to each Postgres instance.
SCRAM-SHA-256A password login method that never sends the password.
PortalPostgres state that tracks one running bound statement.