Some questions about materialized views

[coderwf]

Can we use pathway to implement the function of incremental materialized view? Are there any related cases or projects?

[lewymati]

Hi @coderwf
You can use pw.io.postgres.write_snapshot and Pathway will incrementally maintain a postgres table.

If the source data reside in a database (as I presume, since you mention views), you will need a CDC (change data capture) system like debezium. You may want to check out debezium connector in Pathway: https://pathway.com/developers/api-docs/pathway-io/debezium

[coderwf]

@lewymati Sounds good. The actual scenarios are slightly more complex, such as issues like duplicate consumption, efficiency problems caused by view count inflation, recovery after machine restarts, etc. I'll continue to explore Pathway's documentation. Thank you for your response.

[gaozuo]

Hello Pathway Team,

I’m using pw.io.postgres.reflect_snapshot to maintain a snapshot of a PostgreSQL table and joining it with a CDC streaming table (via pw.io.debezium from Kafka). I have a question regarding the real-time behavior of snapshot table changes in the join results.

Question

When the snapshot table (maintained by reflect_snapshot) changes, can these changes be reflected in the join results in real time? My understanding is that snapshot updates rely on periodic database scans (e.g., every few seconds or minutes), which may introduce latency in propagating changes to the join results. Could you confirm the following:

1, Are snapshot table change detection and updates limited by the scanning interval? If so, are there configurations or optimizations to make changes propagate closer to real time?

2, In a join between a snapshot table and a CDC streaming table, how does the snapshot change latency affect the join result updates? Are updates always incremental?

3, For scenarios requiring near-real-time updates, is it recommended to use CDC (e.g., Debezium) exclusively instead of snapshot tables?

[KamilPiechowiak]

Hey @gaozuo.
Could you post your questions in a separate issue?