PostgreSQL 18 relies on partitioning plus postgres_fdw for sharding; this cheat sheet covers setup, tuning, and distributed query patterns. Read now.
What PostgreSQL 18 sharding actually means
PostgreSQL 18 does not ship a separate distributed database engine. Sharding is still a composition of two mature pieces: native table partitioning to split a logical table into physical chunks, and postgres_fdw to place some of those chunks on remote nodes and query them as if they were local. That design keeps every node a normal PostgreSQL instance you already know how to back up, monitor, and upgrade, at the cost of requiring you to own the data placement and query plan discipline yourself.
Treat partitioning as the shard key boundary and FDW as the transport. The parent table stays the application-facing relation; partitions either live on the same cluster or map to foreign tables on shard servers. Clients write and read against the parent; the planner prunes partitions and, when possible, pushes work to the remote side. Failures usually come from a poorly chosen key, missing constraints that block pruning, or queries that force all shards to ship full row sets home.
Setup that survives production
Start with a single parent partitioned by a key that appears in almost every hot query—tenant id, account id, or a time bucket only when retention and archival matter more than even load. Create one partition per shard range or list value, then attach remote partitions as foreign tables that point at identically shaped tables on the target nodes. Keep schemas aligned: same column names, types, nullability, and check constraints so the planner can trust remote metadata and so ALTER scripts stay mechanical.
- Define foreign servers, user mappings, and connection options once; reuse them across all remote partitions.
- Mirror primary keys and unique constraints per shard; global uniqueness across shards needs an application rule or a carefully designed key space, not hope.
- Put the same indexes on remote tables that you would put on local partitions so remote scans and joins stay selective.
- Document which values map to which node so resharding is a planned move of partitions, not an ad-hoc data hunt.
Run a small canary: insert and select through the parent only, confirm partition pruning in EXPLAIN, then force a cross-shard read and write so you see FDW costs before traffic does.
Tuning remote partitions and the planner
Most distributed latency is not network round-trips alone—it is rows and columns transferred that never needed to leave the shard. Prefer predicates and joins that match the partition key so unnecessary shards drop out early. Keep statistics fresh on both coordinator and shard tables; stale remote stats produce bad join order and over-fetching. Use FDW options that batch fetches and ship useful quals and aggregates when the planner can apply them remotely, and avoid SELECT * patterns that inflate every payload.
On the coordinator, bound work_mem and parallel settings so a bad plan cannot pin memory while waiting on many shards. On each shard, tune for the workload that actually lands there—often fewer concurrent connections than the coordinator, but hotter sequential scans or index-only scans on the local slice. Timeouts, keepalives, and connection pooling between coordinator and shards matter as much as buffer cache: a hung remote session stalls the whole statement.
Distributed query patterns that work
Favor shard-local transactions: route each write so it touches one partition and one node. Multi-shard writes are possible but lose the simple atomicity story of a single PostgreSQL transaction unless you add application-level compensation or two-phase patterns you must operate carefully. For reads, push filters and aggregations down; pull intermediate results only when a global sort, distinct, or join truly needs them. Hash or list keys that co-locate related rows (for example all rows for one tenant) make the common path single-shard; use time-based keys mainly when you archive or detach old partitions cleanly.
When a query must span shards, make it explicit in the design: pre-aggregate per shard, then combine on the coordinator; or denormalize a small lookup so joins do not fan out. Resharding is moving or splitting partitions and updating the parent’s attachment map—practice that offline with a dry run so production is a controlled attach/detach, not a rewrite of the whole dataset. The survival rule is simple: co-locate what you join often, prune aggressively, and never assume the coordinator will magically fix a key that scatters every request.