Don’t Let postgres_fdw Pull the Whole Table Across the Network

Jim Knicely

postgres_fdw is incredibly useful when you need to query a remote PostgreSQL-compatible database from YugabyteDB.

But there is one performance rule you should never forget:

  • Always check what actually got pushed to the remote database.

A query can look simple:

				
					SELECT *
FROM foreign_users
WHERE username = 'alice';

But depending on collations, functions, joins, statistics, and FDW options, YugabyteDB may either:

  • 1. Push the filter to the remote database and fetch only matching rows.
  • 2. Pull a much larger result set across the network and filter locally.

That second case is where FDW performance can fall off a cliff.

Key idea: With postgres_fdw, the most important part of the plan is often not the top node. It is the Remote SQL line inside the Foreign Scan. If your WHERE clause is missing from Remote SQL, the local YugabyteDB node may be pulling extra rows across the network and filtering them locally.

To make this easier to understand, a demo is the best way to see what is really happening. 

In the example below, we will use two simple standalone YugabyteDB clusters started with yugabyted. One cluster will act as the local database, and the other will act as the remote database. This lets us clearly see when postgres_fdw pushes work to the remote side, and when YugabyteDB has to pull rows back and process them locally.

Demo Setup

For this demo, I used two simple standalone YugabyteDB clusters started with yugabyted.

One cluster acts as the local cluster, the other cluster acts as the remote cluster.

				
					yugabyted start \
  --advertise_address=127.0.0.1 \
  --base_dir=~/yb01 > start01.log

yugabyted start \
  --advertise_address=127.0.0.2 \
  --base_dir=~/yb02 > start02.log

Verify each cluster is running:

				
					ysqlsh -h 127.0.0.1 -c "SELECT host FROM yb_servers();"

ysqlsh -h 127.0.0.2 -c "SELECT host FROM yb_servers();"

Expected output:

				
					.  host
-----------
 127.0.0.1

and:

				
					.  host
-----------
 127.0.0.2

In this example:

				
					127.0.0.1 = local cluster
127.0.0.2 = remote cluster

Step 1: Create the Remote Table

Connect to the remote cluster:

				
					ysqlsh -h 127.0.0.2

Create a database and sample table:

				
					CREATE DATABASE remote_db;

\c remote_db

CREATE TABLE source_users (
    user_id  INT PRIMARY KEY,
    username VARCHAR(50),
    email    VARCHAR(100),
    status   VARCHAR(20)
);

INSERT INTO source_users (user_id, username, email, status) VALUES
(1, 'jim', 'jim@example.com', 'active'),
(2, 'jane', 'jane@example.com', 'inactive'),
(3, 'lucy', 'lucy@example.com', 'active');

Create a user for the FDW connection:

				
					CREATE USER fdw_user WITH PASSWORD 'SecurePassword123';

GRANT ALL PRIVILEGES ON TABLE source_users TO fdw_user;

Step 2: Configure postgres_fdw on the Local Cluster

Connect to the local cluster:

				
					ysqlsh -h 127.0.0.1

Create a local database:

				
					CREATE DATABASE local_db;

\c local_db

Enable the extension:

				
					CREATE EXTENSION postgres_fdw;

Create the foreign server:

				
					CREATE SERVER yb_remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
    host '127.0.0.2',
    port '5433',
    dbname 'remote_db',
    fetch_size '10000',
    use_remote_estimate 'true'
);

Create the user mapping:

				
					CREATE USER MAPPING FOR yugabyte
SERVER yb_remote_server
OPTIONS (
    user 'fdw_user',
    password 'SecurePassword123'
);

Create the local foreign table:

				
					CREATE FOREIGN TABLE foreign_users (
    user_id  INT,
    username VARCHAR(50),
    email    VARCHAR(100),
    status   VARCHAR(20)
)
SERVER yb_remote_server
OPTIONS (
    table_name 'source_users'
);

Now query the remote table from the local cluster:

				
					SELECT *
FROM foreign_users;

Expected result:

				
					.user_id | username |        email      |  status
---------+----------+-------------------+----------
       1 | jim      | jime@example.com  | active
       2 | jane     | hane@example.com  | inactive
       3 | lucy     | lucye@example.com | active
Note: A foreign table is not a restored local copy of the remote table. It is a local definition that points to a remote object. If you use IMPORT FOREIGN SCHEMA, it imports foreign table definitions, not the remote table’s physical data, indexes, or full constraint structure.

Good Case: The WHERE Clause Is Pushed Down

Run an EXPLAIN:

				
					EXPLAIN (VERBOSE)
SELECT *
FROM foreign_users
WHERE status = 'active';

Example plan:

				
					Foreign Scan on public.foreign_users  (cost=120.00..213.23 rows=5 width=398)
  Output: user_id, username, email, status
  Remote Filter: ((foreign_users.status)::text = 'active'::text)
  Remote SQL: SELECT user_id, username, email, status FROM public.source_users WHERE ((status = 'active'))

This is good.

The important line is:

				
					Remote SQL: SELECT user_id, username, email, status FROM public.source_users WHERE ((status = 'active'))

The filter was pushed to the remote cluster.

That means the remote cluster evaluates:

				
					WHERE status = 'active'

and only sends matching rows back to the local cluster.

Partial Pushdown: Some Work Is Remote, Some Work Is Local

Now add a volatile function:

				
					EXPLAIN (VERBOSE)
SELECT *
FROM foreign_users
WHERE status = 'active'
  AND user_id > (random() * 0);

Example plan:

				
					Foreign Scan on public.foreign_users  (cost=120.00..213.28 rows=2 width=398)
  Output: user_id, username, email, status
  Filter: ((foreign_users.user_id)::double precision > (random() * '0'::double precision))
  Remote Filter: ((foreign_users.status)::text = 'active'::text)
  Remote SQL: SELECT user_id, username, email, status FROM public.source_users WHERE ((status = 'active'))

This is not terrible, but it is important to understand.

The status = 'active' predicate was pushed down.

The random() predicate was not.

So the remote cluster filters by status, but the local cluster still applies this part:

				
					Filter: ((foreign_users.user_id)::double precision > (random() * '0'::double precision))

That means rows can still be transferred across the network only to be discarded locally.

What to look for:
Remote Filter means the remote database is doing some filtering before sending rows back.
Filter means the local YugabyteDB node is applying that filter after rows have already been fetched from the remote database.

Bad Case: Collation Prevents Pushdown

A common silent performance killer with FDW is collation mismatch.

Try this:

				
					EXPLAIN (VERBOSE)
SELECT *
FROM foreign_users
WHERE username COLLATE "C" = 'alice';

Example plan:

				
					Foreign Scan on public.foreign_users  (cost=120.00..246.88 rows=5 width=398)
  Output: user_id, username, email, status
  Filter: ((foreign_users.username)::text = 'alice'::text)
  Remote SQL: SELECT user_id, username, email, status FROM public.source_users

This is the dangerous pattern.

The filter is local:

				
					Filter: ((foreign_users.username)::text = 'alice'::text)

The remote SQL has no WHERE clause:

				
					Remote SQL: SELECT user_id, username, email, status FROM public.source_users

That means the remote cluster sends the rows back first, and the local cluster filters afterward.

For a tiny demo table, this does not matter. For a production table with millions or billions of rows, this can turn a simple lookup into a large cross-network scan.

Join Trap: Local Join Pulls Remote Rows

Now create a small local table:

				
					CREATE TABLE local_departments (
    dept_id   INT PRIMARY KEY,
    username  VARCHAR(50),
    dept_name VARCHAR(50)
);

INSERT INTO local_departments VALUES
(101, 'jane', 'Engineering');

Run a join between the local table and the foreign table:

				
					EXPLAIN (VERBOSE)
SELECT u.username, d.dept_name
FROM foreign_users u
JOIN local_departments d
  ON u.username = d.username;

Example plan:

				
					Merge Join  (cost=421.30..521.30 rows=5000 width=236)
  Output: u.username, d.dept_name
  Merge Cond: ((u.username)::text = (d.username)::text)
  ->  Foreign Scan on public.foreign_users u  (cost=259.72..282.22 rows=1000 width=118)
        Output: u.user_id, u.username, u.email, u.status
        Remote SQL: SELECT username FROM public.source_users ORDER BY username ASC NULLS LAST
  ->  Sort  (cost=161.58..164.08 rows=1000 width=236)
        Output: d.dept_name, d.username
        Sort Key: d.username
        ->  Seq Scan on public.local_departments d  (cost=20.00..111.76 rows=1000 width=236)
              Output: d.dept_name, d.username

This plan does push the remote sort:

				
					ORDER BY username ASC NULLS LAST

But it still pulls the remote usernames back and performs the join locally.

That can be fine for small tables.

It can be very expensive when the foreign table is large and the local table is small.

Better Pattern: Push Remote Logic to the Remote Side

If multiple remote tables live on the same remote database, avoid importing all of them and joining them locally unless you have verified the plan.

Instead, consider creating a remote view that performs the remote join, filter, or aggregation on the remote database.

On the remote cluster:

				
					CREATE VIEW active_users AS
SELECT user_id, username, email, status
FROM source_users
WHERE status = 'active';

On the local cluster, expose the view as a foreign table:

				
					CREATE FOREIGN TABLE foreign_active_users (
    user_id  INT,
    username VARCHAR(50),
    email    VARCHAR(100),
    status   VARCHAR(20)
)
SERVER yb_remote_server
OPTIONS (
    table_name 'active_users'
);

Then query:

				
					SELECT *
FROM foreign_active_users;

This pattern helps keep remote work remote.

Important FDW Options

These options are often worth reviewing before using postgres_fdw in production.

Option Default Why It Matters
fetch_size 100 Controls how many rows are fetched per remote fetch operation. Larger values can reduce network round trips for queries that return many rows.
use_remote_estimate false Allows the local planner to ask the remote server for row count and cost estimates using remote EXPLAIN. This can improve plans for complex queries, but adds planning overhead.
fdw_tuple_cost 0.2 Represents the extra per-row cost of transferring data between servers. Consider tuning when the remote server is across a high-latency network.
batch_size 1 Controls how many rows are inserted per remote insert operation. This is critical for bulk inserts through a foreign table.
keep_connections on Keeps remote FDW connections open for reuse in the local session. Useful for performance, but important to understand when many local sessions may create many remote connections.

Example:

				
					ALTER SERVER yb_remote_server OPTIONS (
    SET fetch_size '10000',
    SET use_remote_estimate 'true',
    SET fdw_tuple_cost '0.2'
);

For write-heavy FDW workloads:

				
					ALTER SERVER yb_remote_server OPTIONS (
    ADD batch_size '100'
);

If you see this notice:

				
					NOTICE:  no server_type specified. Defaulting to PostgreSQL.
HINT:  Use "ALTER SERVER ... OPTIONS (ADD server_type '<type>')" to explicitly set server_type.

You can make the intent explicit:

				
					ALTER SERVER yb_remote_server OPTIONS (
    ADD server_type 'postgresql'
);

Insert Example: Why batch_size Matters

A single-row insert through the foreign table works as expected:

				
					INSERT INTO foreign_users (user_id, username, email, status)
VALUES (4, 'david', 'david@example.com', 'active');

Verify it:

				
					SELECT *
FROM foreign_users
WHERE user_id = 4;

Expected result:

				
					.user_id | username |       email       | status
---------+----------+-------------------+--------
       4 | david    | david@example.com | active

Now look at a multi-row insert before tuning batch_size:

				
					EXPLAIN (ANALYZE, VERBOSE)
INSERT INTO foreign_users (user_id, username, email, status)
VALUES
(5, 'eve', 'eve@example.com', 'active'),
(6, 'frank', 'frank@example.com', 'inactive');

Example plan:

				
					Insert on public.foreign_users  (cost=0.00..0.03 rows=0 width=0) (actual time=3.328..3.329 rows=0 loops=1)
  Remote SQL: INSERT INTO public.source_users(user_id, username, email, status) VALUES ($1, $2, $3, $4)
  Batch Size: 1
  ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=398) (actual time=0.003..0.008 rows=2 loops=1)

The key line is:

				
					Batch Size: 1

That means rows are not being batched for the remote insert.

Enable batching:

				
					ALTER SERVER yb_remote_server OPTIONS (
    ADD batch_size '100'
);

Run another insert:

				
					EXPLAIN (ANALYZE, VERBOSE)
INSERT INTO foreign_users (user_id, username, email, status)
VALUES
(7, 'grace', 'grace@example.com', 'active'),
(8, 'heidi', 'heidi@example.com', 'inactive');

Example plan:

				
					Insert on public.foreign_users  (cost=0.00..0.03 rows=0 width=0) (actual time=19.599..19.600 rows=0 loops=1)
  Remote SQL: INSERT INTO public.source_users(user_id, username, email, status) VALUES ($1, $2, $3, $4)
  Batch Size: 100
  ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=398) (actual time=0.004..0.006 rows=2 loops=1)

Now the important line is:

				
					Batch Size: 100

Even though the Remote SQL shape still shows one parameterized row, the FDW path is now configured to batch inserts.

For larger inserts, this can reduce the number of remote insert operations dramatically.

Example:

				
					INSERT INTO foreign_users (user_id, username, email, status)
SELECT
    i,
    'user_' || i,
    'user_' || i || '@example.com',
    'active'
FROM generate_series(1000, 1999) AS i;

Without batching, this type of operation can behave like many small remote operations.

With batch_size, rows can be grouped into larger remote insert batches.

Production reminder: postgres_fdw is convenient, but it is not magic. Every row that cannot be filtered, joined, grouped, or sorted remotely may need to move across the network. In YugabyteDB, that cost can stack on top of the normal distributed execution costs inside the local and remote clusters.

Production Checklist

What You See Meaning Action
Remote SQL includes the WHERE clause Good pushdown Remote database filters before sending rows back.
Filter appears above a generic Remote SQL Local filtering Check for collation differences, volatile functions, unsupported functions, or expressions that are not safe to ship.
Remote SQL includes ORDER BY Remote sort pushdown Good, but verify whether too many rows are still being transferred.
Hash or merge join over a foreign scan Possible remote table streaming Verify row counts. Consider remote views, better statistics, nested loop testing, or local caching for small lookup tables.
Batch Size: 1 on inserts Rows inserted one at a time remotely Set batch_size for bulk insert workloads.

Final Takeaway

postgres_fdw can be a great tool for accessing remote PostgreSQL or YugabyteDB data from YSQL.

But the performance difference between a good FDW query and a bad FDW query can be massive.

The best habit is simple:

				
					EXPLAIN (ANALYZE, VERBOSE)

Then inspect:

				
					Remote SQL
Remote Filter
Filter
Batch Size

If the remote SQL contains the important filtering, sorting, joining, or aggregation logic, you are usually in good shape.

If the remote SQL is generic and the local plan is doing the real work, you may be pulling far more data across the network than you realize.

That is when postgres_fdw stops feeling like a feature and starts acting like a hidden performance bottleneck.

Have Fun!

Meet the YugabyteDB team (including me!) at Amazon Web Services (AWS) Summit New York and discover AI-native, distributed YugabyteDB… built for global scalability, multi-region consistency, and ultra-low latency! 🚀

📆AWS New York – 17 June📆

Location: Jacob Javits Convention Center

Booth Number: #900 (Expo Hall is located on Level 3)

Stop by the booth (previewed above) to win cool prizes and chat with database experts. Schedule a personalized meeting to discover how you can build fast, run anywhere, and survive anything!💡

https://lnkd.in/gQUzbAUH