On Wed, Nov 28, 2012 at 8:28 PM, Jeff Davis <pgsql@j-davis.com> wrote: > > The main problem with a long-running delete or update transaction is > that the dead tuples (deleted tuples or the old version of an updated > tuple) can't be removed until the transaction finishes. That can cause > temporary "bloat", but 1.5M records shouldn't be noticeable.
Not really that fast if you have indices (and who doesn't have a PK or two).
I've never been able to update (update) 2M rows in one transaction in reasonable times (read: less than several hours) without dropping indices. Doing it in batches is way faster if you can't drop the indices, and if you can leverage HOT updates.
What I'm trying at this point is:
BEGIN;
DROP INDEX -- only one unique index exists
DELETE FROM table;
COPY table FROM STDIN;
COMMIT;
CREATE INDEX CONCURRENTLY;
Do I understand correctly that DROP/CREATE index are not transactional, and thus the index will disappear immediately for other transactions? Am I better off in that case moving the DROP INDEX outside the transaction?
The access pattern for the table is such that I can afford the occasional stray hit without an index during the reload time. It's been pretty quick using the above.
