The dblink module establishes a persistent connection with the other database. The dblink functions track the details of that connection, so you don't need to worry about doing so yourself. You should be aware that this is an external resource, and so the generic programming problem of resource leaks becomes possible. If you forget about your connection and forget to disconnect it, you may experience problems later. The remote connections will be terminated should your session disconnect.
Note that the remote connection persists even across transaction failures and other errors, so there is no need to reconnect.
The postgres_fdw extension can manage connections transparently and efficiently, so if your use case does not involve commands other than SELECT, INSERT, UPDATE, and DELETE then you should definitely go for it.
The dblink() module executes the remote query and will assemble the result set in the memory before the local reply begins to be sent. This means that very large queries might fail due to lack of memory, and everybody else will notice that. This isn't a problem; dblink is simply not designed to handle bulk data flows. Look at the Loading data from flat files recipe in Chapter 5, Tables and Data, if that's what you want to do.
Running slightly larger queries can be achieved using cursors. They allow us to bring the answer set back in smaller chunks. Conceptually, we need to open the cursor, loop while fetching rows until we are done, and then close the cursor. An example query for that is as follows:
postgres=# SELECT dblink_open('example',
'SELECT generate_series(1,3)', true);
dblink_open
-------------
OK
(1 row)
postgres=# SELECT *
FROM dblink_fetch('example', 10, true)
AS link (col1 integer);
col1
------
1
2
3
(3 rows)
Notice that we didn't need to define the cursor when we opened it, though we do need to define the results from the cursor when we fetch from it, just as we did with a normal query. For instance, to fetch 10 rows at a time, we can do this:
postgres=# SELECT *
FROM dblink_fetch('example', 10, true)
AS link (col1 integer);
col1
------
(0 rows)
postgres=# SELECT dblink_close('example');
dblink_close
--------------
OK(1 row)
The dblink module also allows you to use more than one connection. Using just one connection is generally not good for modular programming. For more complex situations, it's good practice to assume that the connection you want is not the same as the connection that another part of the program might need. The dblink module allows named connections, so you don't need to hope that the default connection is still the right connection. There is also a function named dblink_get_connections() that will allow you to see which connections you have active.