Database field guide · 04
Distributed SQL
For PostgreSQL developers
How familiar SQL, indexes, joins, and transactions change when storage and consensus span nodes and regions.
Franck Pachot7 chapterssharding · consensus · distributed PostgreSQL
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.
01
Distribution adds distance
A monolithic PostgreSQL execution can access shared memory and local storage. Distributed SQL preserves a relational interface while rows, indexes, and transaction participants live on different nodes. Every physical operation must therefore be evaluated in network round trips as well as CPU and storage.
- Logical SQL portability does not imply identical physical cost.
- One local nested loop can become thousands of remote requests.
- Batching and pushdown are central execution techniques.
02
Tablets make placement explicit
Distributed tables are divided into ranges or hash partitions, often called tablets. Hash distribution spreads write load; range distribution preserves locality and ordered access. The primary key usually participates in this physical decision even when SQL syntax looks ordinary.
- Hash for broad distribution and point access.
- Range for locality, scans, and time-oriented retention.
- Avoid monotonically growing hotspots unless splitting and placement absorb them.
03
Indexes are distributed tables
A global secondary index has its own key order and distribution. Updating one base row may synchronously update remote index entries through a transaction. That preserves SQL semantics but makes every additional index a distributed write path.
- Align index keys with filtering, ordering, and distribution needs.
- Covering can avoid a remote base-table lookup.
- Measure write amplification and tablet hotspots.
04
Joins need request locality
A distributed optimizer tries to send predicates and joins toward the data, batch outer keys, and reduce returned rows. Co-location can help tightly coupled small tables, but requiring every relationship to share a shard key gives up much of relational flexibility.
- Filter before crossing the network.
- Batch nested-loop keys when the inner side is indexed.
- Use denormalization only when measured communication cost justifies it.
05
Transactions cross consensus groups
Atomic commits across tablets coordinate multiple replicated groups. Serializable histories, conflict detection, clock uncertainty, and retries become visible operational concerns. This is not weaker ACID; it is ACID paying the latency required by failure tolerance and distance.
- Keep distributed transactions focused and retryable.
- Expect contention errors as part of the API.
- Do not put irreversible external effects inside an uncommitted retry loop.
06
Regions are a business decision
Leader placement, read replicas, follower reads, and transaction geography trade freshness, latency, and resilience. A global topology should follow where writes originate and which failures the application must survive, not a generic multi-region diagram.
- Put leaders near dominant write paths.
- State the staleness contract for local reads.
- Test failover latency and client behavior, not only server recovery.
07
Field manual
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
01
Choose hash or range distribution deliberately
Hash sharding transforms the distribution key into a token and spreads adjacent keys across tablets. It balances point writes but destroys key locality. Range sharding keeps adjacent values together, enabling ordered scans and targeted retention, but a growing edge can become hot. In YugabyteDB, HASH and ASC/DESC in a primary key express this physical choice.
CREATE TABLE account_event (
tenant_id bigint,
event_time timestamptz,
event_id uuid,
payload jsonb,
PRIMARY KEY ((tenant_id) HASH, event_time DESC, event_id)
);
Count RPCs, not just SQL operators
A nested loop that performs one remote lookup per outer row has latency proportional to request count. Distributed executors batch outer keys into array or IN probes and push predicates to tablet servers. EXPLAIN ANALYZE should be read for storage requests, rows scanned, and network execution time in addition to familiar PostgreSQL nodes. Returning ten rows after reading a million remote rows is still an expensive plan.
EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT o.id, i.sku
FROM orders o JOIN order_item i ON i.order_id = o.id
WHERE o.customer_id = 42
ORDER BY o.created_at DESC LIMIT 20;
Model transaction latency as consensus work
A write is durable after the relevant tablet leader replicates it to a quorum. A transaction touching multiple tablets also records transaction status and coordinates commit. Geographic latency therefore follows the slowest required quorum and transaction participants, not the number of SQL statements alone. Keep a transaction within one placement when practical, but preserve the business invariant before optimizing locality.
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Retry the complete unit on serialization or restart errors.
Treat a secondary index as another table
A global secondary index has its own tablets and Raft groups. Each base-table write may create a distributed index write in the same transaction. Covering columns can remove a second network hop from index to base row, but increase replication volume. Evaluate read RPC savings against write amplification and tablet count rather than copying a monolithic index set.
CREATE INDEX order_customer_recent_ix
ON orders (customer_id HASH, created_at DESC, id)
INCLUDE (status, total);
08
Source articles
Optional deep dives with the complete experiments and product-version context behind this guide.
