To: "John A Meinel" <john@arbash-meinel.com>,
	=?iso-8859-15?Q?Veikko_M=E4kinen?= <veikko.makinen@ecom.fi>
Cc: pgsql-performance@postgresql.org
Subject: Re: How does the transaction buffer work?
References: <42B1D2DE.4020709@ecom.fi> <42B1D4A7.60407@arbash-meinel.com>
Message-ID: <op.sshbrxivth1vuj@localhost>
Date: Thu, 16 Jun 2005 22:12:59 +0200
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <42B1D4A7.60407@arbash-meinel.com>
User-Agent: Opera M2(BETA2)/8.0 (Linux, build 987)


>> transaction, delete everything and then just dump new data in (copy
>> perhaps). The old data would be usable to other transactions until I
>> commit my insert. This would be the fastest way, but how much memory
>> would this use? Will this cause performance issues on a heavily loaded
>> server with too little memory even to begin with :)

	Well.

	If you DELETE everything in your table and then COPY in new rows, it will  
be fast, old rows will still be visible until the COMMIT. I hope you  
haven't anything referencing this table with ON DELETE CASCADE on it, or  
else you might delete more stuff than you think.

	Also you have to consider locking.

	You could TRUNCATE the table instead of deleting, but then anyone trying  
to SELECT from it will block until the updater transaction is finished.

	If you DELETE you could also vacuum afterwards.

	You could also COPY your rows to a temporary table and use a Joined  
Update to update your table in place. This might well be the more elegant  
solution, and the only one if the updated table has foreign key references  
pointing to it.