DEVOURED
The lifecycle of a sharded Postgres query
PlanetScale's Neki router manages sharded Postgres by handling complex join logic and connection pooling, exposing the reality that shard-key choice is the most consequential performance factor.
What: The Neki router makes sharded clusters appear as a single Postgres server by orchestrating authentication, protocol parsing, and query planning. It executes cross-shard joins by streaming results into memory or spilling to disk when necessary.
Why it matters: This deep dive highlights how distributed databases balance the trade-off between the flexibility of sharding and the performance overhead of cross-shard network requests and re-aggregation.
Deep dive
- Routers hide the distributed nature of the cluster by implementing the full Postgres authentication and wire protocol.
- Query planning must reconcile the authoritative schema metadata with the data topology cached in
etcd. - Cross-shard joins are often executed using hash joins built inside the router memory.
- Memory limits are handled by spilling join partitions to temporary disk storage.
- Protocol optimization (copying binary results directly) reduces CPU overhead during re-aggregation.
- Co-locating related data (e.g., sharding orders by customer_id) eliminates the need for expensive router-level joins.
Decoder
- Scatter-gather: A query pattern where a router sends a request to all shards and then aggregates the individual responses into a single result.
- Shard key: The specific column used to calculate the hash that determines which physical server (shard) holds a specific row of data.
- Hash join: An algorithm used to combine two datasets by building a hash table of one input in memory and probing it with the other input.
Original article
Full article content is not available for inline reading.
Explore all 74 articles in today's digest
Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi