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

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.