as a developer, I have book table in Postgres with bookId, title, description. and have book_tracking have bookId, viewCount.
Now I want to get viewCount from clickhouse, then mix it with title, description then provide it in a materialized table in postgres. How can I do that?
example:
| POSTGRES TABLE | CLICKHOUSE TABLE | EXPECT POSTGRES TABLE |
|---|---|---|
| books | books_tracking | mv_books |
| bookId | bookId | bookId |
| title | view_count | title |
| description | description | |
| view_count (from clickhouse) |
the first solution is: create Foreign Data Wrappers (FDW) (look like not stable, hard to setup)
second solution is: create a table, update it manually by cronjob. truncate it then fill it with data from clickhouse every 5 minute (I'm using this way) is there any better solution?