public inbox for [email protected]  

help / color / mirror / Atom feed

From: yudhi s <[email protected]>
To: Adrian Klaver <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Moving delta data faster
Date: Thu, 4 Apr 2024 09:24:05 +0530
Message-ID: <CAEzWdqfGN5cHN4cwSJm-rruab4E0y_9tqzihR2jQGpMXHR7cqw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAEzWdqcYGi0U5_cK1FVykx2-OZHmEUD8EZ_VE=kpoVaZKYWJeg@mail.gmail.com>
	<[email protected]>

On Thu, Apr 4, 2024 at 2:41 AM Adrian Klaver <[email protected]>
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.

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Moving delta data faster
  In-Reply-To: <CAEzWdqfGN5cHN4cwSJm-rruab4E0y_9tqzihR2jQGpMXHR7cqw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox