MIME-Version: 1.0
References: <CAEzWdqcYGi0U5_cK1FVykx2-OZHmEUD8EZ_VE=kpoVaZKYWJeg@mail.gmail.com>
 <a9dd7961-dd3e-406e-a1d8-0feb233fa5e9@aklaver.com> <CAEzWdqfGN5cHN4cwSJm-rruab4E0y_9tqzihR2jQGpMXHR7cqw@mail.gmail.com>
 <b43139d3-8f8e-4635-a7cb-cab90e5205eb@aklaver.com> <CAEzWdqfts=HUoh7at7yD0M7DW97BqSe1o+4xqtXOyM_+ZX_XMA@mail.gmail.com>
 <a1abe41c-94d8-4292-899f-ea2f256d76ae@aklaver.com> <CAEzWdqdXvqYJJ0Pbb+uLKHMAEbvLb86kKW_GJ9DDrh=5MU+_GA@mail.gmail.com>
 <f4853f55-5b16-4e9c-bb59-682f2b0bdefc@aklaver.com>
In-Reply-To: <f4853f55-5b16-4e9c-bb59-682f2b0bdefc@aklaver.com>
From: veem v <veema0000@gmail.com>
Date: Sat, 6 Apr 2024 19:32:43 +0530
Message-ID: <CAB+=1TW0weW5XPkSdSjeY3nvmta-fxVEdwcMD1ySEhYz_fKs9Q@mail.gmail.com>
Subject: Re: Moving delta data faster
To: Adrian Klaver <adrian.klaver@aklaver.com>, Greg Sabino Mullane <htamfids@gmail.com>
Cc: yudhi s <learnerdatabase99@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005db88f06156e0785"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TW0weW5XPkSdSjeY3nvmta-fxVEdwcMD1ySEhYz_fKs9Q%40mail.gmail.com>
Precedence: bulk

--0000000000005db88f06156e0785
Content-Type: text/plain; charset="UTF-8"

On Fri, 5 Apr 2024 at 06:10, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

>
> >     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.'
> >
> >
> > The data from S3 will be dumped into the stage table and then the
> > upsert/merge from that table to the actual table.
>
> The S3 --> staging table would be helped by having the data as CSV and
> then using COPY. The staging --> final table step could be done as
> either ON CONFLICT or MERGE, you would need to test in your situation to
> verify which works better.
>

Just a thought , in case the delta record changes are really higher(say
>30-40% of the total number of rows in the table) can OP also evaluate the
"truncate target table +load target table" strategy here considering
DDL/Trunc is transactional in postgres so can be done online without
impacting the ongoing read queries and also performance wise, it would be
faster as compared to the traditional Update/Insert/Upsert/Merge?

--0000000000005db88f06156e0785
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Fri, 5 Apr 2024 at 06:10, Adrian Klave=
r &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.co=
m</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
><br>
&gt;=C2=A0 =C2=A0 =C2=A0S3 is not a database. You will need to be more spec=
ific about &#39;... then<br>
&gt;=C2=A0 =C2=A0 =C2=A0from the S3 it will be picked and gets merged to th=
e target postgres<br>
&gt;=C2=A0 =C2=A0 =C2=A0database.&#39;<br>
&gt; <br>
&gt; <br>
&gt; The data from S3 will be dumped into the stage table and then the <br>
&gt; upsert/merge from that table to the actual table.<br>
<br>
The S3 --&gt; staging table would be helped by having the data as CSV and <=
br>
then using COPY. The staging --&gt; final table step could be done as <br>
either ON CONFLICT or MERGE, you would need to test in your situation to <b=
r>
verify which works better.<br></blockquote><div>=C2=A0</div>Just a thought =
, in case the delta record changes are really higher(say &gt;30-40% of the =
total number of rows in the table) can OP also evaluate the &quot;truncate =
target table +load target table&quot; strategy here considering DDL/Trunc i=
s transactional in postgres so can be done online without impacting the ong=
oing read queries and also performance wise, it would be faster as compared=
 to the traditional=C2=A0Update/Insert/Upsert/Merge?<br class=3D"gmail-Appl=
e-interchange-newline"><div>=C2=A0</div></div></div>

--0000000000005db88f06156e0785--