Back to blog

Single-Shard OLTP First: The Most Important Design Choice in SPQR

The most important thing about SPQR is not that it supports sharding, but the kind of sharding it optimizes for. SPQR is designed around single-shard OLTP queries. That sounds narrow, but it is a strong engineering choice.

TL;DR

• SPQR optimizes the common OLTP path: one query goes to one shard.
• Keeping work on one shard avoids distributed coordination and makes horizontal scaling practical.
• The trade-off is deliberate data modeling: a good shard key keeps latency-sensitive operations local.
• Cross-shard queries and transactions remain available, but they are exceptions rather than the default workload shape.

Why Single-Shard OLTP

OLTP systems usually execute many small queries:

• fetch one account
• insert one order
• update one user's settings
• read one tenant's configuration
• append one device event

These operations are latency-sensitive and frequent. If each of these queries can be routed to exactly one shard, horizontal scaling becomes much more practical.

• Each shard handles a subset of traffic.
• Routers make routing decisions.
• PostgreSQL remains the storage engine.
• Capacity grows by adding shards.

The original idea behind this design is straightforward: take PostgreSQL and run it on multiple machines so they work as a single system. For that to actually scale, each instance must handle its own portion of the workload independently. If every query touches every shard, adding more machines does not help—it just adds coordination overhead. That is why single-shard queries are the foundation. They are the operations that truly benefit from horizontal scaling.

This does not mean cross-shard queries should be avoided entirely. They exist, and they are useful. But overusing them undermines the very reason for sharding in the first place. If most of your traffic requires coordination across shards, you are paying the cost of a distributed system without getting the scalability benefit.

The Cost of Cross-Shard

The alternative is to optimize for arbitrary cross-shard SQL. That is a much harder problem because cross-shard queries require coordination. Cross-shard snapshots require consistency machinery. Cross-shard joins require data movement or distributed execution. Cross-shard transactions require commit protocols and recovery behavior. All of that adds complexity, latency, and failure modes.

PostgreSQL itself provides building blocks in this direction: postgres_fdw can push work to remote PostgreSQL servers and scan multiple foreign partitions asynchronously. These features are useful, but they do not make distributed coordination free.

How the Routing Path Works

SPQR chooses a different center of gravity: keep the common transactional path fast and explicit.

This is visible in the architecture (see SPQR Architecture in 4 Minutes for a full overview): the router is a lightweight layer that speaks PostgreSQL protocol, receives client queries, and routes them to shards. The coordinator manages metadata. Shards are normal PostgreSQL clusters.

For a single-shard query, the ideal path is simple:

Consider a multi-tenant application sharded by tenant_id. A typical query looks like this:

SELECT order_id, total, status
FROM orders
WHERE tenant_id = 42 AND created_at > now() - interval '7 days';

The router parses this query, extracts tenant_id = 42 from the WHERE clause, looks up which shard holds tenant 42, and forwards the query directly to that single PostgreSQL instance. No coordination, no fan-out, no merging of partial results. The shard executes it as a normal local query and returns the rows.

Compare that with a query that lacks a shard key:

SELECT count(*) FROM orders WHERE status = 'pending';

This query has no tenant_id filter, so the router cannot determine which shard to use. It must scatter the query to all shards and combine the results. That still works, but it is slower and more expensive—exactly the kind of operation SPQR treats as an exception rather than the norm.

Choosing a Shard Key

The cost of this simplicity is that the data model matters. A useful shard key keeps hot paths local, distributes traffic evenly, and appears in most latency-sensitive queries.

A good shard key is an application design decision. If most queries are scoped by tenant_id, then tenant_id may be a good key. If user data is naturally independent, user_id may work. If events belong to devices, device_id can be reasonable.

This is why SPQR's single-shard OLTP focus is useful: it forces the right conversation early. Which operations are local? Which data should be colocated? Which queries are truly global? Which reports belong in another pipeline?

Cross-Shard Is Still Possible

But this design choice does not mean that SPQR cannot support cross-shard operations. There are situations where you need them:

• Scatter queries can be useful for administration.
• Bulk operations matter for loading data.
• Distributed transactions exist as a tool, with the usual performance and recovery concerns.

But these should be treated as special cases, not the default shape of the system.

The goal is not to build a distributed database that mimics every behavior of a single PostgreSQL server. The goal is to keep PostgreSQL useful beyond the limits of one primary node. Single-shard OLTP first is the choice that makes that goal practical.

The single-shard path solves the steady state. The next challenge is keeping that path fast while data and traffic change. In the next article, we'll look at data movement and balancing: how SPQR redistributes key ranges without turning growth into an outage.