Re: Moving delta data faster

On Thu, Apr 4, 2024 at 2:41 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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.
>
> What is the source database?
>
> Can it be reached with a FDW?:
>
> https://wiki.postgresql.org/wiki/Foreign_data_wrappers
>
> Can the delta on the source be output as CSV?
>
>

Thank you Adrian.

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.