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