From: PFC <lists@boutiquenumerique.com>
To: John A Meinel <john@arbash-meinel.com>
To: Veikko Mäkinen <veikko.makinen@ecom.fi>
Cc: pgsql-performance@postgresql.org
Subject: Re: How does the transaction buffer work?
Date: Thu, 16 Jun 2005 22:12:59 +0200
Message-ID: <op.sshbrxivth1vuj@localhost> (raw)
In-Reply-To: <42B1D4A7.60407@arbash-meinel.com>
References: <42B1D2DE.4020709@ecom.fi>
<42B1D4A7.60407@arbash-meinel.com>
>> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@boutiquenumerique.com, john@arbash-meinel.com, veikko.makinen@ecom.fi
Subject: Re: How does the transaction buffer work?
In-Reply-To: <op.sshbrxivth1vuj@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox