On Wed, Nov 27, 2013 at 9:00 AM, Joey Quinn <bjquinniii@gmail.com> wrote:
> On Wed, Nov 27, 2013 at 9:50 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> For very large updates on mostly static data it may be better to
>> SELECT the data into a new table then swap it in when done. MY rule
>> of thumb is that updates are 10x more expensive than inserts,
>> particularly in terms of large operations.
>>
> In this case, I'm updating one column. Wouldn't the "swap" part of that
> still have to be an update?
nope. the basic mechanism is to:
BEGIN;
CREATE TABLE scratch (LIKE foo INCLUDING ALL);
INSERT INTO scratch SELECT ... FROM foo ...;
ALTER TABLE foo RENAME TO backup;
ALTER TABLE scratch RENAME TO foo;
COMMIT;
The main pain point is that you will have to recreate and table
dependent structures: views, triggers, etc. this is generally trivial
if you properly keep your schema definitions in scripts and a big
headache otherwise.
You will probably try to avoid updates to 'foo' while the swap is
happening to keep things simple.
merlin