Feedback-ID: i76984098:Fastmail
Message-ID: <c92d3f9b-0468-4a87-b974-24cc232036c2@aklaver.com>
Date: Sat, 6 Apr 2024 09:55:47 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Moving delta data faster
To: yudhi s <learnerdatabase99@gmail.com>, veem v <veema0000@gmail.com>,
 Greg Sabino Mullane <htamfids@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
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>
 <CAB+=1TW0weW5XPkSdSjeY3nvmta-fxVEdwcMD1ySEhYz_fKs9Q@mail.gmail.com>
 <CAEzWdqfww7aUkE+xpXXBM9eTkif1NxE_nGxeHsYPv+8-FY4pmQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEzWdqfww7aUkE+xpXXBM9eTkif1NxE_nGxeHsYPv+8-FY4pmQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/c92d3f9b-0468-4a87-b974-24cc232036c2%40aklaver.com>
Precedence: bulk

On 4/6/24 08:47, yudhi s wrote:
> Thank you Adrian, Greg and Veem.
> 
> I tried writing a small routine to see how the performance differs in 
> these four approaches i.e. Upsert VS traditional update+insert VS Merge 
> vs Truncate+load.
> 
> Initially I was thinking Upsert will perform the same as Merge as the 
> logic looks similar but it seems it's the worst performing among all, 
> not sure why , yet to know the reason though. Truncate+ load seems to be 
> the best performing among all. Hope i am doing it correctly. Please 
> correct me if I'm wrong.

Your original problem description was:

"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."

If the above is not a hard rule, then yes up to some point just 
replacing the data in mass would be the simplest/fastest method. You 
could cut a step out by doing something like TRUNCATE target_tab and 
then COPY target_tab FROM 'source.csv' bypassing the INSERT INTO source_tab.


-- 
Adrian Klaver
adrian.klaver@aklaver.com