🔄 PostgreSQL Data Transfer: `pg_fasttransfer` vs `postgres_fdw`

Transferring large volumes of data between PostgreSQL databases can be challenging when performance, security, and simplicity matter. In this article, we compare two approaches:

pg_fasttransfer: a custom PostgreSQL extension that wraps a high-performance binary tool
postgres_fdw: the official PostgreSQL Foreign Data Wrapper for federated queries

We’ll look at what each does, how to use them, benchmark the same data transfer task, and compare their capabilities.

⚡ What Is `pg_fasttransfer`?

pg_fasttransfer is a custom PostgreSQL extension that calls an external tool (FastTransfer) to perform parallel, batched, encrypted data transfer between two PostgreSQL instances.

It is designed for high-speed ETL operations, making it ideal for data warehouses, migrations, and automated pipelines.

🧩 What Is `postgres_fdw`?

postgres_fdw is a Foreign Data Wrapper included with PostgreSQL. It allows you to:

Connect to a remote PostgreSQL server
Access remote tables as if they were local
Query and join remote and local data

It works well for distributed queries and data integration, but is not optimized for large-scale data migration.

🛠️ How to Use Each Tool

1. Using `pg_fasttransfer`

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_fasttransfer;

-- Create the target table
CREATE TABLE Target_Table;

-- Launch the high-performance transfer
SELECT * FROM xp_RunFastTransfer_secure(
  sourceconnectiontype := 'pgcopy',
  sourceserver := 'localhost:15433',
  sourceuser := 'postgres',
  sourcepassword := '...encrypted...',
  sourcedatabase := 'postgres',
  sourceschema := 'public',
  sourcetable := 'orders',
  targetconnectiontype := 'pgcopy',
  targetserver := 'localhost:5432',
  targetuser := 'postgres',
  targetpassword := '...encrypted...',
  targetdatabase := 'postgres',
  targetschema := 'public',
  targettable := 'orders2',
  degree := 8,
  method := 'Ctid',
  loadmode := 'Truncate',
  batchsize := 1048576,
  mapmethod := 'Position',
  fasttransfer_path := 'D:\pacollet\FastTransfer'
);

⏱ Time Taken: 14.9 seconds 📦 Data: 15 million rows × 9 columns ✅ Exit Code: 0 (success)

2. Using `postgres_fdw`

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Create a foreign server connection
CREATE SERVER postgres_docker_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port '5432', dbname 'postgres');

-- Map a local user to a remote user
CREATE USER MAPPING FOR CURRENT_USER
SERVER postgres_docker_server
OPTIONS (user 'postgres', password 'postgres');

-- Import the remote schema/table (e.g., "orders2")
IMPORT FOREIGN SCHEMA public
LIMIT TO (orders2)
FROM SERVER postgres_docker_server
INTO public;

-- Create a local table to store the data
CREATE TABLE orders2_local (LIKE orders2 INCLUDING ALL);

-- Transfer the data
INSERT INTO orders2_local
SELECT * FROM orders2;

⏱ Time Taken: ~ 201 seconds 📦 Data: 15 million rows × 9 columns

📊 Performance Comparison

Tool	Rows Transferred	Columns	Time Taken	Method
`pg_fasttransfer`	15,000,000	9	14.9 seconds	Parallel + batched copy
`postgres_fdw`	15,000,000	9	201 seconds	Sequential fetch & insert

✅ Feature Matrix

Feature	`pg_fasttransfer`	`postgres_fdw`
Native PostgreSQL extension	❌ No (requires external binary)	✅ Yes
Encrypted password support	✅ Yes (via `pgp_sym_encrypt`)	❌ No
Parallel transfer (multi-threaded)	✅ Yes (`degree := N`)	❌ No
Batched processing	✅ Yes (`batchsize := N`)	❌ No
Logical remote access (query join)	❌ No	✅ Yes
Ideal for large data migration	✅ Yes	❌ Not optimized
One-line command for full transfer	✅ Yes	❌ Requires setup + query
Portable (built-in)	⚠️ Requires binary	✅ Fully portable
Compatible with Windows/Linux	✅ Yes	✅ Yes
Supports other DBMS (SQL Server, etc)	✅ Yes (via CLI adapters)	❌ No (PostgreSQL only)

🏁 Conclusion

🚀 Choose `pg_fasttransfer` when performance matters

If your goal is high-performance, secure, and reliable bulk data transfer, especially for large volumes (millions of rows), pg_fasttransfer is the clear winner. It is:

Blazingly fast — transfers can be 10x faster thanks to parallelization and batching.
Secure — supports encrypted passwords via pgp_sym_encrypt.
Flexible — works across PostgreSQL and other database systems (SQL Server, MySQL, etc.).
Production-ready — ideal for ETL pipelines, database migrations, and bulk imports/exports.

✅ Use pg_fasttransfer for:
Large table transfers, cross-DB migrations, automated backups, data lake ingestion, and any workload where performance is critical.

🔗 Choose `postgres_fdw` for live querying

On the other hand, postgres_fdw is the best choice if your use case requires:

Real-time access to remote PostgreSQL tables.
Querying across databases (joins, filters, etc.).
Minimal setup, especially in homogenous PostgreSQL environments.

✅ Use postgres_fdw for:
Federated queries, microservices needing shared data access, and low-volume read scenarios where speed isn't the bottleneck.

Final thoughts

Both tools have their place — but if you're handling large data transfers, and especially if you're dealing with multiple DBMS, pg_fasttransfer gives you a major edge.

Our services

PostgreSQL Data Transfer: pg_fasttransfer vs postgres_fdw

🔄 PostgreSQL Data Transfer: `pg_fasttransfer` vs `postgres_fdw`

⚡ What Is `pg_fasttransfer`?

🧩 What Is `postgres_fdw`?