Supabase’s Foreign Data Wrapper (FDW) lets you query data from other PostgreSQL databases as if it were local, without ETL.
Let’s say you have a PostgreSQL database running on a different server, and you want to access a table named users in a schema called auth from your Supabase project.
Here’s how you’d set it up. First, you need to install the postgres_fdw extension in your Supabase database.
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
This makes the FDW functionality available. Now, you define a "foreign server" that points to your external PostgreSQL instance. You’ll need the hostname, port, and database name of that external server.
CREATE SERVER external_db_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'your_external_db_host.example.com', port '5432', dbname 'external_database_name');
You also need to tell Supabase how to authenticate with the external database. This is done by creating a "user mapping."
CREATE USER MAPPING FOR postgres -- or your Supabase role
SERVER external_db_server
OPTIONS (user 'external_db_user', password 'your_external_db_password');
Replace your_external_db_user and your_external_db_password with the actual credentials for your external PostgreSQL database. It’s crucial that this user has SELECT privileges on the auth.users table in the external_database_name.
Finally, you define a "foreign table" in Supabase that mirrors the structure of the remote table.
CREATE FOREIGN TABLE local_external_users (
id UUID,
email TEXT,
created_at TIMESTAMP WITH TIME ZONE
)
SERVER external_db_server
OPTIONS (schema_name 'auth', table_name 'users');
Notice that schema_name and table_name in the OPTIONS refer to the remote schema and table. The column names and types in the CREATE FOREIGN TABLE statement must match the actual columns and types of the auth.users table in your external database.
Now, you can query local_external_users just like any other table in your Supabase project.
SELECT id, email
FROM local_external_users
WHERE created_at > '2023-01-01';
Supabase’s FDW handles translating this query into a request to the external_db_server, fetching only the necessary data, and returning it to your application. This avoids the need to copy large datasets and keeps your data synchronized in near real-time.
The most surprising thing is that the FDW doesn’t just fetch rows; it pushes down operations like WHERE clauses and even JOINs (under certain conditions) to the remote server. This means the external database does most of the heavy lifting, making queries significantly more efficient than pulling all data locally and filtering it.
Consider a scenario where you have user profile data in one PostgreSQL database and order history in another. You can use FDWs to join these two datasets without moving data.
-- Assuming 'orders' table exists in the external DB
CREATE FOREIGN TABLE external_orders (
order_id INT,
user_id UUID,
order_date DATE,
total_amount DECIMAL
)
SERVER external_db_server
OPTIONS (schema_name 'public', table_name 'orders');
SELECT u.email, o.order_date, o.total_amount
FROM local_external_users u
JOIN external_orders o ON u.id = o.user_id
WHERE o.order_date > '2023-10-01';
When you execute this query, the FDW will analyze the join condition and the WHERE clause. It will likely send a query to the external_db_server that looks something like:
SELECT u.email, o.order_date, o.total_amount
FROM auth.users u -- (if FDW can access it via another FDW or directly)
JOIN public.orders o ON u.id = o.user_id
WHERE o.order_date > '2023-10-01';
The postgres_fdw extension supports various options for tuning performance, such as fetch_size to control how many rows are retrieved at once, and use_remote_estimate to allow the remote server to provide row count estimates for planning.
The FDW handles data type conversions between the local and remote databases. However, if a data type exists in the remote database but not in the local one, or vice-versa, you might encounter errors. Ensure compatible types or perform explicit casting in your queries if necessary.
One detail that often trips people up is how UPDATE and DELETE statements work. For UPDATE and DELETE to function correctly on a foreign table, the remote table must have a primary key or a UNIQUE constraint that is accessible to the FDW. Without this, the FDW doesn’t know which specific row to modify or delete on the remote server, and these operations will fail.
The next step is often dealing with performance bottlenecks when querying large, remote datasets.