From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Joey Quinn <bjquinniii(at)gmail(dot)com> |
Cc: | Rémi Cura <remi(dot)cura(at)gmail(dot)com>, "Raymond O'Donnell" <rod(at)iol(dot)ie>, PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: tracking scripts... |
Date: | 2013-11-27 15:13:57 |
Message-ID: | CAHyXU0yGzUBFNw1ir-9MVkPCLPqWB4ckyF2ZwRk2tj_pMn=0FA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Nov 27, 2013 at 9:00 AM, Joey Quinn <bjquinniii(at)gmail(dot)com> wrote:
> On Wed, Nov 27, 2013 at 9:50 AM, Merlin Moncure <mmoncure(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David Rysdam | 2013-11-27 15:56:59 | nested query vs left join: query planner very confused |
Previous Message | Albe Laurenz | 2013-11-27 15:12:47 | Re: Documentation of C functions |