On Wed, Nov 27, 2013 at 9:00 AM, Joey Quinn <bjquinn...@gmail.com> wrote: > On Wed, Nov 27, 2013 at 9:50 AM, Merlin Moncure <mmonc...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
