Re: How many insert + update should one transaction handle?

Bruno Wolff III wrote:
> On Fri, Sep 23, 2005 at 12:51:09 +0200,
> Yonatan Ben-Nes <da(at)canaan(dot)co(dot)il> wrote:
>
>>Hi all,
>>
>>Every few days I need to DELETE all of the content of few tables and
>>INSERT new data in them.
>>The amount of new data is about 5 million rows and each row get about 3
>>queries (INSERT + UPDATE).
>>Now because I need the old data to be displayed till all of the new data
>>will be available I'm doing all of the process of deleting the old
>>content and inserting the new one in one transaction.
>>Should I divide the insertion so ill insert the new data into a
>>temporary table and the transaction should be commited every
>>100,1000,10000 whatever queries? or maybe it doesnt matter to the server
>>whats the size of the transaction and its ok to handle such a process in
>>one transaction?
>
>
> Doesn't breaking this into multiple transactions defeat your stated intent
> of keeping the old data visible until all of the need data is loaded?
> You should be able to do this all in one transaction. If this is a one shot
> deal you might want to use vacuum full or cluster to reclaim disk space.
> If it is something you regularly, then a plain vacuum with adequate FSM
> setting should be adequate. You will also want to do an analyze which you
> can piggyback on the vacuum.

Well if ill use multiple transactions then ill insert them into a
temporary table as mentioned and only when all of the process will end
ill replace the old data with the new data.
But actually it doesnt really matter anyway if its ok to do one big
transaction.

Thanks Bruno,
Yonatan Ben-Nes