PostgreSQL 12 High Availability Cookbook - Third Edition

Improvements in PostgreSQL 9.6

Two things that changed in PostgreSQL 9.6 are both associated with deferring certain actions to the remote server. In PostgreSQL 9.6, JOIN and ORDER BY operations are actually transmitted to the remote system, though there are some restrictions:

• Joined foreign tables exist on the same SERVER. In our case, this would be primary_db.

• The remotely joined tables must be distinct. Our third query example was a self-join, which is unfortunately not supported by the pushdown logic.
• We don't want to sort and join at the same time.

Basically, this means that we could create pgbench_branches as a foreign table and joining it with pgbench_accounts would be done by the remote system. We could also sort the results of ...