On 4/3/24 13:38, yudhi s wrote: > Hi All, > It's postgresql database version 15.4. We have a requirement in which > we will be initially moving full table data for 3-4 tables, from source > database to target(i.e. postgres) . Maximum number of rows will be > ~10million rows in those tables. Then subsequently these rows will be > inserted/updated based on the delta number of rows that got > inserted/updated in the source database. In some cases these changed > data can flow multiple times per day to the downstream i.e. postgres > database and in other cases once daily.
And one thing i forgot to mention this target postgresql database would be on AWS RDS whereas the source Oracle databases is on premise. I think we don't have the FDW extension currently in place but we can get that. I am just not able to understand clearly though, but do you mean export the data from source using CSV and do truncate and import on target. And as these data will be moved through the network won't that cause slowness?
The source database here is Oracle database. Correct me if wrong, it looks like foreign data wrapper is like a DB link. Or do you mean writing a query on the target database (which can be UPSERT or MERGE) but will be joining the table from the source database through the DBlink/DDW? But my question was whether we should use UPSERT or MERGE for comparing and loading the delta records to the target postgresql database. Want to understand which is more performant , as I see in the past Merge having performance issues in the past, but not very sure about that.