dblink

What dblink does

dblink provides a set of functions that open connections to remote PostgreSQL databases and execute arbitrary SQL. The core function, dblink(connstr, query), sends a query to a remote server and returns the results as a set of records. Because the return type is anonymous, you must declare the column names and types explicitly in an AS clause — every time, without exception, even when the query is perfectly obvious. One grows accustomed to this. It is the defining characteristic of the dblink API and its main usability trade-off.

Beyond simple queries, dblink provides named connections that persist for the duration of a session, dblink_exec for executing non-SELECT statements (INSERT, UPDATE, DELETE, DDL), and an async pair — dblink_send_query / dblink_get_result — for dispatching queries without blocking.

dblink predates postgres_fdw and was the original way to query across PostgreSQL databases. While postgres_fdw is now the recommended approach for persistent cross-database access, dblink remains useful for ad-hoc queries, remote DDL execution, and async query patterns that postgres_fdw simply never learned.

When to use dblink

dblink fills a specific niche. For most cross-database access patterns, postgres_fdw is the better choice. dblink is the right tool when:

• Cross-database queries on the same server — PostgreSQL does not natively allow queries across databases. dblink is the simplest way to pull data from another database on the same instance without setting up foreign tables.
• One-off remote queries — when you need to run a quick query against a remote database without the setup overhead of creating a foreign server, user mapping, and foreign table definitions.
• Remote DDL execution — postgres_fdw cannot execute DDL on remote servers. If you need to create indexes, alter tables, or run other DDL on a remote database, dblink_exec is the tool.
• Async query execution — dblink_send_query lets you dispatch a query and continue other work while it runs, collecting results later with dblink_get_result.
• Legacy code — existing applications and scripts that were written before postgres_fdw existed (PostgreSQL 9.3+) and use dblink for cross-database access.

Installation and setup

dblink is a contrib module that ships with PostgreSQL — no additional packages or downloads are needed. It does not require shared_preload_libraries, so no server restart is necessary.

-- dblink is a contrib module — ships with PostgreSQL
CREATE EXTENSION dblink;

-- Verify it's installed
SELECT * FROM pg_extension WHERE extname = 'dblink';

After installation, any user with EXECUTE permission on the dblink functions can open connections to remote databases. Consider restricting access with REVOKE if not all users should have remote query capabilities.

Practical examples

Basic remote query

The simplest usage: pass a connection string and a query, then declare the expected result columns in the AS clause.

-- Query a remote database using an inline connection string
SELECT *
FROM dblink(
  'host=remote-server dbname=analytics user=reader password=secret',
  'SELECT id, name, total FROM customers WHERE active = true'
) AS t(id int, name text, total numeric);

Note the AS t(id int, name text, total numeric) at the end. You will be writing this sort of thing rather often with dblink. It is not optional, and it must match exactly what the remote query returns. Get a column type wrong and PostgreSQL will inform you, politely but firmly.

Cross-database queries on the same server

PostgreSQL does not allow joins across databases natively. dblink bridges this gap.

-- Query another database on the same server
-- (common use case — PostgreSQL doesn't allow cross-database queries natively)
SELECT *
FROM dblink(
  'dbname=other_database',
  'SELECT table_name FROM information_schema.tables WHERE table_schema = ''public'''
) AS t(table_name text);

Named connections and dblink_exec

Named connections persist for the session and avoid reconnecting on every call. dblink_exec handles INSERT, UPDATE, DELETE, and DDL statements.

-- Open a named connection for reuse within the session
SELECT dblink_connect('analytics', 'host=remote-server dbname=analytics user=reader');

-- Run queries using the named connection
SELECT *
FROM dblink('analytics', 'SELECT id, name FROM customers LIMIT 10')
AS t(id int, name text);

-- Execute a non-SELECT statement on the remote database
SELECT dblink_exec('analytics', 'UPDATE customers SET synced = true WHERE id = 42');

-- Close the connection when done
SELECT dblink_disconnect('analytics');

Remote DDL execution

Unlike postgres_fdw, dblink can execute DDL on remote databases — creating indexes, altering tables, or any other administrative command.

-- dblink_exec can run DDL on remote databases — something postgres_fdw cannot do
SELECT dblink_exec(
  'host=remote-server dbname=staging user=admin',
  'CREATE INDEX idx_orders_date ON orders (created_at)'
);

Async queries

This is dblink's party trick — the one capability that postgres_fdw has no answer for. Dispatch a query without blocking, then collect results when ready. Useful for parallelizing work across multiple remote databases or running long reports in the background.

-- Open a named connection
SELECT dblink_connect('slow_report', 'dbname=analytics');

-- Send a long-running query without waiting for results
SELECT dblink_send_query('slow_report',
  'SELECT department, sum(revenue) FROM sales GROUP BY department'
);

-- Check if the query is still running (returns 1 if busy, 0 if done)
SELECT dblink_is_busy('slow_report');

-- Collect results when ready
SELECT *
FROM dblink_get_result('slow_report')
AS t(department text, total_revenue numeric);

-- Must call once more to clear the connection for reuse
SELECT * FROM dblink_get_result('slow_report') AS t(dummy text);

-- Clean up
SELECT dblink_disconnect('slow_report');

dblink vs postgres_fdw

For ongoing access to remote tables — regular queries, joins with local data, application-level reads and writes — postgres_fdw is the clear choice. For quick one-off queries, remote DDL, and async execution, dblink remains the more practical instrument. Knowing which to reach for is the kind of distinction that separates a well-managed household from a busy one.

Cloud availability

As a core contrib module, dblink is available on virtually all managed PostgreSQL services. Some providers restrict outbound network connections from the database, which may limit dblink's ability to connect to external hosts. Check your provider's networking documentation for details.

How Gold Lapel relates

I should be forthcoming here. Gold Lapel sits between your application and PostgreSQL, but dblink connections are established by the PostgreSQL backend itself using libpq — they leave through a side door, so to speak, bypassing the proxy entirely. I see the local query that invokes dblink. I do not see the SQL it dispatches to the remote server.

If you need the remote database's queries optimized as well, that database should have its own Gold Lapel proxy. Each household requires its own staff.

In practice this is rarely a concern. dblink is typically employed for occasional cross-database queries, data migration, or administrative tasks — not the high-frequency application queries where optimization matters most. Those flow through Gold Lapel as they should.