Database Reference
In-Depth Information
SELECT *
FROM dblink('otherdb',
'SELECT * FROM bigtable') AS link ( … )
WHERE filtercolumn > 100;
We would need to explicitly add the
WHERE
clause onto the remote query, like the following:
SELECT *
FROM dblink('otherdb',
'SELECT * FROM bigtable' ||
' WHERE filtercolumn > 100') AS link ( … );
which means that, in general, setting up views of remote data isn't very helpful, as it
encourages users to think that the table location doesn't matter, whereas from a performance
perspective, it definitely does. This isn't really any different from other federated or remote
access database products.
There are also a few performance considerations that you may wish to consider. The first is
that when the remote query executes, the current session waits for it to complete. You can
also execute queries without waiting for them to return using the following functions:
F
dblink_send_query()
F
dblink_is_busy()
F
dblink_get_result()
If you are concerned about the overhead of connection time, then you may want to consider
using a session pool. This will reserve a number of database connections that will allow
you to reduce apparent connection time. Look at the connection-pool recipes in the
Server
Control
chapter.
See also
Another, sometimes easier way of accessing other databases is with a tool named PL/Proxy.
PL/Proxy allows you to create a local database function that is a proxy for a remote database
function. PL/Proxy only works for functions, and some people regard that as a restriction, and
that is why I explained dblink for the main part of this recipe.
Creating a local proxy function is simple:
CREATE FUNCTION my_task(VOID)
RETURNS SETOF text AS $$
CONNECT 'dbname=myremoteserver';
SELECT my_task();
$$ LANGUAGE plproxy;
