Subject: Re: Moving delta data faster

From: Adrian Klaver <[email-masked]>
Date: Wed, 3 Apr 2024 21:46:56 -0700

On 4/3/24 20:54, yudhi s wrote:
> On Thu, Apr 4, 2024 at 2:41 AM Adrian Klaver <[email-masked] 
> <mailto:[email-masked]>> 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
>     <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.

My motivation was to get some basic information about your setup and 
what you are trying to achieve.

If I understand correctly you have:

1) An Oracle database with tables that you want to copy the complete 
data from to a Postgres database. For this sort of thing 
COPY(https://www.postgresql.org/docs/current/sql-copy.html) on the 
Postgres end using CSV data generated from the source is probably the 
quickest bulk load method.

2) After the initial load you want to do follow up INSERT/UPDATEs based 
on a delta of the source tables relative to the initial load. This is 
still a bit of mystery to me. How are determining the delta: a) On the 
source end entirely or b) Target relative to source? Also what is the 
anticipated size of the delta per transfer?

Additional information needed:

1) Network distance between source and target?

2) Network capacity?

3) Expected load on both source and target servers from other operations?

-- 
Adrian Klaver
[email-masked]

---

From: yudhi s <[email-masked]>
Date: Thu, 4 Apr 2024 10:54:52 +0530

> 3) Expected load on both source and target servers from other operations?
Thank you. Actually I was trying to understand how to cater the delta load
after the one time load is done . The delta change in records is planned to
be found based on the primary keys on the tables. If it found the key it
will update the records if it does not find the keys it will insert the
rows.

Basically the select query from the source database will fetch the data
with a certain time interval(based on the latest update timestamp or create
timestamp if they are available or else full dump) and put it on S3 and
then from the S3 it will be picked and gets merged to the target postgres
database. As upsert and merge both were looking similar , so was wondering
what we should use here for loading the delta records?

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 4 Apr 2024 08:34:49 -0700

On 4/3/24 22:24, yudhi s wrote:
> 
> On Thu, Apr 4, 2024 at 10:16 AM Adrian Klaver <[email-masked] 
>      > On Thu, Apr 4, 2024 at 2:41 AM Adrian Klaver
>     <[email-masked] <mailto:[email-masked]>
>      > <mailto:[email-masked]
>     <mailto:[email-masked]>>> wrote:
>      >
>      >    > Thank you Adrian.
>      >
>     COPY(https://www.postgresql.org/docs/current/sql-copy.html
>     <https://www.postgresql.org/docs/current/sql-copy.html>) on the
> Thank you. Actually I was trying to understand how to cater the delta 
> load after the one time load is done . The delta change in records is 
> planned to be found based on the primary keys on the tables. If it found 
> the key it will update the records if it does not find the keys it will 
> insert the rows.
> 
> with a certain time interval(based on the latest update timestamp or 
> create timestamp if they are available or else full dump) and put it on 
> S3 and then from the S3 it will be picked and gets merged to the target 
> postgres database. As upsert and merge both were looking similar , so 
> was wondering what we should use here for loading the delta records?

S3 is not a database. You will need to be more specific about '... then 
from the S3 it will be picked and gets merged to the target postgres 
database.'

---

From: yudhi s <[email-masked]>
Date: Fri, 5 Apr 2024 02:12:10 +0530

> from the S3 it will be picked and gets merged to the target postgres
The data from S3 will be dumped into the stage table and then the
upsert/merge from that table to the actual table.

---

From: Greg Sabino Mullane <[email-masked]>
Date: Thu, 4 Apr 2024 17:08:28 -0400

So you want advice from this open source community about moving things from
a proprietary database to another proprietary database, going through a
proprietary storage system along the way? :)

To answer the original question, use MERGE. That's it's job, and your table
size is very tiny, so I doubt the performance impact will be worth worrying
about.

Cheers,
Greg