PostgreSQL 18 Sharding Survival Guide [2026 Cheat Sheet]

As of May 20, 2026, PostgreSQL 18.4 is the latest minor in the PostgreSQL 18 line. What it still does not ship is a cluster-wide shard manager. The native playbook is declarative partitioning plus foreign tables via postgres_fdw, which gives you distributed reads and writes with real tradeoffs around planning, transactions, and constraint safety. This reference compresses the commands, defaults, and failure modes you actually need.

• PostgreSQL 18.4 is the current PostgreSQL 18 minor release.
• Native sharding is still a composition of partitioning and postgres_fdw, not a single turnkey feature.
• enablepartitionpruning must stay enabled or coordinator queries fan out unnecessarily.
• postgres_fdw still does not support ON CONFLICT DO UPDATE on foreign tables.
• Remote transactions still cannot be prepared for two-phase commit.

What PostgreSQL 18 Actually Ships

What is native in PostgreSQL 18

• Declarative partitioning lets a parent table route rows to partitions with PARTITION BY.
• Partitions can be normal local tables or foreign tables, which is the core trick behind native-style sharding.
• postgres_fdw can push remote filters, fetch only needed columns, and run asynchronous scans across foreign tables when configured.
• IMPORT FOREIGN SCHEMA, CREATE SERVER, and CREATE USER MAPPING cover the metadata plumbing.

What is still missing

• No built-in cluster control plane for automatic shard placement, balancing, or resharding.
• No support in postgres_fdw for INSERT ... ON CONFLICT DO UPDATE on foreign tables.
• No support for preparing remote transactions for two-phase commit.
• No guarantee that constraints declared on foreign partitions are enforced locally; PostgreSQL assumes they are true for optimization.

Primary references: release notes, partitioning docs, postgres_fdw docs, and CREATE FOREIGN TABLE.

Command Finder and Setup

Use the filter to narrow the cheat sheet by task, object name, or option. The page shortcuts below let you jump through blocks without touching the mouse.

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER shard_east
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.0.0.21', port '5432', dbname 'app');

CREATE USER MAPPING FOR CURRENT_USER
  SERVER shard_east
  OPTIONS (user 'app_user', password 'replace_me');

CREATE TABLE orders (
  tenant_id bigint NOT NULL,
  region text NOT NULL,
  order_id bigint NOT NULL,
  created_at timestamptz NOT NULL,
  total_cents bigint NOT NULL
) PARTITION BY LIST (region);

CREATE FOREIGN TABLE orders_us_east
  PARTITION OF orders FOR VALUES IN ('us-east')
  SERVER shard_east
  OPTIONS (schema_name 'public', table_name 'orders');

CREATE SCHEMA IF NOT EXISTS fdw_stage;

IMPORT FOREIGN SCHEMA public
  LIMIT TO (orders, order_items)
  FROM SERVER shard_east
  INTO fdw_stage;

EXPLAIN (VERBOSE, COSTS ON)
SELECT region, count(*), sum(total_cents)
FROM orders
WHERE region IN ('us-east', 'us-west')
  AND created_at >= now() - interval '7 days'
GROUP BY region;

ANALYZE orders_us_east;

ALTER FOREIGN TABLE orders_us_east
  OPTIONS (ADD analyze_sampling 'auto');

SELECT * FROM postgres_fdw_get_connections(true);

SELECT postgres_fdw_disconnect('shard_east');
SELECT postgres_fdw_disconnect_all();

Keyboard Shortcuts

Why this matters on a sharding runbook

• Operational docs fail when they are slow to scan under pressure.
• Shortcuts turn this page into a usable incident aid instead of a passive blog post.
• Every pre block also gets a copy button on render for point-and-shoot execution.

Configuration That Matters

Keep these defaults in your head

• fetch_size default: 100.
• batch_size default: 1.
• async_capable default: false.
• useremoteestimate default: false.
• fdwstartupcost default: 100.
• fdwtuplecost default: 0.2.
• keep_connections default: on.
• parallel_commit and parallel_abort default: false.

Set options where they pay off

ALTER SERVER shard_east OPTIONS (ADD use_remote_estimate 'true');
ALTER SERVER shard_east OPTIONS (ADD parallel_commit 'true');
ALTER SERVER shard_east OPTIONS (ADD parallel_abort 'true');
ALTER SERVER shard_east OPTIONS (ADD keep_connections 'on');
ALTER SERVER shard_east OPTIONS (ADD use_scram_passthrough 'true');

ALTER FOREIGN TABLE orders_us_east OPTIONS (ADD fetch_size '1000');
ALTER FOREIGN TABLE orders_us_east OPTIONS (ADD batch_size '500');
ALTER FOREIGN TABLE orders_us_east OPTIONS (ADD async_capable 'true');

• Turn on useremoteestimate when remote cardinality errors hurt join order decisions more than an extra remote EXPLAIN.
• Raise fetch_size for scan-heavy analytics, but test memory and latency tradeoffs.
• Raise batch_size for bulk inserts and COPY into foreign tables.
• Enable async_capable only when concurrent foreign scans actually reduce wall-clock time.
• Use usescrampassthrough to avoid storing plain-text passwords in system catalogs, but remember every participating host must share identical SCRAM secrets for the same user.

Do not forget local pruning

SHOW enable_partition_pruning;

• If enablepartitionpruning is off, PostgreSQL will not optimize partition access as intended.
• Pruning is the difference between a shard-aware query and a coordinator-wide broadcast.

Advanced Usage and Failure Modes

Remote execution and pushdown

ALTER SERVER shard_east OPTIONS (ADD extensions 'pgcrypto');

• postgres_fdw ships built-in immutable operators and functions by default.
• Use the extensions option only when the same extension version and semantics exist on both sides.
• Mismatched extension behavior can make remote execution wrong, not just slow.

Transaction behavior to remember

• Remote transactions follow the local transaction, but they are not prepared for two-phase commit.
• When the local transaction is SERIALIZABLE, the remote one is SERIALIZABLE; otherwise the remote side uses REPEATABLE READ.
• That means even a local READ COMMITTED workflow can observe snapshot-stable remote reads inside the transaction.

Foreign partition correctness

• Constraints on foreign tables are assumed for optimization, not enforced by the core server.
• If a foreign partition contains rows outside its declared bound, query results can be wrong.
• Install matching constraints on the remote shard itself, not just on the coordinator metadata.
• It is possible to move a row from a local partition to a foreign-table partition when the FDW supports tuple routing, but not from one foreign-table partition to another.

Lock-aware partition maintenance

• Prebuild and then ATTACH PARTITION when possible to reduce lock intensity on the parent.
• DETACH PARTITION ... CONCURRENTLY is useful when retiring partitions with less disruption.
• Prefer scripted partition creation and retirement; manual shard DDL is a predictable source of outages.

Deployment Checklist

• Pick a shard key that appears in every latency-sensitive query.
• Keep coordinator tables partitioned on the same key you use for remote placement.
• Verify pruning with EXPLAIN before calling a query distributed-safe.
• Run ANALYZE on foreign tables after major remote data changes.
• Benchmark fetch_size, batch_size, and useremoteestimate with your real network latency.
• Avoid workflows that require atomic cross-shard commit until you have an application-level compensation design.
• Use SCRAM consistently if you enable usescrampassthrough.
• Track remote schema drift aggressively; foreign tables with mismatched types create subtle bugs.