Can anyone give a quick explanation of what FDW offers beyond the existing dblink functionality? I haven't used either, but it seems like it'd be helpful to many to spell out the difference.
There are a lot of conceptual differences, but I'll give a couple very important examples:
* With FDWs, you can push down predicates to the remote end. So, if you do: "SELECT * FROM myRemoteTable WHERE id = 2345", then it can just use the index on the remote side rather than pulling all the data to the local side and filtering.
* You can (as of 9.3) insert, update, and delete on the local side and it will pass through to the remote side.
Comments
Can anyone give a quick explanation of what FDW offers beyond the existing dblink functionality? I haven't used either, but it seems like it'd be helpful to many to spell out the difference.
There are a lot of conceptual differences, but I'll give a couple very important examples:
* With FDWs, you can push down predicates to the remote end. So, if you do: "SELECT * FROM myRemoteTable WHERE id = 2345", then it can just use the index on the remote side rather than pulling all the data to the local side and filtering.
* You can (as of 9.3) insert, update, and delete on the local side and it will pass through to the remote side.
DBLink connects to remote PostgreSQL databases only.
FDW extends that idea to arbitrary data stores. Wrappers exist for Oracle, MySQL, Redis, Amazon S3, text files, etc.