Bruno Wolff III wrote:
On Fri, Sep 23, 2005 at 12:51:09 +0200,
Yonatan Ben-Nes <da@xxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
