The SPQR team is proud to release the first production-ready version of the SPQR (Stateless Postgres Query Router).

SPQR is a query router for horizontal scaling of PostgreSQL via sharding. SPQR is designed to be as lightweight as a connection pooler, reducing knowledge of the data schema to the necessary minimum.

Main features

• Sharding. If possible, the router determines a shard from the first statement of a transaction and keeps client bound to that shard until transaction ends. But you can explicitly specify a shard or a sharding key in a comment request.
• Transaction and session pooling. Just as in your favorite connection pooler (Odyssey or PgBouncer).
• Multiple routers for fault tolerance. The router stores the sharding rules only for cache purposes. Information about the entire installation is stored inside the QDB service, so the number of routers running simultaneously is unlimited.
• Shard rebalancing. Data migration between shards aims to balance the workload across shards proportionally. The main idea is to minimize any locking impact during these migrations, which is accomplished by reducing the size of the data ranges being transferred.
• Multiple servers and failover. In the router configuration, it is possible to specify multiple servers for one shard. Then the router will distribute read-only queries among the replicas. However, in addition to the automatic routing, you also have the option to explicitly define the destination for a specific query by using the target-session-attr parameter within the query.
• Minor overhead for query execution. See benchmarks here and here.

Status of the project

Essential set of features of the SPQR is used in production. But automatic shard rebalancing is not production ready yet. Also, SPQR supports some cross-shard queries, but they have inconsistent snapshot and are not 2PC-locked to provide true cross-shard atomicity.

Currently, the project is being maintained by Yandex Cloud. However, we are looking for new architects and contributors to broaden our technical views. As always, any feedback is welcome.

Links

SPQR is developed and distributed at https://github.com/pg-sharding/spqr.

Released versions can be found at the releases page.