Sharded Postgres query traverses router, four shards, and distributed planner to return unified result

A sharded Postgres query begins with authentication handled by a router that mimics Postgres’s SCRAM-SHA-256 exchange. The router validates credentials and session settings before accepting the query, acting as a single entry point for the distributed system. This design isolates applications from shard topology, but the router itself becomes a critical dependency. Its failure would break all queries, even if individual shards remain healthy. The router’s implementation in Go suggests a focus on performance, but its role as a centralized bottleneck introduces operational risk.

The query then enters the protocol layer, where the router must support both Simple and Extended Postgres wire protocols. Simple protocol sends the entire SQL statement in one message, while Extended protocol splits it into Parse, Bind, Describe, Execute, and Sync steps. This dual support ensures compatibility with any Postgres client, but it also doubles the router’s parsing and planning workload. The router must reconstruct the query from fragmented messages in Extended mode, adding latency. For engineers, this means query performance can vary unpredictably based on driver settings, even for identical SQL.

Shard keys determine where rows are stored, but the example reveals a fundamental challenge: joined tables may not be co-located. Here, `customers` and `orders` are sharded by their primary keys, so a customer’s orders could reside on a different server. The router must fetch rows from multiple shards, perform the join in memory, and return a unified result. This distributed join is transparent to the application but introduces network latency and potential consistency issues if shards are out of sync. The trade-off is clear: sharding enables horizontal scaling, but at the cost of join performance and complexity in query planning.

The router’s distributed query planner must rewrite the original SQL to target individual shards. For the given query, it likely fetches `orders` from all shards first, then uses the `customer_id` values to request matching `customers` rows. This two-phase approach minimizes data transfer but requires careful handling of failures. If a shard is unavailable, the router must either fail the entire query or return partial results, depending on the system’s consistency guarantees. Engineers must design queries with sharding in mind, as operations that span many shards will incur higher latency and resource costs.