[chrisjc]

Wait, you're telling me that you can create materialized views using foreign tables in Postgres?

Is there a way to propagate changes from the foreign data source through the FDW to Postgres?

Or would it just be some kind of task polls the foreign data source pulling a delta?

[radiowave]

The only option that Postgres's materialized views give you is to "refresh" the view, meaning: re-run the query used to define the view, fetching an entirely new dataset, and use that to replace all the existing data in the view.

There's no facility for deltas, or any other way of propagating changes.

Edit: And to answer your original question, yes you can absolutely base the materialized view on data obtained from a foreign table.

[burmecia]

You can create a custom function doing all the delta logic and use pg_cron to schedule it to periodically materialize data using FDW.

[chrisjc]

Yeah, this was the approach I was imagining before someone mentioned Materialized View (proper).

Once I heard those words mentioned, I began to imagine what sort of interaction with a foreign data source's operations log FDW might have.

Of course, and depending on the foreign data source, you could probably even expose an operations log to Postgres via a FDW by defining it as a foreign table... Effectively opening up the possibility of eventually consistent replication. Lots of data stores' operations logs are accessible data collections/tables anyways.

All of this is exciting stuff!