public inbox for [email protected]  

help / color / mirror / Atom feed

Re: Moving delta data faster
2+ messages / 2 participants
[nested] [flat]

* Re: Moving delta data faster
@ 2024-04-05 00:39 Adrian Klaver <[email protected]>
  2024-04-06 14:02 ` Re: Moving delta data faster veem v <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Adrian Klaver @ 2024-04-05 00:39 UTC (permalink / raw)
  To: yudhi s <[email protected]>; +Cc: pgsql-general <[email protected]>

On 4/4/24 13:42, yudhi s wrote:
> 
> On Thu, Apr 4, 2024 at 9:04 PM Adrian Klaver <[email protected] 
> <mailto:[email protected]>> wrote:
> 
>     On 4/3/24 22:24, yudhi s wrote:
>      >
>      > On Thu, Apr 4, 2024 at 10:16 AM Adrian Klaver
>     <[email protected] <mailto:[email protected]>
>      > <mailto:[email protected]
>     <mailto:[email protected]>>> 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.

-- 
Adrian Klaver
[email protected]







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Moving delta data faster
  2024-04-05 00:39 Re: Moving delta data faster Adrian Klaver <[email protected]>
@ 2024-04-06 14:02 ` veem v <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: veem v @ 2024-04-06 14:02 UTC (permalink / raw)
  To: Adrian Klaver <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: yudhi s <[email protected]>; pgsql-general <[email protected]>

On Fri, 5 Apr 2024 at 06:10, Adrian Klaver <[email protected]>
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?


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-04-06 14:02 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-05 00:39 Re: Moving delta data faster Adrian Klaver <[email protected]>
2024-04-06 14:02 ` veem v <[email protected]>

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