postgres_fdw: from where, is the resource utilized? remote or local?

Question

I have an OLTP database and ETL jobs are running in the same database in the background.

I was thinking of separating the OLTP and ETL instances so that resource utilization would be distributed. Basically, the OLTP instance should have fewer or no ETL overhead.

The idea is to create foreign tables on the ETL instance connecting to OLTP remote server using postgres_fdw.

I understand that Postgres will fetch chunks of data from the remote server using the cursor.

Can someone please help me if my understanding is right that running a complex query including foreign tables would use resources(RAM,CPU) from the local server? and is the remote server safe from these executions overhead?

And if I am wrong which instance resources would Postgres use to run a complex SQL with joins on foreign tables?

Thanks in advance!

Answer 1

FDW is not a performance enhancing method. It will generally be slower, sometimes dramatically slower, then having the data all in place and running the queries locally. It will consume resources on both sides plus the network communications to connect them. You can use EXPLAIN (VERBOSE) to see what queries will get passed on to the foreign side, versus what will be run locally. There is no recursive EXPLAIN reporting (alas), so your local plan can only tell you what query text will get sent, not what the plan for that query on the foreign side will be. There is not much generalization that can be done, if you want to see how a specific query will be divided up, you need to look at the plan and see.

• Sajith P Shetty: Hi @jjanes Thanks and sorry for the delay in response. In that case how best can we use Postgres in data warehouse technology? I want to separate my data source OLTP and use another target instance for OLAP.