You always have the latency/bandwidth overhead from moving queries/data between instances, but FDW performance can be surprisingly fast. There's a performance-FDW complexity spectrum and you can choose a point on it that's applicable to your use case.

At its simplest, the FDW can just return all tuples from the remote database without filtering them (letting the local DB run filtering). But more advanced FDWs like postgres_fdw[0] can push down qualifiers and joins to the remote database. postgres_fdw even runs EXPLAIN on the remote instance and parses its output -- essentially letting the local and the foreign query planners collaborate on execution.

[0] https://www.postgresql.org/docs/current/postgres-fdw.html#id...