In PostgreSQL, what do you get by attaching postgres_fdw foreign tables as partitions of a partitioned table, and where does that do-it-yourself sharding break down?
answer
- foreign tables as partitions
- pruning still happens locally
- concurrent scans across servers
- no unique index across shards
- no atomic cross-shard commit
basics
~20 sA partitioned table whose partitions are foreign tables on different servers gives local partition pruning, concurrent remote scans with async_capable, and per-shard aggregate pushdown. It lacks global unique constraints, atomic cross-shard writes and one consistent snapshot.
solid answer
~50 sYou create a partitioned table locally and add partitions with `CREATE FOREIGN TABLE ... PARTITION OF ... SERVER ...`, each pointing at a different server. The planner prunes partitions locally, so a query with the partition key touches one shard; `async_capable` lets an `Append` scan several servers at once (`Async Foreign Scan`); and `enable_partitionwise_aggregate`, off by default, lets each shard compute a `GROUP BY` on the partition key itself. It breaks down on guarantees: no unique or primary-key index can exist on a parent with foreign partitions, the partition bound is trusted rather than checked, an `UPDATE` cannot move a row out of a foreign partition, writes to two shards commit without two-phase commit, each shard reads its own snapshot, and DDL, key generation and rebalancing are all manual. It suits read-mostly reporting, not a sharded OLTP write path.
code
sql · 19 linesCREATE TABLE finance.invoices_all (
invoice_id bigint NOT NULL,
region text NOT NULL,
issued_at timestamptz NOT NULL,
amount_due numeric(12,2) NOT NULL
) PARTITION BY LIST (region);
CREATE FOREIGN TABLE finance.invoices_eu
PARTITION OF finance.invoices_all FOR VALUES IN ('eu')
SERVER billing_eu
OPTIONS (schema_name 'billing', table_name 'invoices', async_capable 'true');
CREATE FOREIGN TABLE finance.invoices_us
PARTITION OF finance.invoices_all FOR VALUES IN ('us')
SERVER billing_us
OPTIONS (schema_name 'billing', table_name 'invoices', async_capable 'true');
SET enable_partitionwise_aggregate = on;
SELECT region, sum(amount_due) FROM finance.invoices_all GROUP BY region;go deeper
Recall that a foreign table can be a partition of a partitioned table, which lets one table span several PostgreSQL servers.
Explain local partition pruning, what async_capable and partitionwise aggregation add, and why the partition bound is only trusted.
List what the pattern cannot guarantee, global uniqueness, atomic multi-shard writes and a consistent snapshot, and design around each for a real reporting workload.
Decide when do-it-yourself FDW sharding is an acceptable reporting layer and when the write path needs a dedicated sharding system with its own operational cost.
## The pattern The billing team has split invoices by region across three PostgreSQL servers, `billing_eu`, `billing_us` and `billing_apac`, each holding `billing.invoices` for its region. The finance database wants one table to query. PostgreSQL's declarative partitioning accepts foreign tables as partitions: 1. Create a local partitioned table, `finance.invoices_all`, partitioned by `LIST (region)`. 2. Create one foreign server and user mapping per billing server. 3. Add each shard with `CREATE FOREIGN TABLE finance.invoices_eu PARTITION OF finance.invoices_all FOR VALUES IN ('eu') SERVER billing_eu OPTIONS (...)`. Queries against `finance.invoices_all` now fan out to the shards. This is the classic do-it-yourself sharding built from core PostgreSQL and postgres_fdw alone. ## What works - **Partition pruning** happens on the finance database, before any remote call. `WHERE region = 'eu'` touches only `billing_eu`. - **Concurrent scans.** With `async_capable 'true'` on the foreign tables or servers, and `enable_async_append` (on by default), an `Append` over several shards shows `Async Foreign Scan` nodes and waits on all servers at once instead of one after another. It helps only across different servers: postgres_fdw runs queries to one server sequentially over one connection per user mapping. - **Per-shard aggregation.** With `enable_partitionwise_aggregate = on` (off by default), a `GROUP BY` that includes the partition key is computed per partition, and postgres_fdw pushes each partition's aggregate to its server (`Relations: Aggregate on (...)` per shard). - **Partitionwise joins** (`enable_partitionwise_join`, also off by default) can join co-partitioned tables shard by shard, and each pair can then be pushed to its server when both sides live there. - **Insert routing.** An `INSERT` into the parent is routed to the matching foreign partition, because postgres_fdw supports tuple routing. ## Reading what the planner did `EXPLAIN VERBOSE` on the regional total shows the pattern working or not. Under the `Append`, each surviving shard appears as a `Foreign Scan` (or `Async Foreign Scan` when `async_capable` is on). With partitionwise aggregation, each one carries `Relations: Aggregate on (...)` and a `Remote SQL` line containing `GROUP BY`, so every billing server returns one row per region. Without it, the same plan shows every invoice row streaming back to the finance database and a single local aggregate above the `Append`: correct, but it moves the whole table across the network. ## Where it breaks down | Limit | What happens | Mitigation | |---|---|---| | No global unique key | a unique or primary-key index on the parent is refused while it has foreign partitions, and a foreign table cannot be attached to a parent that has one | enforce uniqueness per shard and make keys globally unique by construction | | Partition bound not checked | rows on a shard that violate its bound are trusted, so pruning can hide them or return wrong answers | add a matching `CHECK (region = 'eu')` on each remote table | | No row movement out | an `UPDATE` that changes `region` on a row in a foreign partition fails | delete and re-insert, or never change the partition key | | No atomic cross-shard write | postgres_fdw has no two-phase commit; one shard can commit while another fails | keep each business write on one shard; make the rest idempotent | | No global snapshot | each shard's remote transaction takes its own snapshot at first use | accept approximate cross-shard totals, or report from a frozen copy | | Missing SQL | `INSERT ... ON CONFLICT DO UPDATE` is unsupported on foreign tables | upsert on the shard directly | | Manual operations | DDL, key generation and moving data between shards are all yours | script schema changes per shard | The theory behind fan-out costs, cross-shard joins and resharding is engine-neutral and belongs elsewhere; what is specific here is that postgres_fdw gives you the routing and pushdown, but none of the cluster-wide guarantees. ## When it is the right tool 1. **Read-mostly reporting** across regional servers, which is exactly the finance use: one query surface over data that is written elsewhere. 2. **Archiving cold partitions**, where old years live on a cheaper server as foreign partitions while recent ones stay local. 3. **A stepping stone**, to learn the access patterns before adopting a purpose-built sharding extension. It is the wrong tool for a sharded OLTP write path, where global uniqueness, atomic multi-shard writes and rebalancing are daily needs; that is what dedicated sharding extensions such as Citus provide, which is a topic of its own.
- Why does an UPDATE that changes region fail for a row stored in a foreign partition?PostgreSQL can move a row from a local partition into a foreign one, but not out of a foreign partition. The update would leave the row violating its shard's partition bound, so it fails, provided the remote server enforces a matching constraint. Delete the row on one shard and insert it on the other instead.
- How do you stop a wrong-region row landing on the eu shard when someone writes to billing_eu directly?Put `CHECK (region = 'eu')` on the remote `billing.invoices` table. The finance database never checks that a foreign partition's rows match its bound; it trusts the bound for pruning, so a stray row would be invisible to `WHERE region = 'us'` and wrongly counted under `eu`.
- Foreign-partition sharding gives atomic commits across all shards.
- A primary key on the parent enforces uniqueness across every shard.
- PostgreSQL checks that rows on each foreign partition match its partition bound.
- async_capable opens several connections to one server to scan it in parallel.
- Partitionwise aggregation is on by default in PostgreSQL.