On Wed, Nov 28, 2012 at 9:48 PM, Claudio Freire <klaussfreire@xxxxxxxxx> wrote:
On Wed, Nov 28, 2012 at 8:28 PM, Jeff Davis <pgsql@xxxxxxxxxxx> wrote:Not really that fast if you have indices (and who doesn't have a PK or two).
>
> 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.
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.
Mike
